删除临时表空间，Oracle数据库hangs问题解决

来源：互联网发布：阿里云邮箱 ios imap 编辑：程序博客网时间：2024/05/16 01:08

查看临时表空间的使用情况：

SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

找出是哪个会话：

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

会话使用的表空间：

select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks

from v$session a, v$tempseg_usage b

where a.saddr = b.session_addr

order by b.tablespace, b.blocks;

sys@SMS> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=63576;

SID SERIAL# STATUS
---------- ---------- --------
14 63576 ACTIVE

sys@SMS> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=63576;

SID SERIAL# STATUS
---------- ---------- --------
14 63576 ACTIVE

sys@SMS> select inst_id,sid,serial#, event,p1,p2,p3,program ,status from gv$session where sid=14;

   INST_ID        SID    SERIAL# EVENT                                                              P1          P2
---------- ---------- ---------- ---------------------------------------------------------------- ---------- ----------
        P3 PROGRAM                                          STATUS
---------- ------------------------------------------------ --------
         1         14      63576 TCP Socket (KGAS)                                                   6           0
         0 oracle@wapDB (J000)                              ACTIVE

查明是由于自动运行的job引起的问题

等待时间：

sys@SMS> SELECT sid, total_waits, time_waited
2      FROM v$session_event
3     WHERE event='TCP Socket (KGAS)'
4       and total_waits>0
5     ORDER BY 3,2
6    ;

SID TOTAL_WAITS TIME_WAITED
---------- ----------- -----------
14 641830 1925567343

查看自动运行任务的现状：

select job_name,job_type,job_action,enabled,state from user_scheduler_jobs;

exec dbms_scheduler.stop_job('MONITOR_ALERT_LOG');

exec dbms_scheduler.disable('MONITOR_ALERT_LOG');

必要时可以强制停止正在运行的job

强行停止正在运行的job，需要加FORCE=>TRUE条件，默认为FALSE。否则会报错，错误如下：

exec dbms_scheduler.stop_job(JOB_NAME=>'REPORT.MMS_HOUR_UP_REPORT',FORCE=>TRUE);

exec dbms_scheduler.stop_job(
     JOB_NAME=>'REPORT.MMS_HOUR_UP_REPORT',
     FORCE=>TRUE
    );

ERROR at line 1:
ORA-27365: job has been notified to stop, but failed to do so immediately
ORA-06512: at "SYS.DBMS_ISCHED", line 164
ORA-06512: at "SYS.DBMS_SCHEDULER", line 483
ORA-06512: at line 1

然后再次检查一遍，确认后删除临时表空间：

drop tablespace temp including contents and datafiles