ORA-8103 and DDL on a trigger

Once upon a time i was contacted by one of internal customers with a strange situation: some job were failing with error ORA-8103 sometimes. The error itself is not a big deal; there are a lot of documents which describe this error, its reasons and recipes to solve: OERR: ORA-8103 “object no longer exists” / Troubleshooting, Diagnostic and Solution [ID 8103.1] or a wonderful Tanel Poder’s article. However, the case sounded fantastic: the error fired inside of a job which ran a query on a table, while other job disabled a trigger on that table.

I created a test case on that database which fully proved the case, once again: enable/disable on a table’s trigger lead to fail with ORA-8103 a query on those table independently on the state of the trigger.

The test case itself:

0. Drop table from previous run
SQL > drop table t purge;

Table dropped.

1. Create table
SQL > create table t(
    2   id number,
    3   ddate date,
    4   vvalue varchar2(4000)
    5 ) tablespace users;

Table created.

2. Populate it with a test data
SQL > begin
    2   for i in 1..100000
    3   loop
    4     insert into t values(i,sysdate + 100*dbms_random.normal, dbms_random.string('A', 4000));
    5   end loop;
    6   commit;
    7 end;
    8 /

PL/SQL procedure successfully completed.

3. Create a test dummy trigger
SQL > create or replace trigger t_trig
    2 before update on t
    3 for each row
    4 begin
    5   null;
    6 end;
    7 /

Trigger created.

4. Run the query in one session and run <em>alter trigger t_trig disable</em> in other session
while select is running.
SQL > select count(*) from t where vvalue like 'A%Z';
select count(*) from t where vvalue like 'A%Z'
*
ERROR at line 1:
ORA-08103: object no longer exists

The issue hase been reproduced on a several installations of 10.2.0.5 + Patch 14 (13460967 for 32bit and 13460968 for 64bit) on Windows machines. The patch is important: I couldn’t reproduce it with other versions.
There is no happy end here: I’ve opened SR and they was not able to create a bug for it because of current support status of 10gR2.
My customer solved the issue by himself: he just corrected the jobs (query on the table and DDL on its trigger) not to run at the same time.

UPD. Many thanks to pravednik who helped me a lot with testing of this issue.

‹ Bombing a database with Public Synonym

How to forbid maintenance tasks to run during monthly reporting period ›

Posted in ORA-8103, Oracle, trigger