Imp 00003 oracle error 942 encountered

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.

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:

    a. Open a text file
    b. Type in the following command, without the delimiters <CODE> and </CODE>:
    <CODE>
    spool impErrorFix.log
    </CODE>
    c. Copy the statement from the error message
    Example:
    «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»
    d. Remove all soft returns
    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.

    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_owner% with the Rational Portfolio Manager owner

    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.

Log In

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

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:

  • Tek-Tips ForumsTalk 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.

Источник

Понравилась статья? Поделить с друзьями:
  • Immobilizer transponder error
  • Ilink64 error fatal out of memory
  • Immobilizer smartra error
  • Immobilizer eeprom error
  • Ilink32 error unable to perform link