oracle回收表空间

1. 手工计算实际大小
col owner format a20
col table_name format a40
col "act/block %" format 9999999
select owner,
       table_name,
       tablespace_name,
       act_size,
       block_size,
       block_size - act_size,
       round(act_size / block_size * 100, 3) "act/block %",
       last_analyzed
  from (select owner,
               table_name,
               dt.TABLESPACE_NAME,
               round((blocks * 8 / 1024), 3) block_size,
               round((avg_row_len * num_rows / 1024 / 1024), 3) act_size,
               dt.LAST_ANALYZED
          from dba_tables dt
         where owner not in ('SYS','SYSTEM')
          AND OWNER = 'dd'
          -- and dt.TABLE_NAME = 'dd'
           AND blocks > 0
           )
 where block_size > 1000
 and round(act_size / block_size, 3) * 100 < 100
 order by 6 desc;

2. 查看表分配空间大小
SELECT OWNER,SEGMENT_NAME,round(SUM(BYTES/1024/1024/1024),2) FROM DBA_SEGMENTS WHERE OWNER = 'dd' GROUP BY OWNER,SEGMENT_NAME order by 3 desc ;

select DISTINCT DTP.TABLE_OWNER,DTP.TABLE_NAME,DTP.TABLESPACE_NAME from dba_tab_partitions dtp where dtp.table_owner = 'dd';

3.收集统计细腻些
exec dbms_stats.gather_table_stats(ownname=>'dd',tabname=> 'dd');
kubec
--收缩表
alter table dd.dd enable row movement;
alter table dd.dd shrink space compact;
alter table dd.dd shrink space;
alter table dd.dd disable row movement;
--收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'dd',tabname=> 'dd');