rollback segs [message #320815] |
Fri, 16 May 2008 06:07 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
Hil all,
Some info first
Oracle : Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
OS : HP-UX B.11.11 U 9000/800
Please have a look at the following querys:
SQL> select extent_management from dba_tablespaces where tablespace_name='RBS';
EXTENT_MAN
----------
DICTIONARY
SQL>
SQL> alter rollback segment R09 storage(maxextents 32764);
Rollback segment altered.
SQL> alter rollback segment R09 storage(maxextents 32765);
Rollback segment altered.
SQL> alter rollback segment R09 storage(maxextents 32766);
alter rollback segment R09 storage(maxextents 32766)
*
ERROR at line 1:
ORA-02221: invalid MAXEXTENTS storage option value
SQL>
As you can see the first 2 querys are fine. The third however fails. This because the value of the storage option is invalid.
Now I think that there is a maximum for the maxextents option when dictionary managed. (At the moment I think it's 32765)
Now my questions:
1) Is the above assumption that there is maximum to maxextents right?
2) If q1 is answered with yes, then how can I find out this maximum?
3) If q1 is answered with no, where is the quirk in my thinking (or brain)?
Best regards,
Martijn
|
|
|
|
|
|
Re: rollback segs [message #320847 is a reply to message #320841] |
Fri, 16 May 2008 07:49 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 16 May 2008 14:32 | Quote: | Just to clearify... 32765 is a value hardcoded into oracle?
|
Yes
|
thanks.
Michel Cadot wrote on Fri, 16 May 2008 14:32 |
Quote: | With the sql I sometimes see on the sytem I can imagine filling up whole disks with rollback
|
Only if you let it do it with file in autextend mode and unlimited size. Limit the size of the files.
The only case I saw this kind of things happens is when some use (this bullshit of) TOAD and never disconnect.
Regards
Michel
|
yes... I got carried away by the unlimited maxextents...didn't think of maxsize of files anymore. (btw ... I never create datafiles with "autoextend on maxsize unlimited")
I would not sleep well
tnx again,
Martijn
|
|
|