| MAXEXTENTS [message #444756] |
Tue, 23 February 2010 23:47  |
reym21
Messages: 241
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 #444842 is a reply to message #444756] |
Wed, 24 February 2010 08:12  |
joy_division
Messages: 4963
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 |
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 #509690 is a reply to message #509687] |
Tue, 31 May 2011 05:17 |
cookiemonster
Messages: 13959
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 #509817 is a reply to message #509700] |
Wed, 01 June 2011 03:46 |
 |
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 |
cookiemonster
Messages: 13959
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 #509847 is a reply to message #509840] |
Wed, 01 June 2011 05:52 |
Roachcoach
Messages: 1576
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 #509967 is a reply to message #509836] |
Wed, 01 June 2011 22:15 |
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 #510745 is a reply to message #510742] |
Tue, 07 June 2011 08:07 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
