1 Reply Latest reply on Mar 18, 2013 2:28 PM by viconstg

    LOB index without LOB column


      I have the problem with one of my database. First of all:
      SQL> select * from v$version;
      Oracle Database 11g Enterprise Edition Release - 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,