1.在删除TEMP表空间前,已经增加了另外的表空间,并调整了系统的默认临时表空间,以及所有USER的默认临时表空间,这个是确定的,否则,直接删除TEMP表空间会报错。
2.查看当前系统在等待什么
SQL> select sid,event,blocking_session from v$session where wait_class<>'Idle';
SID EVENT BLOCKING_SESSION
---------- ---------------------------------------------------------------- ----------------
69 enq: TS - contention 1345
1096 gc cr request
2531 SQL*Net message to client
3685 LNS wait on SENDREQ
4296 SQL*Net more data from client
4326 SQL*Net more data from client
6 rows selected.
SQL>
3.看看1345是什么
SQL> select sid,event,program from v$session where sid=1345;
这个看到了,ORA-00376,无法读取文件,关于这个问题,应该是这个临时文件有问题,做了删除alter tablespace temp drop tempfile 3,命令成功了,但一直数据字典中还存在。
看来这个问题必须处理。
4.搜了一下MOS,发现还真有类似的问题,具体可以参见 DDL Operations (Alter and Drop) on Temporary Tablespace Hang (Doc ID 1911167.1)
但有两种情况:
一种是BUG: 1- Bug 13028161: DROP TEMP TABLESPACE HANGS DUE TO TS ENQUEUE CONTENTION which was closed as duplicate of unpublished Bug 15913577 : MAIN_LINUX.X64 LRG9H8 RUNS WILL BE SUSPENDED 12/02/12; TOO MANY HANGS
Symptoms:
- Known to hit 11.2.0.3 but could hit any version lower that 12.1.0.2 and 12.2
- Tracing the session using 10046 shows that the session is waiting for event "enq: TS - contention'"
- System state dump shows that the session is blocked by SMON
- SMON is waiting for event "SMON TIMER"
一看,和我这里真对得上,有两个可以通过补丁 P15913577 解决,看了一下,158K,比较小,这个补丁是一个在线补丁,但考虑到现在是生产,不太合适.
另一个是:被删除的临时表空间被死连接占用:Drop temporary tablespace hangs due to dead connections leaving entries in v$session (status inactive) and in v$tempseg_usage.
- There exist few entries in the V$TEMPSEG_USAGE (formerly v$sort_usage) for this tablespace
- 10046 trace shows that the session is waiting for "Enqueue", something like:
Drop tablespace will wait on the TS enqueue in mode 6:
WAIT #1: nam='enqueue' ela= 0 p1=1414725638 p2=3 p3=1
TS "Temporary Segment (also TableSpace)"
解决办法:
-Change temporary tablespace of all the users to different temporary tablespace (If not yet done)
-Find out the all the sessions that are not active and have an entry in V$sort_usage:
SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et
FROM v$session s, v$tempseg_usage t
WHERE s.saddr = t.session_addr;
-Kill those sessions:
alter system kill session '< sid >, < Serial# >';
( Make sure that you are killing only those sessions that are not being used actually but have entries in v$session -- Use LAST_CALL_ET in last query)
-Drop the tablespace
查了一下:
SQL> r
1 SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et,tablespace,logon_time
2 FROM v$session s, v$tempseg_usage t
3 WHERE s.saddr = t.session_addr
4 and tablespace='TEMP'
5* order by 2,3
SQL> SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et,tablespace,logon_time
2 FROM v$session s, v$tempseg_usage t
3 WHERE s.saddr = t.session_addr
4 and tablespace='TEMP'
5 order by 2,3;
--修改默认临时表空间到TEMP
SQL> alter database default temporary tablespace temp;
SQL> select name,value$ from props$ where name like '%DEFAULT_TEMP%';
NAME VALUE$
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL> select 'alter user ' || username || ' temporary tablespace temp_im;' from dba_users where temporary_tablespace='TEMP_IM';
no rows selected
看来没有了,说明以前创建用户,没有明确指定默认临时表空间
再次查看有链接在使用需要删除的临时表空间,手工删除,或者等待他们自动断开
SQL> SELECT s.inst_id,s.username, sid, serial#, contents, sql_address , extents,last_call_et,tablespace,
2 to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') as logdate
3 FROM gv$session s, gv$tempseg_usage t
4 WHERE s.saddr = t.session_addr and tablespace='TEMP_IM'
5 and s.inst_id=t.inst_id
6 order by s.inst_id,logdate;
24 rows selected.
等处理后,删除即可
DROP TABLESPACE TEMP_IM ;
4.4.处理DG备库TEMP 表空间没有临时文件的问题
--dg
。。。
Tue Oct 24 09:04:20 2023
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_ora_52297.trc:
ORA-25153: Temporary Tablespace is Empty
。。。
--确认一下:
SYS@orclDG> select name from v$tempfile;
no rows selected
SYS@orclDG>
SYS@orclDG> alter tablespace temp add tempfile size 30g;
SYS@orclDG> alter tablespace temp_im add tempfile size 30g;
SYS@orcl> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/hisdg/oradata/ORCLDG/datafile/o1_mf_temp_lmgdc4dj_.tmp
/hisdg/oradata/ORCLDG/datafile/o1_mf_temp_im_lmgdcp6l_.tmp