Sql Query to Find and Reclaim the Fragmented Space in Oracle Table

advertisements

_____________________________________________________________________________________________________________________

How the table hits by space fragmentation?

If a table has large number of records and the particular table is getting updated or the rows getting deleted periodically then there will be unused blank spaces (holes) in the table segments. These blank spaces will get created by the row deletions which will not be used without reorg or reclaiming. Over a period of time the volume of the unused space will get accumulated to a huge size. These unused spaces are called fragmentations. The oracle will not automatically release this space into usable free space whereas we have to perform the reorg activity to claim the fragmented space.
The delete or insert DMLs will not reset the High Water Mark (HWM).

What is HWM?

HWM is the pointer for marking used blocks in the database. Oracle assumes that the blocks below the high water mark is occupied or used even though there is lot of fragmentations. The blocks above the HWM is get used always by Oracle. So over a period of time HWM level goes up because of the new data inserts. But, nothing will get changed with the table records deletes or inserts as it won’t reset the HWM automatically.

What is the impact of Fragmentation in table?

Unnecessarily space will get blocked by the tables even though it has free space. The query performance for these tables will get degraded because of unwanted block scanning. There will be lot of chained rows because of lack of space in the individual blocks. This is also hit the performance

Query to find out the table fragmentation-

Total reclaimable space / Fragmentation = Total space occupied (sum of bytes from DBA_SEGMENTS) - actual data volume(Num_rows*avg_row_length - DBA_tables)

To update the table statistics like num_rows & avg_row_length you have run the analyze on the table using following commands.

exec dbms_stats.gather_table_stats(ownname => 'SCHEMA NAME', tabname => 'TABLE NAME', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade =>
TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);

OR

ANALYZE TABLE COMPUTE STATISTICS;

Query

-----
col owner for a20
col segment_name for a20
col SizeMBS for 999999.99
col wastedMBS for 999999.99
select      ds.owner,
      ds.segment_name,
      round(ds.bytes/1024/1024,0) SIzeMBS,
      round((ds.bytes-(dt.num_rows*dt.avg_row_len) )/1024/1024,0) WASTEDMBS
from dba_segments ds, dba_tables dt
where       ds.owner=dt.owner
and   ds.segment_name = dt.table_name
and   ds.segment_type='TABLE'
and   ds.segment_name='&Table_name'
group by ds.owner, ds.segment_name, round(ds.bytes/1024/1024,0) ,round((ds.bytes-(dt.num_rows*dt.avg_row_len))/1024/1024,0)
having      round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;
 
How to reset the HWM and Reclaim the unused space?
Method 1: Table shrinking -
        - Syntax: alter table shrink space
        - Most recommended method
        - Applicable for the databases 10g and above
        - No downtime required
        - None of the depended objects will get invalidated.
        - Minimum time consuming method
 Shrink & Reclaim Space Example
Method 2: Conventional table move to different tablespace and bring it back
        - It is time consuming method
        - Application down time required as the table will not be available during the time of activity
        - There are chances for the depened objects invalidation. It has to be properly verified after reorg
        - Indexes also needs to be rebuild which requires an extra time apart from the table movement
        - This activity requires extra space on other tablespace to move the table.
alter table < table name > enable row movement;
alter table < table name > move tablespace < new tablespace >;
alter table < table name > move tablespace < actual tablespace >;
alter tabel < table name > disable row movement;
 
Rebuild all the invalid Index
 
select status,index_name from dba_indexes where table_name = 'TABLE NAME';
ALTER INDEX REBUILD ONLINE;

Rebuild all the depended objects

Run the following query to find out the status of depended objects. There are chances to get invalidated the depended objects.

SELECT OBJECT_NAME , STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME IN (select name from dba_dependencies where REFERENCED_NAME='TABLE NAME');

Reclaim Space With Table Movement

Method 3: Export the table, Drop table and import the table

        - It is time consuming method
        - Application down time required as the table will not be available during the time of activity
        - There are chances for the depended objects invalidation. It has to be properly verified after reorg
        - Indexes also needs to be rebuild as all the indexes will get deleted as part of the table drop
        - There are lots of risks involved in terms of primary key foreign key relationships etc.
        - Not an easy or recommended task for huge production tables.

_____________________________________________________________________________________________________________________

Website Stats