Just this morning i was asked to investigate why a simple insert was failing . The error the users were getting was
INSERT INTO "ABC" ( "ABC1", "ABC2", "ABC3", "ABC4" ) VALUES
( 'ABC1', 1234, 'N', 'NN' )
ORA-20009: Cannot drop table Drop Not Authorized
ORA-06512: at line 14)
While we knew this was a error generated by our custom trigger that we have implemented for securing our databases from malicious application table drops. We were a little puzzled as to why a simple insert would invoke our table drop prevention trigger.
After a little dose of 10046 this became apparent to us
PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=12 lid=0 This was going on
tim=48271684044651 hv=0 ad='1045a248'
drop table "APP_NAME"."BIN$YQPns5G0IL7gQzwEPI0gvg==$0" purge
END OF STMT
PARSE #3:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=48271684044648
The row insert required the table to extend to a segment that was previously owned by a dropped object . To clear space Oracle actually issues a drop table purge when it needs to extend an existing object.
We are now modifying our security trigger to exclude BIN$ objects .
Edait: after a question in the comments i have now validated that an implicit commit will happen if an insert and purge recycle bin are executed . i am going to test creating an outof space and reuse situation to see if that would cause an implicit commit too. Thanks to Chen Shapiro to point ing out the implicit commits in DDL statements.
Update: Oracle manages the space management operation outside of a transaction and a commit will not happen to the operations that have happened in the outlying transaction. it seems purge dba_recyclebin and an internal drop table purge for space management by oracle are handled differently
INSERT INTO "ABC" ( "ABC1", "ABC2", "ABC3", "ABC4" ) VALUES
( 'ABC1', 1234, 'N', 'NN' )
ORA-20009: Cannot drop table Drop Not Authorized
ORA-06512: at line 14)
While we knew this was a error generated by our custom trigger that we have implemented for securing our databases from malicious application table drops. We were a little puzzled as to why a simple insert would invoke our table drop prevention trigger.
After a little dose of 10046 this became apparent to us
PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=12 lid=0 This was going on
tim=48271684044651 hv=0 ad='1045a248'
drop table "APP_NAME"."BIN$YQPns5G0IL7gQzwEPI0gvg==$0" purge
END OF STMT
PARSE #3:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=48271684044648
The row insert required the table to extend to a segment that was previously owned by a dropped object . To clear space Oracle actually issues a drop table purge when it needs to extend an existing object.
We are now modifying our security trigger to exclude BIN$ objects .
Edait: after a question in the comments i have now validated that an implicit commit will happen if an insert and purge recycle bin are executed . i am going to test creating an outof space and reuse situation to see if that would cause an implicit commit too. Thanks to Chen Shapiro to point ing out the implicit commits in DDL statements.
Update: Oracle manages the space management operation outside of a transaction and a commit will not happen to the operations that have happened in the outlying transaction. it seems purge dba_recyclebin and an internal drop table purge for space management by oracle are handled differently
