Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 18 January 2017

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
Share this article :

1 comments:

  1. I would like to say that this blog really convinced me to do it! Thanks, very good post. auditing company

    ReplyDelete