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

Select sql_ext from v$sql and join addr field of v$lock with taddr of v$session and sql_address of v$session with address of v$sql

Ere is somfink wot I wrote, may be of use....... HTH

Code:

---


/* Script finds SQL in SQLAREA for any given search string.  The address in SQLAREA
  will be provided for any matches.  This is asked for.  Finally, EXPLAIN PLAN FOR is
  appended to the start of the SQL, so the statement can be pasted into a SQLPLUS
  session.
*/

-- grant select on v_$sqlarea to ;
-- grant select on v_$sqltext to ;

SET ECHO OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF


create table temp_sql_plan
(tsp_addr  varchar2(8));

ACCEPT srch_str char PROMPT 'Please enter the string that you want to search for within V$SQLAREA: '

select substr(sql_text,1,50), address
from v$sqlarea
where upper(sql_text) like UPPER('%&srch_str%');

SELECT ' ' FROM DUAL;

Accept line_addr char prompt 'Please enter the address of the desired line: '

SELECT ' ' FROM DUAL;

insert into temp_sql_plan values (upper('&line_addr'));
commit;

declare
  sql_addr temp_sql_plan.tsp_addr%type;
  cursor c1 is select sql_text
              from v$sqltext
              where address = sql_addr
              order by piece;
  big_sql varchar2(4000);
  start_pos  number (9);
  b          number (9);
  c          number (9);
  len        number (4);
  space  boolean;
begin
  select tsp_addr into sql_addr from temp_sql_plan;
  dbms_output.enable(200000);
  big_sql :='';
  start_pos := 1;
  for r1 in c1 loop
    big_sql := big_sql || r1.sql_text;
  end loop;
  dbms_output.put_line('Explain plan for');
  big_sql := replace(big_sql,chr(13),' ') ;
--big_sql := replace(big_sql,'OR ', 'OR ' || chr(13)) ;
--big_sql := replace(big_sql,'AND ', 'AND ' || chr(13)) ;
  len := length(big_sql);
  for b in 1..len  loop
    space :=false;
    if mod(b,90) = 0 then
      c := b;
    while not space loop
        if substr(big_sql,c,1) = ' ' then
          space := True;
        else
          c := c + 1;
        end if;
      end loop;
      space := false;
      dbms_output.put_line(substr(big_sql, start_pos,c-start_pos));
      start_pos := c + 1;
    end if;
  end loop;   
  dbms_output.put_line(substr(big_sql,start_pos) || ';');
--  execute immediate(big_sql);
end;
/

SELECT ' ' FROM DUAL;

DROP TABLE temp_sql_plan;

SET FEEDBACK ON
SET VERIFY ON
SET HEADING ON

---

:)

Ah! Missed the bit about lock queries, so the code above is not quite what you're after.