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
I would like to say that this blog really convinced me to do it! Thanks, very good post. auditing company
ReplyDelete