DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: V$session - Machine column to be added in this query

  1. #1
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    V$session - Machine column to be added in this query

    Oracle 8.1.7 on Red HatAdvance Server.

    objective is - to add column MACHINE name from v$session table in this same query

    select
    l.SID oracle_id,
    decode(TYPE,
    'MR', 'Media Recovery',
    'RT', 'Redo Thread',
    'UN', 'User Name',
    'TX', 'Transaction',
    'TM', 'DML',
    'UL', 'PL/SQL User Lock',
    'DX', 'Distributed Xaction',
    'CF', 'Control File',
    'IS', 'Instance State',
    'FS', 'File Set',
    'IR', 'Instance Recovery',
    'ST', 'Disk Space Transaction',
    'TS', 'Temp Segment',
    'IV', 'Library Cache Invalidation',
    'LS', 'Log Start or Switch',
    'RW', 'Row Wait',
    'SQ', 'Sequence Number',
    'TE', 'Extend Table',
    'TT', 'Temp Table', type) lock_type,
    decode(LMODE,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', lmode) lock_held,
    decode(REQUEST,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', request) lock_requested,
    decode(BLOCK,
    0, 'Not Blocking',
    1, 'Blocking',
    2, 'Global', block) status,
    OBJECT_NAME
    from v\$locked_object lo,dba_objects do,
    v\$lock l
    where lo.OBJECT_ID = do.OBJECT_ID AND l.SID = lo.SESSION_ID;

    The output is this
    =======================
    ORACLE_ID LOCK_TYPE LOCK_HELD LOCK_REQUESTED STATUS OBJECT_NAME

    268 Transaction Exclusive None Not Blocking PRONTO_WISP_PREPAID_CARD
    now i am getting 268 as id , now i want to have machine name in place of Oracle ID. v$session has the machine column.
    V$lock has SID column & v$session has SID column.

    but i am not getting it. can any one guide.

    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    join in v$session based on sid, whats the problem?

  3. #3
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    where will i add in this query i mean where which line i should add , i tried adding at where condition but still no luck,

    like this
    where a.sid = l.sid

    a = v$session
    l= v$lock.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    post your new query with the new stuff you added, also format it properly with the [ code ] and [ / code ] tags (no spaces)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width