Re: How can that be (SQL, v$temp_space_header and dba_tablespaces)

Some of the 'dynamic views' are actually calls to functions which return row sets. Some of the functions require specific inputs - such as a table name.

If a join goes in the wrong order (for example the dynamic view becomes the driving table in a join) then the required input may not exist, so the function is called with a null input, and returns no rows. This may be relevant in the current case - you might try checking the execution plan.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"Yong Huang" <yong321_at_yahoo.com> wrote in message
news:b3cb12d6.0403121426.bc02864_at_posting.google.com...


> What does the doc say exactly? In Metalink Note:225566.995, it appears

> that the sentence "You cannot join dynamic views" is not part of the

> documentation, but the rest is. It's that added sentence that doesn't

> sound right. Otherwise so many scripts we've been using all these

> years are questionable. The known fact that queries on v$ views are

> not always read consistent does not explain this buggy join on

> v$temp_space_header.

>

> In addition to using the join with ORDERED hint, you can of course

> select * from dba_tablespaces where tablespace_name = (select

> tablespace_name from v$temp_space_header where tablespace_name =

> 'TEMP'). That means the string 'TEMP' *is* the same in both views but

> the join between the views does not work.

>

> Alternatively, change a query on v$temp_space_header into a "table":

>

> select th.TABLESPACE_NAME, status from (select tablespace_name from

> v$temp_space_header) th, dba_tablespaces t where th.table

> space_name=t.tablespace_name;

>

> Again, we have to think about an explanation.

>

> Yong Huang