While working with a customer this week, they were testing some audit functionality on the database. The database version being tested was Oracle 11g (184.108.40.206.5). When the user created a table, the entry in the SYS.AUD$ is registered as a CREATE TABLE. When the user issues a DROP TABLE, the entry for DROP TABLE is inserted into the SYS.AUD$ table. So, what is the problem?
In reviewing the SYS.AUD$ table, they noticed that additional entries added. These entries are related to Oracle Spatial, which the customer does not use. So the question is, why are these spatial entries created in the SYS.AUD$ table on a DROP TABLE command?
Looking into this issue, I was able to find a document in My Oracle Support that references this exact issue. The note number is: 1338587.1
Oracle needs to know when someone creates a table that has a sdo_georaster column. The only way to identify this is to fire a trigger that determines if the table being created is a spatial table or not. In short, this means that every table that is created is checked wither or not it is using a spatial column or not.
The solution to this issue is to disable the triggers in the MDSYS schema that are being fired when the table is being dropped. The triggers are listed below; they can also be found in the MOS note 1338587.1.
Disable these triggers from the MDSYS schema.
conn / as sysdba
alter session set current_schema=mdsys;
alter trigger sdo_geor_addl_trigger disable;
alter trigger sdo_geor_bddl_trigger disable;
alter trigger sdo_geor_err_trigger disable;
alter trigger sdo_topo_drop_ftbl disable;
After these triggers (above) have been disabled, rerun any tests for creating and dropping tables. Then check the SYS.AUD$ table and verify that the spatial entries are no longer there.
Side Note: This issue is related to Bug: 12716670 – SPATIAL AUDIT ENTRIES ON AUD$ DOING GENERIC DDL/DML NOT SPATIAL RELATED
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.