How to avoide Tablespace fragmentation

In previuos post we disscused how to find tablespace frgmantation.Here we will disscus how to avoide table fragmentation in oracle.

Once we identify tablespace fragmentation , what do you do about it?  Honeycomb fragmentation is easy to fix.  All that needs to be done is to combine adjacent free segments into one by issuing a coalesce  statement:

alter tablespace USERS coalesce;

 Bubble fragmentation  is more difficult to handle.  Of course, the best course of action is to prevent it in the first place.  The best weapon for this is to use locally-managed tablespaces.If you are using Oracle 8.1.6 or higher you can convert any current dictionary-managed  tablespaces to locally-managed tablespaces.

sys.dbms_space_admin .tablespace_migrate_to_local(‘USERS’)

If you are afraid of how long this procedure might take on large tablespaces, do not be.  It actually runs very fast.  If, for some reason, you would like to take a tablespace that is locally-managed back to dictionary management, you can issue this command:

sys.dbms_space_admin .tablespace_migrate_from_local(‘USERS’)

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

The possible causes for Excessive Undo generation

1)Transactions with huge undo.
——————————-
It is obvious to see high undo usage when there are huge transactions. If that is going to be the case this growth should be expected behavior.

2)The Higher value setting of UNDO_RETENTION.
————————————————
The higher value of UNDO_RETENTION will increase much undo. As it can’t be marked undo extents as EXPIRED till the duration of the UNDO_RETENTION.

3)Autoextensible mode of Undo data files.
————————————————–
Disabling the auto extensible mode of the datafiles of active undo tablespace will reuse the UNEXPIRED extents when undo tablespace suffer in space pressure.If they are set to auto extensible, it will not reuse the space for the new transaction. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.

4)Undo tablespace is using Autoallocate option:
———————————————-
UNDO is using auto allocate option of LMT. As the number of the extents goes up, the extent size will be increased too.When the number is extents reaches hundred the extent size will be bigger. If it reaches thousands it will be even more bigger.If you know the size of most of your transactions you can use UNIFORM rather than AUTO.

Read Full Post »