Reorganization Activity for table to released unused space oracle
Due to lot of insert, update & delete operation table is fragmented. To remove the fragmentation at some extent we have following the steps. Released unused space from segments table and index. Following steps let help to analysed and removed fragmentation from the table level.
Remove fragmentation from tables in Oracle
1. Check the size of tables.
Select table_name, round((blocks*8)/1024,2)||'MB' "size" from user_tables where table_name = 'EMP';
Select table_name, round((blocks*8)/1024,2)||'MB' "size" from dba_tables where table_name = 'TRAN' and owner='SCOTT';
2. Check the actual size of tables.
select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables where table_name = 'EMP';
select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from dba_tables where table_name = 'TRAN' and owner='SCOTT';
3. Enable the row movement for the table which need to shrink.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing. ROWIDs are normally assigned to a row for the life time of the row at insert time.
ALTER TABLE emp ENABLE ROW MOVEMENT;
4. Now start Shrink Space
We have three Statements for Shrink the table:
-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;
-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;
-- Recover space for the object and all dependent objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;
This statement will proceed in two steps:
The first step makes the segment compact by moving rows further down to free blocks at the beginning of the segment.The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.
Table shrinking advantage:
-will adjust the high watermark
-Can be done online
-Will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
-Indexes will be maintained and remain usable
-Can be made in one go
-Can be made in two steps
5. Disable the row movement.
ALTER TABLE emp DISABLE ROW MOVEMENT;
Script to shrink table objects by Owner.
for i in (SELECT obj.owner
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable
FROM dba_tables obj,
(SELECT table_name, COUNT(rownum) cnt
WHERE index_type LIKE 'FUN%'
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND obj.Owner = upper('&1') and NVL(idx.cnt,0) < 1)
execute immediate 'alter table '||i.owner||'.'||i.table_name||' enable row movement';
execute immediate 'alter table '||i.owner||'.'||i.table_name||' shrink space';
execute immediate 'alter table '||i.owner||'.'||i.table_name||' disable row movement';
Note :This can be useful if you cannot get a full table lock during certain hours. you only make the first step and adjust the high watermark later when it is more convenient
The following restrictions apply to table shrinking:
1. It is only possible in table spaces with ASSM.
2. You cannot shrink:
– UNDO segments
– temporary segments
– clustered tables
– tables with a column of datatype LONG
– LOB indexes
– IOT mapping tables and IOT overflow segments
– tables with MVIEWS with ON COMMIT
– tables with MVIEWS which are based on ROWIDs