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 :

2 comments:

  1. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete
  2. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete