oracle-databasebloblob

Query that shows 'BLOBs stored inline'

I need to produce a query that shows a table(table_name, column_name) of where BLOBs are stored inline from user_tables. Any help would be much appreciated.

Solution

  • To determine whether a LOB column is stored in-line or out-of-line, you need to take a look at the IN_ROW column value of user_lobs view (or [dba] [all] _lobs views depending on the privileges granted): YES - inline, NO - out-of-line:

    For instance:

    select t.table_name
     , t.column_name
     , t.in_row
  from user_lobs t
where table_name = 'BLOB_TABLE'

    Result:

    Table Name   Column Name In Row 
--------------------------------
BLOB_TABLE   COL1        YES 
BLOB_TABLE   COL2        YES 
BLOB_TABLE   COL3        YES