Unexpected entries into the SYS.AUD$

While working with a customer this week, they were testing some audit functionality on the database.  The database version being tested was Oracle 11g (11.2.0.3.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

Cause:

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.

Solution:

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

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like: