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.