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');