LOB stands for Large Objects datatypes. In Oracle there are four column's LOB datatypes: BLOB, CLOB, NCLOB and BFILE.

Query below lists:

(A) all tables accessible to the current user in Oracle database having LOB columns

(B) all tables in Oracle database having LOB columns

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select col.owner as schema_name,
       col.table_name, 
       count(*) as column_count
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
-- excluding some Oracle maintained schemas
and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
group by col.owner,
         col.table_name
order by col.owner, 
         col.table_name;

B. If you have privilege on dba_tables and dba_tab_columns

select col.owner as schema_name,
       col.table_name, 
       count(*) as column_count
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
-- excluding some Oracle maintained schemas
and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
group by col.owner,
         col.table_name
order by col.owner, 
         col.table_name;

Columns

• schema_name - schema name, owner of the table
• table_name - name of the table
• column_count - number of LOB columns in a table

Rows

• One row represents one table in a database having LOB column
• Scope of rows: (A) all tables accessible to the current user in Oracle database having LOB columns, (B) all tables in Oracle database having LOB columns
• Ordered by schema name, table_name

Sample results