-
Hi all!
Working on Oracle v8i with OS as win2k/NT!
Query:
My Temp Tablespace was initially set at 100m but after few days I noticed that the Temp datafile size has suddenly increased to 2.15GB!!!Surprising!
Scenario:We are running lots of Reports & Forms v6i Oracle D2k on Oracle v8i.We have around 15 schemas with 1000+ tables,DB size is around 3GB and few tables have thousands of lakhs of records!
My init.ora settings are as follows:
1.sort_area_size=84k.
2.SORT_AREA_RETAINED_SIZE=84K.
3.SHARED_POOL_SIZE=26MB.
4.LARGE_POOL_SIZE=20MB.
5.JAVA_POOL_SIZE=20MB(default).
This problem of sudden rise in temporary Tablespace is becoming frequent and we dont like it.
My users queries takes lot of time even for 1 rows in a table say for any DML operations like DELETE,INSERT,UPDATE is taking some time.
Reports takes few minutes to retrive the output.Sometimes hangs in the middle.Same is applicable for forms!
What is the best solution to avoid this problem so that my temporary datafile should be using space management optimally and should not jump from 100m to 2GB to 3GB suddenly!
At present my Temporary tablespace has only one datafile.
Thanks & Regards,
Amit.
-
Set auto extend off for all of your temporary tablespace datafiles. And use fixed but large maxextent value. And please provide few more details like total memory of server, OS etc.. It looks like you are having problems with paging.. Reduce your sort area size and set your sort_area_retained_size as "0" . Please get back with more details...
