Home » RDBMS Server » Server Utilities » Import - indexes (Oracle 8.1.7.4 on Solaris)
icon4.gif  Import - indexes [message #437637] Tue, 05 January 2010 05:35 Go to next message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
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 which is causing this issue.

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

Report message to a moderator

Re: Import - indexes [message #437653 is a reply to message #437637] Tue, 05 January 2010 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What was the import command?

Regards
Michel

Report message to a moderator

Re: Import - indexes [message #437672 is a reply to message #437653] Tue, 05 January 2010 08:01 Go to previous messageGo to next message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
Hi Michel,

these are the contents of the import parameter file.

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

Thanks!

Report message to a moderator

Re: Import - indexes [message #437677 is a reply to message #437672] Tue, 05 January 2010 08:20 Go to previous message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It seems that PROD80HPSI was the owner of indexes on tables (PS_PSI_REP_TST) it does not owned and that is unreachable to sysadm.

Regards
Michel

Report message to a moderator

Previous Topic: Trace File does not give all the bind variable values when multiple rows are inserted using 1 query
Next Topic: Problem with expdp through EM
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jan 25 15:35:19 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.