Re: Adding space online

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

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