Published on May 12, 2017 by
Greg Mays
Oracle's RDBMS as a product is comprised of thousands of tweakable settings and parameters. Even for someone who works with it every day and actively reads articles on Oracle-related topics and features, useful stuff often still slips through the info net until something hits the wall and you have to dig into a specific usage scenario or error. Here's what we learned from doing some digging:
A few weeks back while working through a deployment involving DDL against an active system during a planned release window we were running into the notorious "ORA-00054: resource busy and acquire with NOWAIT specified" error. After multiple repeat attempts, Oracle's suggested course of action for the error: to apply the DDL we were continuing to fail. We were considering forcing some services which were heavily used to take a quick shutdown or to deploy the Gatling gun ("ALTER SYSTEM KILL SESSION...") to take down the active sessions monopolizing the object.
Making a last ditch search on support.oracle.com and the inevitable Google search brought up a 11g parameter ddl_lock_timeout that would potentially force a DDL attempt to automatically retry the DDL request for a specified amount of time before timing out. Attempting this for our situation allowed our DDL to succeed and we have now incorporated this ALTER SESSION command in our deployment scripts with good success.
-- Set ddl_lock_timeout to 5 minutes to help with DDLs on an active tables and hopefully
-- prevent ORA-00054: resource busy and acquire with NOWAIT specified
alter session set ddl_lock_timeout=300;
This parameter was introduced in 11.1 and can be set at instance or session level and sets the number of seconds a DDL command will wait in the DML lock queue for the resource to become available before throwing the ORA-00054.
Problem solved! At least for the situations we have applied it to thus far. Reading deeper into the docs and support.oracle.com there are some situations where this is NOT the panacea for this issue but it is certainly worth the attempt for the majority of situations.
Documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm#REFRN10267
