Imp 00003 oracle error 942 encountered - Исправление ошибок и поиск оптимальных решений проблем

Problem

This technote explains why in some instances, when restoring an Oracle database or moving it from one version of Oracle to another, there are errors that can be encountered during the import process when using IBM Rational Portfolio Manager.

Symptom

IMP-00017: following statement failed with ORACLE error 942: …
An example of the full error is shown below:
IMP-00017: following statement failed with ORACLE error 942:
«CREATE INDEX «HSRSC_RID_RDT_XX» ON «HISTORY_RESOURCES» («RESOURCE_ID» , «RE»
«C_DATETIME» DESC ) PCTFREE 30 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 6553″
«6 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE «PMO_IDX_64»
«K» LOGGING»
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist

Resolving The Problem

While these instructions and examples are focused on Microsoft Windows, these instructions are valid for Unix and Linux. Please keep in mind the difference between these environments.

Create a SQL Script file to create the Import DB Fix
1. Based on the error messages received, build the SQL script file in the following manner:

c. Copy the statement from the error message

Example:

d. Remove all soft returns

Example:

Note: do not remove any text, the example was truncated to show the beginning and end of the line.

e. Remove all quotes from the statement

Example:

CREATE INDEX HSRSC_RID_RDT_XX ON HISTORY_RESOURCES (RESOURCE_ID, REC_DATETIME DESC ) PCTFREE 30 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 6553 6 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_IDX_64 K LOGGING

f. Add a semi-colon to the end of the statement

Example:

CREATE INDEX HSRSC_RID_RDT_XX ON … K LOGGING;

Note: do not remove any text, the example was truncated to show the beginning and end of the line.

2. Add one line as described in step 1 for each error received during the install process. An example of what this file might contain is located in Appendix A: Import Error Fix

3. At the end of the file add the following commands:

<CODE>

spool off;

exit;

<CODE>

4. Save this file with the name impErrorFix.sql

Open a Windows Command Prompt and execute the SQL file

Open a Windows command prompt, start the SQL process and execute the SQL command file for creating the tablespaces

If you already have a Windows command prompt window open, skip to step 2 below.

1. From the Windows Start Menu select:

Start | Run | CMD

2. Traverse to the location where the file (impErrorFix.sql) was saved.

3. From the Windows command prompt, open a SQLPLUS prompt by typing the following command

o replacing %RPM_Password% with the password for the Rational Portfolio Manager owner password

<CODE>

SQLPLUS %RPM_Owner%/%RPM_Password% @impErrorFix.sql

</CODE>

Example:

c:>SQLPLUS rpm/rpm @impErrorFix.sql

4. Validate that the script executed without errors by reviewing the log file: (impErrorFix.log)

Appendix A Import Error Fix

<CODE>

spool impErrorFix.logCREATE UNIQUE INDEX HASSD_EID_MJIRV_CU ON HISTORY_ASSET_DOCUMENTS (ELEMENT_ID , MAJOR_REVISION DESC , MINOR_REVISION DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE UNIQUE INDEX HCLID_EID_MJIRV_CU ON HISTORY_CLIENT_DOCUMENTS (ELEMENT_ID , MAJOR_REVISION DESC , MINOR_REVISION DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE UNIQUE INDEX HPOOLD_EID_RV_CU ON HISTORY_POOL_DOCUMENTS (ELEMENT_ID , MAJOR_REVISION DESC , MINOR_REVISION DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE UNIQUE INDEX HRD_EID_MJIRV_CU ON HISTORY_RESOURCE_DOCUMENTS (ELEMENT_ID , MAJOR_REVISION DESC , MINOR_REVISION DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE UNIQUE INDEX HSDOC_EID_MJIRV_CU ON HISTORY_DOCUMENTS (ELEMENT_ID , MAJOR_REVISION DESC , MINOR_REVISION DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE INDEX HSDSC_SCID_PDT_XX ON HISTORY_DOCUMENT_SCORECARDS (SCORECARD_ID , PUBLISHED_DT DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE INDEX HSRSC_RID_RDT_XX ON HISTORY_RESOURCES (RESOURCE_ID , REC_DATETIME DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE UNIQUE INDEX HVEND_EID_MJIRV_CU ON HISTORY_VENDOR_DOCUMENTS (ELEMENT_ID , MAJOR_REVISION DESC , MINOR_REVISION DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE UNIQUE INDEX PRFLS_PJPFID_NM_CU ON PROFILES (PROJECT_ID , ROLE_ID , PROF_NUMBER DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE INDEX TCRI_RKEN_XX ON TMT_CRI (RANK , ELEMENT_NAME DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

CREATE INDEX TWBS_TFP_XX ON TMT_WBS (TRANSFER_FLAG , PUBLISHED DESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE PMO_DATA_64K LOGGING;

Spool off;

Exit;

</CODE>

[{«Product»:{«code»:»SSRR2G»,»label»:»Rational Portfolio Manager»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Installation and Configuration: Server»,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF016″,»label»:»Linux»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»7.1.1.1;7.1.1.2″,»Edition»:»»,»Line of Business»:{«code»:»LOB45″,»label»:»Automation»}}]

Источник

INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Thanks. We have received your request and will respond promptly.

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It’s Free!

*Tek-Tips’s functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Errors during import execution

(OP)

18 Aug 06 16:01

I executed the statement below to do an export and it terminated without errors.

exp username/password tables=(employees,countries) file=c:exporthomeokmaps1expdat.dmp log=c:exporthomeokmaps1imp.log

Then i tried to import these two tables into another schema.
using the following command

imp username/password tables=(employees,countries) file=c:exporthomeokmaps1expdata.dmp log=c:exporthomeokmaps1imp.log

The results are below. When i check the import, the tables are where they are supposed to be with data in them. My questions is why do the statements listed below during the import fail. Thanks all you guru out there

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing ETHAN’s objects into JESSELYN
. . importing table «EMPLOYEES» 107 rows imported
. . importing table «COUNTRIES» 25 rows imported
IMP-00017: following statement failed with ORACLE error 942:
«ALTER TABLE «EMPLOYEES» ADD CONSTRAINT «EMP_DEPT_FK» FOREIGN KEY («DEPARTME»
«NT_ID») REFERENCES «DEPARTMENTS» («DEPARTMENT_ID») ENABLE NOVALIDATE»
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
«ALTER TABLE «EMPLOYEES» ADD CONSTRAINT «EMP_JOB_FK» FOREIGN KEY («JOB_ID») «
«REFERENCES «JOBS» («JOB_ID») ENABLE NOVALIDATE»
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
«ALTER TABLE «COUNTRIES» ADD CONSTRAINT «COUNTR_REG_FK» FOREIGN KEY («REGION»
«_ID») REFERENCES «REGIONS» («REGION_ID») ENABLE NOVALIDATE»
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
About to enable constraints…
IMP-00017: following statement failed with ORACLE error 2430:
«ALTER TABLE «EMPLOYEES» ENABLE CONSTRAINT «EMP_DEPT_FK»»
IMP-00017: following statement failed with ORACLE error 2430:
«ALTER TABLE «EMPLOYEES» ENABLE CONSTRAINT «EMP_JOB_FK»»
IMP-00017: following statement failed with ORACLE error 2430:
«ALTER TABLE «COUNTRIES» ENABLE CONSTRAINT «COUNTR_REG_FK»»
Import terminated successfully with warnings.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Join Tek-Tips^® Today!

Join your peers on the Internet’s largest technical computer professional community.
It’s easy to join and it’s free.

Here’s Why Members Love Tek-Tips Forums:

Talk To Other Members
Notification Of Responses To Questions
Favorite Forums One Click Access
Keyword Search Of All Posts, And More…

Register now while it’s still free!

Already a member? Close this window and log in.

Join Us Close

Источник

Hi,

I got foll. errors while importing using fromuser, touser in import.

IMP-00017: following statement failed with ORACLE error 942:
«GRANT SELECT ON «APPLSYS».»FND_ORACLE_USERID_S» TO «ODEVIEW»»
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist

IMP-00041: Warning: object created with compilation warnings
«CREATE FORCE VIEW «APPS».»BOM_RESOURCES_ALL_V» («
«»RESOURCE_ID»,»RESOURCE_CODE»,»DESCRIPTION»,»UNIT_OF_MEASURE»,»COST_ELEMENT»
«_ID»,»DISABLE_DATE»,»ORGANIZATION_ID»,»COST_CODE_TYPE»,»LAST_UPDATE_DATE»,»»
«LAST_UPDATED_BY»,»CREATION_DATE»,»CREATED_BY»,»LAST_UPDATE_LOGIN»,»PURCHASE»
«_ITEM_ID»,»FUNCTIONAL_CURRENCY_FLAG»,»DEFAULT_ACTIVITY_ID»,»RESOURCE_TYPE»,»
«»AUTOCHARGE_TYPE»,»STANDARD_RATE_FLAG»,»DEFAULT_BASIS_TYPE»,»ABSORPTION_ACC»
«OUNT»,»ALLOW_COSTS_FLAG»,»RATE_VARIANCE_ACCOUNT»,»EXPENDITURE_TYPE»,»ATTRIB»
«UTE_CATEGORY»,»ATTRIBUTE1″,»ATTRIBUTE2″,»ATTRIBUTE3″,»ATTRIBUTE4″,»ATTRIBUT»
«E5″,»ATTRIBUTE6″,»ATTRIBUTE7″,»ATTRIBUTE8″,»ATTRIBUTE9″,»ATTRIBUTE10″,»ATTR»
«IBUTE11″,»ATTRIBUTE12″,»ATTRIBUTE13″,»ATTRIBUTE14″,»ATTRIBUTE15″,»REQUEST_I»
«D»,»PROGRAM_APPLICATION_ID»,»PROGRAM_ID»,»PROGRAM_UPDATE_DATE») AS «
«SELECT BR.RESOURCE_ID, BR.RESOURCE_CODE, BR.DESCRIPTION, BR.UNIT_OF_MEASURE»
«, BR.COST_ELEMENT_ID, BR.DISABLE_DATE, BR.ORGANIZATION_ID, BR.COST_CODE_TYP»
«E, BR.LAST_UPDATE_DATE, BR.LAST_UPDATED_BY, BR.CREATION_DATE, BR.CREATED_BY»
«, BR.LAST_UPDATE_LOGIN, BR.PURCHASE_ITEM_ID, BR.FUNCTIONAL_CURRENCY_FLAG, B»
«R.DEFAULT_ACTIVITY_ID, BR.RESOURCE_TYPE, BR.AUTOCHARGE_TYPE, BR.STANDARD_RA»
«TE_FLAG, BR.DEFAULT_BASIS_TYPE, BR.ABSORPTION_ACCOUNT, BR.ALLOW_COSTS_FLAG,»
» BR.RATE_VARIANCE_ACCOUNT, BR.EXPENDITURE_TYPE, BR.ATTRIBUTE_CATEGORY, BR.A»
«TTRIBUTE1, BR.ATTRIBUTE2, BR.ATTRIBUTE3, BR.ATTRIBUTE4, BR.ATTRIBUTE5, BR.A»
«TTRIBUTE6, BR.ATTRIBUTE7, BR.ATTRIBUTE8, BR.ATTRIBUTE9, BR.ATTRIBUTE10, BR.»
«ATTRIBUTE11, BR.ATTRIBUTE12, BR.ATTRIBUTE13, BR.ATTRIBUTE14, BR.ATTRIBUTE15»
«, BR.REQUEST_ID, BR.PROGRAM_APPLICATION_ID, BR.PROGRAM_ID, BR.PROGRAM_UPDAT»
«E_DATE FROM BOM_RESOURCES BR»

I used foll. imp command.

imp system/manager@dev file=xyz.dmp fromuser=apps touser=apps full=N ignore=Y constraints=Y buffer=1000000 log=xyz.log

I checked that APPLSYS.FND_ORACLE_USERID_S and BOM_RESOURCES BR are existing in database.

any idea?

Thanks in Adv.

Источник

Problem

Symptom

Resolving The Problem

Contact US

Come Join Us!

Posting Guidelines

Errors during import execution

Errors during import execution

Red Flag Submitted

Join Tek-Tips® Today!

Читайте также:

Join Tek-Tips^® Today!