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

数据库hangs问题解决"/>

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

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

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