| MAX EXTENT question [message #186579] |
Tue, 08 August 2006 10:18  |
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  |
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 |
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 |
joy_division
Messages: 4963
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 #186812 is a reply to message #186779] |
Wed, 09 August 2006 12:36 |
joy_division
Messages: 4963
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 #186962 is a reply to message #186579] |
Thu, 10 August 2006 05:52 |
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 |
joy_division
Messages: 4963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
