Oracle Segment advisor

Segment advisor

Oracle Table shrink


SQL> SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc;

Any Key...;

TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE Allocated, Mb Used, Mb Reclaimable, Mb
------------------------------ ------------------------------ ------------------ ------------- ---------- ---------------
RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERS GGS_DDL_HIST TABLE 264.95 251.95 12.99
Enable row movement of the table GGATE.GGS_DDL_HIST and perform shrink, estimated savings is 13625759 bytes.
alter table "GGATE"."GGS_DDL_HIST" shrink space
alter table "GGATE"."GGS_DDL_HIST" shrink space COMPACT
alter table "GGATE"."GGS_DDL_HIST" enable row movement



SQL> alter table "GGATE"."GGS_DDL_HIST" enable row movement;

Table altered.

SQL> alter table "GGATE"."GGS_DDL_HIST" shrink space COMPACT;

Table altered.

SQL> alter table "GGATE"."GGS_DDL_HIST" disable row movement ;

Table altered.



alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;





SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations ,
re.task_id, ta.execution_end
FROM TABLE(dbms_space.asa_recommendations()) re, dba_advisor_tasks ta
Where ta.task_id=re.task_id 2 3 4 5 6 7 8 9
10 /
Any Key...;

SEGMENT_NAME ALLOC_MB USED_MB RECLAIM_MB PCTSAVE
------------------------------ ---------- ---------- ---------- ----------
RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TASK_ID EXECUTION
---------- ---------
GGS_DDL_HIST 264.9 252 13 5
Enable row movement of the table GGATE.GGS_DDL_HIST and perform shrink, estimated savings is 13625759 bytes.
5442 16-OCT-11