Table Reorganize

Table Reorganize:


Table Reorg:

Reorganization is very useful and important tasks DBAs perform in order to reduce space used by blocks and it also helps in improving the performance of the Oracle Database.It also helps to reduce the fragmentation.

• ANALYZE TABLE will update the index statistics for the table.
• OPTIMIZE TABLE will actually reorganize the data inside the data file.

 There are 3 ways to do:

1.Export/Import
2.Alter table Move
3.CTAS method(Create table table_name2 as Select *from table_name1)

Step 1: check the last Analyzed and number of rows.

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

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS 13-MAR-15 NO 117766


step 2: Find the table Size.

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS where segment_name='FND_CONCURRENT_REQUESTS';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
--------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS 129.75


Step 3:

Get the list of all the Indexes associated with the table used for reorganization.

SELECT OWNER||' '||table_name||' '||INDEX_NAME||' '||INDEX_TYPE||' '||STATUS FROM USER_INDEXES WHERE TABLE_NAME='<XXXXXXXXX>';

or
SQL> SELECT OWNER||' '||table_name||' '||INDEX_NAME||' '||INDEX_TYPE||' '||STATUS FROM DBA_INDEXES WHERE TABLE_NAME='FND_CONCURRENT_REQUESTS';

OWNER||''||TABLE_NAME||''||INDEX_NAME||''||INDEX_TYPE||''||STATUS
--------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N8 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N7 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N6 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N4 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N1 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N9 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N5 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N3 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N11 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N10 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_U1 NORMAL VALID

OWNER||''||TABLE_NAME||''||INDEX_NAME||''||INDEX_TYPE||''||STATUS
--------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N2 NORMAL VALID

12 rows selected.

SQL>



Step 4:

Move the table:

 SQL> alter table applsys.FND_CONCURRENT_REQUESTS move;


Step 5: Rebuild Indexes

SQL> ALTER INDEX < index_name> rebuild online;

or

SQL> ALTER INDEX < index_name> rebuild


Step 6: Check the status of idexes. if any index status is in UNUSABLE, rebuild those indexes.

SQL> SELECT OWNER||' '||table_name||' '||INDEX_NAME||' '||INDEX_TYPE||' '||STATUS FROM DBA_INDEXES WHERE TABLE_NAME='FND_CONCURRENT_REQUESTS';

OWNER||''||TABLE_NAME||''||INDEX_NAME||''||INDEX_TYPE||''||STATUS
--------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N8 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N7 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N6 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N4 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N1 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N9 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N5 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N3 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N11 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N10 NORMAL VALID
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_U1 NORMAL VALID

OWNER||''||TABLE_NAME||''||INDEX_NAME||''||INDEX_TYPE||''||STATUS
--------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS FND_CONCURRENT_REQUESTS_N2 NORMAL VALID

12 rows selected.

SQL>



Step 7: Run the Gather stats for the table.

exec dbms_stats.gather_table_stats(ownname=>'user_name',tabname=>'table_name',cascade=>true,method_opt=>'for all columns size skewonly');

Instead, you can also go ahead and collect the schema statistics as well.

exec dbms_stats.gather_schema_stats(ownname=>'user_name', cascade=>true,method_opt=>'for all columns size skewonly');


SQL> execute dbms_stats.gather_table_stats('APPLSYS','FND_CONCURRENT_REQUESTS',cascade=>true,method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.


Step 8: verify the statistics, table size after reorganization.



========================================
To reorganize tables:-

SQL> alter table <name> move;
or
SQL> alter table <name> enable row movement;
SQL> alter table <name> shrink space;

To reorganize indexes:-

SQL> alter index <name> rebuild;


==============================