Hi

Started With : SMON was taking 98% of cpu time. all the other query timing out.

Observation : tempdb grown to 25G single file

Suggestion from oracle support :

1. start oracle with event

event = "10061 trace name context forever, level 10"

which bypass the SMON cleaning. so cpu usage down to normal. but still becoz of big tempdb all other query taking long time.

2. asked support if we can simply drop TEMP tablespace. they said yes. and i started DROP TABLESPACE TEMP WITH content

and it is running for past 24 hours and still no luck. And oracle svrmgrl sucks :( i can't put it in background. so if somehow i loose connection ...:(

now i checked DBA_SEGMENT table if count(*) is dropping. it looks like this

SQL> SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE like 'TEMP%';

COUNT(*)
----------
288318

SQL> /

COUNT(*)
----------
287718

SQL>

3. i created another tablespace by TEMP_NEW and assigned that as TEMP for all users. while this DROP ..running nobody is connected to DB.

Questions : 1. is dropping count(*) sign is good. that it is doing something

2. is there is any quick way to drop the TEMP tablespace which is 25G :(

Any other suggestions are WELCOME.

I am modufying actual posting to say it is RUNNING FOR PAST 3 DAYS .
i need this DB up by tomorrow afternoon. i can't think of anything else

thanks and regards
DBKID.

[Edited by dbkid on 12-02-2001 at 09:07 PM]