Monitor Temp Usage

REFER below MOS Document..

How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

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$sort_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*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024;
COMMIT;
END;
/

SELECT JOB FROM DBA_JOBS;

JOB
----------
19

BEGIN
DBMS_JOB.ISUBMIT(JOB => 20,
WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

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

select * from <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
from dba_segments
where segment_name='<temporary tablespace name to monitor>_TEMP_SEG_USAGE;';

truncate table <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

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


select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
from dba_segments
where segment_name='TEMP_TEMP_SEG_USAGE';

SEGMENT_NAME
------------------------------------------
TABLESPACE_NAME USEDMB
------------------------------ ----------
TEMP_TEMP_SEG_USAGE
SYSTEM        .0625

truncate table temp_temp_seg_usage;

Table truncated.