Main FTS Pages |
---|
FtsRelease22 |
Install |
Configuration |
Administration |
Procedures |
Operations |
Development |
Previous FTSes |
FtsRelease21 |
FtsRelease21 |
All FTS Pages |
FtsWikiPages |
Last Page Update |
GavinMcCance 2008-09-01 |
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