Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    49

    Unanswered: TEMP tablespace growing without control

    Hi all,
    i have migrated an oracle istance from 8.1.6 to 9.2. Everything seems to work normally but i have a problem with the TEMP tablespace. some application are working on data with sort operations and the TEMP tablespace keep on growing without releasing any space. the thing is that the automatically extend of my TEMP tablespace make my file system almost full after some time.
    tha same application, working with the older 8.1.6, didn't show this strange behaviour (the old TEMP tablespace was 1 GB and it was more than enough). right now it grows over 2 GB...
    the only solution i have is to create a new TEMP tablespace and remove the growing one every two/three days...

    is this a problem related to the application or to my database?
    how can i solve it?

    thank you very much

    matteo

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I have never, in all the systems I have worked with, had temp tablespace with autoextent on. I just set it at a size, and let it like that. There's no problem with TEMP to be apparently 'full' -- it is just the way it is on a healthy system.

    I suggest you (if can): uncheck autoextent for TEMP, shut down the db to release the occuped TEMP space, set it at your desired size and then put the db back online.

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    TEMP never releases space unless it has to; if autoextend is enabled then TEMP will just keep on autoextending. Disable autoextend & you will see TEMP getting fuller & fuller, until it's just about full & then it appears to stop filling up. This is because TEMP uses all available free space, and then as & when more free space is needed TEMP just overwrites old space (and keeps on overwriting old space).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •