Verifica Status Tablespace

spool D:\util\saving1.log;
set verify off;
set lines 1000;
set pages 300;
select sum(bytes)/1024/1024 as "TamanhoFisico(MB)" from dba_data_files;
col "FileSystem" format a12;
select substr(file_name,1,3) as "FileSystem", sum(bytes)/1024/1024 as "Tamanho(MB)" from dba_data_files group by rollup(substr(file_name,1,3)) order by substr(file_name,1,3);

select tbs.tablespace_name,tot.bytes/1024/1024 total_MB,tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 MB_used,sum(nvl(fre.bytes,0))/1024/1024 free_MB,(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,decode(greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 75),75, '', '*') pct_warn from dba_free_space fre, (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) tot, dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and fre.tablespace_name(+) = tbs.tablespace_name group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes order by 5, 1;

col "file_name" format a50;
select file_name, ceil( (nvl(hwm,1)*8192)/1024/1024 ) "SMALLEST-LIVRE", ceil( blocks*8192/1024/1024) "CURRSIZE-CORRENTE", ceil( blocks*8192/1024/1024) - ceil( (nvl(hwm,1)*8192)/1024/1024 ) "SAVINGS-SALVO", ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) smallest_safe, ceil( blocks*8192/1024/1024) - ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) savings_safe from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) <> 100 order by 4 desc;

select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) <> 100;

select sum(bytes)/1024/1024 as "TamanhoFisico(MB)" from dba_data_files;
col "FileSystem" format a12;
select substr(file_name,1,3) as "FileSystem", sum(bytes)/1024/1024 as "Tamanho(MB)" from dba_data_files group by rollup(substr(file_name,1,3)) order by substr(file_name,1,3);
spool off;