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

I found something this morning. Documented behaviour, but still strange behaviour.

From the doc:
You cannot join dynamic views. You can query the dynamic performance views to extract information from them. However, only simple queries are supported. If sorts, joins, GROUP BY clauses and the like are needed, you should copy the information from each V$ view into a table (for example, using a CREATE TABLE ... AS SELECT statement), and then query from those tables.
Because the information in the V$ views is dynamic, read consistency is not guaranteed for SELECT operations on these views. "

The ORDERED hints makes it work though so I`ll just use it but if someone could explain this rationally I'd be very happy !

-- 
Syltrem

OpenVMS 7.3-1 + Oracle 9.2.0.3
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---
"Yong Huang" <yong321_at_yahoo.com> a écrit dans le message de
news:b3cb12d6.0403120444.4b095583_at_posting.google.com...


> "Syltrem" <syltremzulu_at_videotron.ca> wrote in message

news:<Ag54c.1009$Xy3.3718_at_tor-nn1.netcom.ca>...


> >

> > It looks like I can't link DBA_TABLESPACES with V$TEMP_SPACE_HEADER with

the


> > TABLESPACE_NAME column

> >

> >

> > SQL> select dump(tablespace_name) from dba_tablespaces where

tablespace_name


> > lik

> > e '%TEMP%';

> >

> > DUMP(TABLESPACE_NAME)

>

> --------------------------------------------------------------------------

--


> > ----

> > Typ=1 Len=4: 84,69,77,80

> > SQL> select dump(tablespace_name) from v$temp_space_header where

> > tablespace_name

> >  like '%TEMP%';

> >

> > DUMP(TABLESPACE_NAME)

>

> --------------------------------------------------------------------------

--


> > ----

> > Typ=1 Len=4: 84,69,77,80

> > Typ=1 Len=4: 84,69,77,80

> >

> > SQL> select th.TABLESPACE_NAME, status from v$temp_space_header th,

> >   2  dba_tablespaces t where th.tablespace_name=t.tablespace_name

> >   3  /

> >

> > no rows selected

> >

> > The 2 views *cannot* be linked together.

> > ...

> >

> > What's wrong ?

> > TABLESPACE_NAME (or NAME) is defined as VARCHAR2(30) in all 3 views...

>

> I need to think about the explanation. But for now, just try adding

> ORDERED hint:

>

> SQL> select /*+ ordered */ th.TABLESPACE_NAME, status from

> v$temp_space_header th, dba_tablespaces t where

> th.tablespace_name=t.tablespace_name;

>

> TABLESPACE_NAME                STATUS

> ------------------------------ ---------

> TEMP                           ONLINE

>

> It doesn't matter whether you specify v$temp_space_header first or

> second. Did you search on Metalink?

>

> Yong Huang