Thanks for the question, Paul.
Asked: January 19, 2023 - 10:07 am UTC
Last updated: June 07, 2023 - 6:25 am UTC
Version: 19c
Viewed 1000+ times
You Asked
Is there a SQL script I can use to determine if a table is a good candidate to be shrunk or moved to reclaim space. I'm not looking to use segment advisor, I want something in SQL.
My ultimate goal is to automate this task in PLSQL (without reinventing the wheel) by identifying tables, enabling/disabling row movement and
any foreign keys and report on the time taken and spaced reclaimed.
Below is a test CASE with a CLOB. But many of my tables will have one or more Foriegn keys.
create table t1(c1 clob) lob (c1) store as basicfile;
insert into t1 select lpad('X', 4000) from dual connect by level<=1000;
select bytes/1024/1024 from user_segments where segment_name =
(select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');
BYTES/1024/1024
9
alter table t1 enable row movement;
delete from t1 where rownum <= 900;
alter table t1 modify lob (c1) (shrink space);
select bytes/1024/1024 from user_segments where segment_name =
(select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');
BYTES/1024/1024
.9375
and Connor said...
I generally dont look at used vs free space to determine if a table is a candidate for some sort of space reclamation. For me, it is more about the *activity* you are expecting on a table.
Lets say a table has 50% reclaimable space... If that table is still getting regular inserts, then I probably don't care. That space will be re-used and re-filled by new data. Doing an operation to reclaim the space is a fruitless exercise.
Conversely, lets say a table has only 10% reclaimable space (that's a common one because pctfree defaults to 10). If that table will "never" have any more activity, ie, its reached it final state (or most of the data has), then I might go ahead and compress it/alter table move/ etc to make that table as compact as it possibly can be,
It wasnt the 10 or 50% that drove this ... it was the pattern of expected future activity.
Is this answer out of date? If it is, please let us know via a Comment