Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Alireza.

Asked: March 21, 2024 - 3:13 am UTC

Last updated: March 25, 2024 - 5:46 am UTC


Viewed 100+ times

You Asked

We are going to MOVE the TBS of AUD$ table in PROD. Purpose:
AUD$ table is totally fragmented and the CLEANUP / PURGE runs very slow - even with max 1.000.000 batch size
but as per test in our test environment we had some issues regarding using API (DBMS_AUDIT_MGMT) to MOVE TBS on AUD$.
And we are using STANDARD AUDIT TRAIL!

SELECT * FROM dba_audit_mgmt_config_params where audit_trail ='STANDARD AUDIT TRAIL'
and parameter_name='DB AUDIT TABLESPACE';

But the MOVE worked now after we got some action plan from oracle support to fix the issue in TEST env. so that the MOVE went through via API.

Now we are planning to do the TBS MOVE of AUD in PROD (online!). But we need to have a fallback plan, in case the MOVE hangs, or/and the data in AUD table get inconsistence.
so the fallback plan is:

1) EXP the data in a downtime (disable audit trail) and keep the dump file on the server. but with parameter "DATA_ONLY" - as metadata (table DDL) would still be there.
2) run the MOVE TBS on PROD via API (DMBS).
3) if it goes through and AUD$ is accessible and purgeable, we are good - if not, we need to truncate the data in AUD$ and IMP the SAVED data (as per EXP/dump file) - again with parameter "DATA_ONLY"

So i hope thats clear enough.

The question is now if step 3 would work or not - we are also planning the to test the step 3 in our TEST env. but we are concerned , If this action plan (especially step 3) could impact the PROD - in case we needed to go for the fallback plan (!?).

We would also appreciate any other action plan/ option to save and recover data in AUD$ , in the above scenario.

Thank you!

and Connor said...

For what I mention below, please get the blessing of Oracle Support before proceeding.

Rather then export/import, I would be more inclined do to

- create table BACKUP_AUD$ as select * from AUD$;
- run your move.
- If all successful, simply drop the backup table
- If not, then rename AUD$ to BROKEN_AUD$ and rename BACKUP_AUD$ to AUD$

In that way, you still have the old table to use when talking to Support etc, and everything stayed in the database.

More to Explore


More on PL/SQL routine DBMS_AUDIT_MGMT here