List out the table names having `clob` or `blob` or `lob` containing columns

Question

I have around 1000 table out of which I need to list out the table names having clob or blob or lob containing columns. Is there any query to list out the same from my schema?

Answer 1

Try like this,

SELECT DISTINCT table_name 
FROM   user_tab_cols 
WHERE  data_Type IN ('CLOB', 'LOB', 'BLOB');

Answer 2

Try this one:

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLUMNS 
WHERE DATA_TYPE IN ('CLOB', 'BLOB');

Answer 3

I havent a database handy, but this should work:

select * from ALL_TAB_COLUMNS a where a.DATA_TYPE in ('CLOB','BLOB','NCLOB','BFILE');

(see: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm and http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm)