New Whitepaper: Options for Reducing E-Business Suite Database Sizes

I have yet to encounter a database that ever got smaller.  Like waistlines, all databases seem destined to increase in size.  The E-Business Suite is no exception:  as we add more product capabilities and your business grows, so do your Apps databases.

Oracle-supplied solutions to managing Applications database size fall into two categories:  data growth control methods and data management methods.

Growth Control Methods

  • Archiving and purging
  • Database compression

Data Management Methods

A new Oracle whitepaper discussing these topics is now available:

Screenshot of Oracle Information Lifecycle Management Assistant used with E-Business Suite database tables

This whitepaper discusses how these approaches can be used either individually or together to meet your requirements.  Some examples scenarios:

  • Adopt a 100% pure archive and purge strategy where any important data is initially archived and then finally purged when it is no longer needed. The archived tables could be placed in a read-only schema and the datafiles could be resized to reduce their physical space. In addition, database compression feature could be used in conjunction with or as an alternative to resizing the datafiles.
  • Customers could adopt a combination of an archive/purge and ILM strategy, where inactive data is put onto cheaper storage, then after a period of time, this data is archived and eventually purged. Alternatively, customers could choose not to use ILM but instead use database partitioning.
  • Customers could fully implement ILM and. and place less frequently accessed data, that would ordinarily be archived and purged, onto cheaper and less expensive storage devices.
  • Customers could use a 3rd Party archiving product with Oracle’s ILM technologies.

Although there's a lot of conceptual and strategically-oriented material in this whitepaper, it also has concrete and practical information about:

  • The E-Business Suite Purge Portal
  • Archiving and purging programs and affected tables for the following products:
    • Oracle General Ledger (GL)
    • Oracle Payables/Oracle Purchasing (AP/PO)
    • Oracle Receivables (AR)
    • Oracle Cash Management (CE)
    • Oracle Fixed Assets (FA)
    • Oracle Cost Management (CST)
    • Order Management (ONT)
    • Oracle Application Object Library (FND) and Workflow (WF)
  • How to use the Oracle ILM Assistant with an E-Business Suite environment
    • Defining data classes
    • Creating storage tiers for data classes
    • Creating data migration policies
    • Defining and enforcing compliance policies
  • Considerations when creating an information lifecycle management framework for Apps data

Related Articles



I'm very very pleased to finally a document discussing this topic. Great information !

At Colorcon, we have been working very aggresively on this for the past few years and have seen dramatic results. Our year over year growth for the past 5 years has been 72%, 36%, 18%, 17% and 10%. Clearly the trend has been slower growth rate and has had a very positive impact on our overall ability to manage the environment. We have close to 25 copies of the production environment for test and development. Any amount of space we save in production, we save 25x overall. It also means faster backups, faster clones, better performance and lower storage costs. We ever able to achive all this by two simple techniques, drop unwanted custom/backup tables (both custom and seeded tables created during upgrades etc) and purging (and in some truncating) custom and seeded tables. We are looking to implement advanced compression and ILM techniques to further limit the growth.

Thanks a lot.

Posted by Naveen on December 04, 2008 at 11:04 AM PST #


Glad that you've found this whitepaper useful. I would be *very* interested to hear about your experiences with Advanced Compression and ILM in your EBS environments.


Posted by Steven Chan on December 05, 2008 at 05:22 AM PST #


Here are some stats on a test environment.

Table Name After Compression (MB) Before Compression (MB)

GL_ITEM_CST 253 1,120
GL_SUBR_TST 236 1,160
GL_BALANCES 419 1,284
IC_TRAN_PND 760 1,711
EGO_ITEM_TEXT_TL 364 1,727
GL_ITEM_DTL 799 3,787
GL_SUBR_LED 944 5,108
CM_SCST_LED 2,081 8,462

So on an average, we saw 75% compression on these bug tables.

Posted by Naveen on December 11, 2008 at 12:21 AM PST #


I wanted to bring to your attention an issue that we discovered recently.

We have 5 languages enabled in our EBS environment. Each document attached in EBS, is getting stored for each language. Which means that we have 5 copies of each document in the production environment, one for each language. If you add the 25 clones we maintain of the production environment for various
test and development, we have 125 copies of each document in our storage system. This is a HUGE HUGE waste of space.

I have raised an SR (7245300.993) for this. Hopefully we'll get a resolution on this.


Posted by Naveen on December 11, 2008 at 12:25 AM PST #


Thanks for posting your compression statistics. These are very useful. I've passed them on to our internal teams.

As for your second issue, I'm glad to see in your SR that this was due to errors on the part of your users, rather than an EBS bug.


Posted by Steven Chan on December 30, 2008 at 06:21 AM PST #

Hi Naveen,

I am proposing this as a process improvement in my organization. we have been plagued by uncontrolled growth of our oracle database, currently at 4TB.

Would like to hear your experiences, on the mix and match that is followed to cut down your DB.


Posted by Sunil Dhanemkula on September 14, 2010 at 05:57 AM PDT #

Is there any certified solution for Oracle e-Business Application Archiving available from Oracle? Advance Thanks.....

Posted by Venkat on August 20, 2013 at 03:14 AM PDT #


Thanks for the inquiry. Oracle E-Business Suite does not currently offer an archiving solution. There are third-party solutions available; however, we do not recommend using third-party archiving solutions in a production environment.

We recommend a combination of ILM (as described in this blog article), database partitioning, database compression and purge routines packaged with E-Business Suite.


Posted by Elke Phelps (Oracle Development) on August 20, 2013 at 10:39 AM PDT #

Hi Elke,

Thanks for the quick Update.

Venkat Krish

Posted by Venkat on August 20, 2013 at 08:58 PM PDT #

Hi, everyone,


Can You Use Third-Party Tools to Modify Your EBS Database?


Posted by Steven Chan on January 09, 2014 at 11:55 AM PST #

Hi Steven!
Any plans to certify in-database archiving with compression option for EBS?

Posted by guest on May 05, 2014 at 05:00 AM PDT #

Hi, Guest (L),

We haven’t had an opportunity to assess the use of the Database 12c In-Database Archiving feature yet. Many E-Business Suite users, including Oracle itself, have substantial amounts of historical data in their databases that they do not need when making copies of production for testbed purposes. This new feature could be very useful, so we’re looking forward to doing this analysis in the future.

In the absence of our own tests, I don’t have any guidance on the feature’s usage with EBS databases. We would generally advise you to wait until we’ve had a chance to assess this. If you wish to experiment with this yourselves, I would strongly advise you to take backups – and, naturally, to avoid using this with a production EBS database.

Oracle's Revenue Recognition rules prohibit us from discussing certification and release dates, but you're welcome to monitor or subscribe to this blog. I'll post updates here as soon as soon as they're available.


Posted by Steven Chan on June 18, 2014 at 09:23 PM PDT #

It seems the Human Resources Purge and Archive functionality is limited. Are there any options for purging old absence requests or data from large tables?


Posted by guest on April 14, 2015 at 09:16 AM PDT #

Hi, Stuart,

I'm afraid not. You must use the provided routines for purging data. If you have a requirement that isn't met by the existing routines, your best bet would be to log an enhancement request against HRMS with the details of the data that you'd like to purge.


Posted by Steven Chan on April 14, 2015 at 10:32 AM PDT #

Hi Steven:

Could you please tell us if any data archival strategy is followed within Oracle's own EBS production database.

Posted by guest on June 21, 2016 at 06:59 AM PDT #

Hello, Guest,

We use the published archive/purge routines available with the E-Business Suite's Purge Portal. Aside from that, we do not use any ILM or archiving methods on our own EBS instance.


Posted by Steven Chan on June 21, 2016 at 09:22 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed


« July 2016