pat98's always & forever

Symptoms

The following errors are reported in the Export log file:

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

Recent Changes to this Environment:
    Revoked the execute privilege on UTL_FILE package from Public.

Cause

The issue is caused by the revoke of the execute privilege on UTL_FILE package from Public.

Oracle Workspace Manager 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.

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 may be using.

Solution

To implement the solution, please execute the following steps:

1. if it's necessary to keep the "Revoke execute" on UTL_FILE package, it's available the following
    workaround:

    - Grant execute on SYS.UTL_FILE to WMSYS
    - Lock account WMSYS (if it is not done before)
    - Run script $ORACLE_HOME/rdbms/admin/utlrp.sql

   As this workaround is different from the default settings, there is no guarantees that it works all the time.


2. check for:
    - invalid objects in the WMSYS schema with the statement
       SQL> select object_name,object_type,owner,status from dba_objects where status='INVALID' and
                  owner='WMSYS';

    - errors on the export