Find a Table And Column For LOB Segment

Sometimes people are a bit confused about LOB segments 🙂 That’s why I am here to remind you the DBA_LOBS table .. that’s your best friend here

SELECT owner
, table_name
, column_name
, segment_name
FROM dba_lobs
AND segment_name IN (
                     SELECT segment_name
                     FROM dba_segments
                     WHERE 1=1
                     AND owner = 'JOHNNY'
                     AND segment_type = 'LOBSEGMENT' -- or LOB SUBPARTITION, LOBINDEX, LOB PARTITION