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.<\/p>\n
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.<\/p>\n
\r\nCREATE TABLE TEMPDATA\r\n(\r\n CTIME TIMESTAMP(6) WITH TIME ZONE,\r\n TABLESPACE VARCHAR2(30),\r\n TEMP_SIZE VARCHAR2(41),\r\n INSTANCE NUMBER,\r\n SID_SERIAL VARCHAR2(81),\r\n USERNAME VARCHAR2(30),\r\n PROGRAM VARCHAR2(48),\r\n STATUS VARCHAR2(8),\r\n SQL_ID VARCHAR2(13)\r\n)\r\n<\/pre>\nCreate the procedure<\/h3>\n
\r\nCREATE OR REPLACE procedure colltempdata as\r\nbegin\r\n\r\nbegin\r\n insert into tempdata\r\n SELECT systimestamp ctime,\r\n b.tablespace,\r\n ROUND(((b.blocks*p.value)\/1024\/1024),2)||'M' AS temp_size,\r\n a.inst_id as Instance,\r\n a.sid||','||a.serial# AS sid_serial,\r\n NVL(a.username, '(oracle)') AS username,\r\n a.program,\r\n a.status,\r\n a.sql_id\r\n FROM sys.gv$session a,\r\n sys.gv$sort_usage b,\r\n sys.gv$parameter p\r\n WHERE p.name = 'db_block_size'\r\n AND a.saddr = b.session_addr\r\n AND a.inst_id=b.inst_id\r\n AND a.inst_id=p.inst_id;\r\nend;\r\n\r\nend;\r\n\/\r\n<\/pre>\nCreate a job to execute the procedure<\/h3>\n
In this example, execute every minute for a specfied time period<\/p>\n
\r\nbegin\r\n DBMS_SCHEDULER.CREATE_JOB (\r\n job_name => 'colltempdatajob6',\r\n job_type => 'PLSQL_BLOCK',\r\n job_action => 'begin colltempdata;commit;end;',\r\n start_date => timestamp '2018-07-04 21:30:00',\r\n repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;',\r\n end_date => timestamp '2018-07-04 22:45:00',\r\n enabled => TRUE);\r\nend;\r\n<\/pre>\nSQL to display jobs, check size of data, and query the collected data<\/h3>\n
\r\n-- display job details\r\nselect 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\r\n\r\n-- check size of data\r\nselect sum(bytes)\/1024\/1024\/1024 from dba_segments where segment_name = 'TEMPDATA'\r\n\r\n-- Query data collected\r\nselect * from tempdata order by ctime desc\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 … Continue reading