Init parameterProcesses & sessions

DDL_LOCK_TIMEOUT

What it controls

Seconds a DDL statement waits for a lock before failing with ORA-00054.

Know this before you change it

Set it for your own session before DDL on busy tables, rather than for the whole instance.

Default: 0

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'ddl_lock_timeout';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'ddl_lock_timeout' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET ddl_lock_timeout = 30 SCOPE = BOTH SID = '*';11 12-- Or for your own session only13ALTER SESSION SET ddl_lock_timeout = 30;14 15-- Remove it from the spfile to go back to the default at the next restart16ALTER SYSTEM RESET ddl_lock_timeout SCOPE = SPFILE SID = '*';

Related scripts

• Locked objects and who holds themEvery object with a DML lock, the session holding it and the lock mode in plain words. Exclusive locks from sessions that are INACTIVE usually mean…

Open in the parameter referenceOracle's reference

More in Processes & sessions

• PROCESSESThe most operating system processes that can connect to the instance, including background processes.
• SESSIONSThe most sessions the instance allows.
• OPEN_CURSORSThe most cursors one session can have open at once.
• SESSION_CACHED_CURSORSHow many closed cursors each session keeps cached, which saves repeated soft parses.
• JOB_QUEUE_PROCESSESThe most job slave processes for Scheduler and DBMS_JOB jobs.
• RESOURCE_LIMITWhether profile resource limits such as idle time and CPU per call are enforced.