Oracle Performance Tuning: Tables Defragmentation using “Alter Database Move” Command

Tables in Oracle database become fragmented after mass deletion, or after so many delete and/or insert operations. If you are running a 24×7 shop, you don’t have an option to reorganize (or defragement) the table by traditional export/truncate/import method, i.e., exporting data from affected table, truncate the table, then importing data back to the table. Luckily, there is an “alter table table_name move” command that you can use to defragment tables. Follow these steps to make your customer happy. This method does not apply to tables with with ‘LONG’ columns.

1, Make a list of tables needed to be reorganized.

C:\>sqlplus /nolog

connect deltek/xxx@fin
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\chained_tables.sql;
select table_name, num_rows, chain_cnt from user_tables where chain_cnt >0;
spool off;
exit;

Output:

ASSET 7196 10
DFLT_REG_TS 14188 107
EMPL 14188 21
EMPL_LAB_INFO 51744 3
ESS_PAGE_TEXT 262 11
HB_CVG_OPT 353 2
HB_EMPL_PKG 5691 2
JNL_STATUS 11795 1
OPEN_AP 445934 698
PO_HDR_NOTES 10327 9
PO_LN_NOTES 7564 8
PROJ 51671 17
PROJ_BURD_SUM 5089903 74951
PROJ_EDIT 51671 4
PROJ_EMPL 1005025 2803
PROJ_EMPL_LAB_CAT 859823 1679
PROJ_LAB_CAT 384610 62
PROJ_SUM 3362926 922
PROJ_VEND_LAB_CAT 160866 6
SUB_PD_JNL_STATUS 27615 9
TS_HDR 170091 1070
TS_LN 353365 2
VEND_CHK 274322 1
Z_PRPPPAF_EMPL 972 5

2, Run this sql to create sql statements for defragmentation.

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\defrag.sql;
select 'alter table '||table_name||' move;'
from user_tables where chain_cnt >0;

spool off;

This will generate ouput:

alter table ASSET move;
alter table DFLT_REG_TS move;
alter table EMPL move;
alter table EMPL_LAB_INFO move;
alter table ESS_PAGE_TEXT move;
alter table HB_CVG_OPT move;
alter table HB_EMPL_PKG move;
alter table JNL_STATUS move;
alter table OPEN_AP move;
alter table PO_HDR_NOTES move;
alter table PO_LN_NOTES move;
alter table PROJ move;
alter table PROJ_BURD_SUM move;
alter table PROJ_EDIT move;
alter table PROJ_EMPL move;
alter table PROJ_EMPL_LAB_CAT move;
alter table PROJ_LAB_CAT move;
alter table PROJ_SUM move;
alter table PROJ_VEND_LAB_CAT move;
alter table SUB_PD_JNL_STATUS move;
alter table TS_HDR move;
alter table TS_LN move;
alter table VEND_CHK move;
alter table Z_PRPPPAF_EMPL move;

3, Generate SQL statements for rebuilding indexes after defragmentation

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\rebuild_index.sql;
select 'alter index '|| INDEX_NAME|| ' rebuild;' from user_indexes
where table_name in (select table_name from user_tables where chain_cnt >0);
spool off;

4, Generate SQL statements for compute statistics after defragmentation

set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\compute_stat.sql;
select 'analyze table '|| table_name|| ' compute statistics;' from user_tables where chain_cnt >0;
spool off;

5, Defragment these chained tables.

SQL>@C:\Oracle\job\dba\defrag.sql

6. Rebuild indexes because these tables’s indexes are in unstable state.

SQL>@C:\Oracle\job\dba\rebuild_index.sql

7. Compute statistics

SQL>@C:\Oracle\job\dba\compute_stat.sql

8. Find out if it works.

SQL>select table_name, num_rows, chain_cnt from user_tables where chain_cnt >0;

Tags:

March 27, 2007 at 6:28 pm

13 Comments »

  1. Asanka Prasad said,

    May 13, 2008 @ 6:21 pm

    This is grate. i got exactly what i want from here. thank you very much.

  2. Harshita J said,

    December 18, 2008 @ 8:01 pm

    thanks for such a detailed update !!

  3. Andrés said,

    January 15, 2009 @ 10:03 am

    Exelente… Justo lo que necesitaba.
    Mil gracias viejito.

  4. Ashish Sudhakarrao Surkar said,

    March 18, 2009 @ 11:28 pm

    Thank it help me.

  5. Steve Callan said,

    March 25, 2009 @ 4:19 pm

    And this gains you what when the table goes back to where it just was?

  6. Nihad said,

    June 16, 2009 @ 4:12 am

    so simple, and yet so effective. bravo.

  7. judzin said,

    July 2, 2009 @ 4:05 am

    What about partitioned tables and indexes???

  8. David Brown said,

    July 20, 2009 @ 5:35 pm

    How does this sequence of steps compare/contrast with ‘shrink’?
    i.e. alter table emp shrink space cascade;

    TIA

  9. Pedro said,

    August 4, 2009 @ 1:29 pm

    I need to execute Analyze table before use this script?

  10. Marco said,

    August 4, 2009 @ 7:08 pm

    During execution of “alter table move” script few tables have got problems.
    The message: ORA-00997: illegal use of LONG datatype

  11. shantanu singha said,

    August 12, 2009 @ 4:49 am

    Does the dependent objects(apart from indexes) get invalid by executing the “alter table table_name move” command ? Is there any way to prevent it or we need to recompile the invalid objects once more ?

  12. Pradeepa said,

    August 18, 2009 @ 3:44 am

    Can we defragment one table at a time?

  13. Sanjay said,

    September 11, 2011 @ 5:39 am

    Exellent solution for table Re-oraganization

    Thanks
    Sanjay Kumar

RSS feed for comments on this post · TrackBack URI

Leave a Comment