Re: Adding space online

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
>
>
> Hope this helps,
>
> -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