1 Reply Latest reply: Mar 18, 2013 9:28 AM by viconstg RSS

    LOB index without LOB column

    viconstg
      Hello,

      I have the problem with one of my database. First of all:
      SQL> select * from v$version;
      
      BANNER
      ------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      I have the lob-index which have segments but don't listed in dba_lobs:
      SQL> select index_name, index_type, table_name from dba_indexes where owner = 'PG' and index_type = 'LOB';
      
      INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
      ------------------------------ --------------------------- ------------------------------
      SYS_IL0000273644C00010$$       LOB                         TORDERS
      
      SQL> select * from dba_lobs where owner = 'PG';
      
      no rows selected
      
      SQL> select * from dba_lobs where INDEX_NAME='SYS_IL0000273644C00010$$';
      
      no rows selected
      
      SQL> select column_name, data_type from dba_tab_columns where owner = 'PG' and table_name = 'TORDERS' and data_type like '%LOB%';
      
      no rows selected
      Moreover, this index is "alive": the base table is interval-partitioned, and new partitions created each day for the table and for the LOB index.

      I've found in this forum one similar request -- Lob index in dba_segments but not in dba_lobs but it provide no solution. Please help me: is it possible to drop such a "lost" LOB index?

      Thank you in advance,
      Constantine