Why data pump does not keep LOB index name

From: <Joel.Patterson_at_crowley.com>
Date: Fri, 25 May 2012 15:44:10 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B95B52FB4_at_JAXMSG01.crowley.com>

With Indexes, you can join dba_objects with sys.ind$ to determine if an index name is system generated or not. These two lines work with sys.ind$ -- however lob indexes do not appear to exist in this table. I'm trying to do the same thing with LOB Indexes. A LOB index does not show up in dba_objects, but I was trying kind of the same thing

    decode(bitand(sys.ind$.property,4096), 4096, 'YES', 'NO') SYS_GENERATED,     decode(bitand(sys.ind$.property,1), 1, 'YES', 'NO') unique_index

With LOB indexes, the INDEX is not listed in dba_objects either, so I have tried to query using sys.lob$, and others tables directly (as opposed to dba_lobs), this is shown below.

However, I am not even sure if I can bitand the answer, Can anyone contribute? All the combinations of bitands result in all yes or all no which does not make sense for me considering I certainly appears that I have one - as shown in the results.

(LOB INDEXES Not in DBA_OBJECTS)
select * from dba_segments where segment_name = 'LOB_EXPLICIT_MESSAGE_LOB_IX'; SEGMENT_NAME

---

LOB_EXPLICIT_MESSAGE_LOB_IX select * from dba_indexes where index_name = 'LOB_EXPLICIT_MESSAGE_LOB_IX';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------
LOB_EXPLICIT_MESSAGE_LOB_IX    LOB                         LOB_EXPLICIT

select * from dba_objects where object_name = 'LOB_EXPLICIT_MESSAGE_LOB_IX'; no rows selected

select * from user_lobs where index_name = 'LOB_EXPLICIT_MESSAGE_LOB_IX';

TABLE_NAME           COLUMN_NAME  SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME
-------------------- ------------ ------------------------------ ------------------------------ ------------------------------ --
LOB_EXPLICIT         MESSAGE      LOB_EXPLICIT_MESSAGE_LOB_SG    TOOLS                          LOB_EXPLICIT_MESSAGE_LOB_IX

 column name format a30
 column property format 999999999999

  select o.name table_name,

         io.name index_name,
         decode(bitand(l.property, 4096), 4096, 'YES', 'NO') sys_generated,
         l.property,
         decode(bitand(l.property,2), 2, 'YES', 'NO') bitand_2,
         decode(bitand(l.property,1), 1, 'YES', 'NO') unique_index,
         lo.name segment_name,
         decode(bitand(c.property, 1), 1, ac.name, c.name) column_name
  from sys.obj$ o, sys.obj$ lo, sys.obj$ io, sys.ts$ ts, sys.ts$ ts1,
       sys.lob$ l, sys.user$ u, sys.attrcol$ ac, sys.col$ c
     where o.owner# = u.user#
       and bitand(o.flags, 128) = 0
       and o.obj# = c.obj#
       and c.obj# = l.obj#
       and c.intcol# = l.intcol#
       and l.lobj# = lo.obj#
       and l.ind# = io.obj#
       and l.ts# = ts.ts#(+)
       and u.tempts# = ts1.ts#
       and c.obj# = ac.obj#(+)
       and u.name = USER
       and o.name in
          ('LOB_WITHCLAUSE','LOB_NOCLAUSE','LOB_EXPLICIT')
       and o.obj# = l.obj#

/

TABLE_NAME                     INDEX_NAME                     SYS_GENERATED      PROPERTY BIT UNIQUE SEGMENT_NAME                   COLUMN_NAME
------------------------------ ------------------------------ ------------- ------------- --- ------ ------------------------------ ------------
LOB_EXPLICIT                   LOB_EXPLICIT_MESSAGE_LOB_IX    NO                        2 YES NO     LOB_EXPLICIT_MESSAGE_LOB_SG    MESSAGE
LOB_NOCLAUSE                   SYS_IL0000575648C00004$$       NO                        2 YES NO     SYS_LOB0000575648C00004$$      MESSAGE
LOB_WITHCLAUSE                 LOB_WITHCLAUSE_MESSAGE_LOB_IX  NO                        2 YES NO     LOB_WITHCLAUSE_MESSAGE_LOB_SG  MESSAGE

Have a nice holiday weekend,
See you next week,

Joel Patterson
Database Administrator
904 727-2546

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 25 2012 - 14:44:10 CDT