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;