I enjoyed Tom Kyte’s example of how you find matching a LOB Index to Segment (Expert Oracle Database Architecture, pp. 542). I’ve noticed variations of it posted in various locations. While it works well for sample scheme that have only one LOB, the following works for any number of LOBs in any schema. This simplifies working with system- and user-defined segment names. The first CASE statement ensures that joins between user-named segment names are possible. The second CASE statement ensures two things: (a) Joins between system generated segment names don’t throw an error when matching unrelated system generated return values found in the DBA_SEGEMENTS view; and (b) Joins between named segments are possible and don’t throw an error.