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, along with their LOB columns, accessible to the current user in Oracle database

(B) all tables, along with their LOB columns, in Oracle database

Query was executed under the Oracle9i Database version.

Confused about your Teradata database?

You don't have to be. There's an easy way to understand the data in your databases.

I want to understand

Query

A. Tables accessible to the current user

select col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type
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')  
order by col.owner, 
         col.table_name, 
         col.column_name;

B. If you have privilege on dba_tables and dba_tab_columns

select col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type
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')  
order by col.owner, 
         col.table_name, 
         col.column_name;

Columns

• schema_name - schema name, owner of the table
• table_name - name of the table
• column_name - name of the column
• data_type - data type

Rows

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

Sample results