8 Replies Latest reply on Dec 2, 2016 4:39 PM by Srini Chavali-Oracle

    Create index error ORA-00904: "SYS_NC00086$": invalid identifier

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.1.3

      11gR2

      OL6

       

       

      Per support note ID >

      Create Accounting Low Performance for Oracle Process Manufacturing (Doc ID 1952504.1)

      It suggested to drop and recreate the XLA indexes.

      Capture.PNG

      But when I created one of the indexes with "$" character in it, I got  error

       

      SQL> CREATE INDEX XLA_DISTRIBUTION_LINKS_N1 ON XLA_DISTRIBUTION_LINKS(SOURCE_DISTRIBUTION_TYPE,SOURCE_DISTRIBUTION_ID_NUM_1,"SYS_NC00086$");

      CREATE INDEX XLA_DISTRIBUTION_LINKS_N1 ON XLA_DISTRIBUTION_LINKS(SOURCE_DISTRIBUTION_TYPE,SOURCE_DISTRIBUTION_ID_NUM_1,"SYS_NC00086$")

                                                                                                                             *

      ERROR at line 1:

      ORA-00904: "SYS_NC00086$": invalid identifier

       

       

      Please help....

       

      Kind regards,

      jc

        • 1. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
          Noname123

          In the documentation you shared, it is suggesting that you have to recreate the index using local option.  The sql statement you have shared in the OP doesn't  have local option mentioned there.

          • 2. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
            Pravin Takpire

            set long 100000

            set heading off

            set linesize 120

            exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

            select dbms_metadata.GET_DDL('INDEX','XLA_DISTRIBUTION_LINKS_N1','XLA') from dual;

             

            Run this and you will get DDL statement to recreate index. If you have already dropped index, run it on cloned copy of the instance.

             

            regards

            Pravin

            • 3. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
              Gbenga Ajakaye

              Hi Jenna,

               

              Your query has a reference to a non-existing column SYS_NC00086$ - which doesn't belong to the XLA_DISTRIBUTION_LINKS.

              Can you run the command below and paste the result.

               

              desc XLA_DISTRIBUTION_LINKS

              • 4. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
                Beauty_and_dBest

                Thanks Pravin and ALL

                How do you make the output continuous and not "broken"

                Thanks.

                SQL> set echo on

                SQL> @xla_ndx

                SQL> set long 100000

                SQL> set heading off

                SQL> set linesize 120

                SQL> exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

                 

                PL/SQL procedure successfully completed.

                 

                SQL> select dbms_metadata.GET_DDL('INDEX','XLA_DISTRIBUTION_LINKS_N1','XLA') from dual;

                 

                CREATE INDEX "XLA"."XLA_DISTRIBUTION_LINKS_N1" ON "XLA"."

                XLA_DISTRIBUTION_LINKS" ("SOURCE_DISTRIB

                UTION_TYPE", "SOURCE_DISTRIBUTION_ID_NUM

                _1", NVL("SOURCE_DISTRIBUTION_ID_NUM_2",

                (-99)))

                  PCTFREE 10 INITRANS 11 MAXTRANS 255  LOGGING

                  STORAGE(

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH

                _CACHE DEFAULT) LOCAL

                (PARTITION "AP"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LO

                GGING

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAX

                EXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROU

                PS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CA

                CHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "AR"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1

                MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH

                 

                _CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "PA"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS

                1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FL

                ASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "PO"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTE

                 

                NTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL

                _FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "PN"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINE

                XTENTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C

                ELL_FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                 

                 

                PARTITION "OFA"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGIN

                G

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTE

                NTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                 

                 

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE

                DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "OKL"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LO

                GGING

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAX

                EXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROU

                PS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CA

                CHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "OZF"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1

                MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS

                 

                 

                H_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "CST"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTEN

                TS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_

                FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "PSB"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINE

                 

                XTENTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C

                ELL_FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_IDX" ,

                PARTITION "CE"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTEN

                TS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAUL

                T CELL_FLASH_CACHE DEFAULT)

                 

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "FUN"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LO

                GGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAX

                EXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROU

                PS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CA

                CHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "FV"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                 

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1

                MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS

                H_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "GMF"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTE

                NTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL

                _FLASH_CACHE DEFAULT)

                 

                 

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "LNS"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENT

                S 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT

                CELL_FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "PAY"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOG

                GING

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXE

                XTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUP

                S 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC

                HE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "IGC"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1

                MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS

                 

                 

                H_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "IGI"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTE

                NTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL

                _FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ,

                PARTITION "DPP"

                  PCTFREE 10 INITRANS 11 MAXTRANS 255 LOGGING

                 

                  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENT

                S 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT

                CELL_FLASH_CACHE DEFAULT)

                  TABLESPACE "APPS_TS_TX_DATA" ) ;

                 

                 

                SQL> spool off

                • 5. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
                  Beauty_and_dBest

                  Hi Gb,

                   

                  SQL> desc XLA_DISTRIBUTION_LINKS

                  Name                                      Null?    Type

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

                  APPLICATION_ID                            NOT NULL NUMBER(15)

                  EVENT_ID                                  NOT NULL NUMBER(15)

                  AE_HEADER_ID                              NOT NULL NUMBER(15)

                  AE_LINE_NUM                               NOT NULL NUMBER(38)

                  SOURCE_DISTRIBUTION_TYPE                  NOT NULL VARCHAR2(30)

                  SOURCE_DISTRIBUTION_ID_CHAR_1                      VARCHAR2(240)

                  SOURCE_DISTRIBUTION_ID_CHAR_2                      VARCHAR2(240)

                  SOURCE_DISTRIBUTION_ID_CHAR_3                      VARCHAR2(240)

                  SOURCE_DISTRIBUTION_ID_CHAR_4                      VARCHAR2(240)

                  SOURCE_DISTRIBUTION_ID_CHAR_5                      VARCHAR2(240)

                  SOURCE_DISTRIBUTION_ID_NUM_1                       NUMBER(38)

                  SOURCE_DISTRIBUTION_ID_NUM_2                       NUMBER(38)

                  SOURCE_DISTRIBUTION_ID_NUM_3                       NUMBER(38)

                  SOURCE_DISTRIBUTION_ID_NUM_4                       NUMBER(38)

                  SOURCE_DISTRIBUTION_ID_NUM_5                       NUMBER(38)

                  TAX_LINE_REF_ID                                    NUMBER(15)

                  TAX_SUMMARY_LINE_REF_ID                            NUMBER(15)

                  TAX_REC_NREC_DIST_REF_ID                           NUMBER(15)

                  STATISTICAL_AMOUNT                                 NUMBER

                  REF_AE_HEADER_ID                          NOT NULL NUMBER(15)

                  REF_TEMP_LINE_NUM                                  NUMBER(15)

                  ACCOUNTING_LINE_CODE                               VARCHAR2(30)

                  ACCOUNTING_LINE_TYPE_CODE                          VARCHAR2(1)

                  MERGE_DUPLICATE_CODE                      NOT NULL VARCHAR2(1)

                  TEMP_LINE_NUM                             NOT NULL NUMBER(15)

                  REF_EVENT_ID                                       NUMBER(15)

                  LINE_DEFINITION_OWNER_CODE                         VARCHAR2(30)

                  LINE_DEFINITION_CODE                               VARCHAR2(30)

                  EVENT_CLASS_CODE                                   VARCHAR2(30)

                  EVENT_TYPE_CODE                                    VARCHAR2(30)

                  UPG_BATCH_ID                                       NUMBER(15)

                  CALCULATE_ACCTD_AMTS_FLAG                          VARCHAR2(1)

                  CALCULATE_G_L_AMTS_FLAG                            VARCHAR2(1)

                  ROUNDING_CLASS_CODE                                VARCHAR2(30)

                  DOCUMENT_ROUNDING_LEVEL                            VARCHAR2(30)

                  UNROUNDED_ENTERED_DR                               NUMBER

                  UNROUNDED_ENTERED_CR                               NUMBER

                  DOC_ROUNDING_ENTERED_AMT                           NUMBER

                  DOC_ROUNDING_ACCTD_AMT                             NUMBER

                  UNROUNDED_ACCOUNTED_CR                             NUMBER

                  UNROUNDED_ACCOUNTED_DR                             NUMBER

                  APPLIED_TO_APPLICATION_ID                          NUMBER(15)

                  APPLIED_TO_ENTITY_CODE                             VARCHAR2(30)

                  APPLIED_TO_ENTITY_ID                               NUMBER(15)

                  APPLIED_TO_SOURCE_ID_NUM_1                         NUMBER(15)

                  APPLIED_TO_SOURCE_ID_NUM_2                         NUMBER(15)

                  APPLIED_TO_SOURCE_ID_NUM_3                         NUMBER(15)

                  APPLIED_TO_SOURCE_ID_NUM_4                         NUMBER(15)

                  APPLIED_TO_SOURCE_ID_CHAR_1                        VARCHAR2(30)

                  APPLIED_TO_SOURCE_ID_CHAR_2                        VARCHAR2(30)

                  APPLIED_TO_SOURCE_ID_CHAR_3                        VARCHAR2(30)

                  APPLIED_TO_SOURCE_ID_CHAR_4                        VARCHAR2(30)

                  APPLIED_TO_DISTRIBUTION_TYPE                       VARCHAR2(30)

                  APPLIED_TO_DIST_ID_NUM_1                           NUMBER(15)

                  APPLIED_TO_DIST_ID_NUM_2                           NUMBER(15)

                  APPLIED_TO_DIST_ID_NUM_3                           NUMBER(15)

                  APPLIED_TO_DIST_ID_NUM_4                           NUMBER(15)

                  APPLIED_TO_DIST_ID_NUM_5                           NUMBER(15)

                  APPLIED_TO_DIST_ID_CHAR_1                          VARCHAR2(30)

                  APPLIED_TO_DIST_ID_CHAR_2                          VARCHAR2(30)

                  APPLIED_TO_DIST_ID_CHAR_3                          VARCHAR2(30)

                  APPLIED_TO_DIST_ID_CHAR_4                          VARCHAR2(30)

                  APPLIED_TO_DIST_ID_CHAR_5                          VARCHAR2(30)

                  ALLOC_TO_APPLICATION_ID                            NUMBER(15)

                  ALLOC_TO_ENTITY_CODE                               VARCHAR2(30)

                  ALLOC_TO_SOURCE_ID_NUM_1                           NUMBER(15)

                  ALLOC_TO_SOURCE_ID_NUM_2                           NUMBER(15)

                  ALLOC_TO_SOURCE_ID_NUM_3                           NUMBER(15)

                  ALLOC_TO_SOURCE_ID_NUM_4                           NUMBER(15)

                  ALLOC_TO_SOURCE_ID_CHAR_1                          VARCHAR2(30)

                  ALLOC_TO_SOURCE_ID_CHAR_2                          VARCHAR2(30)

                  ALLOC_TO_SOURCE_ID_CHAR_3                          VARCHAR2(30)

                  ALLOC_TO_SOURCE_ID_CHAR_4                          VARCHAR2(30)

                  ALLOC_TO_DISTRIBUTION_TYPE                         VARCHAR2(30)

                  ALLOC_TO_DIST_ID_NUM_1                             NUMBER(15)

                  ALLOC_TO_DIST_ID_NUM_2                             NUMBER(15)

                  ALLOC_TO_DIST_ID_NUM_3                             NUMBER(15)

                  ALLOC_TO_DIST_ID_NUM_4                             NUMBER(15)

                  ALLOC_TO_DIST_ID_NUM_5                             NUMBER(15)

                  ALLOC_TO_DIST_ID_CHAR_1                            VARCHAR2(30)

                  ALLOC_TO_DIST_ID_CHAR_2                            VARCHAR2(30)

                  ALLOC_TO_DIST_ID_CHAR_3                            VARCHAR2(30)

                  ALLOC_TO_DIST_ID_CHAR_4                            VARCHAR2(30)

                  ALLOC_TO_DIST_ID_CHAR_5                            VARCHAR2(30)

                  GAIN_OR_LOSS_REF                                   VARCHAR2(30)

                   

                   

                  SQL>

                  • 6. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
                    Pravin Takpire

                    you will have to edit it or you can use sqldeveloper or toad to get the DDL

                    regards

                    Pravin

                    • 7. Re: Create index error ORA-00904: "SYS_NC00086$": invalid identifier
                      Beauty_and_dBest

                      Thanks

                       

                      By the way even if I drop and recreate indexes our   CREATE ACCOUNTING is still very looooooong running.

                      Is there a way how to check what is the issue with our create accounting? or where is the bottleneck?

                       

                       

                      Kind regards,