-
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
-
join in v$session based on sid, whats the problem?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|