Oracle Data Definition Language DDL

Oracle Data Definition Language (DDL) Version 11.2.0.3

General Information

Noterow

DDL Statements

ALTER	CREATE	RENAME
ANALYZE	DROP	TRUNCATE
COMMENT

DDL_LOCK_TIMEOUT

Lock Timeout synt

DDL_LOCK_TIMEOUT(<number_of_seconds>);

conn / as sysdba

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'ddl_lock%';

-- range is 0 (the default) to 100,000
ALTER SYSTEM SET ddl_lock_timeout=20;
-- or
ALTER SESSION SET ddl_lock_timeout=20;

Lock Timeout Demo

conn uwclass/uwclass

CREATE TABLE test1 AS
SELECT table_name, tablespace_name
FROM user_tables;

Step	Session 1	Session 2
1	conn uwclass/uwclass	conn uwclass/uwclass
2	desc test1 SELECT * FROM test1;	desc test1 SELECT * FROM test1;
3	LOCK TABLE test1 IN exclusive MODE nowait;	RENAME test1 TO test2;
4	COMMIT;	RENAME test1 TO test2;

5		ALTER SYSTEM SET ddl_lock_timeout=60; SELECT name, value FROM gv$parameter WHERE name LIKE 'ddl_lock%';
6	LOCK TABLE test2 IN exclusive MODE nowait;	RENAME test2 TO test3;
7	COMMIT;

ALTER SYSTEM SET ddl_lock_timeout=0;

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'ddl_lock%';