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

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

"Syltrem" <syltremzulu_at_videotron.ca> wrote in message news:<kHj4c.1022$Xy3.3856_at_tor-nn1.netcom.ca>...
> 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
Received on Fri Mar 12 2004 - 16:26:43 CST