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 »

  1. Very helpful! Thanks a lot!

    Comment by Arkady | August 27, 2010 | Reply

  2. Good ,

    It helped to solve the error mantioned above

    Comment by athil | October 1, 2010 | Reply

  3. Thanks for this message. I just upgrade my Oracle to 10205 and ran into this problem. This surely solved my problem.

    Comment by S Lee | October 29, 2011 | Reply


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: