7
> ALTER PACKAGE blah COMPILE;
(wait about 10 minutes)
> ORA-04021: timeout occurred while waiting to lock object

I understand why I get the timeout error (the package is in use).

Does anyone know if there's a way to change the default 10 minute wait interval? Can this be configured at a database / session / statement level?

Thanks

cagcowboy
  • 30,012
  • 11
  • 69
  • 93

1 Answers1

6

there is a DDL_LOCK_TIMEOUT parameter since 11gR1 :

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

I'm not sure you can change the default timeout in the previous releases.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Thanks. Shame it's not in 10g. – cagcowboy Sep 09 '09 at 10:26
  • 1
    this didnt actually work for me.. have you tried this? i did a `alter session set ddl_lock_timeout = 5;` then an `alter package xxx compile;` but still waited several minutes – ShoeLace Jul 11 '14 at 13:54
  • 3
    @ShoeLace I think it will only change the behaviour if you wait on DML, for instance trying to drop a table while someones has an uncommited transaction on rows of this table (where the default behaviour is to raise an error immediately). It will probably not work when replacing a package currently in use (where the default behaviour is to wait until the package is not used anymore). – Vincent Malgrat Jul 11 '14 at 14:06