4 Replies Latest reply on Aug 20, 2018 4:35 AM by Beauty_and_dBest

    EBS 12.2.7 invalid indexes?

    Beauty_and_dBest

      EBS R12.2.7

      12c

      OL6

       

       

      Hi ALL,

       

      We found this warning in out alert logs.

       

       

      Fri Aug 17 09:30:27 2018

      Some indexes or index [sub]partitions of table PJI.PJI_FM_EXTR_PLNVER4 have been marked unusable

      Fri Aug 17 09:34:26 2018

      Completed checkpoint up to RBA [0x3a.2.10], SCN: 5965194996377

      Fri Aug 17 09:36:01 2018

      Some indexes or index [sub]partitions of table PJI.PJI_FM_EXTR_PLNVER4 have been marked unusable

       

      And invalid view in PA:  APPS      PA_STATUS_RSRC_GENERIC_V VIEW

       

       

      Please help....

       

       

      Kind regards,

      jc

        • 1. Re: EBS 12.2.7 invalid indexes?
          Beauty_and_dBest

          Hi ALL,

           

          Why is that I can not see index partition?

           

           

          CREATE TABLE "PJI"."PJI_FM_EXTR_PLNVER4"

             ("WORKER_ID" NUMBER(15,0),

              "PROJECT_ID" NUMBER,

              "PLAN_VERSION_ID" NUMBER,

              "WBS_STRUCT_VERSION_ID" NUMBER,

              "RBS_STRUCT_VERSION_ID" NUMBER,

              "PLAN_TYPE_ID" NUMBER,

              "PLAN_TYPE_CODE" VARCHAR2(30 BYTE),

              "TIME_PHASED_TYPE_CODE" VARCHAR2(30 BYTE),

              "TIME_DANGLING_FLAG" VARCHAR2(1 BYTE),

              "RATE_DANGLING_FLAG" VARCHAR2(1 BYTE),

              "PROJECT_TYPE_CLASS" VARCHAR2(1 BYTE),

              "WP_FLAG" VARCHAR2(1 BYTE),

              "CURRENT_FLAG" VARCHAR2(1 BYTE),

              "ORIGINAL_FLAG" VARCHAR2(1 BYTE),

              "CURRENT_ORIGINAL_FLAG" VARCHAR2(1 BYTE),

              "BASELINED_FLAG" VARCHAR2(1 BYTE),

              "SECONDARY_RBS_FLAG" VARCHAR2(1 BYTE),

              "LP_FLAG" VARCHAR2(1 BYTE)

             ) PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255  NOLOGGING

            STORAGE(INITIAL 16384 NEXT 16777216 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 10

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA"

            PARTITION BY RANGE ("WORKER_ID")

          (PARTITION "P1"  VALUES LESS THAN (2) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P2"  VALUES LESS THAN (3) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P3"  VALUES LESS THAN (4) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P4"  VALUES LESS THAN (5) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P5"  VALUES LESS THAN (6) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P6"  VALUES LESS THAN (7) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P7"  VALUES LESS THAN (8) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P8"  VALUES LESS THAN (9) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P9"  VALUES LESS THAN (10) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ,

          PARTITION "P10"  VALUES LESS THAN (11) SEGMENT CREATION IMMEDIATE

            PCTFREE 0 PCTUSED 40 INITRANS 10 MAXTRANS 255

          NOCOMPRESS NOLOGGING

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_DATA" ) ;

           

            CREATE INDEX "PJI"."PJI_FM_EXTR_PLNVER4_N1" ON "PJI"."PJI_FM_EXTR_PLNVER4" ("PLAN_VERSION_ID")

            PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS

            STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 4

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "APPS_TS_TX_IDX" ;

           

           

          • 2. Re: EBS 12.2.7 invalid indexes?
            Beauty_and_dBest

            Hi ALL,

             

            How do I validate if there are unusable indexes in our database? Can you help me the query please.

             

            Is below correct?

             

            select owner, index_name from dba_indexes where status='UNUSABLE';

             

            But the op said she did not find unusable index mentioned in alert log

             

             

            Kind regards,

            • 3. Re: EBS 12.2.7 invalid indexes?
              Kanda-Oracle

              Hi JC,

               

              Yes. The below query gives the output that you can directly run to validate the index. Please test it on the TEST instance as always.

               

              SQL> select 'alter index '||owner||'.'||index_name|| ' rebuild online; ' Execute_the_below  from dba_indexes where status='UNUSABLE';

               

              EXECUTE_THE_BELOW

              --------------------------------------------------------------------------------

              alter index APPLSYS.FND_USER_N1 rebuild online;

              :

               

              Hope this helps!

               

              Thanks


              N Kandasamy