Home » RDBMS Server » Server Administration » rollback segs
rollback segs [message #320815] Fri, 16 May 2008 06:07 Go to next message
martijn
Messages: 283
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 #320826 is a reply to message #320815] Fri, 16 May 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to give a value the maximum is 32765.
If you don't want a limit, give UNLIMITED.

Regards
Michel
Re: rollback segs [message #320833 is a reply to message #320826] Fri, 16 May 2008 07:07 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Hi Michel,

Just to clearify... 32765 is a value hardcoded into oracle?

I'm familiar with the unlimited option, however I don't want to use it. With the sql I sometimes see on the sytem I can imagine filling up whole disks Shocked with rollback

Best Regards,
Martijn
Re: rollback segs [message #320841 is a reply to message #320833] Fri, 16 May 2008 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Just to clearify... 32765 is a value hardcoded into oracle?

Yes

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
Re: rollback segs [message #320847 is a reply to message #320841] Fri, 16 May 2008 07:49 Go to previous message
martijn
Messages: 283
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 Smile

tnx again,
Martijn
Previous Topic: enq: TX - row lock contention
Next Topic: Sybase to oracle migration
Goto Forum:
  


Current Time: Tue Dec 06 06:31:45 CST 2016

Total time taken to generate the page: 0.08691 seconds