Hello All,
I want to do some shrinkage on some of my tables that are reporting to have "wasted space"
Now, I know they key here is PCTFREE. If it's high on the table, it's more difficult to actually do the shrinkage.
Here my plan of action.
Step 1: Execute the following SQL to obtain the tables that need to be shrunk.
set lines 200;
column owner format a15;
column segment_name format a30;
select
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;
Step 2:
Apply these 4 alter commands to the tables the above SQL code has retrieved.
A:) ALTER TABLE <tablefromquery> ENABLE ROW MOVEMENT; ~ You have to do this on the table to oracle can actually move the other rows to other free "extents".
B:) ALTER TABLE <tablefromquery> SHRINK SPACE COMPACT; ~ This command shrinks the used space of the table but does NOT update the High Water Mark. This is useful is you don't want to have a row level lock on the table during business hours.
C:) ALTER TABLE <tablefromquery> SHRINK SPACE; ~ This command shrinks the contents of the table and updates the High Water Mark.
D:) ALTER TABLE <tablefromquery> SHRINK SPACE CASCADE; ~ You have to careful with this one because it shrinks the contents of the table and all dependent objects like
indexes...
So guys,
Since its business hours should not do C and D? and just do A and B?
Any advise on this would be appreciated.
ALSO, just a fact about using the "shrink space" command it does not invalidate triggers and indexes.
AND the MAJOR most important question is can theses commands cause ANY possible RISK performance at ALL???
Thanks,
Lady Allora.