今天碰到个问题,系统的临时表空间不断报错,说无法扩展。
ORA-1652: unable to extend temp segment by 128 in tablespace DMPTEMP
我们这个系统的临时表空间应该足够,但不知道为什么会总是报无法扩展,于是查了查资料。
查看当前占用临时表空间的语句:
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
下面就是AWR中在出问题的时间点运行的sql
SELECT DISTINCT TO_CHAR(SUBSTR(b.sql_text,1,4000))
FROM sys.WRH$_SQLTEXT b
WHERE b.sql_id IN
(SELECT sql_id
FROM
(SELECT a.sql_id
FROM sys.WRH$_SQLSTAT a
WHERE a.parsing_schema_name NOT IN ('SYS')
AND a.executions_total >0
AND a.direct_writes_total >0
AND a.SNAP_ID IN
(SELECT SNAP_ID
FROM sys.WRM$_SNAPSHOT
WHERE to_date('2008:08:20 17:20:08','yyyy:mm:dd hh24:mi:ss') BETWEEN begin_interval_time AND end_interval_time
)
ORDER BY a.direct_writes_total/ a.executions_total DESC
)
WHERE rownum<=10
);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/41451/viewspace-1035457/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/41451/viewspace-1035457/