Re: Transportable tablespace, partition and existing functionality

Hi Amit,

You haven't mentioned which versions of EBS and the database you're using, but here are some high-level thoughts:

• Custom partitioning in EBS is not uncommon for purposes like yours (query performance and information lifecycle management). The EBS tech team at Oracle have released a few resources on the topic over the years, including "Using Database Partitioning with Oracle E-Business Suite" (Doc ID 554539.1), discussed here <https://blogs.oracle.com/ebstech/updated%3a-database-partitioning-with-ebs-whitepaper>. The MAA doc for EBS also discusses topics like advanced compression, which may be useful to you.
• Keep licensing in mind when evaluating possible solutions, since some Enterprise Edition features require additional licenses. Although the EBS db includes partitioning, this is a limited license for the default EBS setup. Any partitioning scheme you create beyond the out-of-box partitions will require a full license for the partitioning option.
• Changing the table structures to accommodate partitioning, compression, and TTS is a customization of your EBS environment, and should be documented and tracked as such in your organization. This will be important when interacting with Oracle Support on performance issues, and in the rare circumstances when an EBS patch performs DDL on the tables, since the table definitions in the ODF files will not match the reality of your deployed system.
• You'll want to test your changes quite a bit, *especially* in data archival scenarios. It's difficult to provide any specific product-level advice, since every org's EBS workload is different, so make sure you have a robust set of tests. :)

On Tue, Jul 20, 2021 at 7:47 AM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Dear Listers,
>
> Could you share some thoughts, please?
>
> Best Regards,
> AMIT
>
>
> On Fri, Jul 16, 2021 at 5:24 PM Amit Saroha <eramitsaroha_at_gmail.com>
> 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
>>
>

--
http://www.freelists.org/webpage/oracle-l