Re: Questions on extents, etc.

In article <8dliqa$rqh$1_at_nnrp1.deja.com>,   argosy22_at_my-deja.com wrote:
> HI all,
>
> I've been reading about tablespaces, rollback segments,
> extents, and I have some questions about them.
>
> If I use this statement to create a tablespace:
>
> Create tablespace ROLLBACK_TBS
> DATAFILE '/rollback/somefile.dbf'
> SIZE 25M
> DEFAULT STORAGE
> (
> INITIAL 160K
> NEXT 160K
> PCTINCREASE 0
> MINEXTENTS 2
> MAXEXTENTS 100
> )
>
> and then 3 rollback segments with this statement:
>
> Create ROLLBACK SEGMENT roll1_rbs
> (roll2_rbs, roll3_rbs)
> TABLESPACE ROLLBACK_TBS
> STORAGE
> (
> INITIAL 160K
> NEXT 160K
> MINEXTENTS 2
> MAXEXTENTS 30
> )
>
> I'm wondering:
> - is the whole tablespace being used, or, am I limiting
> its use to 16 megs (160k * 100 = 16000K = 16M) ?
> If so, can we ensure all the 25M is used by just leaving
> out the MAXEXTENTS clause?

the most space you will be using is (3 * (30 * 160k)). You won't use this much space until your rollback segments extend beyong your initial 2 extents (which probably won't take long). If you omit the maxextents clause altogether, I think Oracle will set a maxextents value based on your block size.

>
> - is any transaction using this tablespace/rollback segment
> now limited to 4800K (160K * 30 = 4800K = 4.8M) total rollback
> resource? One big transaction can only use one rollback
> segment, correct?

yes, and yes.

>
> - can I create 10 rollback segments, in the same manner,
> all using the same tablespace, or will that cause an error?
> (10 * 4.8M = 48M. Larger than 25M.)

unless you have autoextend turned on for the datafile '/rollback/somefile.dbf' you will receive an error

>
> - if a transaction runs out of extents, could I just increase
> the rollback segment's MAXEXTENTS to say, 100? Would this
> conflict with the other rollback segments?

yes you could increase the maxextents parameter for the rollback segment, but I would suggest doing it for all of the rbs at the same time. Unless you use the set transaction use rollback segment roll1_rbs (or roll2_rbs or roll3_rbs) you have no way of knowing or controlling which rollback segment the transaction will use. Therefore if you only increase one rollback's maxextents, you only have a 1 in 3 shot at hitting that particular rollback the next time you run the transaction.

>
> - while not a good idea to just give the UNLIMITED clause
> for rollback segments, if it was given, does the rollback
> segment grow until the tablespace reaches 25M? Or, until the
> disk is full?

again, depends on whether or not autoextend is turned on. If it is, the datafile will grow until the disk is full. If not, it will stop at 25M.

>
> I've read what I could on these subjects, but it's not
> quite clear yet (as you can tell).
>
> Thanks,
>
> Argosy

hope this helps.
-Roy

>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 20 2000 - 00:00:00 CDT