11G版本中，无法在标量子查询里，使用INER JOIN之类的SQL-99标准的写法，必须用回老的SQL-92，
如下语句: 被屏蔽的暗红色那段，若放开，会报：ORA-00904, 不认识 "DE"."SEGMENT_NAME" 标识符，但SQL-92写法则没问题。
select owner, segment_type, segment_name, tablespace_name, extents, bytes_m, total_blocks from
(select owner, segment_type, segment_name,
(select dt.read_only from dba_tables dt where dt.owner=de.owner and dt.table_name=de.segment_name) Ro_t,
(select dt.read_only from dba_tables dt, dba_indexes di
where di.owner=dt.owner and di.owner=de.owner and di.index_name=de.segment_name and di.table_name=dt.table_name) Ro_i,
/*(select dt.read_only from dba_tables dt inner join
dba_indexes di on (di.owner=dt.owner and di.owner=de.owner and di.index_name=de.segment_name and di.table_name=dt.table_name)) Ro_i, --*/
tablespace_name, count(blocks) "EXTENTS", sum(bytes/1024/1024) "BYTES_M", sum(blocks) "TOTAL_BLOCKS"
from dba_extents de where owner in (&user) group by owner, segment_type, segment_name, tablespace_name
having count(*) > 100 order by owner, segment_type, tablespace_name, sum(bytes/1024/1024)
) where (ro_t='NO' or ro_i='NO') order byowner, segment_type, segment_name, tablespace_name; |
