Reorganization of table in Oracle Database

Hi All,


As we all know reorganization help in better disk space management and performance management in oracle Database,so this task is often done in companies for Tables,Tablespaces and Databases.It helps to reduce the fragmentation( which affects the performance of the Oracle).

Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:


-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990


--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304


Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16


ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12


Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


If db is up:
=================

select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


INDEXES NEEDS TO BE REBUILDED:
=================================

--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;



Step 3:Make sure to make it noparallel and  logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.



Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off


Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.



SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
APPS                           ORDER_TAB_NU17      UNUSABLE
ORD_SCHEMA                            ORDER_TAB_F2        UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU3   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N10           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N11           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N12           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U1            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N6            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U2            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU1   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N9            UNUSABLE

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA                           XHL_ORDER_TAB_NU4   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N1            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU2   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N7            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N8            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N2            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N3            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N4            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N5            UNUSABLE
APPS                           XHL_ORDER_TAB_NU5   UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_R1    UNUSABLE

22 rows selected.


To check unusable indexes for table:
==========================================

SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>




Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';





Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');


Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out



sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/


Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352




Hope it helps.Enjoy DBA tasks...



Thanks,

Rafi