ORA-600 [16201] when dropping a procedure

Posted by David Yahalom under Oracle

In certain older versions of Oracle database (such as 8.1.7.4 like this case) you may receive an ORA-600 error when trying to drop or recompile a database PL/SQL package or procedure. Usually when the source is wrapped.

SQL> drop procedure schema.proc_name;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

SQL> create or replace procedure schema.proc_name
as
begin
end;
/

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

This is a documented Oracle bug (No. 2422726). It affects Oracle versions 8.1.7.4, 9.0.1.4, 9.2.0.1 and fixed in Oracle 9.2.0.2 and 10g. However, even if you are running an older version of Oracle there’s a possible workaround available to solve this issue. Note, this is an ugly hack, but it works, and sometimes your only solution for this annoying problem.

The solution is as follows:

1) Connect as SYS to the problematic db.

2) Run the following query to identify the object# of the INVALID object you can’t drop.

SQL> select obj#,owner#,type# from sys.obj$ where name = 'PROC_NAME';

OBJ#     OWNER#      TYPE#
---------- ---------- ----------
1396504          5          7

3) Now try and select the procedure from v$procedure using the OBJ# from the above query.

SQL> select * from procedure$ where obj# in (1396504);

no rows selected

4) You can’t. That’s because of the above mentioned bug.

5) The only way to fix this is to insert a fake row into the procedure$ view to fool Oracle to allow you to successfully drop the procedure.

SQL> insert into procedure$ values (1396504, '-----------------------',NULL,2);

1 row created.

SQL> commit;

Commit complete.

6) Now you can successfully drop the INVALID procedure.

SQL> drop procedure schema.proc_name;

Procedure dropped.

As I said, it’s a hack, so you are better of upgrading your Oracle installation to a version not effected by this bug. But as we all know, many times this is simply not an option. And for those occasions the solution I’ve written here seems to be the only one.

Good luck!