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.

About these ads
Posted in ORA-8103, Oracle, trigger
2 comments on “ORA-8103 and DDL on a trigger
  1. odenysenko says:

    Andriy,

    Thanks for pointing on interesting issue!
    Actually, I feel that it has to be almost expected behaviour, because of environment of long running query is inconsistent, even in the case of select there are invalidations based on dependencies.

    Have You tested on latest releases ?

    Oleksandr

    • Yes, I have tested this on 10gR2 (unfortunately we have no such support level so I was not able to download most of the PSU patches around Patch 14) and on 11gR2. It seems that other platforms of 10gR2 (HP-UX and so on) and 11.2.0.1 aren’t affected.
      I have filling that such behavior was a big surprise for support team also. I believe that ora-8103 would raise when data_object_id gets changed after some operation on the object; DDL on the trigger doesn’t change it for a table it belongs to.

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

Oracle ... as usual

Oracle by Laurent Leturgez

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

xt-r.com

Just another oracle developer

Coskan's Approach to Oracle

What I learned about Oracle

Oracle Scratchpad

Just another Oracle weblog

Oleksandr Denysenko's Blog

be prepared for The Future...

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

Follow

Get every new post delivered to your Inbox.

Join 105 other followers

%d bloggers like this: