Sokrates on Oracle

DDL_LOCK_TIMEOUT does not prevent ORA-14450

Posted by Matthias Rogel on 29. August 2011

According to doc, “DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue.”

Actually, I don’t know exactly what is meant by that ( I have some idea what a “DML lock queue” could be, but where is the exact definition ? ), but it doesn’t help in synchronizing DDL and DML operations on a temp object:

SQL> create global temporary table t(v varchar2(1)) on commit preserve rows;

Table created.

SQL> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values('a');
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter session set ddl_lock_timeout = 100;

Session altered.

SQL> set timi on
SQL> alter /* expect exception after 100 seconds */ table t modify(v varchar2(2));
alter /* expect exception after 100 seconds */ table t modify(v varchar2(2))
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

Elapsed: 00:00:00.05
SQL> set timi off
SQL> select type from v$lock where id1 = ( select object_id from user_objects where object_name = 'T' );

TYPE
------
TO

SQL> select description from v$lock_type where type = 'TO';

DESCRIPTION
--------------------------------------------------------------------------------
Synchronizes DDL and DML operations on a temp object

Looks to me that DDL_LOCK_TIMEOUT should behave other than it does.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: