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

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Thu, 11 Mar 2004 16:57:07 -0500
Message-ID: <Ag54c.1009$Xy3.3718@tor-nn1.netcom.ca>

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

-- 
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---

Received on Thu Mar 11 2004 - 15:57:07 CST