When DML becomes DDL

Some time ago I was contacted by one of our developers (smile and wave, yes, you!) with very interesting issue on (fortunately) development database. The next is almost a quote:

insert into SOMETABLE (CODE,...) values ('CODEVALUE', ...);

insert into SOMETABLE (CODE,
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20033: USR has no privileges to DDL any object in this schema...

Ok, 20033 is user-defined exception at recursive level 2… this might be a trigger. And that’s it, I’ve found a ‘DDL prevention trigger’ which I have installed several months ago just for testing. The trigger was:

create or replace trigger system.ddl_prevent_tr
before create or alter or drop or rename
on database
when (
  sys.dictionary_obj_owner in ('APPOWN')
  if ora_sysevent in ('CREATE','DROP') and
    sys.dictionary_obj_owner in ('APPOWN') and
    upper(user) not in ('APPOWN') and
    upper(sys.dictionary_obj_type) in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','TABLE')
    raise_application_error(-20033,Upper(USER)||' has no privileges to DDL any object in this schema...');
  end if;

That’s it but… the trigger is defined as ‘before create or alter or drop or rename’, where DDL came from into our insert? The table SOMETABLE doesn’t belong to APPOWN schema, and calling user isn’t APPOWN also.

After several minutes of stupor I’ve get an idea. To prove it I did the same insert and got the same error, but under 10046 trace. The tracefile produced was a bit huge.

Search backward from the end of the file shows some piece of cake:

EXEC #47408502285272:c=3000,e=2397,p=0,cr=32,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1343647204457842
ERROR #1:err=20033 tim=1343647204457853
CLOSE #47408502285272:c=0,e=10,dep=2,type=0,tim=1343647204457898
EXEC #47408502569656:c=174973,e=174353,p=299,cr=3161,cu=4,mis=0,r=0,dep=1,og=4,plh=0,tim=1343647204457919
ERROR #11:err=604 tim=1343647204457930
kdpurtab: err=604 goc1=604 goc2=20033 goc3=6512
CLOSE #47408502569656:c=0,e=2,dep=1,type=0,tim=1343647204457981

6th line is our error. Some additional search through the tracefile shows that cursor #47408502285272 is our trigger, and #47408502569656 is (tadaaa!)

drop table "APPOWN"."BIN$sdbtwxw2q6DgQAEKFjxyWw==$0" purge

Futher ‘reading’ of the tracefile showed the story:

  1. During insert Oracle didn’t find free space in the table segments and made a decision to allocate a next extent.
  2. Then it didn’t find enough contiguous set of blocks for the new extent and tried to free up some space from recyclebin and this time it was an object “APPOWN”.”BIN$sdbtwxw2q6DgQAEKFjxyWw==$0″
  3. It issued a drop operation for it which is definitely a DDL and object is owned by APPOWN and therefore has been caught by our trigger. That’s it.

The trigger has been fixed by adding additional condition: ora_dict_obj_name not like ‘BIN$%’

Tagged with: ,
Posted in Oracle

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


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...

%d bloggers like this: