DML ( SQL text ) in this LOCK Query
Thanks in advance
ORACLE 8.1.7 *
Redhat advance server
objective - I want to have the DML (SQL Text) in this output also.
I queried the tables v$lock,dba_objects,v$locked_object and saw that there is no such COLUMN as SQL text SO THAT I COULD USE A JOIN HERE where in it will give me information whether it is insert,update query.
which view/table will give me this information and with which column i should join here in this below query.
This query is giving me this output.
MACHINE LOCK_TYPE LOCK_HELD LOCK_REQUESTED STATUS OBJECT_NAME
wisp3.prontonetworks.com DML Row-X (SX) None Not Blocking PRONTO_PRODUCT_PP set heading on
set linesize 700
set pagesize 1500
select
a.machine,
decode(l.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',l.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,v\$session a
where lo.OBJECT_ID = do.OBJECT_ID AND l.SID = lo.SESSION_ID and a.SID=l.SID;
spool off;
EOQ