Monday, February 6, 2017

RDBMS -- ORA-00600: internal error code, arguments: [kqlInvObj:user]

Recently, one of my followers encountered  an ORA-00600 and asked me the solution for it using my forum.

The full error was "ORA-00600: internal error code, arguments: [kqlInvObj:user], [104], [], [], []" and it was encountered while issuing a simple create table statement like the one below;

SQL> CREATE TABLE temp_sales
2 ( ID number(25),
3 PNAME varchar2(23),
4 SALE_date date)
5 PARTITION BY RANGE (SALE_date)
6 (
7 PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-jan-2016','dd-MON-yyyy')),
8 PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-feb-2016','dd-MON-yyyy')),
9 PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')));
CREATE TABLE temp_sales

I see a need for sharing this info, because it is not documented in Oracle Support, or let's say it is not properly documented there.

Anyways, the first argument of ORA-600 error is normally the function, where the code is breaking. 

So in this ORA-600, Oracle was getting an exception during "kqlInvObj:user", which is not documented at all.

That's why I thought that this was caused by a bug  and this bug was related with some problematic records in sys.obj$ table.  (The second argument of this error (specific to kqlInvObj:user) is the owner of the object.)

So, the second argument "104" was the owner# which was stored in sys.obj$. 

Using the following query, I found the objects that belong to the user with the user number 104;

select * from sys.obj$ where owner#=104

Actually, I was going to recommend dropping/recreating/recompiling the objects owned by the related database user, but the query returned lots of records, so I made a deeper rearch and found the following bug ;

Bug 10256218 : IMPDP FAILS WITH ORA 600[KQLINVOBJ:USER], [94].

There was a workaround written in the definition of this bug and I instructed my follower to try it (after taking a full backup)

The workaround worked and the problem dissapeared!

The workaround given was the following;

1. SHUTDOWN IMMEDIATE or NORMAL
2. STARTUP RESTRICT
3. Create a new user called PATCH_USER:

create user PATCH_USER identified by p;

4. Update obj$
update sys.obj$
set owner# = (select user#
from sys.user$
where name = 'PATCH_USER')
where owner# in (88,94);   --REPLACE With your numbers

5. COMMIT;
6. Shutdown abort;
7. STARTUP
8. Drop user created in step 3:

drop user PATCH_USER cascade;

Attention: This is too risky! Requires a full backup as well.

No comments :

Post a Comment