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.
Please give some suggestions.
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?
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.
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
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.
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!