Bug 7581347

I currently support 44 Oracle instances on 40 different servers and that number is growing as new projects come along. The majority of these are 10gR2. A few are 11gR1 and one is 11gR2. We will be moving most of them if not all of them to 11g this calendar year. Yesterday one of the 10.2.0.4 instances residing on 64bit Red Hat Linux apparently hit a combination that opened a can of worms that I identified as Bug 7581347.

Here is the summary of the event:

Enduser was attempting to create a temp table in a process.

Step 15: SQL> create table tmp_somename_tb_12345 as select ...
Step 15: create table tmp_somename_tb_12345 as select Step 15: * Step 15: ERROR at line 1:
Step 15: ORA-00955: name is already used by an existing object

ERROR at line 1:
Step 15: ORA-38301: can not perform DDL/DML over objects in Recycle Bin

First I had them try this statement:

drop table purge;

Logged in as the table owner in question and of course replacing the with the table_name that was dropped and it now generating the error.

Result: Same errors generated.

Then I had them have the table_owner issue a:

purge user_recyclebin;

Note (That if this is issued all the tables that are in that users recycle_bin will be purged and therefore no longer useable for undo).

After purging the user recycle bin and reissuing the statements they got the following errors:

ORA-00600: internal error code, arguments: [ktagetg0-1], [], [], [], [], [], [], []

This ora600 is pointing (as you might expect) toward: (space/spcmgmt support for DML locks and temporary tables associated with table access). And it stands for “Kernel Transaction Access Get Generic DML lock.

This is a bug (7581347). It is fixed in 10.2.0.5. The only workaround given by Oracle was: “Do not drop objects whilst DML is using them.” Not to impressed with that one.

The row cache entry for the object seems to have been corrupted. There were only two things short of an upgrade that I could think of at that time:

1) Flushing the shared pool. (Note: that there may be somewhat of a temporary negative impact on performance when you flush all the SQL from the shared pool).

And if that does not work then:

2) Restart the database.

The idea here, being to try and clear out the row cache.

We ended up having to bounce the instance. And, keeping our fingers crossed, so far haven’t seen it again… yet.

Thx