How to Check and Remove Fragmentation

How to Check and Remove Fragmentation

Check the Table Size
select table_name,bytes/(1024*1024*1024) from user_table where table_name=’table_name’;
Analyze and Gather Stats
exec dbms_stats.gather_table_stats(‘schema_name’,’table_name’);
Check Fragmentation
select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’ table_Name’ AND OWNER LIKE ‘schema_name’;
How to Remove Fragmentation
Method 1: Exp/Imp Table
Export and import the table:- (difficult to implement in production environment)
Method 2: Move Tables
Move tables between Tablespace or same Tablespace
select index_name,status from user_indexes where table_name like ‘table_name’;
alter table table_name enable row movement;
alter table table_name move tablespace new_tablespace_name;
Rebuild Index
select index_name from user_indexes where table_name like ‘table_name’;
Use this command for each index.
alter index index_name rebuild online;
Check Tablespace Size
select table_name,bytes/(1024*1024*1024) from user_table where table_name=’table_name’;
Analyze and Gather Stats
exec dbms_stats.gather_table_stats(‘schema_name’,’table_name’);
Method : 3 Shrink command (fron Oracle 10g):-
Shrink command:
————–
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.
This command is only applicable for tables which are tablespace with auto segment space management.
Before using this command, you should have row movement enabled.
SQL> alter table <table_name> enable row movement;
Table altered.
There are 2 ways of using this command.
1. Rearrange rows and reset the HWM:
———————————–
Part 1: Rearrange (All DML’s can happen during this time)
SQL> alter table <table_name> shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table <table_name> shrink space;
Table altered.
2. Directly reset the HWM:
————————-
SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.
Advantages over the conventional methods are:
——————————————–
1. Unlike “alter table move ..”,indexes are not in UNUSABLE state.After shrink command,indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.