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
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteI would like to say that this blog really convinced me to do it! Thanks, very good post. auditing company
ReplyDelete