Index rebuilding

SELECT * FROM dba_objects WHERE object_id ='696249'; -- this query will give us the corrupt index id

select TABLE_NAME,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns
where TABLE_NAME in ('FND_CONCURRENT_REQUESTS')
order by 1,2,COLUMN_POSITION;

=====================================================================================================================
Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N1 validate structure online; -- Idex Analyzed


select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N1','APPLSYS') from dual;

--- double click the output

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N1" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUESTED_BY",

"ACTUAL_COMPLETION_DATE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N1;  -- type commit once


CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N1" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUESTED_BY",

"ACTUAL_COMPLETION_DATE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"
 
=====================================================================================================================



Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N1 validate structure online; -- Idex Analyzed

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N2','APPLSYS') from dual;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N2" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("STATUS_CODE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

---------------------------------------------------------------------------------
Error - ORA-00054: resource busy and acquire with NOWAIT specified.
conn as sysdba
show parameter DDL_LOCK_TIMEOUT

SQL>alter system set ddl_lock_timeout = 100 ;

Session altered.

Now in the first session issue commit.
SQL> commit;

--------------------------------------------------------------------------------
Step 3

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N2;

Step 4

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N2" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("STATUS_CODE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N3 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N3','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N3;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N3" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("PARENT_REQUEST_ID")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N4 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N4','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N4;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N4" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("PRIORITY_REQUEST_ID")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX" ;

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N5 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N5','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N5;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N5" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("DESCRIPTION")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N6 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N6','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N6;

  CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N6" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("CONCURRENT_PROGRAM_ID",

"PROGRAM_APPLICATION_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N7 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N7','APPLSYS') from dual;

drop index APPLSYS.FND_CONCURRENT_REQUESTS_N7;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N7" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("PHASE_CODE", "STATUS_CODE")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N8 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N8','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N8;


CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N8" ON "APPLSYS"."FND_CONCURRENT_REQUESTS"

("RESPONSIBILITY_APPLICATION_ID", "RESPONSIBILITY_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N9 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N9','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N9;

CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N9" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("OPS_INSTANCE", "STATUS_CODE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N10 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N10','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N10;


  CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N10" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("CD_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N11 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_N11','APPLSYS') from dual;


drop index APPLSYS.FND_CONCURRENT_REQUESTS_N11;


CREATE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_N11" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUEST_DATE")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================


Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_U1 validate structure online;

select dbms_metadata.get_ddl('INDEX','FND_CONCURRENT_REQUESTS_U1','APPLSYS') from dual;



drop index APPLSYS.FND_CONCURRENT_REQUESTS_U1;


CREATE UNIQUE INDEX "APPLSYS"."FND_CONCURRENT_REQUESTS_U1" ON "APPLSYS"."FND_CONCURRENT_REQUESTS" ("REQUEST_ID")
  PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

========================================================================================================================

ERROR at line 1:
ORA-08102: index key not found, obj# 696249, file 406, block 290081 (2)