DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: unable tpo extend temp segment

  1. #1
    Join Date
    Oct 2000
    Posts
    57


    I have three temporaray tablespaces with 10 GB each .
    Recently , When we run some query we are getting unable to
    extent temp segment (ORA 1652 ) . We were running same query for last three months without any problems.

    These queries used to take 20 minutes, and they are now taking 90 to 180 minutes to complete.

    However, what I don't understand is how a query on 300Mb of data, which is summarizing statistcs on 6000-10000 distinct groups and producing a 20 Mb summary can use over 10GB of temporary table space.

    Could anyone have any idea ?


  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    Check to see if Oracle is performing sorts in the ts; these can be cleaned out by bouncing the db. Consider setting a larger extent size in the temp ts (if small), and turn on Autoextend on the ts.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What is the version of Oracle?

    Of what type is this particular temp tablespace - TEMPORARY or PERMANENT? What are tablespace's storage parameters INITIAL, NEXT, PCTINCREASE? If it is of tipe permanent, are you sure you have no permanent segments (like tables, indexes) accidentally inthere? If PCTINCREASE is not 0, are there many free_space chuncks of different sizes there?

    ORA 1652 also reports the number of blocks it was trying to grab for the new extent - what was the number reported (specify also your db_block_size).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2000
    Location
    Santa Clara, CA, USA
    Posts
    12

    Cool

    check if the TEMP tablespace is assigned for this user, otherwise will use SYSTEM tablespace which could be the cause for this problem.

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Check the pctincrease parameter,contents for temporary tablespace. Make sure contents are TEMPORARY and PCTINCREASE not set to zero, allowing SMON to coalesce free space on thats left on TEMP.


    [Edited by sreddy on 01-08-2001 at 06:18 PM]

  6. #6
    Join Date
    Jun 2000
    Location
    Santa Clara, CA, USA
    Posts
    12

    Cool

    check the next_extent from dba_segments view as well if that is very high , you might get the error you wrote.

  7. #7
    Join Date
    Nov 2000
    Posts
    71
    Look for coalesced percent space or your temporary tablespace.
    1 - You should haveto coalesced them;
    2 - However your temporary tablespaces are bad configured; reanalize their storage parameter according to the tablespace dimension and the kind of queries you use to work
    3 - To use yuor db for the moment shoutdown it and than startup it again. This cause all temporary segments to be released.
    Bye

  8. #8
    Join Date
    Oct 2000
    Posts
    57

    Thanks for the ans's

    It's a 8.1.5 db and
    temporary tablespace is 10 GB with
    extent management local uniform size 25M.

    pctincrease = 0
    I could find contents as temporary in dba_tablespaces.

    thanks




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