從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上說有個補丁能解決這個問題，但是一般情況下那個補丁都沒用。