分类: Oracle
2008-10-12 11:49:13
DDL_LOCK_TIMEOUT
parameter, which can be set at instance or session level using the ALTER SYSTEM
and ALTER SESSION
commands respectively.DDL_LOCK_TIMEOUT
parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don't commit the insert.Leave this session alone and in a new session, set theCREATE TABLE lock_tab ( id NUMBER ); INSERT INTO lock_tab VALUES (1);
DDL_LOCK_TIMEOUT
at session level to a non-zero value and attempt to add a column to the table.The session will wait for 30 seconds before failing.ALTER SESSION SET ddl_lock_timeout=30; ALTER TABLE lock_tab ADD ( description VARCHAR2(50) );
If we repeat theALTER TABLE lock_tab ADD ( * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ALTER TABLE
command and commit the insert in the first session within 30 seconds, the ALTER TABLE
will return a successful message.For more information see:ALTER TABLE lock_tab ADD ( description VARCHAR2(50) ); Table altered. SQL>