Adding space online
From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Wed, 27 Jul 2022 02:20:23 +0530
Message-ID: <CAEzWdqfb2WoazTy7eU18OEwiYVQM5D4+k71W3kia4GA2mn6u+g_at_mail.gmail.com>
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.
Date: Wed, 27 Jul 2022 02:20:23 +0530
Message-ID: <CAEzWdqfb2WoazTy7eU18OEwiYVQM5D4+k71W3kia4GA2mn6u+g_at_mail.gmail.com>
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-lReceived on Tue Jul 26 2022 - 22:50:23 CEST