Rollback segments: maxextents changed during create?

Printable View

12-04-2003, 07:35 AM
efrijters

Rollback segments: maxextents changed during create?

Hello guys,

I'm creating a new TS that holds my rollback segments: LMT with uniform size of 512K. (db : oracle 8.1.7.1.1)
I'm recreating the rbs's because I want to remove the optimal-clause (I detected a lot of shrinking actions in v$rollstat) and the original TS was DD.

When creating the rollback segment, I issue a MAXEXTENT 505

Code:

CREATE PUBLIC ROLLBACK SEGMENT RBS01 TABLESPACE "RBS2" STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 10 MAXEXTENTS 505)

When I checked it afterwards, the rbs is created okay, the initial extents are there (checked with "dba_extents"), but the maxextent-item in DBA Studio has changed to 32765, not 505.

Why did the maxextent-value change? Is this a fault of DBA Studio?

Can someone clarify this behaviour?

Thanks in advance!
Erik
12-04-2003, 07:39 AM
Thomasps

Please post the result of the query

select segment_name,MIN_EXTENTS,MAX_EXTENTS
from sys.dba_rollback_segs;
12-04-2003, 07:40 AM
efrijters

Code:

SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS 2 from sys.dba_rollback_segs; SEGMENT_NAME MIN_EXTENTS MAX_EXTENTS ------------------------------ ----------- ----------- SYSTEM 2 505 RBS0 8 8192 RBS1 8 8192 ... etc etc... RBS8 8 8192 RBS9 8 8192 RBS01 1 32765 RBS02 1 32765

Hmmm .... MINEXTENTS now is 1 in stead of 10...
12-04-2003, 07:50 AM
Thomasps

It works perfectly fine for me!!

Code:

SQL> CREATE PUBLIC ROLLBACK SEGMENT RBS01 TABLESPACE RBS STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 10 MAXEXTENTS 505); Rollback segment created. SQL> CREATE PUBLIC ROLLBACK SEGMENT "RBS02" TABLESPACE RBS STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 10 MAXEXTENTS 505); Rollback segment created. SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS from sys.dba_rollback_segs; SYSTEM 2 505 RBS0 8 4096 RBS1 8 4096 RBS2 8 4096 RBS3 8 4096 RBS4 8 4096 RBS5 8 4096 RBS6 8 4096 RBS7 8 4096 RBS01 10 505 RBS02 10 505 11 rows selected.
12-04-2003, 07:53 AM
efrijters

Thanks Thomasps!

This is wierd.... could this be the result of a setting in the TS?

Code:

CREATE TABLESPACE "RBS3" LOGGING DATAFILE 'L:\ORACLE\ORADATA\DB01\RBS31.ORA' SIZE 8M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE 1024M, 'L:\ORACLE\ORADATA\DB01\RBS32.ORA' SIZE 8M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
12-04-2003, 07:56 AM
efrijters

I did a COPY-PASTE of your create-statement and again 32768 MAXEXTENTS and 1 for MINEXTENTS...
12-04-2003, 08:04 AM
Thomasps

Quote:

I did a COPY-PASTE of your create-statement and again 32768 MAXEXTENTS and 1 for MINEXTENTS...

But look at the maxextents it is 32765!!

Code:

SEGMENT_NAME MIN_EXTENTS MAX_EXTENTS ------------------------------ ----------- ----------- RBS01 1 32765 RBS02 1 32765
12-04-2003, 08:09 AM
efrijters

Please excuse me. The 32768(32k) is such a popular number... :rolleyes:

But the original question still stands: maxextents doesn't have the right value (altering this value isn't possible.)

I'll try with a lower value, say 400.

Be right back...
12-04-2003, 08:12 AM
efrijters

And again... same result

Code:

CREATE PUBLIC ROLLBACK SEGMENT RBS04 TABLESPACE RBS3 STORAGE ( INITIAL 512 NEXT 512K MINEXTENTS 10 MAXEXTENTS 400) rollback-segment created. SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS from sys.dba_rollback_segs; SEGMENT_NAME MIN_EXTENTS MAX_EXTENTS ------------------------------ ----------- ----------- SYSTEM 2 505 RBS0 8 8192 RBS1 8 8192 ... etc etc... RBS8 8 8192 RBS9 8 8192 SEGMENT_NAME MIN_EXTENTS MAX_EXTENTS ------------------------------ ----------- ----------- RBS03 1 32765 RBS02 1 32765 RBS04 1 32765

I'm confused... :confused:
12-04-2003, 08:18 AM
Thomasps

CREATE ROLLBACK SEGMENT RBS05
TABLESPACE RBS3
STORAGE (MINEXTENTS 10 MAXEXTENTS 400);

Just curious. We are missing something,,?
12-04-2003, 08:25 AM
efrijters

Okay,

I created one more. Now in the Data Dictionary managed ts.

Code:

1 CREATE PUBLIC ROLLBACK SEGMENT RBS9a 2 TABLESPACE RBS 3* STORAGE ( INITIAL 512 NEXT 512K MINEXTENTS 8 MAXEXTENTS 400) SQL> / Rollback-segment created. SQL> select segment_name,MIN_EXTENTS,MAX_EXTENTS from sys.dba_rollback_segs; SEGMENT_NAME MIN_EXTENTS MAX_EXTENTS ------------------------------ ----------- ----------- SYSTEM 2 505 RBS0 8 8192 RBS1 8 8192 ...etc etc... RBS8 8 8192 RBS9 8 8192 RBS03 1 32765 RBS02 1 32765 RBS04 1 32765 RBS9A 8 400

As you can see: it works great!
So, the difference here is the TS.
How can I lower the maxextent for the LMT-rollback segments? I don't want to create such large rbs's...
12-04-2003, 08:34 AM
abhaysk

Quote:

Originally posted by efrijters
But the original question still stands: maxextents doesn't have the right value (altering this value isn't possible.)

Please read about segments in LMT.
12-04-2003, 09:07 AM
efrijters

Metalink doesn't seem to have any subjects on maxextents and LMT's.

Please, does anyone have a link to the right documentation?
I'll keep reading in the meantime...
12-04-2003, 09:24 AM
efrijters

Quote:

Originally posted by abhaysk
Please read about segments in LMT.

Am I overlooking something simple here?

Calculated guess: Is the MAXEXTENTS-parameter in the create-statement overruled by the MAXEXTENTS-value of the LMT-tablspace?
12-05-2003, 12:20 AM
Thomasps

From Oracle 9i Docs (a96524.pdf, page: 124)

Quote:

A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
For system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.
For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and
DEFAULT STORAGE are not valid for extents that are managed locally.

Thanks abhaysk. It (MAXEXTENTS) helped me too!
12-05-2003, 04:23 AM
efrijters

Okay, I get it (I think). So MAXEXTENT is no longer supported in LMT-ts's.

But: Oracle has a chapter on rollback segments and only displays examples of rbs on data dictionary managed ts's:

Link to 9i documentation on rbs storage params

Code:

..... The following statement creates a rollback segment with these characteristics: CREATE ROLLBACK SEGMENT rbs_01 TABLESPACE rbsspace STORAGE ( INITIAL 100K NEXT 100K OPTIMAL 4M MINEXTENTS 20 MAXEXTENTS 100 ); .....

How about that? :D

Last question: My main concern is to prevent the rollback segment of becoming too large. Now I can only do this via controlling the size of the datafile, right?
12-05-2003, 04:46 AM
Thomasps

Go for automatic undo management. Let oracle take care!
12-05-2003, 04:56 AM
efrijters

Yummie! automatic undo management.... I'd wish I could do that, but all my clients are running on 8i...
And because Oracle doesn't stop supporting 8i, nobody's interested in a migration to 9i... Bummer...!

Thanks Thomasps!

(one small ps: :rolleyes: can someone answer my last question? :rolleyes: I really want to be sure on this...)
12-05-2003, 05:00 AM
jmodic

Quote:

Originally posted by efrijters
(one small ps: :rolleyes: can someone answer my last question? :rolleyes: I really want to be sure on this...)

Yes, it seems to be so. Unless of course you want to switch back to DMT for rollback segments.
12-05-2003, 05:30 AM
efrijters

Thanks! All doubts are gone.

Quote:

Originally posted by jmodic
Yes, it seems to be so. Unless of course you want to switch back to DMT for rollback segments.

DMT - Suddenly I feel a sickness coming up... :D