COMP_NAME STATUS ----------------------------------- --------- Oracle XML Database VALID Oracle Expression Filter VALID Oracle Rules Manager VALID Oracle Workspace Manager VALID Oracle Database Catalog Views VALID Oracle Database Packages and Types VALID JServer JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID 9 rows selected.While trying to export a user schema following error could be observed, even though export job completed with all rows being exported.
expdp asanga/asa directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga
Export: Release 11.2.0.3.0 - Production on Tue Jun 12 20:01:26 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01": asanga/******** directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('ASANGA',0,1,'11.02.00.00.00',newblock)
ORA-04063: package body "SYS.DBMS_CUBE_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9427
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('ASANGA',1,1,'11.02.00.00.00',newblock)
ORA-04063: package body "SYS.DBMS_CUBE_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9427
. . exported "ASANGA"."X" 5.679 KB 100 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dpdumps/asa.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 20:02:09
Root cause of this error is missing OLAP components. But on standard editoin this isn't an option at all. One solution is to remove the OLAP related components as mentioned in a related post.
This may not be applicable in this case as the database was created with custom template option and does not have the OLAP components at all (not even in invalid state).
Solution is to remove the DBMS_CUBE_EXP package. First create a backup of the package and then delete it. It's also advised to take a full backup of the database before running below sql statements
SQL> create table sys.exppkgact$_backup as select * from sys.exppkgact$; Table created. SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS'; 1 row deleted. SQL> commit;This seem to resolve the issue as afterwards export doesn't show the error
expdp asanga/asa directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga Export: Release 11.2.0.3.0 - Production on Tue Jun 12 20:03:52 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production Starting "ASANGA"."SYS_EXPORT_SCHEMA_01": asanga/******** directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS . . exported "ASANGA"."X" 5.679 KB 100 rows Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is: /home/oracle/dpdumps/asa.dmp Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:04:01Update 13 June 2012
It turns out with the above solution would only eliminate exports taken from the same edition database (standard edition). Even after executing the above delete statement issue was still there when the export was done on a enterprise edition and import was done in a standard edition database. In this case export was done on a 11gR1 (11.1.0.7) enterprise edition database and import was done 11gR2 (11.2.0.3) standard edition database. Import statement still had following error messages
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 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_END' must be declared ORA-06550: line 3, column 1: PL/SQL: Statement ignored Failing sql is: BEGIN dbms_cube_exp.schema_info_imp_beg(0, '11.01.00.00.00'); dbms_cube_exp.schema_info_imp_end(0, '11.01.00.0 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC ... ... ... 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 ORA-06550: line 8, col Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 18 error(s) at 13:29:50To resolve this case the export must be done with version=10.2 (as per 789549.1) or if network link is used for import then specify the version in the impdp statement.
Following is the output with and without version=10.2 and using a network link for import.
First without version=10.2
impdp asanga/asa directory=dumpdir network_link=remoteloc logfile=imp.log schemas=asanga Import: Release 11.2.0.3.0 - Production on Wed Jun 13 13:50:52 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options Starting "ASANGA"."SYS_IMPORT_SCHEMA_01": asanga/******** directory=dumpdir network_link=remoteloc logfile=imp.log schemas=asanga Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 13.75 MB Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"ASANGA" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 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_END' must be declared ORA-06550: line 3, column 1: PL/SQL: Statement ignored Failing sql is: BEGIN dbms_cube_exp.schema_info_imp_beg(0, '11.01.00.00.00'); dbms_cube_exp.schema_info_imp_end(0, '11.01.00.0 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS" already exists ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS_TAB" already exists Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS" already exists ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS_TAB" already exists Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "ASANGA"."IT_RS2" 300000 rowsWith version=10.2
impdp asanga/asa directory=dumpdir network_link=remoteloc logfile=imp.log version=10.2 schemas=asanga Import: Release 11.2.0.3.0 - Production on Wed Jun 13 13:41:57 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options Starting "ASANGA"."SYS_IMPORT_SCHEMA_01": asanga/******** directory=dumpdir network_link=remoteloc logfile=imp.log version=10.2 schemas=asanga Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 13.75 MB Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"ASANGA" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS" already exists ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS_TAB" already exists Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "ASANGA"."IT_RS2" 300000 rowsNo errors encountered when version=10.2 option is used.
Related metalink notes
Ora-39127: Unexpected Error From Call To Export_string :=Sys.Dbms_cube_exp [ID 811934.1]
DataPump Schema Export (EXPDP) Fails With Errors ORA-39127 And ORA-37111 [ID 852794.1]
DataPump Import (IMPDP) Receives The Errors ORA-39083 PLS-201 identifier DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG [ID 789549.1]
Data Import for SY910 Fails with Error "PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared" During Platform Pack for Install or Upgrade to EnterpriseOne 9.1 [ID 1457747.1]
Related post : dbms_cube_exp.schema_info_imp_beg error while importing with impdp
