Hi guys, today i will show how to reorganize fragmented data and reclaim the unused space using Data-Pump Utility.
Assume that you received the call from particular Department Users.. For example we will take a "Hospital Pharmacy Department" The pharmacy users are not able to order the medicine from the pharmacy table.
As a DBA following actions you have to take....
1. First take the table-name of Pharmacy module from the Apps Team.
2. Then check the health of a database like datafile size,table size,Index status etc ...
3. If every thing is fine, then contact your developer, if they did any modifications in the table. Specially Deletions and Updations.
Note:- Because when rows are deleted or updated in the table Oracle will not Reset the High water mark Which leads to Fragmentation, unnecessary oracle scan all the empty blocks also when query is executed which will degrade the performance.
High Water Mark : Is a pointer where oracle stops writing in the blocks and put a mark where it ends. OR we can say its an Barrier between Used and Unused Blocks.
Example :-
Assume that you have 5 lac rows in your table which has accommodated in 1000 blocks, when you are executing SELECT query its taking 20 minutes to give the output, and If you deleted 4 lac rows from this table. and run again same SELECT query it will take same 20 minutes to read again. Here oracle read 1000 blocks again This is because of HWM is not reset by oracle after deletion.
Reorganizing Steps:-
So first we have to check how much data got fragmented in that particular Table.
SQL> set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;
Enter value for owner: HMIS
Enter value for table_name: PMPMCSTK
---------- ---------------------------------------------------------------------------------------------------
HMIS PMPMCSTK 640 382 639
>> Now we start Re-Organizing the table in production and Import it back on Test database using expdp/impdp
Production Server
D:\exp_table> expdp dumpfile=PMPMCSTK.DMP directory=exp_table tables=HMIS.PMPMCSTK
> Announce the Down time if you are doing on production because you to have drop the table and import it back.
Target Test Server
C:\imp_table> impdp dumpfile=PMPMCSTK.DMP directory=imp_table tables=HMIS.PMPMCSTK
> Now table has re-organized check whether data is still fragmented....
SQL> set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;
Enter value for owner: HMIS
Enter value for table_name: PMPMCSTK
---------- ------------------------------ ---------- ----------------------------------------------------
HMIS PMPMCSTK 384 382 383
- You can observe here how High Water Mark has reset and reclaim the space from 639 to 383 and 640 to 384.
- Now gather table level stats to updating Optimizer for creating Fresh Execution plan as per newly reorganized table data..
SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'HMIS',
tabname =>'PMPMCSTK',
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
Advantages if we are using Expdp method for Reorganizing:-
1. Faster compared to other re-org options.
2. Extra space is not required if source table is dropped before import.
3. Easier to use when there are too many objects for reorganization.
Drawbacks:-
1. Table availability is affected. So, application downtime should be planned.
2. Any DML performed on the table between export completion and import start time will not be recorded.
Happy Learning...
