Oracle in World: 2/21/10

Saturday, February 27, 2010

EXP-00008, ORA-04063, ORA-06508, EXP-00083 PL/SQL: could not find program unit

Problem Description
Oracle full database export fails with error stack EXP-00008, ORA-04063, ORA-06508, EXP-00083 like below.
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.schema_info_exp

Cause of the Problem
The above problems are caused by the revoke of the execute privilege on UTL_FILE package from Public.

Oracle Workspace Manager (OWM) and some other database components (which are not installed in the database) need the privilege to execute procedure SYS.UTL_FILE.

During the creation of a default Database, the Workspace Manager gets installed by default. and the WMSYS user becomes intertwined with the export process and must be valid or at least the major parts for an export to complete properly as the export process looks for any "version-enabled" tables that Workspace Manager schema may be using.

Solution of the Problem
In order to solve above problems do following steps.

1. Grant execute privilege on SYS.UTL_FILE package to WMSYS user.
SQL> grant execute on SYS.UTL_FILE to WMSYS;

2. check for invalid objects in the WMSYS schema using following query,
SQL> select object_name,object_type,owner,status from dba_objects where status='INVALID' and owner='WMSYS';

3. Run script $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

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

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

Friday, February 26, 2010

ORA-39127 ORA-04063 ORA-06508 ORA-06512 package body "WMSYS.LT_EXPORT_PKG" has errors

Problem Description
While doing data pump full database export, in the error log following error messages are generated.

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.system_info_exp(0,dynconnect,10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5334
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.system_info_exp(1,dynconnect,10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5334
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('SYS',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('SYSTEM',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('OUTLN',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('VIEWSTAR',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('EXP_DBA',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('TSMSYS',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('PTASUSER',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('VSUSER',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('PARTDB',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('WORKFLOW',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('QUEUEDB',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('CASCAN',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('DAADMIN',0,1,'10.02.00.02.00',newblock)
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Saturday, February 27, 2010

EXP-00008, ORA-04063, ORA-06508, EXP-00083 PL/SQL: could not find program unit

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

Friday, February 26, 2010

ORA-39127 ORA-04063 ORA-06508 ORA-06512 package body "WMSYS.LT_EXPORT_PKG" has errors

Tag Cloud

Blog Archive

Which Oracle database version you use?