FTS table defragmentation - CERN specific procedure

Other site, please look at FtsRelease20TableFragmentation.

This is the CERN-specific procedure.

Impact

The procedure may be run online. It slows the DB a little.

Procedure

2- make a variable defining the table you want to defragment (here done with Benthic) define TNAME=WEEKLY_USER_STATS;

3- check if there are function based indexes and get the pretty recreation DDL. Save the output of the following script!!

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
select 'DROP INDEX '||index_name||'; ' DROP_CLAUSE, trim(translate(dbms_metadata.get_ddl('INDEX',a.index_name),chr(10)||'"',' '))||';' CREATE_CLAUSE from user_ind_expressions a where table_name='&TNAME';

4- run the DROP_CLAUSE of the previous script (if any)

5- Defragment the table with the two following commands:
alter table &TNAME enable row movement;
alter table &TNAME shrink space;

6- run the CREATE_CLAUSE of the script on step 3 (if any)

7- Gather new table statistics
exec dbms_stats.gather_table_stats(NULL,'&TNAME');

-- GavinMcCance - 01 Sep 2008