Thursday, February 06, 2014

ORA-00604, ORA-04092, ORA-06512 and ORA-00942

I saw an ORA-00604 in a database recently and decided to see what might have caused it. The test below was run in an Oracle 9 database and shows one possible reason for an ORA-00604. Strangely enough, when I repeated the test in Oracle 10 and Oracle 11 databases, the ORA-00604 did not appear. If / when I find out why, I will update this post accordingly. First I ensured that _system_trig_enabled was set to true. You will see why at the end:
 
SQL> conn / as sysdba
Connected.
SQL> alter system
  2  set "_system_trig_enabled"=TRUE
  3  /
 
System altered.
 
SQL>
 
Then I created a user:

SQL> grant create session to andrew
  2  identified by reid
  3  /
 
Grant succeeded.
 
SQL>
 
The user looked for a table which did not exist and got an ORA-00942 (as you do):
 
SQL> conn andrew/reid
Connected.
SQL> select * from tab1
  2  /
select * from tab1
              *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL>
 
Next I created a user to own a trigger. This trigger is to fire on an after servererror on database event, write an error message to a table and commit it. To do this the user needs create trigger and administer database trigger privileges:  
 
SQL> conn / as sysdba
Connected.
SQL> create user fred identified by bloggs
  2  default tablespace users
  3  quota unlimited on users
  4  /
 
User created.
 
SQL> grant create session,
  2  create table,
  3  create trigger,
  4  administer database trigger
  5  to fred
  6  /
 
Grant succeeded.
 
SQL>
 
SQL> conn fred/bloggs
Connected.
SQL> create table failure_log
  2  (message varchar2(40))
  3  /
 
Table created.
 
SQL> create or replace trigger error_trigger
  2  after servererror on database
  3  begin
  4    insert into failure_log
  5    values('We have a problem');
  6    commit;
  7  end;
  8  /
 
Trigger created.
 
SQL>
 
You can identify these triggers as they have a trigger_type of AFTER EVENT:
 
SQL> select trigger_name, trigger_type
  2  from user_triggers
  3  /
 
TRIGGER_NAME         TRIGGER_TYPE
-------------------- ----------------
ERROR_TRIGGER        AFTER EVENT
 
SQL>
 
The next time Andrew tried to look for the table which did not exist, he got an ORA-00604 and an ORA-04092 as well because Fred’s trigger tried to do a commit:
 
SQL> conn andrew/reid
Connected.
SQL> select * from tab1
  2  /
select * from tab1
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 4
ORA-00942: table or view does not exist
 
SQL>
 
I set _system_trig_enabled to false to stop Fred’s trigger firing:
 
SQL> conn / as sysdba
Connected.
SQL> alter system
  2  set "_system_trig_enabled"=FALSE
  3  /
 
System altered.
 
SQL>
 
So the next time Andrew looked for the missing table, he just got an ORA-00942:
 
SQL> conn andrew/reid
Connected.
SQL> select * from tab1
  2  /
select * from tab1
              *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 

No comments: