I have 3 rollback segments 2 of which are 800m and one 1.5g. I know it is a better practice to have a larger number of rollback seg's but smaller in size. Please let me know how I can resize my rollback segments.
Well, it mainly depends upon the type of db. If it's OLTP, you can have many small ones, in DW/DSS you can have a few large ones. What type of db is this?? How many concurrent transactions are you having in the db at any given time. The thumb rule it to have # rbs=#concurrent transactions/4, with transactions_per_rbs=4 or 5. And # rbs should be a multiple of 4. You can resize them, no problem.
It is not OLTP. I have about 50 users. 20 of them (max) would be logged on at the same time.
Also, another question I have is regarding the rollback tablespace itself. It is 2G, 1.2 of which is taken. What would be a right size of my tablespace?
Thank you in advance.
For 20 concurrent users, you might want to have atleast 4 rbs. 2 Gb should be fine. Make sure that there are no file size limitations on the OS if you have Autoextend ON for the ts. You may want to consider using optimal storage parameters for the rbs (if they are not). You may want to (re)size the rbs based on max(rssize). This is a good way to size them and monitor their behavior for future modifications.