Defragmentation and Reclaimation of space from the table

Defragmentation and Reclaimation of space from the table:
----------------------------------------------------------------------

1. Find the TABLESPACE associate with the tables 
2. Check the space Availability and total space of <Table space> and TEMP 
3. Add space to <Table space> if the rwquired space is not available. 
4. Bounce the DB to clear any deadloack and session block 
5. Start Reorg 
5.1 Perform One by one ( Simultaneosly perform may decrease performance) if multiple tables. 

ALTER TABLE <table name> MOVE; 

6. Findout the Session ID for table move. 
7. Check how much time reorg will going to complete (Below query gives you estimated time to complete) 

select sid,SOFAR,TOTALWORK,to_char(START_TIME,'dd-mm-yy hh24:mi:ss') "Start",to_char(LAST_UPDATE_TIME,'dd-mm-yy hh24:mi:ss') "LastUpdate",TIME_REMAINING 
from v$session_longops where sid like '<Seesion ID>'; 

8. Once table reorg complete check for Indexes status associated with that TABLE 
select owner,index_name,table_name from dba_indexes where status='UNUSABLE' and table_name='<table_name>'; 

9. If indexes are UNUSABLE status, please rebuild the indexes 
select 'alter index '||owner||'.'||index_name||' rebuild online parallel 12; ' from dba_indexes where status='UNUSABLE' and table_name='<table_name>'; 

10. Once rebuild done, check the status of indexes 

11. Compute Gather stat for reorganized table.