select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
          ceil(HWM * a.block_size)/1024/1024 ResizeTo,
          (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
          'alter database datafile '''||a.name||''' resize '||
          ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
   from v$datafile a,
        (select file_id,max(block_id+blocks-1) HWM
          from dba_extents
          group by file_id) b
  where a.file# = b.file_id(+)
   and (a.bytes - HWM *block_size)>0;

select substr(a.tablespace_name,1,20) tablespace,
round(sum(a.total1)/1024/1024, 1) Total,
round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1)
used,
round(sum(a.sum1)/1024/1024, 1) free,
round(round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1),1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(a.cnt) fragments
from
(select tablespace_name, 0 total1, sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0
from dba_data_files
group by tablespace_name
) a
group by a.tablespace_name order by FREE DESC;

官网:
Doc ID 130866.1http://www.cndba.cn/hbhe0316/article/121037

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle