Monitor TEMP Tablespace with a DBMS_SCHEDULER job

I was faced with a situation in which a process (Oracle pl/sql procedure within a package, scheduled outside Oracle in the evenings) was exhausting the TEMP tablespace. In order to investigate, the following procedure was used to monitor TEMP tablespace usage during the period of time the job was executed. This allowed quick identification of the offending user and SQL that was causing TEMP tablesace exhaustion.

Code is quick and dirty, no exception handling. I’m assuming the executing user has all the required privileges. There are other ways to investigate this, but this works.

Create a table to store data

CREATE TABLE TEMPDATA
(
  CTIME       TIMESTAMP(6) WITH TIME ZONE,
  TABLESPACE  VARCHAR2(30),
  TEMP_SIZE   VARCHAR2(41),
  INSTANCE    NUMBER,
  SID_SERIAL  VARCHAR2(81),
  USERNAME    VARCHAR2(30),
  PROGRAM     VARCHAR2(48),
  STATUS      VARCHAR2(8),
  SQL_ID      VARCHAR2(13)
)

Create the procedure

CREATE OR REPLACE procedure colltempdata as
begin

begin
    insert into tempdata
    SELECT systimestamp ctime,
           b.tablespace,
           ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
           a.inst_id as Instance,
           a.sid||','||a.serial# AS sid_serial,
           NVL(a.username, '(oracle)') AS username,
           a.program,
           a.status,
           a.sql_id
    FROM   sys.gv$session a,
           sys.gv$sort_usage b,
           sys.gv$parameter p
    WHERE  p.name  = 'db_block_size'
    AND    a.saddr = b.session_addr
    AND    a.inst_id=b.inst_id
    AND    a.inst_id=p.inst_id;
end;

end;
/

Create a job to execute the procedure

In this example, execute every minute for a specfied time period

begin
    DBMS_SCHEDULER.CREATE_JOB (
         job_name             => 'colltempdatajob6',
         job_type             => 'PLSQL_BLOCK',
         job_action           => 'begin colltempdata;commit;end;',
         start_date           => timestamp '2018-07-04 21:30:00',
         repeat_interval      => 'FREQ=MINUTELY;INTERVAL=1;',
         end_date             => timestamp '2018-07-04 22:45:00',
         enabled              => TRUE);
end;

SQL to display jobs, check size of data, and query the collected data

-- display job details
select job_name, start_date, run_count, last_start_date, next_run_date  from dba_scheduler_jobs where job_name like 'COLLTEMPDATAJOB%' order by start_date

-- check size of data
select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'TEMPDATA'

-- Query data collected
select * from tempdata order by ctime desc