HOW TO DO TABLE AND INDEX REORGANIZATION [ID 736563.1]

1). Alter table move (to another tablespace, or same tablespace).
alter table table_name move; This would do the table reorg.

2).
SQL> alter table table_name enable row movement;
Table altered.
There are 2 ways of using this command.
Method 1. Break in two parts: In first part rearrange rows and in second part reset the HWM.
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table table_name shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table table_name shrink space;
Table altered.
Method 2. All in one go:
SQL> alter table table_name shrink space; (Both rearrange and restting HWM happens in one statement)
The above mentioned methods are online reorg and would not cause any impact.
Oracle 10g describes the reorganization operations that are possible using the ONLINE clause in the
SQL> CREATE/ALTER INDEX and TABLE statements.
ALTER TABLE emp MOVE ONLINE;
CREATE INDEX emp.ename.idx ON emp(ename) ONLINE;
ALTER INDEX emp.ename_idx REBUILD ONLINE;
ALTER INDEX emp.ename_idx COALESCE;
Reclaiming Unused Space

==============    Truncate Table   ===================

TRUNCATE TABLE XXASL.XX_EMF_ERROR_DETAILS;
ALTER TABLE XXASL.XX_EMF_ERROR_DETAILS ENABLE ROW MOVEMENT;
ALTER TABLE XXASL.XX_EMF_ERROR_DETAILS SHRINK SPACE;
ALTER INDEX XXASL.IDX$$_2A2D0001 REBUILD;

=============  Indix is Valid / Not    ===================
SQL> select INDEX_NAME,STATUS from dba_indexes where INDEX_NAME='&indx_name' and owner='&Owner';
If  the status is UNUSABLE then fire the below comman then check the status will bechanged to  VALID

SQL> alter index owner.<indx_name> rebuild online;

............................................&&&..................................................