Please look at the following statistics:

Rollback segemnt Status Exist extents Current extent Current block Transactions
---------------- ---------- ------------- -------------- ------------- ------------
R02 FULL 360 192 33 2
SYSTEM ONLINE 5 0 7 0
IBSRB01 ONLINE 1,605 720 19 3
R04 ONLINE 421 364 4 3
R03 ONLINE 358 193 16 2
R01 ONLINE 548 166 13 2


It shows that the R02 is FULL, but it only writes up to 192 extents while there are 360 extents allocated to it. It has two transactions, I use another query to find that these two transations are INACTIVE, then how come the status of R02 is FULL?

We had a bunch of small sized rollback segments, which were causing some batch jobs to hit snapshop too old error, so I proceeded to create a new tablespace containing about 10 of them sized properly ( 20m uniform, etc.. etc.. etc.. ). I took the newly created ones online, then I checked v$rollstat to see if active transactions were still alive on the old ones, I waited until xacts were 0 on all, then I proceeded to take the old ones offline. I then took the tablespace were the old rbs were offline as well. After a while, some users were having applications error, freezing and what not, complaining about data not available ( sorry, I couldn't catch the exact error number - darn me ). This I do it all with the db online.

a) I am right about thinking that when I proceeded to take the old rbs offline, some active transactions were still using them, but these changes were cached in the buffer instead that on the rbs itself, hence why v$rollstat showed me no active transactions.

b) ( this one is exclusive ). Does v$rollstat shows whenever any transactions uses any rollback segments being this undo information cached or not ? I suppose that when the information is small enough, it can be cached, so no rollback segment is used.

Thank you!

I will have to owe you the error I guess ( I dont have it as I said -- Forms 6i is the application frotend). I wonder why I got the error since xacts on v$rollstats were all 0, which lead me to believe they were no "active" transactions. And even thought, why would Oracle let me put a rollback segment offline if then it's going to complain about it ?

.. Tom, it is not that error I was refering to, It was oracle complaining about missing the file. Fortunately, I did another test and got the same exact error message, please see below:

ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'xxxxxx/xxxxxxxxxxx/RBS01.DBF'

What I did was this:
( a ) Created new tablespace with its associated rbs.
( b ) Brought the new tablespace online.
( c ) Took offline the old rbs.
( d ) Took offline the tablespace where the old rbs where.

The file is complaining about is the datafile associated with the tablespace where the old rbs where. Any thoughts ?

We are running 8.1.7

And that's my concern, I especifically waited until all of the users where offline ( past work time ). I checked the v$ tables, and the old rbs's were not even listed there. Then I proceeded to take its tablespace down ( also, woulnd't taking a tablespace offline with "active" rbs raise an error ? ).

This morning, users came, application startup, after a transaction, they got this message. I'm still trying to figure out what I did wrong, and the root of the problem. I will need to wait until I have an option to restart the instance to see if that fix the problem.

Hello Tom,
How can you custom use a rollback segment (specify the rollback segment to use for processing). Is it a good idea to do so or let Oracle manage this task for you.

Thanks very much

> can you replicate this just in sqlplus?

Hi tom, me again (still with the problem, but dealing with it). I dont know how to actually replicate this in sqlplus (can you tell me ?).

Here's some info about the tablespace and segments on it..

SQL@8i> select segment_type, segment_name from dba_segments where tablespace_name = 'RBS';

SEGMENT_TYPE SEGMENT_NAME
------------------ -------------------------------------------------------------------------
ROLLBACK RBS0
ROLLBACK RBS1
ROLLBACK RBS2
ROLLBACK RBS3
ROLLBACK RBS4
ROLLBACK RBS5

6 rows selected.

SQL@8i> select segment_name, status from dba_rollback_segs where tablespace_name = 'RBS';

SEGMENT_NAME STATUS
------------------------------ ----------------
RBS0 OFFLINE
RBS1 OFFLINE
RBS2 OFFLINE
RBS3 OFFLINE
RBS4 OFFLINE
RBS5 OFFLINE

6 rows selected.

but no matter what I do, if I set the tablespace offline, I begin to get oras 00376 every now and then at the application level. As you can see, there is nothing in that tablespace, allthough there was information there at some point in time (wouldn't even let me resize the datafile because of this).

Still trying to figure out what is causing the error though.

Hi Tom,

I am getting ORA-01555 error when I my one job is executing. I have read your stuff that was very useful. Now, I know why it does happen, but still I have some confusion in my case. I have two questions.

i) I am getting error "ORA-01555: Snapshop too old, rollback segment number 9 with name R08."

you can see I have no such rollback segment "R08" #9.

sys@NPESDH> select * from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS INSTANCE_NUM RELATIVE_FNO
------------------------------ ------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ---------------------------------------- ------------
SYSTEM SYS SYSTEM 0 1 2 65536 65536 2 1017 0 ONLINE 1
SDH_1_RS PUBLIC RBS 1 4 2 524288 524288 8 32765 0 ONLINE 4
SDH_2_RS PUBLIC RBS 2 4 258 524288 524288 8 32765 0 ONLINE 4
SDH_3_RS PUBLIC RBS 3 4 514 524288 524288 8 32765 0 ONLINE 4
SDH_4_RS PUBLIC RBS 4 4 770 524288 524288 8 32765 0 ONLINE 4
SDH_5_RS PUBLIC RBS 5 4 1026 524288 524288 8 32765 0 ONLINE 4

6 rows selected.

ii) I understood that I need more space for UNDO data, so I need to increase the size of the RBSs. But when I checked the v$rollstat view, some questions raised in my mind.

sys@NPESDH> SELECT * FROM V$ROLLSTAT;

USN EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------
0 12 770048 16954 0 67615 0 770048 0 0 0 0 0 ONLINE 7 1
1 8 4177920 2442235008 0 1013711291 8522 4194304 315080704 10672 210536 103340 247405 21768962 ONLINE 2 4
2 8 4177920 1072970924 0 1027716833 8909 4194304 302497792 11378 218060 110552 186896 572087 ONLINE 6 1
3 8 4177920 2150316978 0 949092291 8857 4194304 481804288 11780 220611 114414 352403 1567145 ONLINE 0 6
4 8 4177920 789762662 0 656273091 8704 4194304 472367104 11280 215398 109330 131722 585784 ONLINE 2 31
5 8 4177920 3162564776 0 984217837 6283 4194304 303022080 8382 160836 81080 459862 10987782 ONLINE 1 24

6 rows selected.

Now, as you can see non of my rollback segment has grown to its full capacity.

sys@NPESDH> select usn, hwmsize/power(2,30) hwmsize, (524288*32765)/power(2,30) capacity from v$rollstat;

USN HWMSIZE CAPACITY
-------------------- -------------------- --------------------
0 .0007171630859375 15.99853515625
1 .2934417724609375 15.99853515625
2 .2817230224609375 15.99853515625
3 .4487152099609375 15.99853515625
4 .4399261474609375 15.99853515625
5 .2822113037109375 15.99853515625

6 rows selected.

NOW, I am not sure that if I increase the size of my RBS by increasing the EXTENT SIZE or MAXEXTENT, will work.

Thanks and regards,

Thanks a lot for your response Tom.

I was not correctly reporting my problem. Actually my query was selecting data from other database using a DB link (which I myself came to know later). And some other job is removing data from this table after a regular interval of time. I think

that's database's job is overwriting rollback segment blocks and causing ORA-01555.

So, i think i got the answer of my first (i) question in previous posting. That rollback segment "R08" #9 was not mine, it

was that database's rollback segment.

SELECT /*+ FULL(ALARM) */ config_alarm, ...
FROM ecat.alarm@ecat <------- DB LINK
WHERE ..... ;

Now, it is clear to me that why the HWM of my RBS is showing this much low against capacity (actually HWM was correct, they

never required to grow beyond that, and my RBS never caused ORA-01555).

I have some more to ask.

1) Have I recognized my problem right?
2) Was my way of calculation in previous posting right?
(comparing RBS max capacity v/s HWM of RBS)
3) One transaction can only use only one rollback segment.
4) How to estimate the size of a transaction? (to avoid wraping)

Thanks and regards,

Tom,

Large inserts into multiple tables were part of a script without any commit inbetween the inserts. So the whole script was one big transaction. The no of records approximated 10-15 million records in each table. Some tables in between were just a few thousand records.

After a couple of large table inserts the status of which I was monitoring in v$sesion_longops, the insert into the small table (around 10000 records) started, and I find the time estimated for the insert to finish was increasing from 5 minutes to over 4 hrs.

a. Could this situation arise because of one large transaction?

What I did was killed the session. The status of that session though showing KILLED, I find the LOGICAL READS and the PHYSICAL READS going up and up (from the Ent Mgr).

b. Is this part of the rollback going on?
c. How do I ascertain which insert is being rolled back?
d. Is there a way to know how much more time it could take?
d. Is there any way to cutshort this process? As even a select count(*) on the table from where the insert was going on is taking lots of time ( around 10 mts for a 4 million row table).

Thanks Tom. Excuse me for giving a narrative on what was going on.

The database is configured and running with
undo_management = auto, undo_retention = 300
undo _tablespace = <tablespace_name>

Is there a way to temporarily configure
undo_mangement = manual, create a large rollback segment
for maintenace and switch back

When I created a large rollback segment when
AUTO is in effect, the rbs was created. But when
I edited the init.ora (commented out undo parameters)
and specified the rollback segment, oracle could not
recognize the rollback segment when try to start the
database