Database Defragmentation

Purpose of the BLOG:

Usually in OLTP environment, tables are often get fragmented due to many DML activities on the database. When the objects are fragmented, queries on those tables will automatically get slow due to scanning many blocks which has no data on it, here we trying to explain you how to De-fragment and to reclaim the fragmented space from objects to tablespace and from tablespace to database and finally from database to OS Storage.

Proposed Solutions:

Step 1:

Minimize tablespace usage on the database level, so you have free space in the database but OS files remains as is with no savings. (Can be achieve using Online shrink or Offline Move)

Step 2:

Minimize tablespace usage on the OS level, so you minimize database space and then reclaim space on the OS level.
To achieve that, one preferably should first use either of the above methods, and then use a special procedure to make the free space above the datafile high water mark. Finally, reclaim the space on the OS level by issuing “alter database datafile resize”.

1.a) Online Segment Shrink

Online Segment Shrink will ‘gather’ all rows in a table into as few extents as possible and then empty extents can be freed, if an extent contains one or more rows then that extent cannot be freed.

Also it reclaims fragmented free space below the high water mark.

Before invoking online segment shrink, we need to view the findings and recommendations of the Segment Advisor, and invoke the segment shrink for the segments which are eligible.

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM) and Row movement need to be enabled.

Commands:

• alter table enable row movement;
• alter table shrink space compact;
• alter table shrink space;
• alter table shrink space cascade;
• alter table disable row movement;

1.b) Offline Move

Alter table …. Move is an Offline activity, which does not permit DML against the table while the statement is executing

MOVE statement enables you to relocate data of a nonnonpartitionedble or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota.

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error.

The indexes on the table must be dropped or rebuilt.

Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table

If the table includes LOB column(s), move the table along with LOB data and LOB index segments (associated with this table)

Commands:

• Move to Same Tablespace:
  ALTER TABLE MOVE;
  ALTER INDEX REBUILD;
• Move to different Tablespace:
  ALTER TABLE MOVE TABLESPACE ;
  ALTER INDEX REBUILD TABLESPACE ;
• LOB Objects:
  alter table move lob (column name) store as (tablespace );

2. Datafile resize:

The Primary Goal is to minimizing database space and then reclaim space on the OS level can be achieved only when we complete 1.an or 1.b

Reducing the size of a datafile is more complicated than increasing the size of a datafile as space cannot be deallocated from a datafile that is currently being used by database objects

To reclaim space from a datafile, you have to have contiguous free space at the END of the data file, which means we can resize (decrease) up to it HWM (HIGH WATER MARK)

The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points to become a used block and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

The high-water mark is the level at which blocks have never been formatted to receive data.

You may receive the following error (while re-sizing, if there is data at data file’s end i.e. HWM):
ORA-03297: file contains used data beyond requested RESIZE value

Ensuring that the database’s RECYCLE_BIN does NOT contain any of the Dropped content(s) ORIGINALLY from the tablespace (to be de-fragmented).

2. A

If there is a difference in actual datafile size(high) and the HWM(low), then we can easily resize(decrease) the size of datafile to its HWM

2. B

If there is no difference in actual allocated size of datafile and the HWM but the actual objects size is less than the allocated size then we need to identified the object holding at the end of datafile which is HWM and move that object to different tablespace, so that the HWM will get reduced(low) when compared to actual size of the datafile(high) and allow us to resize(decrease) the size of the datafile to its HWM, the space reclaimed will be less when compared to next outcome and also it is a tedious process to identify all the objects which are holding the HWM at the end of datafile

2. C

For the same scenario, we can move all the objects present in the tablespace and move it to different tablespace, which will reset the HWM of all the datafile in the tablespace, now we are good to resize the Datafile to its MAX, The Reclaimable space is Significantly High

Commands

• ALTER DATABASE DATAFILE ” RESIZE ;

References:

• Optimizing Database disk space using Alter table shrink space/move compress (Doc ID 1173241.1)
• Why no space is released after an ALTER TABLE … SHRINK? (Doc ID 820043.1)
• How to Resize a Data file (Doc ID 1029252.6)?
• How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table High watermark (Doc ID 130866.1)
• Shrinking Database Segments Online – http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#CBBBIADA
• Moving a Table to a New Segment or Tablespace – http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables006.htm

Posted on behalf of Arunkumar. S | 4iapps.com