Home » RDBMS Server » Server Administration » MAXEXTENTS (Oracle 7.3.4)
MAXEXTENTS [message #444756] Tue, 23 February 2010 23:47 Go to next message
reym21
Messages: 195
Registered: February 2010
Location: Philippines
Senior Member

Pls. help! I encountered this error yesterday:
"ORA-1631: max # extents 504 reached in table/cluster <table_name>", I've found the ff. parameters for the said
table using:
a) dba_tables:
initial_extent = 16384,
next_extent = 8192,
min_extents = 1,
max_extents = 504,
pct_increase = 0,
pct_free = 20,
pct_used = 60,
b) dba_segments:
blocks = 511,
c) init.ora:
db_block_size = 8192,
db_block_buffer_size = 200.

Now my question is if I ALTER the TABLE, what would be my next max_extents value (Oracle 7.3.4)? How to come up with a value given the ff. parameters? Thanks in advance.
Re: MAXEXTENTS [message #444764 is a reply to message #444756] Wed, 24 February 2010 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With your block size the maximum is 505.
You can set it to unlimited if parameter "unlimited_max_extents" (or the like, I didn't remember clearly something that is 15 years old) is set to true.

Regards
Michel
Re: MAXEXTENTS [message #444842 is a reply to message #444756] Wed, 24 February 2010 08:12 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
I would just recreate the table with bigger extent sizes. Based on those parameters, the table is roughly 4Mb.
you would have to recreate indexes and constraints and recompile any objects that become invalidated (but I do not remember if v7 even had stored procedures and functions).
Re: MAXEXTENTS [message #444931 is a reply to message #444756] Wed, 24 February 2010 21:14 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
7.3.4 is not limited to 504 extents.

You can ALTER TABLE tablename STORAGE (MAXEXTENTS UNLIMITED)

or

ALTER TABLE tablename STORAGE (MAXEXTENTS 2000)


However, your problem is that the NEXT_EXTENT is *too* low.

You should also increase your NEXT_EXTENT size !

ALTER TABLE tablename STORAGE (NEXT 1M)


Hemant K Chitale
Re: MAXEXTENTS [message #444957 is a reply to message #444931] Wed, 24 February 2010 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ALTER TABLE tablename STORAGE (MAXEXTENTS 2000)

No you can't do that.
It is either the maximum allowed by the block size (that is 505 here) or UNLIMITED, it can't be between.

Regards
Michel
Re: MAXEXTENTS [message #509686 is a reply to message #444957] Tue, 31 May 2011 05:06 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

Hello Michel

I referred Oracle metalink there They are saying that by setting

ALTER TABLE tablename STORAGE (maxextents UNLIMITED) We will have future problems in the database So please kindly help me on that ..Smile

Re: MAXEXTENTS [message #509687 is a reply to message #509686] Tue, 31 May 2011 05:08 Go to previous messageGo to next message
Roachcoach
Messages: 1401
Registered: May 2010
Location: UK
Senior Member
Probably not very significant compared to the problems arising from using 19 year old software Wink
Re: MAXEXTENTS [message #509690 is a reply to message #509687] Tue, 31 May 2011 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
@Roachcoach - I wouldn't be assuming that, since the person you are replying to isn't the OP.
@kapardi - what metalink note are you refering to? What version of oracle are you using?
Re: MAXEXTENTS [message #509691 is a reply to message #509690] Tue, 31 May 2011 05:21 Go to previous messageGo to next message
Roachcoach
Messages: 1401
Registered: May 2010
Location: UK
Senior Member
/mutters about replying to posts instead of making new ones with all data Razz
Re: MAXEXTENTS [message #509696 is a reply to message #509690] Tue, 31 May 2011 05:37 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

@cookiemonster
support.oracle metalink
Re: MAXEXTENTS [message #509698 is a reply to message #509696] Tue, 31 May 2011 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which note? This is the question, give the number.

Regards
Michel
Re: MAXEXTENTS [message #509700 is a reply to message #509698] Tue, 31 May 2011 05:44 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I also asked for your oracle version.
Re: MAXEXTENTS [message #509817 is a reply to message #509700] Wed, 01 June 2011 03:46 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

hello michel cadot please check the following matter that appeared in metalink

<Removed copyrighted material - Note is ID 152475.1>




CM: removed copyrighted material

[Updated on: Wed, 01 June 2011 04:13] by Moderator

Report message to a moderator

Re: MAXEXTENTS [message #509825 is a reply to message #509817] Wed, 01 June 2011 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is a request to supply a metalink note id really that hard to follow?

Metalink notes are copyrighted and as such should not be copied here. So I've removed most of your post.
Next time do as we ask.
Re: MAXEXTENTS [message #509828 is a reply to message #509817] Wed, 01 June 2011 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a VERY old note that was not correctly updated.
The warning comes from the fact that having numerous extents (more than a couple of hundreds) in dictionary managed tablespace (DMT) could impact the performances.
This is no more the case with LMT that can support dozen of thousands without any problem.

Regards
Michel
Re: MAXEXTENTS [message #509836 is a reply to message #509828] Wed, 01 June 2011 05:28 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

@michel cadot but it clearly says that IT CAUSES SERIOUS PROBLEMS IN DATABASES So how can we reduce this problem Smile
Re: MAXEXTENTS [message #509840 is a reply to message #509836] Wed, 01 June 2011 05:31 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

@michel So michel if this is the case why should a DBA must depend on metalink?? and if they are not updated i think there must be SR to be raised for that note to be updated

Regards
Kapardi
Re: MAXEXTENTS [message #509847 is a reply to message #509840] Wed, 01 June 2011 05:52 Go to previous messageGo to next message
Roachcoach
Messages: 1401
Registered: May 2010
Location: UK
Senior Member
What version did the note apply to? If it is out of support then it wouldn't be a huge shock if it was no longer accurate.
Re: MAXEXTENTS [message #509855 is a reply to message #509840] Wed, 01 June 2011 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kapardi wrote on Wed, 01 June 2011 12:31
@michel So michel if this is the case why should a DBA must depend on metalink?? and if they are not updated i think there must be SR to be raised for that note to be updated

Regards
Kapardi


If you read the referenced note named "ALERT: Using UNLIMITED Extent Format [ID 50380.1]", it says:
Quote:
*** Note: This alert was updated on 15-May-2002 to include information
about Oracle8i and Oracle9i.

And carefully read the section "Recommendations" you will understand how out dated the first article is.

Regards
Michel

Re: MAXEXTENTS [message #509967 is a reply to message #509836] Wed, 01 June 2011 22:15 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
>it clearly says that IT CAUSES SERIOUS PROBLEMS IN DATABASES
Rubbish. You are drawing your own inferences and attributing them to Oracle Support.


It has the phrases "use this with caution"
and ".... could result in large amounts of of [sic] space management operations in dictionary managed tablespaces".

A. It doesn't say that this is dangerous.
B. It says that "large amounts of space management operations" come into play in dictionary managed tablespaces.

If you read a note and do not understand it do not use ALL CAPITALS when commenting about it.


Hemant K Chitale
Re: MAXEXTENTS [message #510742 is a reply to message #509967] Tue, 07 June 2011 07:46 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

hello hemanth

Please give respect and take respect . I think its not the way to reply to a comment kindly please know how to reply to a comment

Thanks and Regards
Kapardi
Re: MAXEXTENTS [message #510745 is a reply to message #510742] Tue, 07 June 2011 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the argument is valid and I understand his passion (above all when you put it in capitals to emphasize your point).
Why don't you quote the note instead of interpreting it?

Regards
Michel

[Updated on: Tue, 07 June 2011 08:07]

Report message to a moderator

Re: MAXEXTENTS [message #510757 is a reply to message #510742] Tue, 07 June 2011 09:57 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
Kapardi,

Quote the note and what you've said in CAPITALS on any other Oracle DBA quorum and you will find that your comments in CAPITALS would not be accepted.

Hemant K Chitale
Re: MAXEXTENTS [message #510759 is a reply to message #509836] Tue, 07 June 2011 10:18 Go to previous message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
Kapardi,
1.The Oracle Support article "Overview Of ORA-01631: Max # Extents (%s) Reached in Table %s.%s [ID 152475.1]" that you quote states that a large number of extents is not advisable in dictionary managed tablespaces
2. Referes to article "ALERT: Using UNLIMITED Extent Format [ID 50380.1]". This article links the issue of UNLIMITED extents with dictionary managed tablespaces
3. Note 50380.1 states that Databases using locally managed tablespaces do not suffer these issues
4. Since 9i, the default extent management attribute is locally managed *not* dictionary managed.
5. 9.2 was released in July 2002 (9.0.1 would have been in 2000 or 2001). It has been at least 9 years since the default extent management in Oracle is locally managed tablespaces.


Hemant K Chitale
Previous Topic: Unable to Drop User
Next Topic: How do I add disk to ASM diskgroup?
Goto Forum:
  


Current Time: Tue Sep 27 19:33:48 CDT 2016

Total time taken to generate the page: 0.12886 seconds