Skip to Main Content
  • Questions
  • Is it Possible to Audit Manual Partition Creation Specifically?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jerry.

Asked: July 09, 2018 - 8:56 pm UTC

Last updated: July 11, 2018 - 11:16 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Are you aware of any way to specifically audit the addition of new partitions? So far my searching has come up fruitless. Auditing is enabled within the DB which records each
alter table
command. However, that is too large a net. The specific use cases is finding out if partitions are being added by a particular user (not using INTERVAL- partitions or not using a script to handle the partition creation.

and Chris said...

Wait... auditing alter table casts too wide a net?! How often are you doing this? This should be a relatively rare operation!

And you can correlate the alter table audits with created date of partitions in *_objects:

For example:

create table t (
  x int
) partition by range (x) (
  partition p0 values less than (10),
  partition p1 values less than (20)
);

audit alter on t;

conn chris/chris

alter table t add y integer;
alter table t add partition p2 values less than (30);

select uo.subobject_name
from   user_objects uo
join   dba_audit_trail aud
on     uo.created = cast ( cast (aud.timestamp as timestamp with time zone ) at time zone '-7:00' as date )
where  uo.object_type = 'TABLE PARTITION';

SUBOBJECT_NAME   
P2  


But I'm not aware of a way to use audit only for partition maintenance.

If that doesn't help, you could use DDL triggers on alter to capture the event. See https://asktom.oracle.com/pls/asktom/asktom.search?tag=capturing-ddl-changes-on-a-table for an example trigger.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great Ideas

Jerry, July 10, 2018 - 8:20 pm UTC

I know alter table doesn't sound like a wide net but when managing hundreds of production databases with auditing enabled it is :). Both of your answers are great ideas - thanks!
Chris Saxon
July 11, 2018 - 11:16 am UTC

OK, maybe you do need something more targeted than plain alter table ;) Glad this helped.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.