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.

What could make your query suddenly run slow .

Many times I had been asked from Developer Team "Why this query become slow when it used to run quickly?". I think Jonathan Lewis well answered this question as follows:

1. A change in execution plan due to unlucky bind variable peeking with histograms
2. A change in execution plan because the statistics have been slowly going out of sync with the data/query
3. A change in execution plan because new (and unlucky) statistics have been generated since the last time query ran.
4. A change in execution plan because a few new, empty, partitions have been added to a critical table
5. An unlucky change in execution plan because a small change in actual data volume (with correct stats in place) can have a dramatic impact on the shape of the plan
6. A change in execution plan because you had an incomplete set of hints in your SQL and your luck just ran out
7. An unlucky change in data order that has a big impact on the success of subquery caching
8. A small slippage in timing that results in the SQL suffering a direct data collision (locking / read-consistency) with some other process.
9. A small slippage in timing that leaves the SQL running concurrently with something that is processing unrelated data but competing for the raw (disc / CPU / PX Slave) resources.
10. A small slippage in timing that means some other process cleared everything you wanted from the buffer cache before your SQL started running.
11. A small slippage in timing that means a process that normally follows your SQL preceded it, and you’re spending all your time on delayed block cleanout.
12. Someone dropped some indexes (or made them invalid)
13. Someone rebuilt one or more indexes since the last time the SQL executed
14. Someone has done a “shrink space” on a critical table since the last time you ran the SQL

Reference:

http://jonathanlewis.wordpress.com/2013/12/23/plan-changes/

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

Oracle Interval Partitioning Tips

I just found this topic much wonderful and helpful and there is no rebloging tool on dba-oracle.com so I copied the same.

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.

Range partitioning allows an object to be partitioned by a specified range on the partitioning key.  For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition. 

Therefore, every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result:

ORA-14400: inserted partition key does not map to any partition 

If this situation occurs, data loading will fail until the new partitions are created.  This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process.  Mission critical reports might be delayed or incorrect due to this problem. 

An Interval Partitioning Example

Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data.  Interval partitioning is enabled in the table’s definition by defining one or more range partitions and including a specified interval.  For example, consider the following table:

create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3),
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

Here, two partitions have been defined and an interval of one month has been specified.  If data is loaded into this table with a later date than the greatest defined partition, Oracle will automatically create a new partition for the new month.  In the table above, the greatest defined interval is between July 1, 2007 and August 1, 2007. 

Inserting a row that has a date later than August 1, 2007 would raise an error with normal range partitioning.  However, with interval partitioning, Oracle determines the high value of the defined range partitions, called the transition point, and creates new partitions for data that is beyond that high value.

insert into pos_data (start_date, store_id, inventory_id, qty_sold)
values ( '15-AUG-07', 1, 1, 1);

SELECT
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_POSITION,
   HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Notice that a system generated partition named SYS_P81 has been created upon inserting a row with a partition key greater than the transition point.  Oracle will manage the creation of new partitions for any value beyond the high value.  Therefore, the values do not need to be inserted in sequence.

Since the partitions are named automatically, Oracle has added a new syntax in order to reference specific partitions effectively.  The normal way to reference a specific partition is to use the partition (partition_name) in the query:

select
   *
from
   pos_data partition (SYS_P81);

However, it would be cumbersome to look up the system generated partition name each time.  Therefore, the new syntax to specify a partition is by using the partition for (DATE) clause in the query:

select
   *
from
   pos_data partition for (to_date('15-AUG-2007','dd-mon-yyyy')); 

Another useful feature of partitioning is the ability to distribute partitions across different tablespaces.  With interval partitioning, this can be accomplished by naming all of the tablespaces in the table definition’s “store in” clause.  The system created partitions are then assigned to different tablespaces in a round robin manner.  For example, if the choice was to distribute the table across three tablespaces - tablespaceA, tablespaceB, and tablespaceC - use the following clause in the table definition.

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (tablespaceA, tablespaceB, tablespaceC)

Restrictions on  Interval Partitioning

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables
• Must use only one partitioning key column and it must be a DATE or NUMBER
• Cannot create domain indexes on interval partitioned tables
• Are not supported at the sub-partition level

This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using alter table <table_name> set interval(expr).

Consider this range partitioned table:

create table
pos_data_range (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
   PARTITION BY RANGE (start_date)
(
   PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

If a row with a date of August 15, 2007 is inserted into the table, it will cause an error.

SQL> insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
  2  values ( '15-AUG-07', 1, 1, 1);
insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Interval partitioning is now enabled, and the row with 15-AUG-07 can be inserted without error since Oracle will automatically create the new partition. To convert the table back to only range partitioning, use the following command:

alter table pos_data_range set INTERVAL();

The table is converted back to a range partitioned table and the boundaries for the interval partitions are set to the boundaries for the range partitions.

Using the same syntax, the interval can also be changed for existing interval partitioned tables.  If changing the original table to be partitioned every three months instead of monthly, use:

alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

After inserting a row with the date of 15-NOV-07, a new partition is automatically generated with a high value of 01-DEC-07.

insert into
   pos_data (start_date, store_id, inventory_id, qty_sold)
values
   ('15-NOV-07', 1, 1, 1);

SELECT
   TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE    TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P84    TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

The tablespace storage of the interval partitioned table can also be changed using a similar syntax.  For example, when using a round robin tablespace assignment for the table between tablespace1 to tablespace3, issue the following command:

alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);

Oracle interval partitioning offers a very useful extension to range partitioning.  This greatly improves the manageability of range partitioned tables.  In addition to providing system generated new partitions, Oracle has provided a new syntax to simplify the reference of specific partitions.  Furthermore, Oracle offers a group of commands to manage the new partitioning option. 

---

Interval Partitioning for Oracle 11g

By Ben Prusinski

Interval partitions build upon the range partitioning for Oracle 11g. Interval partitioning resolves the limitations built into range partitioning when a specific range is unknown by the  developer or DBA creating the partitions for the table. It tells Oracle to automatically setup new  partitions for a particular interval when data inserted to tables are greater than the range partitions. 
As such the requirement of interval partitioning dictates that at least one range partition is specified. Once the range partitioning key is given for the high value of the range partitions, this transition point is used  as the baseline to create interval partitions beyond this point.
The nice thing about the new interval  partitioning feature for Oracle 11g is that it eases the management of new partitions for the busy  Oracle DBA or development staff. The following exercise will demonstrate how interval partitioning  works with Oracle 11g. First we will create a new table to use for interval partitioning:

SQL> CREATE TABLE sales_interval
  2  (product_id                NUMBER(6),
  3  customer_id                NUMBER,
  4  time_id                    DATE,
  5  channel_info               CHAR(1),
  6  promo_id                   NUMBER(6),
  7  qty_sold                   NUMBER(3),
  8  amt_sold                   NUMBER(10,2)
  9  )
 10  PARTITION BY RANGE (time_id)
 11  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
 12  (PARTITION t0 VALUES LESS THAN (TO_DATE('1-1-2005','DD-MM-YYYY')),
 13  PARTITION t1 VALUES LESS THAN (TO_DATE('1-1-2006','DD-MM-YYYY')),
 14  PARTITION t2 VALUES LESS THAN (TO_DATE('1-7-2006','DD-MM-YYYY')),
 15  PARTITION t3 VALUES LESS THAN (TO_DATE('1-1-2007','DD-MM-YYYY')) ); 

Table created.

Figure 1.8: Interval Partitioning Example with Oracle 11g

The above statement creates a table with four new interval based partitions using a one month period   for the width of the interval with January 1, 2007 as the transition point with t3 partition as the high  bound and the other partitions (t0-t2) as the range section with all partitions above it occur in the  interval range. However, like other forms of partitioning there are some limitations as follows.
 Interval partitioning restrictions include:

• Index Organized tables (IOTs) are not supported by interval partitioning.
• Domain index cannot be created on interval partitioned tables.
• Only one partitioning key column can be set for the interval partition and it must be either a DATE or NUMBER data type.

 Reference:
http://www.dba-oracle.com/t_interval_partitioning.htm

Oracle 12c Database Installation

This is a step by step Oracle Database 12c Release 1 installation on Windows Server 2008 (64x).

After download the software from Oracle website, unzip downloaded files into one folder and then run the setup file you need to do the following:

     1-      After launching Oracle Installer, you may provide your email and Oracle Support password, otherwise uncheck the checkbox and press NEXT.

     2-      The next screen you will be asked if you want to download latest updates from oracle then enter your Oracle Support credential, for test purpose I’m going to select Skip Software Updates. 

     3-      Select your installation options whether to install only binaries or binaries along with upgrade current database or create new database. Choose your appropriate radio button and click Next.

     4-      In grid installation options screen select the type you want to perform according to the plan of your installation either Single node or RAC or RAC one node if have one node you are planning to add more nodes in future.

     5-      Select your additional language you might use for your database.

     6-      Select your edition which you are going to license.

     7-      One of the new features is Oracle Home User which used to run windows services for Oracle Home. If you already have an account just enter it along with its password or create a new user and that what I’m going to do.

Note: Oracle 12c supports to use non administrator account and use a standard account.

     8-      Enter your Oracle Base and Oracle Home and then click Next.

     9-      Perform Prerequisite Checks screen will check the satisfaction of the minimum requirements.

The beauty here is that you can take an action to fix come up issues without need to re launch the installer once again as it was in previous Oracle versions.

     10-      If everything goes smooth you will end up with Summary screen, showing and summarizing your inputs. Click Install after review your inputs and if you require changing some options, click edit beside the option.

      The remains two steps only to show the installation progress and the last step tells that you have finished the installation successfully, then click Finish.

Congratulations now you have the latest version of Oracle Database installed at you PC or your Server.