Archive

Posts Tagged ‘bug’

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

December 7, 2010 Leave a comment

 

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

 

 

Categories: DBA Tags:
%d bloggers like this: