Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Export Full=y fails with PLS-00201 ORA-06510 ORA-06512

Export Full=y fails with PLS-00201 ORA-06510 ORA-06512

Problem Description
In the Oracle export with FULL=Y option fails with error PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared. From the log file error stack is shown below.

Table DEF$_AQCALL will be exported in conventional path.

. . exporting table DEF$_AQCALL

EXP-00008: ORACLE error 6550 encountered

ORA-06550: line 1, column 18:

PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204

ORA-06512: at “SYS.DBMS_SQL”, line 323

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 97

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

Table DEF$_AQERROR will be exported in conventional path.

. . exporting table DEF$_AQERROR

EXP-00008: ORACLE error 6510 encountered

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 50

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

. . exporting table DEF$_CALLDEST

EXP-00008: ORACLE error 6550 encountered

ORA-06550: line 1, column 18:

PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204

ORA-06512: at “SYS.DBMS_SQL”, line 323

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 97

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

. . exporting table DEF$_DEFAULTDEST

EXP-00008: ORACLE error 6510 encountered

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 50

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

. . exporting table DEF$_DESTINATION

EXP-00008: ORACLE error 6550 encountered

ORA-06550: line 1, column 18:

PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204

ORA-06512: at “SYS.DBMS_SQL”, line 323
Cause of the Problem
The error occurred during full database export operation because the user who is performing export operation does not have execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION. Though the user might be a dba or sysdba user but it need explicit privilege to these two packages.

Solution of the Problem

Note that the role IMP_FULL_DATABASE, this is not enough to export FULL database schema in this case. So, it’s necessary to grant the execute right over this package directly to the user and not through a role.

The solution is,
1) Connect to database as sys user.
SQL> conn / as sysdba

2) Explicitly grant the execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION to the user performing the export as shown below.

SQL> GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO <user_name>;

SQL> GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO <user_name>;

3) Run the export operation again.

January 6, 2010 Posted by | oracle | 3 Comments