oracle 列出包含 `clob` 或 `blob` 或 `lob` 列的表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21303837/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List out the table names having `clob` or `blob` or `lob` containing columns
提问by being_uncertain
I have around 1000 table out of which I need to list out the table names having clobor blobor lobcontaining columns. Is there any query to list out the same from my schema?
我有大约 1000 个表，我需要在其中列出具有clob或blob或lob包含列的表名。是否有任何查询可以从我的架构中列出相同的内容？
回答by Dba
Try like this,
试试这样，
SELECT DISTINCT table_name
FROM user_tab_cols
WHERE data_Type IN ('CLOB', 'LOB', 'BLOB');
回答by Wernfried Domscheit
Try this one:
试试这个：
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE DATA_TYPE IN ('CLOB', 'BLOB');
回答by Flare Star
I havent a database handy, but this should work:
我手头没有数据库，但这应该有效：
select * from ALL_TAB_COLUMNS a where a.DATA_TYPE in ('CLOB','BLOB','NCLOB','BFILE');
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.htmand http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm)
（参见：http: //docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm和http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm)
