RE: Adding space online

AND (not but, Tim is spot on as usual), IF your partition is time based such that inserts only occur in the newest or newest few partitions, instead of moving the old partitions, change where new partitions are created.  

This is NOT what you asked, however IF the conditions I described pertain, this avoids moving anything possibly leading to an eventual drop partition of the oldest partitions (at which time you could include the old partition in the rotation described below.)  

For extra credit (and if the time slicing of the tablespaces is reasonable), create two (or more) new tablespaces and alternate where the new partitions are created as time rolls forward. For i/o performance this only matters if you have independent sets of non-SSD disks on different media and/or other limited pieces of the i/o stack, but it also reduces space allocation contention. If you have several “days” eligible for insertion (say, perhaps, delivery completed records with non-instantaneous receipt of the delivery record, for example) the reduction in contention can be significant, especially if record receipts and the attendant inserts are batchy.  

Zero of this is in contention with what Tim wrote, and he answered your actual question. I’m speculating on possibilities.  

By the way, read some version of this paper “Scaling to Infinity.” It is likely to expand your mind regarding how to manage space in Oracle. While the same information may be available elsewhere, Tim’s rendering of the concepts in this paper puts the how AND why together in most elegant and logical prose I’ve ever seen.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of yudhi s Sent: Wednesday, July 27, 2022 1:40 AM
To: tim.evdbt_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Adding space online  

Got it. Thank you very much.

On Wed, 27 Jul 2022, 3:09 am Tim Gorman, <tim.evdbt_at_gmail.com> wrote:

Yudhi,

Please ignore the first sentence, I was thinking of something else...

Thanks!

-Tim

On 7/26/2022 2:38 PM, Tim Gorman wrote:

Yudhi,

Consider using EXCHANGE PARTITION to convert the partition you'd like to move into a standalone table, then move it to the other tablespace, then exchange it back?

1. Use CTAS to create a new table in the new tablespace with a copy of the data in the partition to be moved out of the old tablespace, complete with the same indexing
   • Gather stats on NEW_TABLE and indexes
2. Exchange the partition with NEW_TABLE using the WITHOUT VALIDATION INCLUDING INDEXES UPDATE GLOBAL INDEXES clauses
3. DROP TABLE NEW_TABLE PURGE

-Tim

On 7/26/2022 1:50 PM, yudhi s wrote:

Hello All, We have a client database on 11.2.0.4 Oracle version. It's a 24 by 7 running system and we have one of the tablespace defined as a bigfile tablespace and queries inserting into objects in this are experiencing "enq-hw contention" and are crawling. And we found we are going to reach the max size limit and also the max number of datafile limits for that tablespace.  

We were thinking of quickly moving some of the old partitions from that tablespace to the new tablespace to have some headroom. but as we are on 11.2 , "partition move" will make the respective index partition unusable and thus impact read queries till the time we are finishing rebuilding the respective indexes. This is because queries not having explicit filters on partition keys are relying on global stats so if any one of the index partitions unusable will make the optimizer not to use that index at all.

So is there a way we can move those old partitions to new tablespace in online fashion without impacting others?  

Regards

Yudhi      

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 27 2022 - 13:32:38 CEST