Rollback segments: maxextents changed during create?

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...

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.

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...

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?

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!

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?

Go for automatic undo management. Let oracle take care!

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...)

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.

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