Show Children Hide Children

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

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

Edit | Attach | Watch | Print version | History: r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r1 - 2008-09-01 - GavinMcCance
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback