Oracle Database Discussions

Hi all,

I can display the sql_txt of the blocked sql using this:

select distinct s1.username ||'@'|| s1.machine ||'(INST=' || s1.inst_id ||' SID= '|| s1.sid ||')

is blocking '|| s2.username || '@' || s2.machine || ' (INST=' || s1.inst_id||' SID='||s2.sid ||')'    

AS blocking_status, s2.program,s3.sql_id, s3.sql_text from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2, v$sql s3

    where s1.sid=l1.sid

    and s2.sid=l2.sid

    and l1.block=1

    and l2.request >0

    and l1.id1=l2.id1

    and l2.id2=l2.id2

    and s2.sql_id = s3.sql_id;

But I can display the blocker sql

Can you help me how to get the blocking sql_txt?

I tried to revise the program which is:

select s1.sid,s1.serial#,s1.inst_id,s1.SQL_ID from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2, v$sql s3

    where s1.sid=l1.sid

    and s2.sid=l2.sid

    and l1.block=1

    and l2.request >0

    and l1.id1=l2.id1

    and l2.id2=l2.id2

    and s2.sql_id = s3.sql_id;

       SID    SERIAL#    INST_ID SQL_ID

       ---------- ---------- ---------- -------------

        88        148          1

But s1.sql_id is null. where do I get it?

Thanks