IBM Support

Resolving Oracle import errors in Portfolio Manager

Technote (troubleshooting)


Problem(Abstract)

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

Cause

Unknown

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.log

CREATE 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>


Document information

More support for: Rational Portfolio Manager
Installation and Configuration: Server

Software version: 7.1.1.1, 7.1.1.2

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1330529

Modified date: 23 November 2010


Translate this page: