Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted December 17, 2015

Oracle's utlrp.sql Script and Edition-based Redefinition

By David Fitzjarrell

Edition-based Redefinition can be a real asset when modifying existing application code such as procedures, packages and functions but it can also throw a monkey wrench into the works when invalid objects rear their ugly heads, as ORA-00600 errors can be thrown in Oracle release

ERROR at line 1:
ORA-4045: errors during recompilation/revalidation of ...
ORA-600: internal error code, arguments: [17016], [0x1BD9EC6A0], [], [],[],[], [], [], [], [], [], []
ORA-6512: at "SYS.DBMS_UTILITY", line 1294
ORA-6512: at line 1

The customer reporting this has gone through the following steps:

Upgrade Oracle applications
Upgrade Oracle database
Upgrade third-party application which uses Edition-based Redefinition

The steps to upgrade the Oracle applications and the database complete without error; the problem arises in the upgrade of the third-party application where the cleanup process (which identifies and recompiles invalid objects) fails with the above-mentioned error. The utlrp script was then used to validate the invalid objects, which returned the same error.

Although the error was reported for Linux x86-64 it has been determined this is a generic error that can affect any port of Oracle. A similar issue plagued Oracle releases using Edition-based Redefinition (Bug 12950694) but this has been listed as fixed, the only known issue to affect utlrp with respect to Edition-based Redefinition. An interesting point to consider is that the UTL_RECOMP package has not been reported as invalid. Another interesting piece is that from the SQL*Plus command line objects that throw this error from utlrp.sql recompile successfully. From logs generated when the error occurs the offending statement is an ALTER TYPE for an Oracle-generated type name. For releases not using Edition-based Redefinition no such errors have been reported.

One possibility is the existence of more than one type with the same name, yet querying the database throwing this error proves only one record is returned when querying against the object name. Another problem with this issue is that existing editions cannot be pushed into production. So, even though the objects can be validated from the SQL*Plus command line the edition changes cannot be moved into production.

Oracle Support has no work-around for this, and the customer who reported the problem can always reproduce the issue. Oracle Support cannot generate the error and no test case is available, which makes it difficult, if not impossible, to investigate. Since no other reports of this behavior have been submitted to Oracle Support it's possible that this is a one-off issue related to either the client's configuration or relating to the third-party application they are using (this application has not been identified in the bug report, and no indication of any communication with the application vendor was supplied to Oracle Support). The incident file from the customer reports:

Starting incident default dumps (flags=0x2, level=3,mask=0x0)[TOC00003]
----- Current SQL Statement for this session (sql_id=c19hkha8n9qcy) -----
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object handle     line number  object name
0x1befdc068              1294  package body SYS.DBMS_UTILITY
0x1b3bcfb48                 1  anonymous block
0x1b3f6f268               413  package body SYS.UTL_RECOMP
0x1b3f6f268               559  package body SYS.UTL_RECOMP
0x1b3f6f268               807  package body SYS.UTL_RECOMP
0x1bee3c3f0                 4  anonymous block

Only the third-party cleanup process or utlrp generate this error and call stack trace. Even when the object is listed as valid (through a command-line recompilation) this error is thrown at the customer site. Again, as of now no reproducible test case can be created and the error cannot be duplicated by Oracle Support.

This was reported toward the end of November of this year so I suspect that Oracle Support is still digging into the issue to see what may be causing the problem. Since it's a third-party application that is causing the issue (from all current reports) it may be a problem the vendor needs to address. For at least one customer this has been a show stopper, and they are in a bind until a resolution can be found.

Remember that the Oracle applications upgrade and the Oracle database upgrade were successful and error-free, so using Edition-based Redefinition doesn't seem to be an issue. Unfortunately for the customer involved this is a production system; a properly configured test environment, including the third-party application, may have revealed this issue before it affected the production environment and the customer could still conduct business while the issue was being investigated and, hopefully, resolved. This is one of the best arguments for having a test environment that mirrors production. As can be seen from this case having such an environment can be invaluable, which makes it definitely worth the investment.

See all articles by David Fitzjarrell

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM