Home » RDBMS Server » Server Administration » MAX EXTENT question
MAX EXTENT question [message #186579] Tue, 08 August 2006 10:18 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
CREATE INDEX PSAG_LD_ARCHIVE ON PS_G_LD_ARCHIVE (G_LD_CHARGED_COMP, G_LD_MAIN, G_LD_CHARGED_DEPT, RUN_ID, G_LD_EMP_NO) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS 300 PCTINCREASE 0) PCTFREE 10

throws error..
ORA-01630: max # extents (300) reached in temp segment in tablespace PSINDEX

Can someone tell me how to troubleshoot this issue .
Re: MAX EXTENT question [message #186581 is a reply to message #186579] Tue, 08 August 2006 10:21 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
PSINDEX Tablespace propery is as follows ...
INITIAL_EXTENT-1,048,576
NEXT_EXTENT - 1,048,576
MAX_EXTENTS -512,000

Free space in PSINDEX Tablespace is 11,347,591,168 bytes
Re: MAX EXTENT question [message #186598 is a reply to message #186579] Tue, 08 August 2006 12:38 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
In index creation script ,i used MAXEXTENTS 300 .
And my tablesapce MAX_EXTENTS is 512,000 .
Any idea wht change i have to make.in script or tablespace definition ?
Re: MAX EXTENT question [message #186609 is a reply to message #186598] Tue, 08 August 2006 14:15 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I don't understand what you are asking, but using MAXEXTENTS in an INDEX creation will override the tablespace. I wouldn't give ANY storage clause for the object and just let it inherit those values for the tablespace.

You aren't using Dictionary Managed Tablespaces, are you? I see the legacy PCTINCREASE in your create command.

[Updated on: Tue, 08 August 2006 14:20]

Report message to a moderator

Re: MAX EXTENT question [message #186727 is a reply to message #186579] Wed, 09 August 2006 04:31 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Can someone tell me what is the issue here ?how to troubleshoot?
Re: MAX EXTENT question [message #186772 is a reply to message #186727] Wed, 09 August 2006 07:51 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Huh? I already gave you an answer.
Re: MAX EXTENT question [message #186779 is a reply to message #186579] Wed, 09 August 2006 08:21 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
In my index creation script(given in first post) , changing 300 to UNLIMITED will solve the issue ??
Re: MAX EXTENT question [message #186812 is a reply to message #186779] Wed, 09 August 2006 12:36 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
gkrishn wrote on Wed, 09 August 2006 09:21

In my index creation script(given in first post) , changing 300 to UNLIMITED will solve the issue ??


Maybe. Either that or give NO STORAGE PARAMETERS and let the objects use the default for the tablespace.

You didn't answer me about whether you are using Dictionary Managed Tablespaces. Because you have given STORAGE parameters, you are reaching 300 extents. 1 extent at roughly 40k and the next 299 extents and roughly 100k. You are limiting the successful creation of an index to less than 29Mb. That's not very flexible. Don't use ANY storage parameters! Use Locally managed tablespaces.

[Updated on: Wed, 09 August 2006 12:41]

Report message to a moderator

Re: MAX EXTENT question [message #186831 is a reply to message #186579] Wed, 09 August 2006 15:02 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
I am sorry if i am wrong but storage clause is only applicable in dictionary managed tablepsaces.
Re: MAX EXTENT question [message #186962 is a reply to message #186579] Thu, 10 August 2006 05:52 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hey Joy division ,

Thnx for ur reply. My tablespace is DICTIONARY managed.Could you please explain how it matters ?
Re: MAX EXTENT question [message #186995 is a reply to message #186962] Thu, 10 August 2006 08:24 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Ok, if you're using DMT, you can still leave off the STORAGE clause and it will use the default for the TABLESPACE itself, which in your case would be 512k and your extent sizes would be 1Mb.

Note to Yasir: You can use STORAGE clause in LMT for things like INITIAL and NEXT, which would simply create a number of extents to satisfy the INITIAL parameter that was given. PCTINCREASE has no meaning and I believe would give an error.
Previous Topic: jserver problem
Next Topic: Oracle 10g RAC+ASM Storage Issue
Goto Forum:
  


Current Time: Wed Dec 07 20:39:27 CST 2016

Total time taken to generate the page: 0.05483 seconds