Why data pump does not keep LOB index name

From: <Joel.Patterson@xxxxxxxxxxx>
To: <Oracle-L@xxxxxxxxxxxxx>
Date: Fri, 25 May 2012 15:44:10 -0400
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



--
//www.freelists.org/webpage/oracle-l
Why data pump does not keep LOB index name

Other related posts:
