4 Replies Latest reply on May 3, 2020 7:35 AM by Andy Haack

    How to kill blocking session in Oracle eBS 12.1.3?

    Ariz - Apps DBA

      Hi, We have 2 node RAC DB (11.2.0.4), 2 node apps tiers (12.1.3) with shared applicaiton top running on AIX 7.1. How to kill blocking session which are from frmweb process?

       

      Regards

      Ariz

        • 1. Re: How to kill blocking session in Oracle eBS 12.1.3?
          yoonas

          Hi,

           

          If you know which users are causing you can ask one of them to close the blocking form so that other user can continue and finish his/her work.

          If you identify the form session which is causing block you can kill the form session which will automatically kill database session.

          There are other occasion lock happens due to bug also, in which case you may have to apply patch.

           

          I am not aware of any standard procedure  for EBS blocking sessions.

           

          Regards,

          Yoonas

          • 2. Re: How to kill blocking session in Oracle eBS 12.1.3?
            Ariz - Apps DBA

            Hi Alex,

            The query is failing with error "[Error] Execution (12: 1): ORA-00904: "XXEN_UTIL"."CLIENT_TIME": invalid identifier".

             

            Regards

            Ariz

            • 3. Re: How to kill blocking session in Oracle eBS 12.1.3?
              Ariz - Apps DBA

              Well explained, Thanks Yoonas.

              • 4. Re: How to kill blocking session in Oracle eBS 12.1.3?
                Andy Haack

                Hi Ariz,

                 

                Looks like you tried running DBA Blocking Sessions based on v$wait_chains from the blitz report library, which requires the xxen_util package to translate e.g. module codes to forms display names.

                If you can not easily deploy the xxen_util package to your database, you can use below SQL instead, which still shows the EBS users (client_identifier) blocking each other, but does not translate the responsibility (action) and forms (module) names nicely to the display names as the original report does.

                 

                select

                vwc.instance inst_id,

                gs.sid||' - '||gs.serial# sid_serial#,

                lpad(' ',2*(level-1))||level level_,

                gs.client_identifier,

                gs.action,

                gs.module,

                lpad(' ',2*(level-1))||gs.status||nvl2(vwc.blocker_sid,null,' - blocking') status,

                vwc.in_wait_secs seconds,

                gs.prev_exec_start,

                vwc.wait_event_text wait_event,

                (select do.owner||'.'||do.object_name||' ('||do.object_type||')' from dba_objects do where case when vwc.wait_event_text like 'enq: TM%' then vwc.p2 when vwc.wait_event_text like 'enq: TX%' then vwc.row_wait_obj# end=do.object_id) object,

                gs.sql_id,

                gsa.sql_fulltext sql_text,

                case when vwc.wait_event_text like 'enq: TX%' then (

                select

                'select * from '||do.owner||'.'||do.object_name||' where rowid='''||dbms_rowid.rowid_create(1,do.data_object_id,ddf.relative_fno,vwc.row_wait_block#,vwc.row_wait_row#)||'''' show_blocked_row

                from

                dba_objects do,

                dba_data_files ddf

                where

                vwc.row_wait_obj#=do.object_id and

                vwc.row_wait_file#=ddf.file_id

                ) end show_blocked_row,

                lpad(' ',2*(level-1))||gs.module module,

                gs.machine,

                gs.username db_user,

                gs.osuser,

                gs.client_identifier client_id,

                gs.action,

                gs.program,

                case when gs.type<>'BACKGROUND' then 'alter system disconnect session '''||gs.sid||','||gs.serial#||',@'||gs.inst_id||''' immediate;' end disconnect_db_session,

                case when gs.type<>'BACKGROUND' then 'kill -9 '||gp.spid end kill_server_process,

                connect_by_root vwc.sid root_blocking_sid

                from

                v$wait_chains vwc,

                gv$session gs,

                gv$process gp,

                (

                select distinct

                gsa.sql_id,

                min(gsa.inst_id) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) inst_id,

                min(gsa.plan_hash_value) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) plan_hash_value

                from

                gv$sqlarea gsa

                ) gsa0,

                gv$sqlarea gsa

                where

                (vwc.blocker_sid is not null or vwc.num_waiters>0) and

                vwc.sid=gs.sid(+) and

                vwc.sess_serial#=gs.serial#(+) and

                vwc.instance=gs.inst_id(+) and

                gs.inst_id=gp.inst_id(+) and

                gs.paddr=gp.addr(+) and

                gs.sql_id=gsa0.sql_id(+) and

                gsa0.sql_id=gsa.sql_id(+) and

                gsa0.inst_id=gsa.inst_id(+) and

                gsa0.plan_hash_value=gsa.plan_hash_value(+)

                connect by

                prior vwc.sid=vwc.blocker_sid and

                prior vwc.sess_serial#=vwc.blocker_sess_serial# and

                prior vwc.instance=vwc.blocker_instance

                start with

                vwc.blocker_sid is null