Join between v$tempfile and v$temp_space_header returns no row. I have
observed this behaviour on 8.1.7.2 and 9.0.1.0
Why would a join to v$temp_space_header with v$tempfile or v$tempstat
returns zero row with or without a predicate. Here are some examples.
17:59:41 REPADMIN@TST0(11)> select * from v$temp_space_header; -- to show
that it has a record.
TABLESPACE_NAME
============================================================================
==============
FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE
==================== ==================== ====================
====================
BLOCKS_FREE RELATIVE_FNO
==================== ====================
TEMP
1 3145728 384 38797312
4736 1
18:01:28 REPADMIN@TST0(11)> select a.file#, b.file# from v$tempfile a,
v$tempstat b; -- returns one row, a similar join between v$tempfile and
v$temp_space_header doesn't, WHY???
FILE# FILE#
=== ===
1 1
18:06:22 REPADMIN@TST0(11)> select a.file#, b.file_id from v$tempfile a,
v$temp_space_header b;
no rows selected
With predicate
1 select a.file#, b.file_id from v$tempfile a, v$temp_space_header b
2* where a.file# = b.file_id
no rows selected
1 row selected.
I even pass the file# and file_id to the ascii function after querying both
tables independently, both returned with a value of 49.
1* select ascii(file_id) from v$temp_space_header
ASCII(FILE_ID)
====================
49
17:55:49 SYS@WW4T(12)> select ascii(file#) from v$tempfile;
ASCII(FILE#)
====================
49
Any explaination would be greatly appreciated.
Jun Erroba
Oracle DBA
Menlo Worldwide Technologies
Ph: (503) 450-5771
Email: [EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, May 14, 2002 5:53 PM
To: Multiple recipients of list ORACLE-L
> As a result of this query, I got the wait stats and I have
> found explanation for all wait events except for the
> following. Could you please help me understand as to what
> these events or where can I find the explanations for these events.
>
> SID EVENT P1 P2
> P3 WAIT_TIME SECONDS_IN_WAIT STATE
> ----- ------------------------------ ---------- ----------
> ---------- ---------- --------------- ----------
> 22 PX Deq: Execution Msg 268566527 6
> 0 0 34 WAITING
> 36 PX Deq: Execution Msg 268566527 18
> 0 0 34 WAITING
> 63 PX Deq: Execution Msg 268566527 18
> 0 0 34 WAITING
> 48 PX Deq Credit: send blkd 268566527 1
> 0 0 38 WAITING
Looks like PQ slave process waits. Are you expecting Parallel queries on
your database?
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
The manuals for Oracle are here: http://tahiti.oracle.com
The manual for Life is here: http://www.gospelcom.net
** The opinions and statements above are entirely my own and not those of my
employer or clients **
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Erroba, Ildefonso N
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
