Well smon shouldnt free temp extents in a temporary tablespace so may I ask if your tablespaces status is temporary or permanent? If it's permanenet change it to temporary and you should see a boost in the perfomance database wise
Regarding loose connection with svrmgrl, write a shell script then you can either put it in crontab or do
this way even you lose connection the process will keep on running
so DROP TABLESPACE was running for past 3 days. and still hasn't finished completly.
vette86 you said , you had similar problem. do you remember what kind of machine you had. mine is sun c cpu 333 MHz machine.
i had to kill the drop tablespace and let people use the machine.
i am not sure whether i should start drop tablespace thing again or not becoz it chews up 98% of 1 cpu time. but i have to do it at some time.
any advices / ideas are welcome. can somebody also explores the locally managed temp files idea given by oracle support. though i am reading the documentation for it. but your experience will make great difference.
I have an HP9000 L2000 server with dual 440 processors.
The database is the only thing running on this machine.
I do not remember the drop taking that much processor to
complete, but it has been a little while.
The tablespace scheme that I am using on the new temp
tablespace is as follows:
initial extent 512K
For each datafile use autoextend with 1024K as increment
and max file size set at 500MB. I currently have 6 datafiles
for this tablespace. If you limit the filesize to 500MB, you
won't have problems moving/copying the file if needed.
You should also check your sort_area_size parameter.
If it is set too low, you will start sorting in the temp tablespace (thus the file getting large).
I have 2GB Ram on that machine and set the sort_area_size
to 4MB. Have only really hit the temp tablespace once.
An application programmer at the company ran a query
that was looking (sorting) nearly 20Million records.
so finally my drop tablespace finished after 8 days and 3 hours. sometime oracle sucks :( ( please do not mind )
so i have question for you guys again :
1. as per my knowledege SMON awakes every 3 min and cleans the free extents from dba_extents and puts in dba_free_space.
there is nobody connected to database and still i see this quesry returning same rows
select count(*) from dba_extents where segment_type = 'TEMPORARY'
don't you think it should clear it.
i am running oracle 8.1.7 db.
FYI : Stats on my DROP TABLESPACE
- initial count on dba_extents for TEMP segment 378,560
- machine sun U2
- how long it took to drop temp tablespace - 8days + 3 hours
- it initially cleared everything from dba_extents and put it in dba_free_space and then it cleared dba_free_space
Now i am using locally managed temp file which is cool