How to identify table name for LOB segments in Oracle?
corpline

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.

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Navigation Panel