Re: How to get the statment sql that caused the lock

HI Eriovaldo
I do not think it's possible to "find the sql that couased the lock"; however, you may find the row which is currently locked and blocking:   select ROW_WAIT_OBJ# from v$session where sid = :BLOCKED_SID

is the object_id where the row is and
  select dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) as wanted_rowid from v$session where sid = :BLOCKED_SID is the rowid which is currently blocked and waited for HTH regards
Andrea

Il giorno dom 28 ott 2018 alle ore 00:50 Eriovaldo Andrietta < ecandrietta_at_gmail.com> ha scritto:

> Hello,
>
> I using Oracle 12c R2.
> I have this query that shows blocked sid:
>
> Query 1 :
> select * from gv$lock where sid in
> (select sid from gv$session where blocking_session is not null);
>
> and here , the query shows the blocker sid. These sessions are IDLE.
> Query 2
> select * from gv$lock where sid in
> (select blocking_session from gv$session where blocking_session is not
> null);
>
> My doubt is : what is the query that caused the lock that ran in the
> blocker sessions?
>
> I tried the query below, but did not get any query using the locked table.
>
> select * from
> (
> select sql_id
> ,to_char(last_active_time,'dd-hh24:mi:ss') last_active
> ,executions
> ,elapsed_time/1000000 elap_tot
> ,decode(executions,0,elapsed_time,(elapsed_time/executions))/1000000
> elap_exec
> ,decode(executions,0,disk_reads,(disk_reads/executions)) disk_exec
> ,decode(executions,0,buffer_gets,(buffer_gets/executions))
> buffer_exec
> ,tot
> ,sql_text
> from (select s.sql_id
> ,substr(s.sql_text,1,225) sql_text
> ,max(last_active_time) last_active_time
> ,sum(executions) executions
> ,sum(elapsed_time) elapsed_time
> ,sum(disk_reads) disk_reads
> ,sum(buffer_gets) buffer_gets
> ,count(*) tot
> from gv$sql s
> ,gv$open_cursor o
> where s.inst_id = o.inst_id
> and s.sql_id = o.sql_id
> and o.user_name = s.parsing_schema_name
> and o.sid=&vSid
> and o.inst_id = nvl('&vInstId',1)
> and s.parsing_schema_name <> 'SYS'
> group by s.sql_id
> ,substr(s.sql_text,1,225))
> order by to_char(last_active_time,'yyyymmddhh24mi'), elap_exec
> )
> where elap_exec > &vElap;
>
> What is the way to find out the query that caused the lock (in sessions
> showed in the Query 2 above)
>
> Regards
> Eriovaldo
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l