Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
DDL Statements
ALTER
CREATE
RENAME
ANALYZE
DROP
TRUNCATE
COMMENT
DDL_LOCK_TIMEOUT
Lock Timeout
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@pdbdev
CREATE TABLE test1 AS
SELECT table_name, tablespace_name
FROM user_tables;
Step
Session 1
Session 2
1
conn uwclass/uwclass@pdbdev
conn uwclass/uwclass@pdbdev
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%';