Wednesday, December 15, 2010

Drop TEMP tablespace and waiting

After I could not resize tempfile.
ORA-03297: file contains used data beyond requested RESIZE value
So, I recreated TEMPORARY Tablespace, In step, "drop old temp tablespace"... I found waiting and blocking.
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
--waiting--
On EM:
My session ID = 203
Grid Control showed my session was blocked by "SMON"... I had to find who using TEMP segment on "TEMP" tablespace.
Idea to check in GV$TEMPSEG_USAGE
SQL> select inst_id, sid,serial#, machine, program from gv$session where (inst_id, username, saddr) in (select inst_id, username, session_addr from GV$TEMPSEG_USAGE where tablespace='TEMP');

INST_ID SID SERIAL# MACHINE PROGRAM
---------- ---------- ---------- -------------------- ------------------------------------------------
2 222 6792 host02 emagent@host02 (TNS V1-V3)
1 250 58534 host01 emagent@host01 (TNS V1-V3)
My issue, Grid Control agent was using TEMP tablespace. I stopped/started agent and then I could drop it.
However, If we drop "TEMPORARY Tablespace" and waiting, we should check who using temp segment on that tablespace and fix it(Example: kill sessions or stop clients or...)

No comments: