DDL_LOCK_TIMEOUT does not prevent ORA-14450

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.

ORA-8103s

looking forward to Tanel Poder’s webinar this evening.
Especially, the announcement to learn something about “ORA-8103s” called my attention.
The reason is, that I was hitting some bug or bugs in 11.2 (sporadically and not reproducible ORA-8103s) last November and working with support on this since then (issue has status “Development working” since some weeks now)
They used to occur several times per day in complex queries (selects in batch jobs).
Usually, the following workaround was helpful: wait for a minute, then issue the same select again.
So, we “patched” the batch jobs they occurred in, set parameter event to
‘8103 trace name errorstack forever, level 10’
and uploaded tons (dozens of GBs) of tracefiles to support.
In the meantime, we applied some patches concerning other issues and monitored the number of ORA-08103’s per day via
with days as
(
select date'2010-11-19' + level as day
from dual
connect by date'2010-11-19' + level <= sysdate
)
select
days.day,
count(distinct x.message_text) as count_08103s_per_day
from x$dbgalertext x, days
where x.message_text(+) like '%ORA-08103%'
and trunc(x.originating_timestamp(+)) = days.day
group by days.day
order by days.day


After ugrading to 11.2.0.2.2 via Patch 11724916, this number went strongly down, but we still had issues from time to time with a false ORA-08103.

Today I noticed that since applying Patch 9285259 we did hit no ORA-08103 anymore !

Coincidence ? I don’t know

Update August 28th:
I was wrong, false ORA-08103s have not disappeared yet, we encountered some of them last week, re-activated the SR (which is open unsolved now since last November …).
The reason that I thought it had disappeared was that I didn’t see it mentioned in the alert.log anymore, however, since I had unset event, it was not logged there.