-
I have a tablespace whose max_extent is 505. I am creating an index which comes out giving an error max_extent reached. I want to increase the max_extent for that particular tablespace. Can I do it by issuing an alter tablespace command? I am using oracle 7.3.3 on HP-Unix 10.20. What will be the syntax for the same?
adg
-
I think the actual problem is the lack of space to create that index. Add some more space to that tablespace or add one more datafile to that tablespace
Originally posted by anjandg
I have a tablespace whose max_extent is 505. I am creating an index which comes out giving an error max_extent reached. I want to increase the max_extent for that particular tablespace. Can I do it by issuing an alter tablespace command? I am using oracle 7.3.3 on HP-Unix 10.20. What will be the syntax for the same?
-
the problem is no. of extents
I can't add space on that tablespace as there is no space left in the server. If I see the dba_tablespaces, the maximum no. of extents for that particular tablespace is 505. Now I want to create an index on that tablespace giving initial extent 10240K and next_extent 10240K. After sometime it gives an error similar to 'maximum no. of extent (505) reached for the tablespace' . While creating a tablespace the default is set for maximum no. of extents. Can it be changed to UNLIMITED by issuing an alter tablespace statement ? It it can be changed, what will be the syntax ?
adg
-
Re: the problem is no. of extents
First you try to provide the sufficient space for that tablespace on your server by delete some unnecesary files or moving some files to another location then you try to create the index
The Syntax for allocating unlimited quota to a tablespace is:
alter tablespace quota unlimited
Cheer ...
Originally posted by anjandg
I can't add space on that tablespace as there is no space left in the server. If I see the dba_tablespaces, the maximum no. of extents for that particular tablespace is 505. Now I want to create an index on that tablespace giving initial extent 10240K and next_extent 10240K. After sometime it gives an error similar to 'maximum no. of extent (505) reached for the tablespace' . While creating a tablespace the default is set for maximum no. of extents. Can it be changed to UNLIMITED by issuing an alter tablespace statement ? It it can be changed, what will be the syntax ?
-
Sekhar,
Thankyou for extending your help. I don't think the problem is relaing to quota. Anyway, I am trying to create the index again by allocating more initial and next extent. That will reduce the no. of extent. I will also drop unnecessary indexes to create space on that tablespace.
Thanks again.
adg
-
It seems to me that either you have many objects in that tablespace or you have few objects with many small extents.
To alter tablesapce maxextent caluse you can use;
ALTER TABLESPACE ts_name STORAGE (MAXEXTENTS n);
Also check the INITIAL and NEXT clause of the index you are going to create. Rebuilding the existing indexes may also free-up some extents in the tablespace.
HTH
Sanjay
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|