Yesterday we had a very interesting issue popped up on 10.2.0.5 database where the team running a sql script got “ORA-01408: such column list already indexed” though the dba_indexes showed no index on the table.The index to be created was “test_u1”
23:11:01 [email protected] > @index Enter value for owner: anand Enter value for table_name: TEST no rows selected @index script --> SELECT table_owner,table_name,owner AS index_owner,index_name,tablespace_name,num_rowsclustering_factor clust,status,index_typeFROM dba_indexes WHERE table_owner = UPPER('&owner')AND table_name = UPPER('&table_name') ORDER BY table_owner, table_name, index_owner, index_name; To verify again 23:11:58 [email protected] > select owner,object_name,object_type,status from dba_objects where object_name='TEST_U1'; no rows selected
So, dba_indexes showed no index but the team was getting ORA-01408. Asked them to check for the connectivity,if they had connected to the correct database and they replied yes.Strange!!!!!!
Started digging some more and as the error said column list already indexes, checked dba_ind_columns-
23:47:40 [email protected] > select table_name,index_name,column_name,column_position from dba_ind_columns where column_name like '%ROW_ID%' and table_name='TEST'; TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------- -------------------- --------------- TEST BIN$qTQK8doMsJngQFSM4+RAgg==$0 R_ROW_ID 1
Checked the recylebin and it showed having the index –
23:36:23 [email protected] > select * from recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE ---------------------------------------- -------------------------------- --------- ------------------------- TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED ------------------------------ ------------------- ------------------- ---------- -------------------------------- --- --- ---------- BASE_OBJECT PURGE_OBJECT SPACE ----------- ------------ ---------- BIN$qTQK8doMsJngQFSM4+RAgg==$0 TEST_U1 DROP INDEX TEST_IDX_01 2007-03-04:14:13:51 2011-07-29:05:49:59 1.2275E+13 NO YES 54403 54403 64252 1664
Purged the recyclebin and asked the team to run the script. This time the create index failed with
ORA-00600: internal error code, arguments: [kcbnew_3], [0], [8], [1102432], [], [], [], []
Did some google search, and found it to be a bug on 10.2.0.4, but still we faced it on 10.2.0.5.
A cache buffer holding a database block is in the process of being reused.The buffer is in state “current” and may be reused only if the object is of type temp or undo.The consistency check comparing the block class in the buffer header with the block class passed to the cache by the caller is failing.
Flushed the buffer cache on all the nodes as re-ran the script and this time all went fine and index got created.
alter system flush buffer_cache;
But still i am not able to understand “How/Why did we have only index in the recyclebin?”Tried few stuff but still can’t figure out why only the index was present is the recyclebin. Dropping only an index, doesn’t put in in the recyclebin
[email protected]> select * from recyclebin; no rows selected [email protected]> @index Enter value for owner: anand Enter value for table_name: bang no rows selected [email protected]> create index band_obj_id_indx on bang(object_id); Index created. [email protected]> @index Enter value for owner: anand Enter value for table_name: bang TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME TABLESPACE_NAME NUM_ROWS CLUST STATUS INDEX_TYPE -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- ---------- -------- ----------- ANAND BANG ANAND BAND_OBJ_ID_INDX TEST 1153648 1153648 VALID NORMAL [email protected]> drop index BAND_OBJ_ID_INDX; Index dropped. a[email protected]> select * from recyclebin; no rows selected [email protected]>
If we drop the table and flashback it, all the indexes and triggers will be restored too but with BINxxxx naming convention.
[email protected]> @table_info Enter value for table_name: bang TABLE_NAME OWNER TABLESPACE_NAME NUM_ROWS LAST_ANAL AVG_ROW_LEN BLOCKS EMPTY_BLOCKS ------------------------------ ------------------------------ -------------------- ---------- --------- ----------- ---------- ------------ BANG ANAND TEST 1153648 15-OCT-11 97 17263 0 [email protected]> @index Enter value for owner: anand Enter value for table_name: bang TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME TABLESPACE_NAME NUM_ROWS CLUST STATUS INDEX_TYPE -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- ---------- -------- ------------ ANAND BANG ANAND BAND_OBJ_ID_INDX TEST 1153648 1153648 VALID NORMAL [email protected]> select * from recyclebin; no rows selected [email protected]> drop table bang; Table dropped. [email protected]> select * from recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE -------------------------------- --- --- ---------- ----------- ------------ ---------- BIN$mBvbmkMiRw+iHskuRyPIMQ==$0 BAND_OBJ_ID_INDX DROP INDEX TEST 2011-10-17:15:02:34 2011-10-17:16:00:26 3541779 NO YES 77311 77311 77430 2688 BIN$nLMd1KIkQKWVeJxUrajZfA==$0 BANG DROP TABLE TEST 2011-10-15:16:20:53 2011-10-17:16:00:26 3541783 YES YES 77311 77311 77311 17408 [email protected]> flashback table bang to before drop; Flashback complete. [email protected]> select * from recyclebin; no rows selected [email protected]> @table_info Enter value for table_name: bang TABLE_NAME OWNER TABLESPACE_NAME NUM_ROWS LAST_ANAL AVG_ROW_LEN BLOCKS EMPTY_BLOCKS ------------------------------ ------------------------------ -------------------- ---------- --------- ----------- ---------- ------------ BANG ANAND TEST 1153648 15-OCT-11 97 17263 0 [email protected]> @index Enter value for owner: anand Enter value for table_name: bang TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME TABLESPACE_NAME NUM_ROWS CLUST STATUS INDEX_TYPE -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- ---------- -------- ------------ ANAND BANG ANAND BIN$mBvbmkMiRw+iHskuRyPIMQ==$0 TEST 1153648 1153648 VALID NORMAL [email protected]>
So dba_indexes do show the index but with BIN$xxxx name. So still now i am not able to reproduce the issue where index is left over in recyclebin.Any suggestion would be great!!! 🙂