Problem with Temp Datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Problem with Temp Datafile

Hybrid View

  1. #1
    Join Date
    Oct 2001
    Posts
    127
    Hi ,
    The size of my temp datafile is 10352640kbytes .It is autoextensible.I am wondering how this has become this much.My understanding about the temp datafile is that it is temporary.After the usage the size should go down.What can i do to avoid such a large temp datafile.The pctincrease is 0 for this tablespace.
    Thanks

  2. #2
    Join Date
    Oct 2001
    Posts
    127
    Hi ,
    Please give some suggestions.
    Amit

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you have autoextend on and you have not set a maxsize then the file will extend as it needs

    where did you read the theory of temp datafile size goes down?

  4. #4
    Join Date
    Oct 2001
    Posts
    127
    Thanks for the reply,
    Then what is the difference between permanent and temporary tablespace .I read some where that after the completion of process tem segment should get freed.If it is not the case then temp tablespace will always grow.
    What should be done at this point ,to make it to the normalsize ,as 10gb temporary tablespace is too big.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    just set autoextend on and maxsize, when it reaches maxsize it wont be able to extend anymore (but you will get ORA_xxx if the transaction needds more temporary extents)

    The theory you read is from Oracle 7, from 8 temporary extents are not freed, they are used over and over again

  6. #6
    Join Date
    Oct 2001
    Posts
    127
    Thanks Pando,
    So now i can resize the temp datafile and from now onwards can i make it permanent with pctincrease as 1 so that SMON can take of it.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm smon doesnt have to do anything to temp tablespace, just set pctincrease to 0

    resize your file to an appropriate size
    set a reasonable maxsize for autoextend (for example if you resize to 4GB then probably a maxsize of 6GB is reasonable? depends on you)

    thatīs all folks!

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