查询占据高水位的对象并Shrink释放空间

1. 查询占据高水位的对象
select owner,table_name,object_type, count(1) num_obj
from (
select e.owner, nvl(nvl(t.table_name,l.table_name),i.table_name) table_name,
       case
         when t.table_name is not null then 'TABLE'
         when l.table_name is not null then 'LOB'
         when i.table_name is not null then 'INDEX'
       else
         null
       end  object_type
from   dba_extents e,
       dba_tables  t,
       dba_lobs    l,
       dba_indexes i
where e.owner = t.owner(+)
and   e.segment_name = t.table_name(+)
and   e.owner = l.owner(+)
and   e.segment_name = l.segment_name(+)
and   e.owner = i.owner(+)
and   e.segment_name = i.index_name(+)
and   e.block_id > 1966080
and   e.file_id in (data_file_id_list of the tablespace)
)
group by owner, table_name, object_type
;

上述SQL根据提供的数据文件ID查询占用BLOCK大于15GB位置的对象。

2. 收缩表和Lob字段
收缩表
alter table table_name enable row movement;
alter table table_name shrink space compact;
alter table table_name shrink space;

收缩Lob字段
alter table table_name modify lob (lob_column_name) (shrink space cascade);

3. 查询每个数据文件可回收的空间
SELECT a.tablespace_name,
       a.file_id,
       a.file_name,
       CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024)  smallest,
       CEIL(blocks * blksize / 1024 / 1024)         currsize,
       CEIL(blocks * blksize / 1024 / 1024) -
       CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024)  savings
       --,'alter database datafile ''' || file_name || ''' resize ' ||
       --CEIL((NVL(hwm, 1) * blksize) / 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,
       (SELECT TO_NUMBER(value) blksize
          FROM V$PARAMETER
         WHERE name = 'db_block_size')
 WHERE a.file_id = b.file_id(+)
   AND CEIL(blocks * blksize / 1024 / 1024) -
       CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) > 0
   AND TABLESPACE_NAME = 'USERS'
 ORDER BY 1, 2 desc;