Unable to extend temp segment. - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Unable to extend temp segment.

  1. #11
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    This is what I get.

    SQL> select sum(bytes) from dba_free_space where tablespace_name='TEMPORARY_DATA';

    SUM(BYTES)
    ----------
    104855552

    I have increased the initial and next extent to 1MB and also made the pctincrease to 0.


    Even then I am getting the error
    SQL> analyze table test compute statistics;
    analyze table test compute statistics
    *
    ERROR at line 1:
    ORA-01652: unable to extend temp segment by 512 in tablespace TEMPORARY_DATA.

    Thanks
    Anurag

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    ops sorry I forgot to add something in the query

    anyway query dba_free_space to find out the largest extent available in your tablespaces


    select tablespace_name, max(bytes) "largest free extent" from dba_free_space
    group by tablespace_name
    /

    may be you dont have enough free contiguos space for the tablespace temp to extend (it has enough free space but they are not contiguos)



  3. #13
    Join Date
    Sep 2000
    Posts
    362
    This is what I get when i run the query.

    SQL> select tablespace_name, max(bytes) "largest free extent" from dba_free_space
    2 group by tablespace_name
    3 /

    TABLESPACE_NAME largest free extent
    ------------------------------ -------------------
    REC_DATA 103821312
    ROLLBACK_DATA 3008512
    SYSTEM 1261568
    TEMP 1054720
    TEMPORARY_DATA 112195584
    USERS 10240
    USER_DATA 1392640


    Also please note that Now I have assigned the TEMP tablespace to the user and its largest free extent is 1054720.

    I am still getting the error.

    BTW whats the best way to size the temporary tablespace rather than to keep on increasing its size every now and then. There should be some way where I can predict the temporary tablespace size requirements by doing some calculations.

    Thanks
    Anurag

    Thanks
    Anurag

    [Edited by anuragmin on 05-30-2001 at 01:57 PM]

  4. #14
    Join Date
    Feb 2001
    Posts
    176

    Lightbulb

    Anurag,

    what version of oracle are you using. There is a bug in 8.1.5 that causes similar problems and has been fixed in 8.16 or 8.1.7

    If you are using 8.1.5, try setting your temporary tablespace to permanent and try your query.

    uday

    [Edited by uday on 05-30-2001 at 01:59 PM]

  5. #15
    Join Date
    Sep 2000
    Posts
    362
    Uday,
    I am using Oracle 8.0.5 for windows NT.

    Anurag

  6. #16
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    You know what, since you have pctincrease 50 previously and your inital and next were so small that your current NEXT might be huge! (actually I think this is what is happening)

    I dont know a way to deallocate the sort segments in temporary tablespace except

    restarting the database
    or
    recreate the temporary tablespace

    if itīs a critical database I would create a new temporary tablespace with pctincrease 0 and assign that new temp tablespace to all users then drop the old temporary tablespace



    hth

  7. #17
    Join Date
    Sep 2000
    Posts
    362
    I increased the datafile size of the temporary table space to 300 MB and set the maximum extents values for the temporary table space to unlimited and it worked.

    In the OEM it shows me that used is 261 MB.

    It needs this much of space only while querying one particular table which has millions of rows in it. This is the only table in that schema.

    Is it OK if I assign a specific temporary table space to it which no other user uses and define the storage parameters as Initial = 300MB so that it does not have to assign extents and will inturn improve performAnce.

    Thanks
    Anurag

  8. #18
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Always set INITIAL and NEXT parameters value for the temp tablespace to multiples of SORT_AREA_SIZE.

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