Reorganizing or Shrink table/index in Oracle 11gR2 DB
Posted by Mir Sayeed Hassan on November 28th, 2017
Reorganizing or Shrink table/index in Oracle 11gR2 DB
A Simple demonstration to perform shrinking the object in Oracle Database 11gR2
[oracle@testdb ~]$ !sq
SQL> conn mir/**** Connected.
SQL> create table shrink_test as select 'Oracle_shrink_method' as product, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id, 100 as amount_sold, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2017','dd.mm.yyyy') as time_id from dual connect by level<=1e6;
Table created.
SQL> select time_id ,count(*) from shrink_test group by time_id order by 1; TIME_ID COUNT(*) --------- ---------- 01-JAN-17 83333 01-FEB-17 83334 01-MAR-17 83334 01-APR-17 83334 01-MAY-17 83334 01-JUN-17 83333 01-JUL-17 83333 01-AUG-17 83333 01-SEP-17 83333 01-OCT-17 83333 01-NOV-17 83333 TIME_ID COUNT(*) --------- ---------- 01-DEC-17 83333
SQL> select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='SHRINK_TEST'; SEGMENT_NAME MB --------------------------------------------------------------------------------- ---------- SHRINK_TEST 49
The table contains about 83333 rows per month; try to delete the first quarter of rows:
SQL> delete from shrink_test where time_id<to_date('01.04.2017','dd.mm.yyyy');
250001 rows deleted.
SQL> commit; Commit complete.
SQL> select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='SHRINK_TEST'; SEGMENT_NAME MB --------------------------------------------------------------------------------- ---------- SALES_SHRINK 54
As you can see with above deleted query although 250k rows got deleted, hence the table consumes the same space as before. In other words: The High Water Mark did not move. A reorganization would move the High Water Mark & would regain the space that was consumed by the 250k rows
SQL> select time_id ,count(*) from shrink_test group by time_id order by 1; TIME_ID COUNT(*) --------- ---------- 01-APR-17 83334 01-MAY-17 83334 01-JUN-17 83333 01-JUL-17 83333 01-AUG-17 83333 01-SEP-17 83333 01-OCT-17 83333 01-NOV-17 83333 01-DEC-17 83333 9 rows selected.
Even if you perform the Insert or delete to the same table, does not affect the high water mark until you perform the shrink into the table, in production database – Try to take the backup of the table or index for safer side & then perform as per my suggestion.
Steps to perform Shrink
SQL> alter table shrink_test enable row movement; Table altered.
SQL> alter table shrink_test shrink space; Table altered.
SQL> select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='SHRINK_TEST'; SEGMENT_NAME MB --------------------------------------------------------------------------------- ---------- SHRINK_TEST 36.125
SQL> alter table shrink_test disable row movement; Table altered.