7 Replies Latest reply on Jan 8, 2010 6:00 AM by 668860

    Importing indexes causing - ORA-00942

    668860
      Hello,

      we have an automated refresh using export/import utilitiy on our Oracle 8.1.7.4 database.
      during import we are getting ORA-00942 errors while creating indexes for only few tables.
      one weird thing is import process is trying to create indexes for few tables before table creation itself.

      Import creates indexes after importing table, not sure what is happening in this case:

      Below is the import log.

      any idea whats causing this...

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



      . . importing table "PS_PSI_PRFTSHR_EMP" 92 rows imported
      . . importing table "PS_PSI_PRFTSHR_SEC" 0 rows imported
      . . importing table "PS_PSI_PROFIT_SHAR" 21374 rows imported
      . . importing table "PS_PSI_PRSNNL" 13793 rows imported
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE UNIQUE INDEX "PS_PSI_REP_TST" ON "PS_PSI_REP_TST" ("EMPLID" , "EMPL_"
      "RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INI"
      "TIAL 532480 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FR"
      "EELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" LOGGI"
      "NG"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS0PSI_REP_TST" ON "PS_PSI_REP_TST" ("DEPTID" , "EMPLID" , "E"
      "MPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE"
      "(INITIAL 655360 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE "
      "0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" L"
      "OGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS1PSI_REP_TST" ON "PS_PSI_REP_TST" ("JOBCODE" , "EMPLID" , ""
      "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAG"
      "E(INITIAL 655360 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE"
      " 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" "
      "LOGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS2PSI_REP_TST" ON "PS_PSI_REP_TST" ("COMPANY" , "EMPLID" , ""
      /
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS2PSI_REP_TST" ON "PS_PSI_REP_TST" ("COMPANY" , "EMPLID" , ""
      "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAG"
      "E(INITIAL 655360 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE"
      " 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" "
      "LOGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      . . importing table "PS_PSI_PRSNNL_ALL" 132293 rows imported
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE UNIQUE INDEX "PS_PSI_REP_TST_HIS" ON "PS_PSI_REP_TST_HIS" ("EMPLID" "
      ", "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STO"
      "RAGE(INITIAL 4341760 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCR"
      "EASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSIND"
      "EX" LOGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS0PSI_REP_TST_HIS" ON "PS_PSI_REP_TST_HIS" ("DEPTID" , "EMPL"
      "ID" , "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 255"
      " STORAGE(INITIAL 5570560 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCT"
      "INCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "P"
      "SINDEX" LOGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS1PSI_REP_TST_HIS" ON "PS_PSI_REP_TST_HIS" ("JOBCODE" , "EMP"
      "LID" , "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 25"
      "5 STORAGE(INITIAL 5570560 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PC"
      "TINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ""
      "PSINDEX" LOGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      IMP-00017: following statement failed with ORACLE error 942:
      "CREATE INDEX "PS2PSI_REP_TST_HIS" ON "PS_PSI_REP_TST_HIS" ("COMPANY" , "EMP"
      "LID" , "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 25"

      "CREATE INDEX "PS2PSI_REP_TST_HIS" ON "PS_PSI_REP_TST_HIS" ("COMPANY" , "EMP"
      "LID" , "EMPL_RCD" , "EFFDT" , "EFFSEQ" ) PCTFREE 10 INITRANS 2 MAXTRANS 25"
      "5 STORAGE(INITIAL 5079040 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PC"
      "TINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ""
      "PSINDEX" LOGGING"
      IMP-00003: ORACLE error 942 encountered
      ORA-00942: table or view does not exist
      . . importing table "PS_PSI_PRU_BR_DTL" 806 rows imported
      . . importing table "PS_PSI_RANDOM_TBL" 990 rows imported
      . . importing table "PS_PSI_RC_BASH110" 41 rows imported
      . . importing table "PS_PSI_RC_BASHEM00" 0 rows imported
      . . importing table "PS_PSI_RC_BENH" 28 rows imported
      . . importing table "PS_PSI_RC_BENH01" 2 rows imported
      . . importing table "PS_PSI_RC_BENH02" 3 rows imported
      . . importing table "PS_PSI_RC_CRW" 35 rows imported
      . . importing table "PS_PSI_RC_PSIGL04" 11 rows imported
      . . importing table "PS_PSI_RC_PSIHR009" 1 rows imported
      . . importing table "PS_PSI_RC_PSIPY001" 17 rows imported
      . . importing table "PS_PSI_RC_PSIPY002" 44 rows imported
      . . importing table "PS_PSI_RC_PSIPY01A" 17 rows imported
      . . importing table "PS_PSI_RC_PSIPY02A" 33 rows imported
      . . importing table "PS_PSI_RC_PSIPY02B" 12 rows imported
      . . importing table "PS_PSI_RC_JOB_PRCS" 1 rows imported
      . . importing table "PS_PSI_RC_MERIT_LD" 1 rows imported
      . . importing table "PS_PSI_RC_MERIT_PR" 1 rows imported
      . . importing table "PS_PSI_RC_PER036S" 7 rows imported
      . . importing table "PS_PSI_RC_REORG_PR" 1 rows imported
      . . importing table "PS_PSI_RC_TC1_EDIT" 17 rows imported
      . . importing table "PS_PSI_RC_TRUE" 13 rows imported
      . . importing table "PS_PSI_REORG_LOAD" 0 rows imported
      . . importing table "PS_PSI_REP_TST" 13793 rows imported
      . . importing table "PS_PSI_REP_TST_HIS" 132293 rows imported
        • 1. Re: Importing indexes causing - ORA-00942
          Srini Chavali-Oracle
          Did this start happening recently ? If so, what changes have been made that might cause this ? Pl post your full OS version and the exp and imp commands.

          Srini
          • 2. Re: Importing indexes causing - ORA-00942
            668860
            Srini,

            we are not sure what was changed recently which is causing this issue.


            this is on Sun OS 5.8 and oracle enterprise version 8.1.7.4


            - export.parm

            buffer = 5242880
            full = y
            grants = y
            indexes = y
            rows = y
            constraints = y
            compress = y
            statistics = none
            file = /orabackup/p80hpsi/20100104.1900.export/p80hpsi.dmp
            log = /orabackup/p80hpsi/20100104.1900.export/p80hpsi.log
            userid = system/password





            - import.parm

            userid=b80hspi/b80hspi
            buffer=10000000
            commit=y
            ignore=y
            grants=n
            constraints=n
            file=/orabackup/PRODPSI.dmp
            log=import.log
            fromuser=P80HPSI
            touser=b80hspi

            please let me know if you need further details.

            thanks!
            • 3. Re: Importing indexes causing - ORA-00942
              Lakmal Rajapakse
              Do the tables actually exists?

              Maybe the table owner and index owner are different.
              • 4. Re: Importing indexes causing - ORA-00942
                Srini Chavali-Oracle
                Can you try the import with the system account (instead of the b80hspi account) ?

                Srini
                • 5. Re: Importing indexes causing - ORA-00942
                  732412
                  Hello user5841842.
                  As the log identifies the create index statements that failed, you could always run these after the import completes. Of course, this option is only useful if this is a one time activity, but it would be a good test for the other details on the indexes like the PSINDEX tablespace.

                  I'm not certain if successful and error logging is written sequentially or possibly in different buffers for the import log, but what you may be seeing is buffered successful logging, error logging (that isn't buffered) and then more buffered successful logging, making it appear as if the index creates are occurring before the table creates.

                  Hope this helps,
                  Luke

                  Please mark the answer as helpful or answered if it is so. If not, provide additional details.
                  Always try to provide create table and insert table statements to help the forum members help you better.
                  • 6. Re: Importing indexes causing - ORA-00942
                    Lubiez Jean-Valentin
                    Hello,


                    May you try to make your import in 2 steps:

                    - 1. You import all except the Indexes and Constraints:
                    rows = y
                    indexes = n
                    constraints = n
                    - 2. You import The Indexes and the Constraints
                    ignore = y
                    rows = n
                    indexes = y
                    constraints = y
                    Hope it can help.
                    Best regards,
                    Jean-Valentin

                    Edited by: Lubiez Jean-Valentin on Jan 5, 2010 9:52 PM
                    • 7. Re: Importing indexes causing - ORA-00942
                      668860
                      hello,

                      thanks everyone....the index owner is same as table owner, as mentioned wiill try to run the import using system account over the weekend and keepo you posted.

                      thanks!