Verifica Datafiles

=====================================================
# Para ver a localização dos arquivos da Tablespace #
=====================================================

select substr(file_name,1,50), MAXBYTES from dba_data_files where tablespace_name='SYSTEM';

==============================
#     Select Próprio...      #
==============================

select TABLESPACE_NAME, sum(bytes/1024/1024) Tamanho, sum(maxbytes/1024/1024) tamanho_maximo from dba_data_files group by TABLESPACE_NAME;


=====================================================
#     Criação de Datafiles para uma Tablespace      #
=====================================================

SQL> alter tablespace <TABLESPACE> add datafile '<CAMINHO>' size 500m autoextend on next 100m maxsize 3000m;


=====================================================
#     Criação de tempfiles para uma Tablespace      #
=====================================================


alter TABLESPACE temp add tempfile '/oraprd04/oradata/orcl/temp02.dbf' size 3000m;

=====================================================
#    alteração de Datafiles para uma Tablespace     #
=====================================================

alter database datafile '<Caminho>' autoextend on next 200M maxsize 3000m;


=========================================================
#    alteração de tempfiles para uma Tablespace TEMP    #
=========================================================

alter database tempfile '<Caminho>' autoextend on next 200M maxsize 3000m;


=====================================================
#      resize de Datafiles para uma Tablespace      #
=====================================================

alter DATABASE datafile '<caminho>' RESIZE 800K;


=======================================================
#     alteração de Datafiles para uma Tablespace      #
=======================================================

alter database rename file '<CAMINHO ANTIGO>' TO '<CAMINHO NOVO>';


==============================

set lines 140
set pages 100
column "Tablespace" format A20
column "Usado" format '999,990.00'
column "Livre" format '999,990.00'
column "Expansivel" format A12
column "Total" format '999,990.00'
column "Usado %" format '990.00'
column "Livre %" format '990.00'
column "Tipo Ger." format A12

select t.tablespace_name "Tablespace",
round(ar.usado,2) "Usado",
round(decode(NVL2(cresc.tablespace,0,sign(ar.Expansivel)),1,(ar.livre+ar.expansivel),ar.livre),2) "Livre",
NVL2(cresc.limite,'ILIMITADO',round(ar.expansivel,2)) "Expansivel",
round(decode(NVL2(cresc.tablespace,0,sign(ar.Expansivel)),1,ar.usado/(ar.total+ar.expansivel),(ar.usado/ar.total))*100,2) "Usado %",
round(decode(NVL2(cresc.tablespace,0,sign(ar.Expansivel)),1,(ar.livre+ar.expansivel)/(ar.total+ar.expansivel),(ar.livre/ar.total))*100,2) "Livre %",
round(decode(NVL2(cresc.tablespace,0,sign(ar.Expansivel)),1,(ar.total+ar.expansivel),ar.total),2) "Total",
t.Contents "Conteudo",
t.Extent_Management "Tipo Ger."
from dba_tablespaces t,
(
select df.tablespace_name tablespace,
(sum(df.bytes)-sum(NVL(df_fs.bytes,0)))/1024/1024 Usado,
sum(NVL(df_fs.bytes,0))/1024/1024 Livre,
sum(decode(df.autoextensible,'YES',decode(sign(df.maxbytes-df.bytes),1,df.maxbytes-df.bytes,0),0))/1024/1024 Expansivel,
sum(df.bytes)/1024/1024 Total
from dba_data_files df,
        (select tablespace_name, file_id, sum(bytes) bytes from dba_free_space
         group by tablespace_name, file_id) df_fs
where df.tablespace_name = df_fs.tablespace_name (+)
and df.file_id = df_fs.file_id (+)
group by df.tablespace_name
union
select tf.tablespace_name tablespace,
sum(tf_fs.bytes_used)/1024/1024 Usado,
sum(tf_fs.bytes_free)/1024/1024 Livre,
sum(decode(tf.autoextensible,'YES',decode(sign(tf.maxbytes-tf.bytes),1,tf.maxbytes-tf.bytes,0),0))/1024/1024 Expansivel,
sum(tf.bytes)/1024/1024 Total
from dba_temp_files tf, V$TEMP_SPACE_HEADER  tf_fs
where tf.tablespace_name = tf_fs.tablespace_name
and tf.file_id = tf_fs.file_id
group by tf.tablespace_name
) ar,
(
select df.tablespace_name tablespace,
'ILIMITADO' limite
from dba_data_files df
where df.maxbytes/1024/1024/1024 > 30
and df.autoextensible = 'YES'
group by df.tablespace_name
union
select tf.tablespace_name tablespace,
'ILIMITADO' limite
from dba_temp_files tf
where tf.maxbytes/1024/1024/1024 > 30
and tf.autoextensible = 'YES'
group by tf.tablespace_name
) cresc
where cresc.tablespace (+) = t.tablespace_name
and ar.tablespace (+) = t.tablespace_name
/


set pages 2000
set lines 2000;
col FILE_NAME for a60;
col TABLESPACE_NAME for a20;
col STATUS for a10;
select
    a.FILE_ID,
    b.BIGFILE,
    a.FILE_NAME,
    a.TABLESPACE_NAME,
    a.STATUS,
    (BYTES/(1024)/1024) as "ESPACO EM MB",
    (MAXBYTES/(1024)/1024) as "MAXBYTES"
from
    dba_data_files a,
    dba_tablespaces b
where
    a.tablespace_name = b.tablespace_name order by 1, 4, 3;


   
   
select a.FILE_ID, b.BIGFILE, a.FILE_NAME, a.TABLESPACE_NAME, a.STATUS, (BYTES/(1024)/1024) as "ESPACO EM MB", (MAXBYTES/(1024)/1024) as "MAXBYTES" from dba_data_files a, dba_tablespaces b where a.tablespace_name = b.tablespace_name order by 1, 4, 3;


set pages 2000
set lines 2000;
col FILE_NAME for a60;
col TABLESPACE_NAME for a20;
col STATUS for a10;
select
    a.FILE_ID,
    b.BIGFILE,
    a.FILE_NAME,
    a.TABLESPACE_NAME,
    a.STATUS,
    (BYTES/(1024)/1024) as "ESPACO EM MB",
    (MAXBYTES/(1024)/1024) as "MAXBYTES"
from
    dba_temp_files a,
    dba_tablespaces b
where
    a.tablespace_name = b.tablespace_name order by 1, 4, 3;