10g_export_errors

[divroro12]

Hi DBAs,

I'm trying to export a schema from my 10g RAC database, but i'm getting a whole lot of errors. Infact, the same script i used to export the same schema from a 9i database successfully is what i'm using, as shown below:

> exp system/xxxxxx@labtest1 file=/u02/epiphany_sys.dmp owner=epiphany_sys_meta_data log=/u02/epiphany_sys.log statistics=none buffer=80000 consistent=y

The errors i'm getting are:

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 US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4067 encountered
ORA-04067: not executed, package body "SYS.LT_EXPORT_PKG" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
. exporting foreign function library names for user EPIPHANY_SYS_META_DATA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EPIPHANY_SYS_META_DATA
About to export EPIPHANY_SYS_META_DATA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

Does anyone have an idea of what's going on? Your help will be highly appreciated.

Thanks,

[PAVB]

Here is what I would do...

1- Log as SYS and check if package SYS.LT_EXPORT_PKG exists.
2- If package is there check if package SYS.LT_EXPORT_PKG is VALID, if not then compile it.
3- If package is there and it's VALID logout, log into the system as SYSTEM and check if offending package SYS.LT_EXPORT_PKG is visible.

Please post your findings.

[divroro12]

Hi PAVB,

As SYS,

SQL> select owner,object_name,status
2 from dba_objects
3 where OWNER = 'SYS' and OBJECT_NAME = 'LT_EXPORT_PKG';

OWNER OBJECT_NAME STATUS
---------- --------------- -------
SYS LT_EXPORT_PKG VALID

As SYSTEM,

SQL> select owner,object_name,status
2 from dba_objects
3 where OWNER = 'SYSTEM' and OBJECT_NAME = 'LT_EXPORT_PKG';

no rows selected

[PAVB]

As SYSTEM,

SQL> select owner,object_name,status
2 from dba_objects
3 where OWNER = 'SYSTEM' and OBJECT_NAME = 'LT_EXPORT_PKG';

no rows selected

Your first query shows the package is alive and kicking, which is good.
Your second query is wrong, log as SYSTEM but try to find SYS.LT_EXPORT_PKG as opposed to SYSTEM.LT_EXPORT_PKG

[divroro12]

SQL> select owner,object_name,status
2 from dba_objects
3 where OWNER = 'SYS' and OBJECT_NAME = 'SYS.LT_EXPORT_PKG';

no rows selected

[PAVB]

no rows selected

That means user SYSTEM has no rights on that SYS owned package.
Log into the system as SYS and grant execute privs on the SYS owned package to SYSTEM.

[divroro12]

SQL> connect sys / as sysdba
Enter password:
Connected.
SQL> grant execute on LT_EXPORT_PKG to system;

Grant succeeded.
SQL> connect system/xxxxxxxx
Connected.

SQL> select owner,object_name,status
2 from dba_objects
3 where OWNER = 'SYS' and OBJECT_NAME = 'SYS.LT_EXPORT_PKG';

no rows selected

[davey23uk]

the object_name is LT_EXPORT_PACKAGE not SYS.LT_EXPORT_PACKAGE

[divroro12]

SQL> show user
USER is "SYSTEM"
SQL> select owner,object_name,status
2 from dba_objects
3 where OWNER = 'SYS' and OBJECT_NAME = 'LT_EXPORT_PKG';

OWNER OBJECT_NAME STATUS
---------- --------------- ----------
SYS LT_EXPORT_PKG VALID
SYS LT_EXPORT_PKG VALID

Now, when i re-run the export, i get these errors:

Export: Release 10.2.0.3.0 - Production on Mon May 5 11:23:29 2008

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 US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user EPIPHANY_SYS_META_DATA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EPIPHANY_SYS_META_DATA
About to export EPIPHANY_SYS_META_DATA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

[PAVB]

Glad we solved your original issue... you are very welcome.

Oops... my fault, you didn't say "Thank you"; you are not a polite person, that's baaaaad.

So... you are facing a new issue? what kind of research have you done so far?