How To Remove a Table From an Audit Group

1. Take a backup of Shadow Table MY_TABLE_A as follows

Create Table MY_TABLE_A_BACKUP as Select * from MY_TABLE_A;

Note: When you drop a shadow table your Audit DATA will be permanently lost.

2. The cleaning steps

a. Drop shadow tables and synonyms.

Drop table MY_TABLE_A;

Drop synonym MY_TABLE_A;

b. Drop the audit trail triggers

Drop trigger MY_TABLE_AI;

Drop trigger MY_TABLE_AU;

Drop trigger MY_TABLE_AD;

Please note:

There are BEFORE STATEMENT Triggers used by fnd_user_audit (_AC / _AH / _AT).

These Triggers should not be dropped.

c. Drop the audit trail procedures.

Drop procedure MY_TABLE_AIP;

Drop procedure MY_TABLE_AUP;

Drop procedure MY_TABLE_ADP;

d. Drop the audit trail views

(I) First get the view names by using the following select statement

SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE (OBJECT_NAME LIKE 'MY_TABLE%_AC%' OR OBJECT_NAME LIKE 'MY_TABLE%_AV%') AND OBJECT_TYPE - 'VIEW';

(II) Then drop them one by one (note that views will end with AC# or AV#)

Drop view View Name;

e. Remove the table and its Columns from the auditing table

(I) First get the application ID and the table ID using the following statment

SELECT APPLICATION_ID, TABLE_ID

FROM FND_TABLES

WHERE TABLE_NAME = 'MY_TABLE';

(II) Delete the columns of the table from the audit tables

Delete from FND_AUDIT_COLUMNS

where TABLE_APP_ID = <result from I>

and TABLE_ID = <result from I>;

(III) Delete the table from the audit tables

Delete from FND_AUDIT_TABLES

where TABLE_APP_ID = <result from I>

and TABLE_ID = <result from I>;

Run the request ==> AuditTrail Report for Audit Group Validation

(This should return valid for the Audit Trail Group Objects)

c. Run the Request ==> AuditTrail Update Tables