Oracle Apps R12 Purge Project

Introduction

This technical case study documents a database optimization initiative undertaken for an Argentine manufacturing technology firm running Oracle E-Business Suite R12 since 2009.

The Challenge

The organization maintained a 6.2 terabyte database, which constrained their infrastructure expansion plans. They initially resisted implementing additional test environments due to storage constraints.

Solution Approach

The team executed a comprehensive data purge targeting General Ledger and Inventory modules, retaining only two years of historical data.

Katrina DevOps Implementation

We implemented Katrina DevOps for multiple instances to streamline the purge process and ensure consistency across environments.

Results Achieved

Database size reduction: From 6.2TB to 2.9TB

Performance improvements: Gather Schema Statistics and other maintenance operations performance increased drastically

Data defragmentation: Accomplished through export, table truncation, and reimport procedures

Technical Implementation

Oracle Data Pump Scripts

The implementation utilized Oracle Data Pump (expdp/impdp) utilities for parallel extraction and loading of multiple GL and Inventory tables:

GL_BALANCES

GL_JE_HEADERS

GL_JE_LINES

MTL_UNIT_TRANSACTIONS

MTL_MATERIAL_TRANSACTIONS

Purge Process Steps

1. Identify tables for purging based on retention policies

2. Export data using Data Pump with parallel processing

3. Truncate original tables

4. Reimport required historical data

5. Rebuild indexes and gather statistics

Critical Recommendations

Always make sure to keep a backup and do all steps needed to keep information safe.

Best Practices

Document all purge activities

Test purge scripts in non-production first

Validate data integrity after each step

Monitor system performance during execution

Conclusion

A well-planned purge project can significantly improve system performance and reduce storage costs while maintaining necessary historical data for business operations.