Identify SQL statements consuming temporary tablespace

You may have to find out the SQL statements which are using temporary tablespace when you get storage pressure for temporary tablespace.

The SQL statements you are interested may be found out with one of following ways,

1. SQL statements currently consuming temporary tablespace

Following SQL command can find out currently running SQL statements which are consuming temporary tablespace,

select t.username,t.sql_id,t.segtype,t.blocks,t.tablespace,s.sql_fulltext
  from v$tempseg_usage t, v$sqlarea s
 where t.sql_id=s.sql_id
 order by t.blocks;

Sample output

USERNAME   SQL_ID         SEGTYPE       BLOCKS TABLESPACE  SQL_FULLTEXT
--------   -------------  ---------  --------- ----------- --------------------------------------------------------------
USER01     83cnu7akfzpsa  LOB_INDEX     425984 TEMP        select 'jsonVdsCurrentFeedSNCallback({"i":"'||to_unix_date_str
                                                           (max(last_updated))||'"});' from business_station_status
USER01     1f3jj3y97ud8h  LOB_DATA    15759360 TEMP        insert into USER03.business_station_load (detector_id,last_upd
                                                           ated,number_of_lanes,latitude,longitude) values(trim(' dw9995d
                                                           el'),to_timestamp_tz('1970-01-01 00:00:00 00:00','yyyy-mm-dd h
                                                           h24:mi:ss tzh:tzm'),3, 43.641342, -79.379648)
USER01     83cnu7akfzpsa  LOB_DATA    15761152 TEMP        select 'jsonVdsCurrentFeedSNCallback({"i":"'||to_unix_date_str
                                                           (max(last_updated))||'"});' from business_station_status

2. SQL statement getting ORA-1652

When ORA-01652 happens, error message will be shown in alert log,

2021-11-17 15:48:16.571000 -05:00
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [porcl]

The message shows that tablespace TEMP is used up in pdb "porcl" at 3:28pm Nov 17th, 2021, but it does not show which SQL statement got the errors. By default, Oracle does not log the SQL which gets ORA-01652.

In order to trace the SQL, following SQL command can be run by SYS

alter system set events '1652 trace name errorstack';

It enables the trace for error ORA-1652. Oracle will create trace file and record file name in alert log

2021-11-29 20:17:12.417000 -05:00
OS Pid: 19721 executed alter system set events '1652 trace name errorstack'
2021-11-29 20:20:20.629000 -05:00
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [porcl]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_21725.trc

In the trace file 'orcl_ora_21725.trc', you will find the sql which got ORA-1652

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffd907494f0 placed dbkda.c@298
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
<error barrier> at 0x7ffd9074ced8 placed ktrs.c@969
----- Current SQL Statement for this session (sql_id=g7rhwsjkmjh7x) -----
select t1.*,t2.* from tb01 t1, tb02 t2 order by t1.object_name,t2.object_name

Note: Remember to turn off the trace by running following sql as sys when the trace is not needed

alter system set events '1652 trace name context off';

Is the captured SQL exhausting the temporary tablespace? The answer probably is no, it could just get bad luck to request temporary space while the temparory tabelspace is used up by other SQL statements.

3. SQL statements consuming temporary tablespace when ORA-1652

In order to capture all temporary space exhausting SQL statements while ORA-1652 happens, an AFTER SERVERERROR ON DATABASE trigger can be created by user SYS as following,

create table tb_sql_statements
  as select sysdate as snap_time, t.username, t.sql_id, t.segtype,
            t.blocks, t.tablespace, s.sql_fulltext
       from v$tempseg_usage t, v$sqlarea s
      where t.sql_id=s.sql_id and 1=2;

create or replace trigger dump_temp_exhausting_sql
 after servererror on database
 when ( ora_server_error(1) = 1652 )  -- Only triggered when ORA-1652
begin
  insert into tb_sql_statements
  select sysdate as snap_time, t.username, t.sql_id, t.segtype,
         t.blocks, t.tablespace, s.sql_fulltext
    from v$tempseg_usage t, v$sqlarea s
   where t.sql_id=s.sql_id;
exception
  when others then
    null;
end;
/

Line 1-4 creates an empty table "tb_sql_statements", which will be used to save SQL statements consuming temporary tablspace.

Line 6-18 creates an AFTER SERVERERROR ON DATABASE trigger "dump_temp_exhausting_sql", the trigger will be triggered when ORA-1652 happens. The trigger save all SQL statements consuming temporary space into table "tb_sql_statements".