Dev's Weblog

Reading time: 2 - 2 minutes

EXAMPLE of how to monitor Temporary Segment Usage over time

1) Login as SYS AS SYSDBA

AAA 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.A

Also monitor space usage of the table as it couldA grow very fast depending on job interval; delete rows or truncate table as appropriate.

SQL> select * from temp_temp_seg_usage;
DATE_TIME USERNAMEAAAAAAAAAAAA SIDAAA SERIAL
--------- --------------------- ----- ----
OS_USERAAAAAAAAAAAAAAAAAAAAAAA SPACE_USED
------------------------------ ----------
SQL_TEXT
-----------------------------------------
29-JUN-07 SYSAAAAAAAAAAAAAAA 158AAA 13
sygaw-ca\sygawAAAAAAAAAAAAAAAA 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_NAMEAAAAAAA TABLESPACE_NAME USEDMB
------------AAAAAAA ----------AAAAA ------
TEMP_TEMP_SEG_USAGE SYSTEMAAAAAAAAA .0625


SQL> truncate table temp_temp_seg_usage;
Table truncated.