ARHLSTG1.SQL and recyclebin

During the application of a merged functional patch which had FIN_PF.G, PJ_PF.M and 300 other patches, worker running arhlstg1.sql failed:

FAILED: file arhlstg1.sql on worker 4.

Examining the worker log showed this error:

Start time for file is: Thu Jul 03 2008 16:33:09

sqlplus -s APPS/***** @$AR_TOP/patch/115/sql/arhlstg1.sql &un_ar
DECLARE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at line 306

A search on metalink revealed bug 6762542, which advises dropping the tables created by arhlstg1.sql

Instead of dropping the tables, we first dropped the synonyms in APPS with the same name as the tables:

DROP SYNONYM APPS.HZ_IMP_PARTIES_SG;
DROP SYNONYM APPS.HZ_IMP_ADDRESSES_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTPTS_SG;
DROP SYNONYM APPS.HZ_IMP_CREDITRTNGS_SG;
DROP SYNONYM APPS.HZ_IMP_CLASSIFICS_SG;
DROP SYNONYM APPS.HZ_IMP_FINREPORTS_SG;
DROP SYNONYM APPS.HZ_IMP_FINNUMBERS_SG;
DROP SYNONYM APPS.HZ_IMP_RELSHIPS_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTS_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTROLES_SG;
DROP SYNONYM APPS.HZ_IMP_ADDRESSUSES_SG;

Restarted the worker but the error persisted. Then we checked if these tables had any data:

SELECT COUNT(*) FROM AR.HZ_IMP_PARTIES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_ADDRESSES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTPTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CREDITRTNGS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CLASSIFICS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_FINREPORTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_FINNUMBERS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_RELSHIPS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTROLES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_ADDRESSUSES_SG

All of them returned zero rows except HZ_IMP_CONTACTPTS_SG

select count(*) from AR.HZ_IMP_CONTACTPTS_SG returned

ORA-00600: internal error code, arguments: [20445], [6892510], [6901726], [], [], [], [], []

Trying to drop table AR.HZ_IMP_CONTACTPTS_SG returned

ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []

A seach through ORA-600 lookup tool in metalink showed up metalink note 456974.1 which advises:

Symptoms

You encounter this error while running a query against a partitioned table.:

ORA-00600: internal error code, arguments: [20445], [222112], [417107], [], [], [], [], []

If you search your trace file, you may find some of these codes.:

kkdl1ck kkdlack kkmfcbbt kkmfcblo kkmpfcb qcsprfro

Changes

This may be triggered by moving datafiles or making other changes to your database.

Cause

This is caused by unpublished Bug 3738431.

Solution

Unfortunately, there is currently no fix available for this bug.

However, the following workaround should resolve the issue.:

1. purge recyclebin;

2. Bounce the database and retry the statement again.

If above does not help, then try

3. Set init.ora parameter "_recyclebin" = false, bounce the database and drop
the table.

References

@ Bug 3738431 - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [20445], [43290] IN OBJECT EVOLUTION

So we gave the command
SQL> purge recyclebin;
SQL> shutdown immediate;
SQL> startup

Once the database came up, we could do a select coun(*) on the table without triggering ORA-600. So we just resumed the patch, which was able to successfully drop the tables without failing.

Metalink Note 265253.1 describes 10g recyclebin features and methods to disable it:

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.


There is no issues with DROPping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.

When we drop a tablespace or a user there is NO recycling of the objects.

o Recyclebin does not work for SYS objects