Purging and partitioned schemas in SOA with Benefits of partitioning

Overview of Purging in SOA:

Purging removes obsolete data (completed/failed instances, audit trails, dehydration store entries) from SOA databases to

Improve performance (smaller tables = faster queries)

Reduce storage costs

Maintain compliance (data retention policies)

Partitioned Schemas in SOA

Partitioning splits large tables (e.g., cube_instance, dlv_message) into smaller, manageable chunks based on:

Time ranges (e.g., monthly partitions)

Instance status (completed vs. active)

Component type (BPEL, Mediator, etc.)

Benefits of Partitioning:

Faster purging (drop entire partitions instead of row-by-row)

Improved query performance (partition pruning)

Easier maintenance (archive old partitions)

Purging Methods

Manual Purging with purge.sh Script

# Syntax (SOA 12c):

$ORACLE_HOME/soa/bin/purge.sh \

  -jdbcUrl "jdbc:oracle:thin:@//db_host:1521/ORCLPDB1" \

  -dbUser soa_infra \

  -dbPassword Welcome1 \

  -olderThan 30 \          # Purge instances older than 30 days

  -purgeCompositeInstances true \

  -purgeAuditTrails true

Automated Purging (Database Scheduler)

-- Create a DB job to run purge monthly

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'SOA_PURGE_JOB',

    job_type        => 'EXECUTABLE',

    job_action      => '/u01/app/oracle/soa/bin/purge.sh',

    number_of_arguments => 6,

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1',

    enabled         => TRUE

  );

END

Partition-Specific Purging

-- Drop partitions older than 6 months (example for CUBE_INSTANCE)

ALTER TABLE soa_infra.cube_instance 

  DROP PARTITION p_202301;