我要找出用户USER01在 SYSTEM表空间下的非LOB字段表:
select table_name from dba_tables
whereowner = 'USER01'
and tablespace_name = 'SYSTEM'
and table_name NOT IN
(select a.table_name from dba_lobs a,dba_segments b
where a.segment_name=b.segment_name
and b.owner='USER01'
and b.tablespace_name='SYSTEM');
上面这个sql执行有点慢，能有更好的方式吗？
SELECT DISTINCT A.TABLE_NAME
FROM ALL_TAB_COLS a, dba_segments b
WHERE a.OWNER = 'USER01'
AND a.DATA_TYPE'CLOB'
and a.TABLE_NAME = b.segment_name
and b.owner = 'USER01'
and b.tablespace_name = 'SYSTEM'
