1 查看表空间使用情况
select a.tablespace_name,a.bytes/1024/1024 “Sum MB”,(a.bytes-b.bytes)/1024/1024 “used MB”,b.bytes/1024/1024 “free MB”,
round(((a.bytes-b.bytes)/a.bytes)*100,2) “percent_used”
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc

2 查看可回收datafile
select ‘alter database datafile ‘’’||a.file_name||’’’ resize ’ ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||‘M;’,
a.filesize,c.hwmsize
from ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a,
( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.file_id = c.file_id and a.filesize - c.hwmsize > 100

-查询DBF文件、数据库空间、高水位线占用情况
select /*+ ordered use_hash(a,b,c) */
a.file_id,
a.file_name,
a.tablespace_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 tablespace_name,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
order by unsedsize_belowhwm desc

TRUNCATE后可释放表数据库空间，表默认把TRUNCATE数据前的空间大小作为初始空间大小，表的高水位线不会降低，需要Shring收缩表的大小后方可降低表的水位线