Corrupted INDEX Tablespace with PK,FK,UQ Objects

NOTE: My Scenario we are using Oracle "STANDARD E" no Any backup available in this case how to rebuild and re-create indexes ... Not in another Object (Tables/View/MV...etc)


SOME INDEX REBUID BELOW THIS COMMAND ELSE ANOTHER TYPE OF INDEX P,F AND U BELOW THE STEPS TO FOLLOW

 Analyze index DB_MASTER.PK_APPLICANT_DETAILS REBUILD TABLESPACE USERS;



------  P ,U and F key Reference Curropted the block -------.     

SQL> alter index db_master.UK_TDBQD rebuild tablespace USERS;
alter index db_master.UK_TDBQD rebuild tablespace USERS
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 20, block # 685)
ORA-01110: data file 20: '/u03/datafs/userindex01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


1. Flush Te Buffer
alter system flush buffer_cache;

2.Verify Block and object and object Type.
SELECT segment_name , segment_type , owner , tablespace_name FROM dba_extents WHERE file_id = 20 AND 321 BETWEEN block_id and block_id + blocks -1;


3.Verify index else constrains belongs to which table and information about index and constraints.

SELECT owner,CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME FROM ALL_CONSTRAINTS WHERE index_name='PK_APPLICANT_DETAILS';
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME ='TDB_APPLICANT_DETAILS';



4.Information give regarding dependent key foreign key object
----REFERENCE----
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints  where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='TDB_APPLICANT_DETAILS');



5.DDL generate regrading the Constraints and Reference Foreign key.
select dbms_metadata.get_ddl('CONSTRAINT','PK_APPLICANT_DETAILS','DB_MASTER') from dual;

---Reference Key table
select dbms_metadata.get_ddl('TABLE','TDB_APPLICANT_DETAILS_HISTORY','DB_MASTER') from dual;


6.Save the DDL  ,Repeate  this point after 7. point else drop the Key.
 ALTER TABLE "DB_MASTER"."TDB_APPLICANT_DETAILS" ADD CONSTRAINT "PK_APPLICANT_DETAILS" PRIMARY KEY ("INWARD_NO") USING INDEX TABLESPACE USERS ENABLE;

alter table db_master.TDB_APPLICANT_DETAILS_HISTORY  add CONSTRAINT "FK1_TDBADH" FOREIGN KEY ("INWARD_NO")  REFERENCES "DB_MASTER"."TDB_APPLICANT_DETAILS" ("INWARD_NO");




7. Drop Main table constratint + Reference key also deleted.
alter table "DB_MASTER"."TDB_APPLICANT_DETAILS" drop constraint PK_APPLICANT_DETAILS cascade;  

DROP INDEX DB_MASTER.PK_APPLICANT_DETAILS;



8.------ Verify Again exist -------
SELECT owner,CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME FROM ALL_CONSTRAINTS WHERE index_name='PK_APPLICANT_DETAILS';
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME ='TDB_APPLICANT_DETAILS';


----REFERENCE----
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints  where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='TDB_APPLICANT_DETAILS');


9. Now structure is Fine.

    Analyze index DB_MASTER.PK_APPLICANT_DETAILs validate structure;