1. alter tablespace INDEX offline immediate
--IMMEDIATE keyword instructs Oracle to take the tablespace offline with attempting to flush any dirty buffers from the tablespace to disk.
2. Prepare the DDL from dictionary for recreation, before drop the damaged tablespace
2.1 Either by check the dicionary : dba_ind_columns
2.2 Or dbms_metadata.get_ddl
2.3 Or use database pump
a) expdp hr/hr directory=dp_dir dumpfile=ind.dmp include=index;
b) then export to a text file
impdp hr/hr directory=dp_dir dumpfile=ind.dmp sqlfile=ind.sql
3. Drop the tablespace and delete its datafiles
--drop tablespace
4. Create a new tablespace
5. Re-create the indexes. To speed up creation, using nologging and parallelism.
