Executing Parallel Purge script

Parallel purge script

1. Login as sysdba and execute below grant commands

SQL> sqlplus "sys/welcome1 as sysdba"

GRANT EXECUTE ON DBMS_LOCK to SOAINFRA_db_user;

GRANT CREATE ANY JOB TO SOAINFRA_db_user;

GRANT CREATE ANY TABLE TO SOAINFRA_db_user;

GRANT CREATE ANY DIRECTORY TO SOAINFRA_db_user;

GRANT EXECUTE ON UTL_FILE TO SOAINFRA_db_user;

3. Create folder $> mkdir -p /tmp/purge

4. Login as SOAINFRA_db_user and execute below command

SQL>create directory SOA_PURGE_DIR as '/tmp/purge'

5. Enable Debug mode by executing soa_purge/common/debug_on.sql

6. set the serverout (sql> set serverout on)

7. Executing Parallel purge script by login as SOAINFRA_USER/password

8. logs are generated under /tmp/purge. Number of log files depends on number of threads used inthe client. In the below example it would generate 3 log files

Sample client

DECLARE

max_creation_date timestamp;
min_creation_date timestamp;
retention_period timestamp;
BEGIN

min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');

soa.delete_instances_in_parallel(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
DOP => 3,
max_count => 1000000,
purge_partitioned_component => false);

END;
/

================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================