EXAMPLE of how to monitor Temporary Segment Usage over time
1) Login as SYS AS SYSDBA
NOTE: If SYS is unacceptable then use a schema that has access of each of the DBA views below
2) Create a table to hold your temporary space monitoring
CREATE TABLE TEMP_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));
3) Create a job in the job queue to insert into your monitoring table
For 9.2 and above:
============
CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN
insert into TEMP_TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*8192 > 1024;
COMMIT;
END;
/
SQL> SELECT JOB FROM DBA_JOBS;
JOB
----------
20
BEGIN
DBMS_JOB.ISUBMIT(JOB => 21,
WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/
4) Periodically query your monitoring table <temporary tablespace name to monitor>_TEMP_SEG_USAGE.
Also monitor space usage of the table as it could grow very fast depending on job interval; delete rows or truncate table as appropriate.
SQL> select * from temp_temp_seg_usage;
DATE_TIME USERNAME SID SERIAL
--------- --------------------- ----- ----
OS_USER SPACE_USED
------------------------------ ----------
SQL_TEXT
-----------------------------------------
29-JUN-07 SYS 158 13
sygaw-ca\sygaw 768
select * from dba_objects order by object_id, object_name
SQL> select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
2 from dba_segments
3 where segment_name='TEMP_TEMP_SEG_USAGE';
SEGMENT_NAME TABLESPACE_NAME USEDMB
------------ ---------- ------
TEMP_TEMP_SEG_USAGE SYSTEM .0625
SQL> truncate table temp_temp_seg_usage;
Table truncated.
Popular Posts