Oracle DBA Tips and Techniques

By Arjun Raja

IMPDP FAILS IN DATABASE WITHOUT OLAP – ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG’ must be declared

Posted by Arjun Raja on March 19, 2013

Export taken from database with OLAP – 11.1.0.7 database

While importing the exported dump into a 11.2.0.3 database – following warnings appear –

ORA-39083: Object type PROCACT_SCHEMA failed to create with error: ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG’ must be declared ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 3, column 1:
PLS-00201: identifier ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_LOOP’ must be declared ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END’ must be declared

The above are just warnings and can be ignored if OLAP is not required.

But if you prefer a clean import without all this clutter and provided OLAP is not required then

Export the data in the source 11g database with the following option added to the expdp command

VERSION=10.2

Example of parameter file for export –

directory=data_pump_dir
dumpfile=schemaowner.dmp
logfile=schemaowner.log
CONTENT=ALL
SCHEMAS=SCHEMAOWNER
VERSION=10.2

Warning -If VERSION=10.2 is used, the COMPRESSION=ALL cannot be used in the parameter file….Therefore the export dump size will be much larger.

The dump above can be imported into the 11.2.0.3 database which does not have OLAP – no warnings will be displayed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: