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

"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 - 06:44:20 CST