0

DB:

Oracle RAC Database Release 11.2.0.4.0

I want to shrink SYS.AUD$ table. Some records were deleted but table still has about 25GB. I read that it is necessary to move AUD$ segment to another tablespace using dbms_audit_mgmt package.

begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDTBS');
end;

Then:

alter table sys.aud$ enable row movement;

alter table sys.aud$ shrink space cascade;

alter table sys.aud$ disable row movement;

My questions are:

  1. Is this "dbms_audit_mgmt.set_audit_trail_location" an ONLINE operation?
  2. After that, should i move aud$ table back to SYSTEM tablespace?

Thank you guys

1

Where did you 'read' that the procedure you state is necessary to shrink the table?

Yes, you should use dbms_audit_mgmt to relocate your audit table out of the SYSTEM or SYSAUX table space. Not to shrink the table, but because that is best practice.

Is it an online operation? What does the doc say?

Usage Notes

The following usage notes apply:

This procedure involves data movement across tablespaces. This can be a resource intensive operation especially if your database audit

trail tables are already populated. Oracle recommends that you invoke the procedure during non-peak hours.

Should you move it back to the SYSTEM TS? No.

Also, when considering shrinking table you need to remember that space that is freed up by deleting rows is still allocated to the table and will be reused by future inserts. Unless a delete is a one-time cleanup and permanent reduction in the number of rows, a shrink table is worse than wasted effort because future inserts will just incur additional overhead to re-acquire the needed space.

But remember, relocating the table into its own TS is just good management, regardless of shrinking the table.

Also, you state you are on 11.2.0.4. That is out of support and long past time to upgrade.

2
  • What i meant was, you can't shrink table in SYSTEM tablespace. First you need move it to another. Ok, i will move table to another tablespace with dbms_audit_mgmt and just shrink SYSTEM datafile. There is no need to shrink AUD$ table in new tablespace. Am i right? Thanks – lear1 Nov 24 '20 at 19:46
  • "There is no need to shrink AUD$ table in new tablespace. Am i right? " It's been so long since I've done it that I don't recall. And I'm not sure I really cared about that point. If you are going to move the table anyway, just go ahead and move it. After it has moved you can look at it and see if it is worth trying to shrink it. – EdStevens Nov 24 '20 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.