随时监控临时表空间使用情况

因告警日志中出现ORA-1652错误，根据MOS上的建议，新增一个监控，随时记录临时表空间的使用情况。

export ORACLE_SID=xxx
sqlplus / as sysdba
alter session set container=xxx;
--为数据库临时表空间不够用的报错设置event
ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';
--以下为根据甲骨文工程师给的文档Doc ID 364417.1 增加一个随时监控临时表空间使用情况的脚本
--创建临时的表
CREATE TABLE PSTEMP_TEMP_SEG_USAGE(
DATE_TIME DATE,
USERNAME VARCHAR2(30),
SID VARCHAR2(6),
SERIAL# VARCHAR2(6),
OS_USER VARCHAR2(30),
SPACE_USED NUMBER,
SQL_TEXT VARCHAR2(1000));
--创建一个插入监控数据的procedure
CREATE OR REPLACE PROCEDURE PSTEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN
insert into PSTEMP_TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'PSTEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024*1024;
COMMIT;
END;
/
--创建调度任务，因遇周末，一小时一次。等周一再调整。
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MONITOR_PSTEMP_USAGE_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN PSTEMP_TEMP_SEG_USAGE_INSERT; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=60',
end_date => NULL,
enabled => TRUE,
comments => 'Job created to monitor pstemp usage.');
END;
/