oracle表空间暴涨,【大话IT】temp表空间暴增的原因

第一個可以在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