今天主要简单介绍一下怎么去回收表空间大小，下面一起了解一下~这块忘记放到ORACLE dba实战脚本总结里面了，大家有空也可以放到一起..
select /*+ ordered use_hash(a,b,c) */
a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
from (select file_id, file_name, round(bytes / 1024 / 1024) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id, round(max(block_id) * 8 / 1024) HWMsize
from dba_extents
group by file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
and a.file_name like '%lob3%'
order by unsedsize_belowhwm desc
说明：
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
where file_id in (select b.file#
From v$tablespace a, v$datafile b
where a.ts# = b.ts#
and a.name = 'LOB3')
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
order by 5
alter database datafile '+DATA/otmdb/datafile/lob307.dbf' resize 10866M;
alter database datafile '+DATA/otmdb/datafile/lob305.dbf' resize 10871M;
alter database datafile '+DATA/otmdb/datafile/lob306.dbf' resize 10871M;
alter database datafile '+DATA/otmdb/datafile/lob308.dbf' resize 10861M;
alter database datafile '+DATA/otmdb/datafile/lob303.dbf' resize 18531M;
alter database datafile '+DATA/otmdb/datafile/lob304.dbf' resize 15266M;
alter database datafile '+DATA/otmdb/datafile/lob301.dbf' resize 11371M;
1、查看表空间大小
2、查看ASM磁盘空间大小
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
关注微信
