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

On Thu, 11 Mar 2004 16:57:07 -0500, Syltrem wrote:

> Hi
>
> 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.
>
> Also,
>
> SQL> select * from v$temp_space_header natural join dba_tablespaces;
>
> no rows selected
>
> SQL>
>
>
> On the other hand,
> 1 select th.TABLESPACE_NAME, INCLUDED_IN_DATABASE_BACKUP
> 2 from v$temp_space_header th,
> 3* v$tablespace t where th.tablespace_name=t.name
> SQL> /
>
> TEMP Tablespace INC
> --------------- ---
> TEMP YES
> TEMP YES
>
> 2 rows selected.
>
> Those 2 views *can* be linked together.
>
> What's wrong ?
> TABLESPACE_NAME (or NAME) is defined as VARCHAR2(30) in all 3 views...
>
> Thanks

Bonjour Syltrem !

Just a "me too" post. I don't know why it behaves like this. Whenever v$temp_space_header is in the "from" clause, no rows are returned... Very strange. Please let us know if you found out meanwhile.

select * from dba_objects,v$temp_space_header;  

no rows selected Received on Fri Mar 12 2004 - 04:04:16 CST