第一個可以在3秒內反應就用不能請使用第二個SQL看看 TEMPORARY TABLESPACE 被什麼使用掉啦.
第一個SQL:
SELECT NVL (SE.USERNAME, '(ORALCE)') AS USERNAME,
'alter system kill session '''
|| SE.SID
|| ''
|| ','
|| SE.SERIAL#
|| ',@'
|| SE.INST_ID
|| ''' IMMEDIATE;'
AS RUN_DATA,
SE.PREV_EXEC_START,
SYSDATE AS NOW_DATE,
(SYSDATE - SE.PREV_EXEC_START) * 24 * 60 * 60 AS RUN_SEC,
(SYSDATE - SE.PREV_EXEC_START) * 24 AS RUN_TIME,
C.SQL_FULLTEXT,
C.CHILD_NUMBER,
C.SQL_ID,
SE.SID,
SE.INST_ID,
SE.SERIAL#,
SE.SQL_ADDRESS,
SE.MACHINE,
SE.PROGRAM,
SU.TABLESPACE,
SU.SEGTYPE,
SU.CONTENTS,
SU.BLOCKS * 8 / 1024 || 'MB' AS USED_SPACE
FROM GV$SESSION SE, GV$SORT_USAGE SU, gv$sql C
WHERE SE.SADDR = SU.SESSION_ADDR
AND SE.INST_ID = SU.INST_ID
AND SE.sql_address = C.address(+)
AND SE.INST_ID = C.INST_ID(+)
ORDER BY SE.sid,
C.SQL_ID,
C.CHILD_NUMBER,
SE.serial#,
SE.SQL_ADDRESS;
或
第二個SQL:
SELECT NVL (SE.USERNAME, '(ORALCE)') AS USERNAME,
'alter system kill session '''
|| SE.SID
|| ''
|| ','
|| SE.SERIAL#
|| ',@'
|| SE.INST_ID
|| ''' IMMEDIATE;'
AS RUN_DATA,
SE.PREV_EXEC_START,
SYSDATE AS NOW_DATE,
(SYSDATE - SE.PREV_EXEC_START) * 24 * 60 * 60 AS RUN_SEC,
(SYSDATE - SE.PREV_EXEC_START) * 24 AS RUN_TIME,
SE.SQL_ADDRESS, -- key1
SE.SQL_HASH_VALUE, -- key2
SE.INST_ID, -- key3
SE.SID,
SE.SERIAL#,
SE.MACHINE,
SE.PROGRAM,
SU.TABLESPACE,
SU.SEGTYPE,
SU.CONTENTS,
SU.BLOCKS * 8 / 1024 || 'MB' AS USED_SPACE
FROM GV$SESSION SE, GV$SORT_USAGE SU
WHERE SE.SADDR = SU.SESSION_ADDR
AND SE.INST_ID = SU.INST_ID
ORDER BY SE.sid,
SE.serial#,
SE.SQL_ADDRESS;
SELECT *
FROM gv$sql C
WHERE C.ADDRESS = '00000076072151B0' -- SE.SQL_ADDRESS
and C.HASH_VALUE = '2424365054' -- SE.SQL_HASH_VALUE
AND c.INST_ID = 1 -- SE.INST_ID