Oracle Adaptive Access Manager(OAAM) 11g DataBase purging

Hello,

In this post we will learn how to configure and run OAAM Database purging scripts.

Note: OAAM purge scripts are recommended to run using SQLPLUS command line. Avoid using SQL developer. I have faced issues while creating and running the purge scripts.

Step 1: Locate OAAM database purging scripts "oaam_db_purging_scripts.zip" under OAAM Middleware folder.

Step 2: Extract ZIP file to desired location.

Step 3: Login as SYS or SYSDBA into OAAM database and run below scripts

                GRANT create any procedure TO <schema_username>;

               GRANT create any table TO <schema_username>;

               GRANT create any index TO <schema_username>;

               GRANT create procedure TO <schema_username>;

               GRANT execute any procedure TO <schema_username>;




Step 4: Now login as OAAM schema owner and run "create_purge_proc.sql" script under oracle_db folder. When prompted enter Index Table Space & Data Table Space as <SchemaPrefix>_BRSADATA & <SchemaPrefix>_BRSAINDX.

Step 5: Below table has list of purge scripts 

Type of Data	Corresponding Script
Login, Device Data	exec_sp_purge_tracker_data.sql
Rules, Policy Log Data	exec_sp_purge_rule_log.sql
Transactions, Entities Data	exec_sp_purge_txn_log.sql
Autolearning Data	exec_sp_purge_workflow_data.sql
Profile Data	exec_sp_purge_profile_data.sql
Cases related Data	exec_sp_purge_case_data.sql
Monitor Data	exec_v_monitor_purge_proc.sql

Step 6: Set the p_days1 and p_archived(either to "Y" or "N") parameters using a text editor when you run the scripts.

Parameter details:

1. p_days represents parameter to purge data which is more than specified number of days.
2. p_archived has default value "Y" which says archive the data. Change it to "N" to purge the data.

Step 7: Login to database as OAAM schema owner and run one of the script from the above table as per the requirement.

-- Siva Pokuri.