Skip to content

How to Find LOB Segment Table Name

  • by

Sometimes, you may see segment type like LOBSEGMENT in the top 10 segments list. They are Large Object (LOB) which may be Binary Large Object (BLOB) or Character Large Object (CLOB).

Such LOB segments, like SYS_LOB0000220085C00094$$ are named by system, we can never guess which table column owns the LOB by its name.

Fortunately, we can find which table and column owns the LOB segment by querying the ALL_LOBS dictionary view.

SQL> column owner format a10;
SQL> column table_name format a20;
SQL> column column_name format a20;
SQL> select owner, table_name, column_name from all_lobs where segment_name = 'SYS_LOB0000220085C00094$$';

OWNER      TABLE_NAME           COLUMN_NAME
---------- -------------------- --------------------
ERPAP      ERP35_GWRLTIRIVA     PAPER_SINGED

If it occupied too much space and bothered you for a long time, you may move the LOB segment to another tablespace.

Leave a Reply

Your email address will not be published. Required fields are marked *