Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Adding rollback
bw wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message > news:<41221dde$0$3928$afc38c87_at_news.optusnet.com.au>...
> > > Thanks to all for your help and education. as it turns out, the > UNDO_MANAGEMENT param is set to manual and we are running from the > spfile.
Well, that's another reason why I prefer to stick to dba_rollback_segs as a view, since it works in both manual and auto environments.
> Im still shaky on ROLLBACK_SEGMENTS as it relates to this > issue. UNDO_MANAGEMENT set to manual and ROLLBACK_SEGMENTS set to > null in the parameter file are confusing me.
It shouldn't do. Undo_management=MANUAL means "I will create all my own rollback segments, and do all subsequent management of them". A null value for ROLLBACK_SEGMENTS means "I have not created any of my own rollback segments and I have nothing to subsequently manage".
If you want a read-only database, the apparent contradiction between the two statements is perfectly OK (try some ordinary DML on an ordinary table in your database right now and you should get an error message along the lines of 'can't use system rollback segment for updates in non-system tablespace').
> does oracle still > generate the needed rollback based on the calculation of the > TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT parameters?
Oracle generating anything unspecified rather suggests an 'automatic' setting for something, no? Your undo setting is 'manual'. So no, it won't do anything automatically for you.
What those two parameters you mention do is this: if, at startup, Oracle finds some public rollback segments already created, it will bring as many of them online automatically as it computes are required for normal database operations. And the computation it performs is to take the transactions parameter and divide it by the transactions_per_rollback_segment one. That saves you having to create a bunch of private rollback segments yourself and then laboriously list them in the ROLLBACK_SEGMENTS parameter -because that's the only way a private rollback segment can be brought online when the database is first opened.
So: if you want to list lots of stuff in ROLLBACK_SEGMENTS, you need to do this:
create tablespace RBS datafile 'etc/etc/etc' size whatever EXTENT MANAGEMENT DICTIONARY default storage (initial yM next yM minextents 20);
create rollback segment RB1 tablespace rbs; create rollback segment RB2 tablespace rbs; create rollback segment RB3 tablespace rbs; AND SO ON
And then: ROLLBACK_SEGMENTS=RB1,RB2,RB3 etc etc etc
Or, you can do this:
create tablespace RBS datafile '/etc/etc/etc' size whatever extent management dictionary default storage (initial yM next yM minextents 20);
create public rollback segment RB1 tablespace rbs; create public rollback segment RB2 tablespace rbs; create public rollback segment RB3 tablespace rbs;
And then: TRANSACTIONS=10000 (or whatever) TRANSACTIONS_PER_ROLLBACK_SEGMENT=4 (or whatever)
In either case, I've suggested you use dictionary managed tablespace for the rollback segment tablespace. It's a minor thing, but you can't create your first non-system rollback segment in locally-managed tablespace. You'll get the 'can't use the system rollback segment for non-system DML' error I mentioned before if you do. Therefore, using dictionary-managed tablespace is easier. (There is a workaround, which if you decide to go this route, but have to use locally-managed tablespaces, I'll let you know if you ask).
Far, far easier than any of that old nonsense, however, is:
create undo tablespace RBS datafile '/etc/etc/etc' size whatever;
And then: UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=RBS
There's no reason NOT to use automatic undo. There's every reason to do so.
Regards
HJR
Received on Tue Aug 17 2004 - 18:46:57 CDT