Home > DBA > Encountered Oracle Bug 6392040 while doing exp of table with XMLTYPE column

Encountered Oracle Bug 6392040 while doing exp of table with XMLTYPE column

 

You know when you have a table with XMLTYPE column types then you can use expdp but then the impdb will not succeed if the database is not Oracle 11g(e.g 10.2 in my case).  Data Pump Export and Data Pump Import utilities do not support XMLType data in this case.  So we need to use exp and imp utilities. Go on and export the table with the XMLTYPE column with exp and here is what we get:

 

C:\export>exp system/psw@SID FILE=exp_xmldata.dmp TABLES=TEST
TEMPLATES LOG=exp_xmldata.log statistics=none

Export: Release 10.2.0.3.0 – Production on TЄ -хъ. 7 10:39:06 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 15:
PLS-00201: identifier ‘SYS.DBMS_EXPORT_EXTENSION’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

 

We can check for invalid objects and recompile mannually if it is the case

 

SELECT OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS =
‘INVALID’ AND OWNER = ‘SYS’;
ALTER PACKAGE SYS.DBMS_EXPORT_EXTENSION COMPILE BODY;

 

In my case this was not. The workaround for this is to:

 

GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO system;

 

And then it worked and the export finished successfully.

 

C:\export>exp system/psw@SID FILE=exp_xmldata.dmp TABLES=TEST
TEMPLATES LOG=exp_xmldata.log statistics=none

Export: Release 10.2.0.3.0 – Production on TЄ -хъ. 7 10:40:07 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in CL8MSWIN1251 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
Current user changed to RBANK
. . exporting table                      TEMPLATES          10 rows exported
Export terminated successfully without warnings.

 

This is an Oracle Bug 6392040 and you can check Metalink 464672.1.  The cause is:
“There are two execute privileges missing in the DBA role and  the EXP_FULL_DATABASE role.
There is no fix in this bug because the export utility is not longer supported in 11g and should be replaced by the Data Pump Export.”

 
Cheers,
Maria

 

 

Advertisements
Categories: DBA Tags:
  1. No comments yet.
  1. No trackbacks yet.

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: