Lock Incident Details Job

I've been asked to provide statistics of our Lock Incidents happened last month? I had have only the number of the incidents with no details about who were locked, in which module happened and what was the caused query so I decided to enhance my Lock Job Query to contain more details about each indecent.

we're going to have 2 files;

Lock.sql which contains the following:

spool on
spool /u02/app/oracle/DBScripts/output.log

SELECT COUNT (1) AS "Number of Blocked Sessions"
  FROM gv$lock l1,
       gv$session s1,
       gv$lock l2,
       gv$session s2
 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
HAVING COUNT (1) > 0;

-- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Blocking Locks                                              |
PROMPT | Database: ORCL                                                        |
PROMPT +------------------------------------------------------------------------+
SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (Summary)                                               |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET serveroutput ON FORMAT WRAPPED
SET feedback OFF

DECLARE
   CURSOR cur_BlockingLocks
   IS
        SELECT iw.instance_name AS waiting_instance,
               sw.status AS waiting_status,
               lw.sid AS waiting_sid,
               sw.serial# AS waiting_serial_num,
               sw.username AS waiting_oracle_username,
               sw.osuser AS waiting_os_username,
               sw.machine AS waiting_machine,
               pw.spid AS waiting_spid,
               SUBSTR (sw.terminal, 0, 39) AS waiting_terminal,
               SUBSTR (sw.program, 0, 39) AS waiting_program,
               ROUND (lw.ctime / 60) AS waiting_lock_time_min,
               DECODE (lh.TYPE,
                       'CF', 'Control File',
                       'DX', 'Distributed Transaction',
                       'FS', 'File Set',
                       'IR', 'Instance Recovery',
                       'IS', 'Instance State',
                       'IV', 'Libcache Invalidation',
                       'LS', 'Log Start or Log Switch',
                       'MR', 'Media Recovery',
                       'RT', 'Redo Thread',
                       'RW', 'Row Wait',
                       'SQ', 'Sequence Number',
                       'ST', 'Diskspace Transaction',
                       'TE', 'Extend Table',
                       'TT', 'Temp Table',
                       'TX', 'Transaction',
                       'TM', 'DML',
                       'UL', 'PLSQL User_lock',
                       'UN', 'User Name',
                       'Nothing-')
                  AS waiter_lock_type,
               DECODE (lw.request,  0, 'None'        /* Mon Lock equivalent */
                                             ,  1, 'NoLock'            /* N */
                                                           ,  2, 'Row-Share (SS)' /* L */
                                                                                 ,  3, 'Row-Exclusive (SX)' /* R */
                                                                                                           ,  4, 'Share-Table' /* S */
                                                                                                                              ,  5, 'Share-Row-Exclusive (SSX)' /* C */
                                                                                                                                                               ,  6, 'Exclusive' /* X */
                                                                                                                                                                                ,  '[Nothing]') AS waiter_mode_request,
               ih.instance_name AS locking_instance,
               sh.status AS locking_status,
               lh.sid AS locking_sid,
               sh.serial# AS locking_serial_num,
               sh.username AS locking_oracle_username,
               sh.osuser AS locking_os_username,
               sh.machine AS locking_machine,
               ph.spid AS locking_spid,
               SUBSTR (sh.terminal, 0, 39) AS locking_terminal,
               SUBSTR (sh.program, 0, 39) AS locking_program,
               ROUND (lh.ctime / 60) AS locking_lock_time_min,
               aw.sql_text AS waiting_sql_text
          FROM gv$lock lw,
               gv$lock lh,
               gv$instance iw,
               gv$instance ih,
               gv$session sw,
               gv$session sh,
               gv$process pw,
               gv$process ph,
               gv$sqlarea aw
         WHERE     iw.inst_id = lw.inst_id
               AND ih.inst_id = lh.inst_id
               AND sw.inst_id = lw.inst_id
               AND sh.inst_id = lh.inst_id
               AND pw.inst_id = lw.inst_id
               AND ph.inst_id = lh.inst_id
               AND aw.inst_id = lw.inst_id
               AND sw.sid = lw.sid
               AND sh.sid = lh.sid
               AND lh.id1 = lw.id1
               AND lh.id2 = lw.id2
               AND lh.request = 0
               AND lw.lmode = 0
               AND (lh.id1, lh.id2) IN (SELECT id1, id2
                                          FROM gv$lock
                                         WHERE request = 0
                                        INTERSECT
                                        SELECT id1, id2
                                          FROM gv$lock
                                         WHERE lmode = 0)
               AND sw.paddr = pw.addr(+)
               AND sh.paddr = ph.addr(+)
               AND sw.sql_address = aw.address
      ORDER BY iw.instance_name, lw.sid;

   TYPE t_BlockingLockRecord IS RECORD
   (
      WaitingInstanceName     VARCHAR2 (16),
      WaitingStatus           VARCHAR2 (8),
      WaitingSid              NUMBER,
      WaitingSerialNum        NUMBER,
      WaitingOracleUsername   VARCHAR2 (30),
      WaitingOSUsername       VARCHAR2 (30),
      WaitingMachine          VARCHAR2 (64),
      WaitingSpid             VARCHAR2 (12),
      WaitingTerminal         VARCHAR2 (30),
      WaitingProgram          VARCHAR2 (48),
      WaitingLockTimeMinute   NUMBER,
      WaiterLockType          VARCHAR2 (30),
      WaiterModeRequest       VARCHAR2 (30),
      LockingInstanceName     VARCHAR2 (16),
      LockingStatus           VARCHAR2 (8),
      LockingSid              NUMBER,
      LockingSerialNum        NUMBER,
      LockingOracleUsername   VARCHAR2 (30),
      LockingOSUsername       VARCHAR2 (30),
      LockingMachine          VARCHAR2 (64),
      LockingSpid             VARCHAR2 (12),
      LockingTerminal         VARCHAR2 (30),
      LockingProgram          VARCHAR2 (48),
      LockingLockTimeMinute   NUMBER,
      SQLText                 VARCHAR2 (1000)
   );

   TYPE t_BlockingLockRecordTable IS TABLE OF t_BlockingLockRecord
                                        INDEX BY BINARY_INTEGER;

   v_BlockingLockArray           t_BlockingLockRecordTable;
   v_BlockingLockRec             cur_BlockingLocks%ROWTYPE;
   v_NumBlockingLocksIncidents   BINARY_INTEGER := 0;
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);

   OPEN cur_BlockingLocks;

   LOOP
      FETCH cur_BlockingLocks INTO v_BlockingLockRec;

      EXIT WHEN cur_BlockingLocks%NOTFOUND;

      v_NumBlockingLocksIncidents := v_NumBlockingLocksIncidents + 1;

      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingInstanceName :=
         v_BlockingLockRec.waiting_instance;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingStatus :=
         v_BlockingLockRec.waiting_status;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingSid :=
         v_BlockingLockRec.waiting_sid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingSerialNum :=
         v_BlockingLockRec.waiting_serial_num;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingOracleUsername :=
         v_BlockingLockRec.waiting_oracle_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingOSUsername :=
         v_BlockingLockRec.waiting_os_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingMachine :=
         v_BlockingLockRec.waiting_machine;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingSpid :=
         v_BlockingLockRec.waiting_spid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingTerminal :=
         v_BlockingLockRec.waiting_terminal;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingProgram :=
         v_BlockingLockRec.waiting_program;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingLockTimeMinute :=
         v_BlockingLockRec.waiting_lock_time_min;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaiterLockType :=
         v_BlockingLockRec.waiter_lock_type;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaiterModeRequest :=
         v_BlockingLockRec.waiter_mode_request;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingInstanceName :=
         v_BlockingLockRec.locking_instance;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingStatus :=
         v_BlockingLockRec.locking_status;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingSid :=
         v_BlockingLockRec.locking_sid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingSerialNum :=
         v_BlockingLockRec.locking_serial_num;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingOracleUsername :=
         v_BlockingLockRec.locking_oracle_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingOSUsername :=
         v_BlockingLockRec.locking_os_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingMachine :=
         v_BlockingLockRec.locking_machine;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingSpid :=
         v_BlockingLockRec.locking_spid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingTerminal :=
         v_BlockingLockRec.locking_terminal;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingProgram :=
         v_BlockingLockRec.locking_program;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingLockTimeMinute :=
         v_BlockingLockRec.locking_lock_time_min;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).SQLText :=
         v_BlockingLockRec.waiting_sql_text;
   END LOOP;

   CLOSE cur_BlockingLocks;

   DBMS_OUTPUT.
    PUT_LINE (
      'Number of blocking lock incidents: ' || v_BlockingLockArray.COUNT);
   DBMS_OUTPUT.PUT (CHR (10));

   FOR RowIndex IN 1 .. v_BlockingLockArray.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Incident ' || RowIndex);
      DBMS_OUTPUT.
       PUT_LINE (
         '---------------------------------------------------------------------------------------------------------');
      DBMS_OUTPUT.
       PUT_LINE (
         '                        WAITING                                  BLOCKING');
      DBMS_OUTPUT.
       PUT_LINE (
         '                        ---------------------------------------- ----------------------------------------');
      DBMS_OUTPUT.
       PUT_LINE (
            'Instance Name         : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingInstanceName, 41)
         || v_BlockingLockArray (RowIndex).LockingInstanceName);
      DBMS_OUTPUT.
       PUT_LINE (
            'Oracle SID            : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingSid, 41)
         || v_BlockingLockArray (RowIndex).LockingSid);
      DBMS_OUTPUT.
       PUT_LINE (
            'Serial#               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingSerialNum, 41)
         || v_BlockingLockArray (RowIndex).LockingSerialNum);
      DBMS_OUTPUT.
       PUT_LINE (
            'Oracle User           : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingOracleUsername, 41)
         || v_BlockingLockArray (RowIndex).LockingOracleUsername);
      DBMS_OUTPUT.
       PUT_LINE (
            'O/S User              : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingOSUsername, 41)
         || v_BlockingLockArray (RowIndex).LockingOSUsername);
      DBMS_OUTPUT.
       PUT_LINE (
            'Machine               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingMachine, 41)
         || v_BlockingLockArray (RowIndex).LockingMachine);
      DBMS_OUTPUT.
       PUT_LINE (
            'O/S PID               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingSpid, 41)
         || v_BlockingLockArray (RowIndex).LockingSpid);
      DBMS_OUTPUT.
       PUT_LINE (
            'Terminal              : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingTerminal, 41)
         || v_BlockingLockArray (RowIndex).LockingTerminal);
      DBMS_OUTPUT.
       PUT_LINE (
         'Lock Time             : '
         || RPAD (
               v_BlockingLockArray (RowIndex).WaitingLockTimeMinute
               || ' minutes',
               41)
         || v_BlockingLockArray (RowIndex).LockingLockTimeMinute
         || ' minutes');
      DBMS_OUTPUT.
       PUT_LINE (
            'Status                : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingStatus, 41)
         || v_BlockingLockArray (RowIndex).LockingStatus);
      DBMS_OUTPUT.
       PUT_LINE (
            'Program               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingProgram, 41)
         || v_BlockingLockArray (RowIndex).LockingProgram);
      DBMS_OUTPUT.
       PUT_LINE (
         'Waiter Lock Type      : '
         || v_BlockingLockArray (RowIndex).WaiterLockType);
      DBMS_OUTPUT.
       PUT_LINE (
         'Waiter Mode Request   : '
         || v_BlockingLockArray (RowIndex).WaiterModeRequest);
      DBMS_OUTPUT.
       PUT_LINE (
         'Waiting SQL           : ' || v_BlockingLockArray (RowIndex).SQLText);
      DBMS_OUTPUT.PUT (CHR (10));
   END LOOP;
END;
/
spool off
exit


The second file is Lock.sh which contains

ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_UNQNAME=imcpr; export ORACLE_UNQNAME
ORACLE_SID=orcl2; export ORACLE_SID
$ORACLE_HOME/bin/sqlplus / as sysdba @/u02/app/oracle/DBScripts/Lock.sql
FILE=/u02/app/oracle/DBScripts/ouput.log

grep -w "Number of Blocked Sessions" $FILE >/dev/null

if [ $? -eq 0 ]
        then

                   cat /u02/app/oracle/DBScripts/AlterSys.sql | mail -v hmhamed@
feco.com

        else
                    echo null
fi

Now let's schedule this executable file in the crontab to be executed every 5 minutes

$ crontab -e

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /u02/app/oracle/DBScripts/Lock.sh -cron -silent start

The output form will be as below:

Number of Blocked Sessions                                                     

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

                         2                                                     

+------------------------------------------------------------------------+

| Report   : Blocking Locks                                              |

| Database: ORCL                                                             |

+------------------------------------------------------------------------+

+------------------------------------------------------------------------+

| BLOCKING LOCKS (Summary)                                               |

+------------------------------------------------------------------------+

Number of blocking lock incidents: 1

Incident 1

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

                        WAITING                                  BLOCKING

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

Instance Name     : orcl1                                   orcl1

Oracle SID            : 584                                      693

Serial#                   : 25655                                  8223

Oracle User           : APPS                                 APPS

O/S User               : hmhamed                           hmhamed

Machine               : FECO-PC                            FECO-PC

O/S PID                : 725012                                 442788

Terminal               : FECO-PC                            FECO-PC

Lock Time            : 4 minutes                            4 minutes

Status                   : ACTIVE                              INACTIVE

Program               : Toad.exe                              Toad.exe

Waiter Lock Type          : Transaction

Waiter Mode Request   : Exclusive

Waiting SQL                   : update reg_type set required='N'

How to Query Oracle Hidden Parameters

The hidden parameters cannot queried from v$parameter or "show parameter" unless they are changed explicitly by "alter system" command or in init.ora. However having SYSDBA role make you able to query those parameters alongside their values and descriptions as follow:

  SELECT a.ksppinm Param,
         b.ksppstvl SessionVal,
         c.ksppstvl InstanceVal,
         a.ksppdesc Descr
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
   WHERE     a.indx = b.indx
         AND a.indx = c.indx
         AND a.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY 1

Caution:

Hidden and undocumented parameters are very critical and only be useful to Sr. DBA who clearly understand how this parameter would change the internal behaviour for Oracle. So don't change any of them unless suggested by Oracle Support for specific case.

How to convert Partition table from RANGE to INTERVAL

This topic is pursue to previous tips by Burleson Team to show in steps how to convert a partition table from range to interval in order to avoid ORA-14400 error described before.

Let’s assume our partition table as below:

SQL> CREATE TABLE SALES

  2    (

  3   SALES_DATE DATE not null,

  4   SALES_DESC VARCHAR2(100)

  5    )

  6   PARTITION BY RANGE (SALES_DATE)

  7    (

  8   PARTITION P1  VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD') ),

  9   PARTITION P2  VALUES LESS THAN (TO_DATE('2012-07-01', 'YYYY-MM-DD') ),

 10   PARTITION P3  VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD') ),

 11   PARTITION PMAX VALUES LESS THAN (MAXVALUE));

Table created.

 The first step is to ensure that we don’t have MAXVALUE partition to avoid:

ORA-14759: SET INTERVAL is not legal on this table.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'SALES' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

PMAX            NO MAXVALUE

As described above that SALES table have a MAXVALUE partition and that would prevent the start of interval partitioning. Since this partition made to avoid outage insert, it should have zero row so we can drop it with no problem otherwise backup your data and truncate the partition and then drop it.

SQL> select count(*) from SALES partition (PMAX);

  COUNT(*)

----------

         0

SQL> alter table SALES drop partition PMAX;

Table altered.

In the next step we will convert the SALES table to interval and define the interval period of each partition.

Here I’m going to define 6 months interval:

SQL> alter table SALES set INTERVAL(NUMTOYMINTERVAL(6, 'MONTH'));

Table altered.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'ORDERS' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

Next, let’s test it by insert a row out of partitioned date range.

SQL> insert into SALES values(TO_DATE('2013-05-10', 'YYYY-MM-DD'),’DELL PC 930’ );

SQL> commit;

Commit complete.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'ORDERS' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P731        YES TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

The newly created partition has a system generated name and YES value for INTERVAL column and it has been allocated the right 6 months which is defined previously.

Finally, easily we can rename this system generated name to any name by:

SQL> alter table SALES rename partition SYS_P731 to P4;

Table altered.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'ORDERS' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P4              YES TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA