Oracle Apps Support

We are using Oracle Apps 11.5.8 since last five years on Oracle 8i and we have huge amount of data that is hampering the performance of the application. We are planning to upgrade in near future possibly on SAP, however  till we go live we need to support the business on this application . We would like to Archive old data and keep data only for 2 or 3 yrs. This would improve Application & Database Performance. We usually run reports for last 2 yrs but whenever needed we should be able to retrieve data using the same Oracle Standard & customized reports. Please let me know our options.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
My 8i experience is very old and my Oracle apps experience is even older but I'm responding to a neglected question alert.

Is it possible to partition your larger tables and indexes?  That might ease the performance issues and keep the older data available with the least impact to the apps.

I know it requires a separate license with newer releases but I can't remember if it did in 8i.
sdstuberConnect With a Mentor Commented:
Try using partition views - it's a little more cumbersome than real partitioning,  but it's free.

The idea is quite simple

Create 2 tables (or more) that are identical in structure and indexes

Add a check constraint to each table such that each table can only have data for their specified dates

then create a view that does a UNION ALL of the tables

To reinforce the metadata (which is good to do since the 8i optimizer was still relatively new),  on each select of the unions, impose the same date range condition

something like

create or replace my_view as
select col1,col2,col3 from my_old_data where col1 < to_date('20090101','yyyymmdd')
union all
select col1,col2,col3 from my_new_data where col1 >=to_date('20090101','yyyymmdd')

since this is an existing system  rename your old table, then create the view with the same the table used to have

Mark GeerlingsDatabase AdministratorCommented:
Unfortunately, Oracle does not offer an archiving mechanism for Oracle Apps in any version.  There are a couple of third-party providers that do this (IBM and Informatica) but these solutions are not cheap!  Oracle does offer *some* purging programs for Oracle Apps, but these do *NOT* archive the data before purging it.
Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

Android26Author Commented:

Since we have to maintain this data for next few years and the hardware that it currently rests on  will not last that long, we are looking at porting this data to some new hardware by way of migration or archival utility. Any leads on that?
slightwv (䄆 Netminder) Commented:
Can you migrate ALL the data to the new hardware?  

It will require downtime to ship the data files over but from the posts above, it appears without a manual data archival of some type, it might be your only option.
Android26Author Commented:
Yes all the data can be migrated to new hardware,  and in fact I want to migrate all the data on the new hardware as the current hardware has become obsolete. Downtime for few hours can be manageable.
If you go with the partitioned view route, you could precreate all of the tables you need and as you migrate, put the data into whichever table is appropriate.

You could do that by exporting in pieces and importing and renaming the tables
or create db links and simply insert/select
or by exporting everything, importing everything then splitting the data
slightwv (䄆 Netminder) Commented:
>>Yes all the data can be migrated to new hardware

Are you wanting to move the Oracle instance (Oracle software/server processes/???) to the new server as well?  Basically move it all and shutdown the old server?

If the new hardware will support Oracle 8 (or visa versa), this shouldn't be a problem.
Android26Author Commented:
Have decided to keep the status quo till we start the new ERP implementation and with the help of implementation partner will address this concern.
what was the solution?
I recommend split on


These address the archiving issue itself.
If the asked has another solution to accept that's ok too but delete seems inappropriate when there are answers available with no counter
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.