Temporary tablespace filled up
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Temporary tablespace filled up

  1. #1
    Join Date
    Nov 2003
    Posts
    89

    Temporary tablespace filled up

    How can I make the temp tablespace free up.
    User ran a query last night and has taken 80 % of tablespace. but temp tablespace has not been freed up. If you restart the database the temp tablespace gets freed up, but is there any other way how a tablespace can free up.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Bet you're looking at the temp space through OEM aren't you.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Nov 2003
    Posts
    89
    Adding to above, if the user runs one more create table as selct query, it would take 60% more. User is going to get an Error when the temp tablespace is filled. I want to free up the temp tablespace. is there any way I can do that??

    When i issue below command I see free space 100%

    select SUBSTR(SYSDATE,12) "TIME",Current_Users, Total_Blocks, Used_Blocks, Free_Blocks,
    Round(100 * free_blocks / total_blocks,2) "% FREE"
    from V$SORT_SEGMENT;


    But if my freespace was 20% then what would be solution. I have my PCTINCREASE As 100 and initial and next 8mb.

  4. #4
    Join Date
    Nov 2003
    Posts
    89
    I am looking at temp space through TOAD.

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    create a new one and drop the old.

  6. #6
    Join Date
    Nov 2003
    Posts
    89
    create a new one and drop the old.

    That cannot be solution every time and I guess thats not correct practice.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    twmp will always show as full

    are you getting unable to extend errors?

  8. #8
    Join Date
    Nov 2003
    Posts
    89
    I am getting unable to extentd error
    ORA-1652: unable to extend temp segment by 1024 in tablespace TEMP

  9. #9
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    How big is tablespace TEMP? Can you increase it's size?

    Has the users CTAS sql got a cartesian join in it?

  10. #10
    Join Date
    Nov 2003
    Posts
    89
    Tablespace is 8g.

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