This article presents new features of 11g related to locking.

• DDL commands can wait for DML locks to be released:
  • DDL_LOCK_TIMEOUT initialization parameter  0..100000 seconds
• New WAIT[timeout] clause for LOCK TABLE command
• Exclusive locks(X) switch for shared exclusive for following commands

  • CREATE INDEX ONLINE

  • CREATE MATERIALIZED VIEW LOG

  • ALTER TABLE ENABLE CONSTRAINT NOVALIDATE

Example for DDL command and DDL_LOCK_TIMEOUT parameter

ALTER session SET nls_language=english;
 
--set to default parameter ddl_lock_timeout
ALTER session SET ddl_lock_timeout=0;
 
--drop table test
DROP TABLE test;
 
--create table test
CREATE TABLE test
(id NUMBER);
 
/* SESSION 1 */
--create exclusive lock on table
LOCK TABLE test IN EXCLUSIVE MODE;
--or lock for row and table
INSERT INTO test VALUES(10);
 
/* SESSION 2 */
 
--drop column in nowait mode
ALTER TABLE test DROP column id;
/*
immediate error
SQL Error: ORA-00054: resource busy and acquire 
with NOWAIT specified or timeout expired
*/
 
--set ddl_lock_timeout to 10 seconds
ALTER session SET ddl_lock_timeout=10;
 
--waits maximum 10 seconds
ALTER TABLE test DROP column id;
/*
error after 10 seconds
SQL Error: ORA-00054: resource busy and acquire 
with NOWAIT specified or timeout expired
*/

Example for LOCK WAIT[timeout]

alter session set nls_language=english;

--drop table test
drop table test;

--create table test
create table test
(id number);

/* SESSION 1 */

--lock table in exclusive mode
lock table test in exclusive mode;

/* SESSION 2 */

--it has no effect on next command
alter session set ddl_lock_timeout=1;

--try to lock table in exclusive mode but waits
--maximum 10 seconds to acquire lock
lock table test in exclusive mode wait 10;

--it has no effect on next command
alter session set ddl_lock_timeout=1;

--try to lock table in exclusive mode but can wait forever
--session will be hanging till session 1 release lock
lock table test in exclusive mode;

Have a fun 🙂

Tomasz