Table Re-organization And Index Re-Build

• As a Oracle Database Administrator before doing table Re-org activity ensure export backup for tables
• During table Re-org archives are getting generated frequently
• Keep monitor archive mount point during the reorganization activity
• Create temporary tablespace for Re-org activity

1. Take export backup dump for the table

EXP file=TB_TXN_HISTORY-BEFORE-REORG.dmp log=TB_TXN_HISTORY-BEFORE-REORG.log tables=MY_APP.TB_TXN_HISTORY

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------  ----------

MYORADB   READ WRITE

2. Check and list out the no. of invalid objects at database level and schema level

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

  --------

         7

SQL> select count(*) from dba_objects where status='INVALID' and owner='MY_APP';

  COUNT(*)

  --------

         4

3. Check the no. of rows available in table

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS from dba_tables where OWNER='MY_APP' and TABLE_NAME IN('TB_TXN_HISTORY');

OWNER   TABLE_NAME          TABLESPACE_NAME              NUM_ROWS

------  --------------      ---------------              --------

MY_APP  TB_TXN_HISTORY      TS_MY_APP_DAT                25983023

4. Check the owner,tablespace,size and segment type

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME, BYTES/1024/1024 from dba_segments where owner='MY_APP' and SEGMENT_NAME In('TB_TXN_HISTORY');

OWNER  SEGMENT_NAME    SEGMENT_TYPE  TABLESPACE_NAME BYTES/1024/1024

------ --------------  ------------  --------------- ---------------

MY_APP TB_TXN_HISTORY  TABLE         TS_MY_APP_DAT   8748

5. Check indexes related to table and Indexes Size

SQL> select OWNER,INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS,DEGREE,BLEVEL from dba_indexes where owner='MY_APP' and table_name='TB_TXN_HISTORY';

OWNER    INDEX_NAME           TABLE_NAME      TABLESPACE_NAME  STATUS   DEGREE  BLEVEL

------   -------------------  --------------  ---------------  ------   ------  ------

MY_APP   IX_TB_TXN_HISTORY01  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

MY_APP   IX_TB_TXN_HISTORY02  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

MY_APP   IX_TB_TXN_HISTORY03  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       2

MY_APP   IX_TB_TXN_HISTORY04  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

MY_APP   IX_TB_TXN_HISTORY05  TB_TXN_HISTORY  TS_MY_APP_IDX    VALID    1       3

SQL>  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,

BYTES/1024/1024 from dba_segments where owner='MY_APP' and segment_name in('IX_TB_TXN_HISTORY01', 'IX_TB_TXN_HISTORY02','IX_TB_TXN_HISTORY03','IX_TB_TXN_HISTORY04', 'IX_TB_TXN_HISTORY05');

OWNER  SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME           BYTES/1024/1024

------ --------------------      ------------       ---------------           ---------------

MY_APP IX_TB_TXN_HISTORY01       INDEX              TS_MY_APP_IDX             2304

MY_APP IX_TB_TXN_HISTORY02       INDEX              TS_MY_APP_IDX             2624

MY_APP IX_TB_TXN_HISTORY03       INDEX              TS_MY_APP_IDX             1008

MY_APP IX_TB_TXN_HISTORY04       INDEX              TS_MY_APP_IDX             2295

MY_APP IX_TB_TXN_HISTORY05       INDEX              TS_MY_APP_IDX             3264

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024

from dba_segments where segment_name in('TB_TXN_HISTORY');

OWNER    SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME     BYTES/1024/1024

------   -------------             ------------       ---------------     ---------------

MY_APP   TB_TXN_HISTORY            TABLE              TS_MY_APP_DAT       8748

6.1 we can re-org the table in same tablespace if sufficient free space available in same tablespace.

 SQL>Alter table MY_APP.TB_TXN_PALLETIZING move tablespace TS_MY_APP_DAT;

6.2 We can re-org the table in another tablespace if sufficient free space is not available

 6.2.1 Create New temporary tablespace to re-org the table

 SQL> Create tablespace TEMP_REORG datafile '/ILAPP/DATA1/TEMP_REOG01.dbf' size 10240M autoextend off;

 6.2.2 Reorg table to newly created tablespace

 SQL> Alter table MY_APP.TB_TXN_PALLETIZING move tablespace TEMP_REORG;

 6.2.3 Revert back table to original tablespace after releasing fregmentation

 SQL>Alter table MY_APP.TB_TXN_PALLETIZING move tablespace TS_MY_APP_DAT;

7. Rebulid all indexes related to table

SQL> alter index MY_APP.IX_TB_TXN_HISTORY01 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY02 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY03 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY04 rebuild tablespace TS_MY_APP_IDX online;

SQL> alter index MY_APP.IX_TB_TXN_HISTORY05 rebuild tablespace TS_MY_APP_IDX online;

Note : Stabdard Edition doesn't support online rebuild

8. Check indexes status and degree

SQL> select OWNER,INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS,DEGREE,BLEVEL from dba_indexes where table_name='TB_TXN_HISTORY' and owner='MY_APP';

OWNER    INDEX_NAME           TABLE_NAME           TABLESPACE_NAME   STATUS  DEGREE    BLEVEL

------   -------------------  --------------       ---------------   ------  ------    ------

MY_APP   IX_TB_TXN_HISTORY01  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

MY_APP   IX_TB_TXN_HISTORY02  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

MY_APP   IX_TB_TXN_HISTORY03  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         2

MY_APP   IX_TB_TXN_HISTORY04  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

MY_APP   IX_TB_TXN_HISTORY05  TB_TXN_HISTORY       TS_MY_APP_IDX     VALID   1         3

9. Cross check segments size after re-org

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where owner='MY_APP' and SEGMENT_NAME In('TB_TXN_HISTORY');

OWNER    SEGMENT_NAME      SEGMENT_TYPE    TABLESPACE_NAME           BYTES/1024/1024

------   --------------    ------------    ---------------           ---------------
MY_APP   TB_TXN_HISTORY    TABLE           TS_MY_APP_DAT             5120

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS from dba_tables where OWNER='MY_APP' and TABLE_NAME IN('TB_TXN_HISTORY');

OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS

------                         --------------                 ---------------                  --------

MY_APP                         TB_TXN_HISTORY                 TS_MY_APP_DAT                    49863007

SQL> select OWNER,INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,STATUS,DEGREE,BLEVEL from dba_indexes where owner='MY_APP' and table_name in('TB_TXN_HISTORY');

OWNER  INDEX_NAME           TABLE_NAME          TABLESPACE_NAME STATUS   DEGREE  BLEVEL

------ -------------------  ----------          --------------- ------   ------  ------ 

MY_APP IX_TB_TXN_HISTORY01  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

MY_APP IX_TB_TXN_HISTORY02  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       2

MY_APP IX_TB_TXN_HISTORY03  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

MY_APP IX_TB_TXN_HISTORY04  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

MY_APP IX_TB_TXN_HISTORY05  TB_TXN_HISTORY      TS_MY_APP_IDX   VALID    1       3

SQL>  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where owner='MY_APP' and segment_name in('PK_TB_TXN_HISTORY','PK_TB_TXN_LOTTRANSACTION02','IX_TB_TXN_LOTTRANSACTION03','IX_TB_TXN_LOTTRANSACTION04','IX_TB_TXN_LOTTRANSACTION05','IX_TB_TXN_PALLETIZING01','PK_TB_TXN_PALLETIZING02','IX_TB_TXN_PALLETIZING03','PK_TB_TXN_PALLETIZING03','IX_TB_TXN_HISTORY01','IX_TB_TXN_HISTORY03','IX_TB_TXN_HISTORY05','IX_TB_TXN_HISTORY06','IX_TB_TXN_HISTORY02','IX_TB_TXN_LOTTRANSACTION01','PK_TB_TXN_PALLETIZING04');

OWNER  SEGMENT_NAME                        SEGMENT_TYPE       TABLESPACE_NAME BYTES/1024/1024

------ ----------------------------------- ------------------ --------------- ---------------

MY_APP IX_TB_TXN_HISTORY01                 INDEX              TS_MY_APP_IDX             1024

MY_APP IX_TB_TXN_HISTORY02                 INDEX              TS_MY_APP_IDX             1800

MY_APP IX_TB_TXN_HISTORY03                 INDEX              TS_MY_APP_IDX             750

MY_APP IX_TB_TXN_HISTORY04                 INDEX              TS_MY_APP_IDX             1098

MY_APP IX_TB_TXN_HISTORY05                 INDEX              TS_MY_APP_IDX             2004

10 Finally cross check all invalid objects at db level and schema level

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

  --------

         7

SQL> select count(*) from dba_objects where status='INVALID' and owner='MY_APP';

  COUNT(*)

  --------

         4

Note:  As per oracle recomend perform gather stats

exec DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME =>'MY_APP', TABNAME =>'TB_TXN_HISTORY', ESTIMATE_PERCENT => 50, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 4, GRANULARITY => 'ALL', CASCADE =>TRUE, NO_INVALIDATE =>FALSE);

######################################################  All The Best ######################################################