How to increase max_extent for a tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to increase max_extent for a tablespace

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    18

    Question

    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

  2. #2
    Join Date
    Jan 2002
    Posts
    78
    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?

  3. #3
    Join Date
    Mar 2002
    Posts
    18

    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

  4. #4
    Join Date
    Jan 2002
    Posts
    78

    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 ?

  5. #5
    Join Date
    Mar 2002
    Posts
    18
    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

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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
  •  


Click Here to Expand Forum to Full Width