Sunday, June 21, 2009

ORA-600 [ktadrprc-1]

During my visit to the one of the customer, I found following error while dropping old partition from partitioned table.

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

When we search metalink, it was found that this error can occur when there is mismatch between seg$ and ind$ entries. There was a mismatch because of an index entry with the no-segment. This is the feature introduced by oracle to test the index selectivity without creating an index. Following is the test case for the same –

Create a partitioned table



SQL> create table partt (n number, dt date, v varchar2(20)) partition by range (dt)
2 (
3 partition p1 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),
4 partition p2 values less than (to_date('02-JAN-2009','DD-MON-YYYY')),
5 partition p3 values less than (to_date('03-JAN-2009','DD-MON-YYYY'))
6 );

Table created.





Create one normal index and one nosegment index.



SQL> create index indpart on partt(n) local;

Index created.

SQL> create index indpartx on partt(v) local nosegment;

Index created.



Note that, virtual index entry cannot be found in dba_indexes but is available in dba_ind_columns.



SQL> select owner, index_name, table_name from dba_indexes where table_name='PARTT';

OWNER INDEX_NAME TABLE_NAME
---------- ------------------------------ ------------------------------
SYS INDPART PARTT

1 row selected.

SQL> select index_owner, index_name, table_owner, table_name, column_name
2 from dba_ind_columns where table_name='PARTT';

INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
------------ ------------ ------------ ------------ ------------
SYS INDPARTX SYS PARTT V
SYS INDPART SYS PARTT N

2 rows selected.





Now, when we try to drop one of the partitions, we will get the error.



SQL> alter table partt drop partition p1;
alter table partt drop partition p1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], []



Solution:

To resolve this error, when we drop the virtual index, the partition got dropped.



SQL> drop index indpartx;

Index dropped.

SQL> alter table partt drop partition p1;

Table altered.


Note :

The solution given above is valid for 10gR2 (tested) version. For previous versions (Oracle 8i and 9i), even dropping an index will give following ORA-600 error.



SQL> drop index indpartx;

drop index indpartx
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [4823], [], [], [], [], [], [], []


Whereas in 8.1.7.4 version while dropping partition, you may get following error –


SQL> alter table partt drop partition p1;
alter table partt drop partition p1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [4610], [], [], [], [], [], [], []



Conclusion:

Be aware of the creating virtual indexes on partitioned tables just for testing purpose. If it is very big production table, you may end up with unwanted troubles.

No comments: