-
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
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
-
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.
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
|