please help....

to force the SMON to clean the temp segments from dba_extents w/o restarting the instance

does that means before 8i , you always need to reboot your oracle instance to do this ?

and same will be applicable for oracle 8i if you are not using locally managed temp files.

thanks and regards

DBKID

Try the famous trick :

Change a storage parameter of the tablespace where these extents are sitting. E.g. if the PCT_INCREASE is 0, change it to 1 and immediately change it again back to 0.

This causes the SMON to wake up and do the job.

Quote:

---

Originally posted by dbkid
to force the SMON to clean the temp segments from dba_extents w/o restarting the instance

does that means before 8i , you always need to reboot your oracle instance to do this ?

and same will be applicable for oracle 8i if you are not using locally managed temp files.

thanks and regards

DBKID

---

well the problem (well not a problem really) is if your tablespace is temporary and NOT PERMANENT then SMON Does NOT need to free up extents because these extents are used over and over again, if your tablespace is permanent then you will hit perfomance problem because smon has to do extra work

Temporary tablespace can be created from Oracle 8 onwards and it is meant to improve perfomance by bypassing the extra jobs smon has to perform

Well , i thought again that why i wants it clear out. I was worried that if temp segment count in dba_extents again reaches to high value , which will slow down the whole system again.

but since i have increased sort_area_size , i don't see anybody using temp area for temp , so no more extent allocation also for temp.

and as you said , oracle instance is going to use the temp extents allocated again & again , i think i should be fine.

please correct me if i am wrong !

thanks and regards
DBKID

well as long as your tablespace is created using

create temporary tablespace..........

statement

created using create temporary tablespace command.
thanks and regards
DBKID