Pages

Sunday, May 1, 2016

Error 'PLS-00801: internal error [1401]' received when recompiling packages

Error 'PLS-00801: internal error [1401]' received when recompiling packages

Error:

SQL> alter package OE_DEFAULT_LINE_PATTR compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY OE_DEFAULT_LINE_PATTR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [1401]
0/0 PLS-00801: internal error [1401]
0/0 PLS-00801: internal error [1401]
0/0 PLS-00801: internal error [1401]
0/0 PLS-00801: internal error [1401]
1604/17 PL/SQL: Statement ignored
 -- Steps To Reproduce:
1. Open database SQL*Plus session
2. Run the below command to recompile package

alter package OE_DEFAULT_LINE_PATTR compile body;

Cause:

This issue is caused by inconsistencies in underlying application codelines and binaries as a result of recent patching and (potential) database upgrades.

Solution:

To implement the solution, please execute the following steps:

1. Review the comments in the 2 scripts below:

$ORACLE_HOME/rdbms/admin/utlirp.sql
$ORACLE_HOME/rdbms/admin/utlrp.sql

NOTE: Please note the requirements under "USAGE"to restart the database in UPGADE-mode before running script.

NAME
utlirp.sql - UTiLity script to Invalidate Pl/sql modules

DESCRIPTION
This script can be used to invalidate and all pl/sql modules
(procedures, functions, packages, types, triggers, views)
in a database.

This script must be run when it is necessary to regenerate the
compiled code because the PL/SQL code format is inconsistent with
the Oracle executable (e.g., when migrating a 32 bit database to
a 64 bit database or vice-versa).

Please note that this script does not recompile invalid objects
automatically. You must restart the database and explicitly invoke
utlrp.sql to recompile invalid objects.

USAGE
To use this script, execute the following sequence of actions:
1. Shut down the database and restart in UPGRADE mode
(using STARTUP UPGRADE or ALTER DATABASE OPEN UPGRADE)
2. Run this script
3. Shut down the database and restart in normal mode
4. Run utlrp.sql to recompile invalid objects. This script does
not automatically recompile invalid objects.

NOTES
* This script must be run using SQL*PLUS.
* You must be connected AS SYSDBA to run this script.
* This script expects the following files to be available in the
current directory:
standard.sql
dbmsstdx.sql
* There should be no other DDL on the database while running the
script. Not following this recommendation may lead to deadlocks.


NAME
utlrp.sql - Recompile invalid objects

DESCRIPTION
This script recompiles invalid objects in the database.

When run as one of the last steps during upgrade or downgrade,
this script will validate all remaining invalid objects. It will
also run a component validation procedure for each component in
the database. See the README notes for your current release and
the Oracle Database Upgrade book for more information about
using utlrp.sql

Although invalid objects are automatically re-validated when used,
it is useful to run this script after an upgrade or downgrade and
after applying a patch. This minimizes latencies caused by
on-demand recompilation. Oracle strongly recommends running this
script after upgrades, downgrades and patches.

2. Run these scripts using SQL*Plus as SYSDBA

3. Review your invalid listing for resolution

Reference metalink Doc ID 949222.1

No comments:

Post a Comment