DBA_INDEXES shows no index but Create Index errors out with ORA-01408: such column list already indexed

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 SYS@matrix1 > @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 SYS@matrix1 > 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 SIEBEL@matrix1 > 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 SIEBEL@matrix1 > 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

anand@MATRIX> select * from recyclebin;

no rows selected
anand@MATRIX> @index
Enter value for owner: anand
Enter value for table_name: bang

no rows selected

anand@MATRIX> create index band_obj_id_indx on bang(object_id);

Index created.

anand@MATRIX> @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

anand@MATRIX> drop index BAND_OBJ_ID_INDX;

Index dropped.

anand@MATRIX> select * from recyclebin;

no rows selected

anand@MATRIX>

If we drop the table and flashback it, all the indexes and triggers will be restored too but with BINxxxx naming convention.


anand@MATRIX> @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

anand@MATRIX> @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

anand@MATRIX> select * from recyclebin;

no rows selected

anand@MATRIX> drop table bang;

Table dropped.

anand@MATRIX> 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
anand@MATRIX> flashback table bang to before drop;

Flashback complete.

anand@MATRIX> select * from recyclebin;

no rows selected

anand@MATRIX> @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

anand@MATRIX> @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

anand@MATRIX>

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!!! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s