Oracle”table or view does not exist” error on project migration

Tags:

Question ID: 105736

We are migrating/upgrading some projects from ALM 11 to 11.52.

We have a problem with one project. We are using ORACLE 11.2.0.3 and did the HP-suggested EXP/IMP (Oracle "Original" backup) method.

When we do RESTORE from Site Admin, we get the "Failed to restore project" error.

Here is the Error Text:

Failed to restore project; Failed to load data from EXTENSIONS table of project ‘default_myProject_db@jdbc:mercury:oracle:TNSNamesFile=D:\\oracle\\product\\11.2.0\\client_1\\network\\admin\\tnsnames.ora;TNSServerName=myServer(default_myProject_db)’; [Mercury][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist Failed SQL: /* ~~QC */ SELECT EX_NAME FROM EXTENSIONS;

Stack Trace:

java.sql.SQLException: [Mercury][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist Failed SQL: /* ~~QC */ SELECT EX_NAME FROM EXTENSIONS

Marked as spam

Posted by gobroncos (Questions: 193, Answers: 14)

Asked on December 1, 2014 4:35 pm

227 views

Answers (1)

▲

▼

✔

Private answer

Check the ORIGINAL Schema for this project from the OLD QC version.
Is that table (called ''Extensions'') empty there?

If you used the EXP command (Oracle ''original'' backup), it has a new ''feature'' as of Oracle 11g release 2 (i.e. 11.2.x.x), EXP drops empty tables.

From Oracle:

- Beginning in Oracle Database 11g
Release 2, when creating a
non-partitioned heap-organized table
in a locally managed tablespace,
table segment creation is deferred
until the first row is inserted. This
is set via setting the initialization
parameter DEFERRED_SEGMENT_CREATION
TRUE, which is default when you
create DB. Having said that unless
you insert first record object is not
visible to our traditional exp tool.

Your DBA may be able to set the parameter ''DEFERRED_SEGMENT_CREATION'' to FALSE for your old Oracle instance/server and try EXP again - it should export the ''empty'' tables. (you would need to drop the previous migrated schema and replace)

If you want to work with your existing migrated schema, you will need to either create the table EXACTLY the same as it is on the old Oracle instance.

Alternately, you could start over with the migration and use Oracle Data ''pump'' instead.

Marked as spam