0

I've been trying to locate the table that belongs to a specific big lob segment. However, the results always specify that this lob segment does not belong to any table (no rows selected)

SQL> select owner, table_name, column_name 
  2  from dba_lobs 
  3  where segment_name = 'SYS_LOB0000551103C00007$$';
no rows selected

Is there any way to find the table that where the lob belongs?

APC
  • 144,005
  • 19
  • 170
  • 281
xka
  • 53
  • 4
  • 11

1 Answers1

1

The LOB segment might have been orphaned. When a LOB column is set unused, it is cleaned up from the dba_lobs view, but it can still be seen in dba_objects and dba_segments.

Use the following query to find the details the LOB segment.

select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
where o.owner# = u.user#
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 c.obj# =
ac.obj#(+)
and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000551103C00007$$';

Or, LOB segment might belong to a dropped table residing in the recycle bin. You can use the same query to find the table.

atokpas
  • 3,231
  • 1
  • 11
  • 22