The strategy is sound, apart from not knowing how you might plan to report on
the old data once it is severed (archived by transportation, presumably to
another database).
If you use a copy-out the “young” stuff and move the entire existing tables,
make certain that you restore all the permissions correctly and understand that
if you do not use EBS approved widgets (which do not include all modules) and
manipulate the storage of the data, that is not something covered by routine
support if you don’t get it right.
There is (or was) an entire OAUG sig devoted to these issues and special
interests in various products, most especially workflow. I would not be
surprised if the history of stuff from Karen Brownfield is still the best
overview of how do care for workflow. IF you want access acceleration for some
products, such as GL, there are comprehensive papers available, including
reordering some key (in both senses) tables so that the programs do less work
on the same data.
Working out the details of your exact project is very likely something given
professional attention, rather than the level of how to advice you will get for
free here. The advice is likely to be very good, but you probably don’t want to
be implementing that advice for the first time by yourself with your job on the
line.
Oracle can probably tell you exactly which modules they can make this easy for
with pre-built tools. They probably still bundle this sort of thing into the
overall deal if you move the part you want to keep “live” to their cloud. Make
sure you (or your CFO and you) understand the costs, features, and support if
you are making a move.)
Good luck.
Possibly Mike Brown (president of the DBSIG) has something to share.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Amit Saroha
Sent: Tuesday, July 20, 2021 10:47 AM
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx)
Subject: Re: Transportable tablespace, partition and existing functionality
Dear Listers,
Could you share some thoughts, please?
Best Regards,
AMIT
On Fri, Jul 16, 2021 at 5:24 PM Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Dear All,
We want to make use of transportable tablespaces and partitioning in our Oracle
EBS environment to archive more than 7 years of old data and improve the
performance in some areas. Please let me know if it makes sense to you. We
don't want to buy third-party products to achieve our end goal.
I am afraid that some processes where the optimizer is choosing FTS can impact
because of this. I am also not sure about indexes and views dependent on tables
as well.
Kindly request you to provide me some insights on how to proceed with this
analysis.
Thank you in advance for your help.
Best Regards,
AMIT