How to Reorg a Table in oracle

1. Take the count of invalid objects.
------------------------------

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

2.Note existing sizes of tables and status of indexes on the tables.
--------------------------------------------------------------------
select owner,segment_name,bytes/1024/1024 from dba_segments where segment_type='TABLE' and segment_name in
('TEST1','TEST2','TEST3','TEST4','TEST5');

3. Note the Indexes for those tables
------------------------------------
select TABLE_NAME,index_name,status,owner from dba_indexes where table_name IN ('TEST1','TEST2','TEST3','TEST4','TEST5') order by table_name;

output=>
TABLE_NAME INDEX_NAME STATUS OWNER
---------- --------- ------ --------
TEST1 TEST1_INDX VALID TESTUSER
TEST2 TEST2_PK VALID TESTUSER
TEST3 TEST3_INDX VALID TESTUSER
TEST4 TEST4_IDX VALID TESTUSER
TEST5 TEST5_IDX VALID TESTUSER

4.Create stat table and take export of Schame states
----------------------------------------------------
(i) - exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'TESTUSER', STATTAB => 'STATS_TABLE' , TBLSPACE => 'TESTING');

(ii) - exec dbms_stats.export_table_stats(OWNNAME => 'TESTUSER', TABNAME=> '',PARTNAME => NULL, STATTAB=> 'STATS_TABLE',STATID=> NULL, CASCADE=>TRUE); ----- (Run the same script for all 5 tables by changing the table name)

5.Reorg the tables using -
--------------------------
Alter table move; ----(Run for 5 tables)

6.Rebuild the indexes -
-----------------------
alter index TESTUSER. rebuild online; -- (Run for the above 5 indexes)

7.Check invalid objects.
-------------------------

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

8. run the utlrp script.
------------------------
@$ORACLE_HOME/rdbms/admin/utlrp.sql

9.Import schema stats
---------------------
exec dbms_stats.import_table_stats(OWNNAME => 'TESTUSER', TABNAME=> '', stattab => 'STATS_TABLE'); ------(Run the same script for all 5 tables by changing the table name)

10.Note the sizes of the tables after reorg -
---------------------------------------------
select owner,segment_name,bytes/1024/1024 from dba_segments where segment_type='TABLE' and segment_name in
('TEST1','TEST2','TEST3','TEST4','TEST5');