I need to confirm my understanding about the following two issues, prior to changing them in production database;
1. For example, consider a table has been partitioned in to 2 partitions based on a range(each of them in a separate tablespace TS1 and TS2)and each partition is sub divided into 256 sub-partitions based on a hash. TS1 has INITIAL of 4M. Now, whether Oracle allocates only 4M as initial or 4 times 256?
2. The rollback segments on the production db have not been sized properly. I wanted to resize them. For this, I thought the best way is to create new rollback segments of required size, bring them online, offline the old rollback segments and then drop them. And include the new names in init.ora file.
Is this procedure okay, or there is a better way?
It shall be 4M and before dropping the old rollback segments , check if there are any transactions held up in that rollback segment.
Also u may do this way, then u donot need to shutdown the database.
Create one more rollback segment, check any of the old rollback segments and then bring it offline and drop it and then recreate it with new parameters, continue one by one the old ones and then drop ,if needed the first new one u created.
Thank you, gpsingh.
Okay, I will know from v$rollstat if there are any active transactions going on or not in any rbs. But how do I ensure that Oracle does not allocate the fresh transactions to the old rollback segs?
My understanding was that even if I give offline command, Oracle waits till the current active transactions are completed and then only takes the rbs offline. Is that not so?
100% true ....
Oracle wait till all transactions completed in the roll back segment before bringing offline.
That way you are sure.
So first create the rbs_big roll back segments and put them online
next bring the old roll back segs offline...
check the status....
then drop the old one one..
It will work.No need to bounce the database Sir.
Here the problem is next time you bounce you should sure the rbs are inluded in the parameter file.(I hope)
because it will not put them in online by default if it is not included.
[Edited by thanigai on 03-29-2001 at 08:11 PM]
Click Here to Expand Forum to Full Width