Saturday, February 27, 2010

EXP-00008, ORA-06550, PLS-00201, EXP-00083 identifier 'SYS.LT_EXPORT_PKG' must be declared

Problem Description
When I try to do full database export it fails will following errors as below.
exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.LT_EXPORT_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
. exporting statistics
.
Cause of the Problem
The above problem happened because the Workspace Manager component is not there. You can check from querying db_registry as below.
SQL> select comp_id, version, status from dba_registry is giving the following info :

COMP_ID VERSION STATUS
------------------------------ ------------------------------ -----------
CATPROC 10.1.0.2.0 VALID
RAC 10.1.0.2.0 INVALID
CATALOG 10.1.0.2.0 VALID
XDB 10.1.0.2.0 VALID

So we see that OWM is not installed. May be WMSYS user has been dropped.
The SYS.LT_EXPORT_PKG is created by the Oracle Workspace Manager (OWM).

OWM (Oracle Worspace Manager) is now closely integrated with much of the functionality of Oracle, especially the export. The export process looks for any "version-enabled" tables that Workspace Manager may be using. The WMSYS user is used to store all the metadata information for OWM and it is recommended to not remove the OWM user.

Solution of the Problem
The error stack "EXP-00008, ORA-06550, PLS-00201, EXP-00083 identifier 'SYS.LT_EXPORT_PKG' must be declared" are just an informational messages only and you can avoid those. The export utility is throwing warnings and then proceeding ahead. The export does not error out or stop abruptly.

Note that, the versioning feature has been closely incorporated into the export utility and hence it checks the versioning repository for such version enabled tables. But as the versioning metadata information is not found (as the WMSYS user holding this metadata has been dropped), it throws this informational messages.

If you really do not bother with the Oracle Workspace Manager component, just ignore the
message. But in case you want to eliminate this warning messages as well, you can either deinstall the Oracle Workspace Manager or re-install it.

Steps to Recreate WMSYS user(Oracle Workspace Manager)

1) Execute $ORACLE_HOME/rdbms/admin/owminst.plb as SYS user from SQL*PLUS prompt.
- $sqlplus / as sysdba
- SQL> @$ORACLE_HOME/rdbms/admin/owminst.plb


Steps to uninstall Oracle Workspace Manager (OWM)
1) Disable versioning on all version-enabled tables in the database before de-installing Oracle Workspace Manager.

2) Login to SQL*Plus as SYSDBA, invoke the owmuinst.plb de-installation script as below.
$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/owmuinst.plb


Note that, de-installing Oracle Workspace Manager will remove any existing workspaces and the associated metadata.
Related Documents
http://arjudba.blogspot.com/2010/02/exp-00008-ora-06550-pls-00201-exp-00083.html
http://arjudba.blogspot.com/2010/02/ora-39127-ora-04063-ora-06508-ora-06512.html
http://arjudba.blogspot.com/2010/02/exp-00008-ora-04063-ora-06508-exp-00083.html
http://arjudba.blogspot.com/2009/12/export-fully-fails-with-pls-00201-ora.html
http://arjudba.blogspot.com/2009/12/export-fails-with-exp-00002-error-in.html
http://arjudba.blogspot.com/2009/01/ora-31655-no-data-or-metadata-objects.html
http://arjudba.blogspot.com/2009/01/expdp-fails-with-ora-31693-ora-06502.html
http://arjudba.blogspot.com/2008/12/ora-39095-dump-file-space-has-been.html
http://arjudba.blogspot.com/2008/09/expdp-fails-with-ora-31626-ora-31633.html
http://arjudba.blogspot.com/2008/07/data-pump-export-fails-with-ora-39000.html
http://arjudba.blogspot.com/2009/07/ora-39165-schema-sys-was-not-found-ora.html
http://arjudba.blogspot.com/2009/07/ora-39166-object-was-not-found-sys.html
http://arjudba.blogspot.com/2009/05/ora-39000-ora-39143-dump-file-may-be.html
http://arjudba.blogspot.com/2009/05/expdp-fails-with-ora-39001ora-39169ora.html

No comments:

Post a Comment