DML ( SQL text ) in this LOCK Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DML ( SQL text ) in this LOCK Query

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

    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

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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
    Last edited by simply_dba; 01-19-2005 at 03:38 AM.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Ah! Missed the bit about lock queries, so the code above is not quite what you're after.

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