How to identify table name for LOB segments in Oracle?
To identify the table name based on a LOB (Large Object) segment name in Oracle, you can use the following SQL query:
select owner, table_name, column_name from all_lobs where segment_name = 'LOB_SEGMENT_NAME';
#Replace LOB_SEGMENT_NAME with the actual name of the LOB segment you want to search for.
column owner format a10;
column table_name format a20;
column column_name format a20;
select owner, table_name, column_name from all_lobs where segment_name = 'SYS_LOB0000110089C00092$$';
OWNER TABLE_NAME COLUMN_NAME
---------- -------------------- --------------------
db_user OLDB_TAB EXT_DATA
The query will retrieve the owner, table name, and column name associated with the specified LOB segment.
Note that this query retrieves information from the ALL_LOBS view, which contains information about all LOB columns in the database accessible to the current user. If you have restricted privileges, you may need to use a different view or consult your database administrator for access to the necessary data dictionary views.
Related content