Multiple v$session records for some v$process records

From: Fowler, Kenneth R <Kenneth.R.Fowler_at_pfizer.com>
Date: Fri, 27 Oct 2006 14:51:17 -0400
Message-ID: <2AFB753ED0140B43A933D24EAC51D7FA06B4BFC2@groamrexm01.amer.pfizer.com>

Hi,

The question refers to Oracle 9.2.0.5 on Solaris 2.8. I noticed that in one database I look after we have multiple v$session records for some v$process records...

SQL> select * from v$session where sid in (45, 435);

SADDR                   SID    SERIAL#     AUDSID PADDR

USER#
---------------- ---------- ---------- ---------- ----------------

USERNAME                          COMMAND    OWNERID TADDR


------------------------------ ---------- ---------- ----------------

LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME


---------------- -------- --------- ----------

------------------------------

OSUSER                         PROCESS


------------------------------ ------------

MACHINE

TERMINAL

PROGRAM                                          TYPE       SQL_ADDRESS


------------------------------------------------ ----------

----------------

SQL_HASH_VALUE PREV_SQL_ADDR PREV_HASH_VALUE
-------------- ---------------- ---------------

MODULE                                           MODULE_HASH


------------------------------------------------ -----------

ACTION                           ACTION_HASH


-------------------------------- -----------

CLIENT_INFO

FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK#
-------------------- ------------- -------------- ---------------
ROW_WAIT_ROW# LOGON_TIME LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI
------------- ----------- ------------ --- ------------- ---------- ---
RESOURCE_CONSUMER_GROUP PDML_STA PDDL_STA PQ_STATU
-------------------------------- -------- -------- --------
CURRENT_QUEUE_DURATION

CLIENT_IDENTIFIER

00000003CEDFB8A8 45 4857 25131663 00000003CECB40D8 57466

OPS$JOSEPM                              0 2147483644
                 INACTIVE DEDICATED      57466 OPS$JOSEPM
OPAREPS                        1816:4668

AMER\MOPPGRDOC04
MOPPGRDOC04

                                                 USER       00
             0 00000003F09A5220       974596382
                                                           0
                                           0

            83637922       4984091             45               0
            0 23-OCT-2006        31353 NO  NONE          NONE       NO
DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED
                     0


00000003CEEFD008        435       1070   25131662 00000003CECB40D8
57466
OPS$JOSEPM                              0 2147483644
                 INACTIVE DEDICATED      57466 OPS$JOSEPM
OPAREPS                        1816:4668

AMER\MOPPGRDOC04
MOPPGRDOC04

                                                 USER
00000003D647AE90
    3477528002 00                             0
                                                           0
                                           0

            19302446        600018              7               0
            0 23-OCT-2006       310368 NO  NONE          NONE       NO
DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED
                     0

There are a few examples...

SQL> select paddr from v$session group by paddr having count(*) > 1;

PADDR

00000003CECB40D8
00000003CECB9660
00000003CECBA578
00000003CECC64A8
00000003CECDE308 Can anyone tell me under what circumstances this can happen? I have searched metalink and google to no avail. I noticed it because it caused some scripts I have that join v$session and v$process to give odd results.

Thanks,
Ken

Enterprise Informatics - Common Engineering Groton/New London Manager, Database Administration Services Email (Inbox): mailto:Kenneth.R.Fowler_at_pfizer.com Office: T274/T227
Eastern Point Road,
Groton, CT 06340
M/S 5274-2006

LEGAL NOTICE
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 27 2006 - 13:51:17 CDT