28 end;
29 end;
30 /
PL/SQL procedure successfully completed
task_id
---------
560
SQL> col task_name format a8
SQL> col segname format a8
SQL> col partition format a8
SQL> col type format a8
SQL> col message format a100
SQL> select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
2 from dba_advisor_findings af, dba_advisor_objects ao
3 where ao.task_id = af.task_id
4 and ao.object_id = af.object_id
5 and ao.owner = 'ROBINSON';
TASK_NAM SEGNAME PARTITIO TYPE MESSAGE
-------- -------- -------- -------- ----------------------------------------------------------------------------------------------------
test TEST TABLE Enable row movement of the table ROBINSON.TEST and perform shrink, estimated savings is 28451785 bytes.
下面是一个生产环境数据库，可以看到有很多表都需要shrink,我就不贴出具体的处理步骤了。
SQL> select a.owner || '.' || a.table_name "Table",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,
2 round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4) "Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"
3 from dba_tables a left join all_tab_modifications b
4 on a.owner=b.table_owner and a.table_name=b.table_name inner join dba_segments c on a.owner=c.owner and a.table_name=c.segment_name
5 where a.last_analyzed is not null and a.partitioned='NO' and b.deletes>100 and c.bytes/1024/1024>100 and a.owner not like '%SYS%'
6 and (a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0)))/c.bytes<0.5 order by b.deletes desc;
Table NUM_ROWS AVG_ROW_LEN INSERTS DELETES TOTAL_ROWS Calculate_Sizle(Mb) Segment_Size(Mb)
------------------------------ ---------- ----------- ---------- ---------- ---------- ------------------- ----------------
IDWSU14.OMCR4_SHIP_HIST_SRC_V 39370237 77 805667850 884712377 -39674290 -2913.3991 1088
IDWSU12.SH08_STJP7T_7001_VIEW 39761320 133 229568455 268620133 709642 90.0101 6164
IDWSU14.SH30_DP_SSFV_PROD_REPL 4871971 11 231242749 230333428 5781292 60.6482 132
C_2_AA
IDWSU14.SH30_DP_SSFV_PROD_REPL 4868627 11 231242749 230333428 5777948 60.6131 130.125
C_2_NEA
IDWSU14.SH30_DP_SSFV_PROD_REPL 4877610 11 231242749 230333428 5786931 60.7073 130.125
C_2_GC
IDWSU11.YMCR4_SHIP_HIST_SRC_V 22753157 76 203507302 226332317 -71858 -5.2082 688
IDWSU13.SH30_DP_SSFV_PROD_REPL 5638000 11 204891145 204449473 6079672 63.7783 136
C_2_WE
IDWSU13.SH30_V_CUST_SUBCTRY_67 4232552 35 165554103 167511636 2275019 75.937 452
7
IDWSU14.OMCR7_SHIP_HIST_SRC_V 40399302 77 123348208 163744322 3188 0.2341 1136
IDWSU14.SH30_V_CUST_SUBCTRY_67 4232198 35 156844906 156528506 4548598 151.8258 432
7