oracle返回值显示空值,从dba_extents返回结果为空 - Oracle10g

DBA经常需要用如下SQL来查看某个数据块数据哪个SEGMENT，但是在10g里这句SQL返回结果通常是空的。

select * from dba_extents where file_id=30 and block_id<=1000 and block_id+blocks>=1000;[@more@]

SOLUTION：

SQL> alter session set "_optimizer_push_pred_cost_based"=false;

Session altered

SQL> select * from dba_extents where file_id=30 and block_id<=1000 and block_id+blocks>=1000;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------

CTXSYS DR$WAITING TABLE CTXD 0 30 985 131072 16 30

关于参数_optimizer_push_pred_cost_based：uses cost-based query transformation for push pred optimization.

With the presence of the parameter, the CBO would push the join predicates into the view involved in the query.

This pushing of predicate may lead to full table scan instead of doing index access.

Metalink上说有个补丁能解决这个问题，但是一般情况下那个补丁都没用。