Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

[Oracle] CREATE / REBUILD INDEX ONLINE - Table lock

stefan_koehler
Active Contributor
‎06-18-2008 11:16 PM

The theory

You can peform DML statements while you create or rebuild an index object online, but in the beginning and in the end the table is locked for a very short moment.
If you have a still ongoing DML operation which is not commited or rolled back you can run into a lock situation.

The documentation

Quote from the SQL documentation:
"Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index"


Quote from the Administrator Guide:
"While you can perform DML operations during an online index build, Oracle recommends that you do not perform major/large DML operations during this procedure. This is because while the DML on the base table is taking place it holds a lock on that resource. The DDL to build the index cannot proceed until the transaction acting on the base table commits or rolls back, thus releasing the lock."

The proof


This example table locks while an CREATE INDEX ONLINE are created on a 6.40 system with Oracle 10.2.0.2.0 on SLES 10.

1) The test table BALDAT

desc SAPDAT.BALDAT;
Name                           Null     Type
------------------------------ -------- ----------------
MANDANT                        NOT NULL VARCHAR2(9)
RELID                          NOT NULL VARCHAR2(6)
LOG_HANDLE                     NOT NULL VARCHAR2(66)
BLOCK                          NOT NULL VARCHAR2(18)
SRTF2                          NOT NULL NUMBER(10)
CLUSTR                         NOT NULL NUMBER(5)
CLUSTD                                  LONG RAW()



2) Create a lock situation with oracle default setting

Session 1:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
82

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('001');
1 row created.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (68,69,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
82                     TM   17414                  0                      3                      0                      0
82                     TX   262158                 4225                   6                      0                      0


Session 2:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
69

SQL> CREATE INDEX SAPDAT.BALDAT_TEST ON SAPDAT.BALDAT(MANDANT) ONLINE;

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (68,69,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
69                     TM   51437                  0                      4                      0                      0
69                     DL   17414                  0                      3                      0                      0
69                     DL   17414                  0                      3                      0                      0
69                     TM   17414                  0                      2                      4                      0
82                     TX   262158                 4225                   6                      0                      0
82                     TM   17414                  0                      3                      0                      1


Session 3:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
68

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('002');

Session 3 is hanging now!

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (68,69,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
68                     TM   17414                  0                      0                      3                      0                     
69                     DL   17414                  0                      3                      0                      0                     
69                     DL   17414                  0                      3                      0                      0                     
69                     TM   17414                  0                      2                      4                      0                     
69                     TM   51437                  0                      4                      0                      0                     
82                     TM   17414                  0                      3                      0                      1                     
82                     TX   262158                 4225                   6                      0                      0                     


3) Create a lock situation with event "10629 trace name context forever, level <n>"

Session 1:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
70

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('001');
1 row created.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (69,70,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
70                     TX   524313                 4218                   6                      0                      0
70                     TM   17414                  0                      3                      0                      0


Session 2:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
82

SQL> CREATE INDEX SAPDAT.BALDAT_TEST ON SAPDAT.BALDAT(MANDANT) ONLINE;

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (69,70,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
70                     TM   17414                  0                      3                      0                      0                     
70                     TX   524313                 4218                   6                      0                      0                     
82                     TM   51447                  0                      4                      0                      0                     
82                     TM   17414                  0                      2                      0                      0                     
82                     DL   17414                  0                      3                      0                      0                     
82                     DL   17414                  0                      3                      0                      0

Session 3:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
69

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('002');
1 row created.

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (69,70,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
69                     TX   131076                 4054                   6                      0                      0
69                     TM   17414                  0                      3                      0                      0
70                     TM   17414                  0                      3                      0                      0
70                     TX   524313                 4218                   6                      0                      0
82                     TM   51447                  0                      4                      0                      0
82                     DL   17414                  0                      3                      0                      0
82                     DL   17414                  0                      3                      0                      0
82                     TM   17414                  0                      2                      0                      0

Session 3 is not hanging and you can still perform DML on the table.

Sapnote #869521 describes the oracle event 10629.

References

Labels in this area
Popular Blog Posts