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
>
>
> 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