SQL QUERY TO FIND SORT AREA USAGE TEMPORARY TABLESPACE ORACLE -(ORA-1652)

Use below SQL query to identify the sql statements which currently perform sorting operation like group by or order by or hash operations in temporary tablespace

set lines 200 pages 1000
col USERNAME format a10
col TABLESPACE format a15
col SQL_TEXT format a20
col SID format 999999
col EVENT format a15
col PROGRAM format a15
col serial# format 99999
col used format 99999
col 
select su.username,
            su.sql_id,
            su.tablespace,
            sq.sql_text, 
            se.sid,
            se.program,
            se.serial#,
            se.event,
            (su.blocks)*(tb.block_size/1048576) usedMB
from v$sort_usage su
inner join v$sqlarea sq on su.sql_id=sq.sql_id
inner join v$session se on su.session_addr=se.saddr
inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name
order by su.username;

Below query can be used to find the count of sorts with respect to the process and session

set lines 200 pages 1000
col USERNAME format a10
col TABLESPACE format a10
col SQL_TEXT format a20
col SID format 999999
col EVENT format a15
col PROGRAM format a15
col serial# format 99999
col used format 99999
col spid format 999999
col sorts format 999
col SORT_CNT for 999
col USEDMB for 999999
select su.username,
       su.sql_id,
       su.tablespace,
       p.spid,
       count(*) sort_cnt,            
       se.sid,  
       se.program,
       se.serial#,
       se.event,
       sum(su.blocks)*(tb.block_size/1048576) usedMB
from v$sort_usage su
inner join v$session se on su.session_addr=se.saddr
inner join v$process p on p.addr=se.paddr
inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name
group by su.username,su.sql_id,su.tablespace,p.spid,se.sid,se.program,se.serial#,se.event,tb.block_size/1048576
order by su.username;

OUTPUT:

set lines 200 pages 1000
col SID format 999999
col EVENT format a15
col PROGRAM format a15
col serial# format 99999
col used format 99999
col spid format 999999
col sorts format 999
select su.username,su.sql_id,su.tablespace,p.spid,count(*) sorts,se.sid,se.program,se.serial#,se.event,sum(su.blocks)*(tb.block_size/1048576) usedMB
from v$sort_usage su
inner join v$session se on su.session_addr=se.saddr
inner join v$process p on p.addr=se.paddr
inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name
group by su.username,su.sql_id,su.tablespace,p.spid,se.sid,se.program,se.serial#,se.event,tb.block_size/1048576
order by su.username;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6    7

USERNAME   SQL_ID        TABLESPACE SPID                     SORTS     SID PROGRAM         SERIAL# EVENT                  USEDMB
---------- ------------- ---------- ------------------------ ----- ------- --------------- ------- --------------- ----------------------------------------
TEST       g4y6nw3tts7cc TEMP       17348                        1      55 sqlplus.exe          63 SQL*Net message    11
                                                                                                    from client

TEST1      g4y6nw3tts7cc TEMP       18513                        1      72 sqlplus.exe         143 SQL*Net message    28
                                                                                                    from client

OUTPUT:

kish@exdbx<>col USERNAME format a10
col TABLESPACE format a15
col SQL_TEXT format a20
col SID format 999999
col EVENT format a15
col PROGRAM format a15
col serial# format 99999
col used format 99999
select su.username,su.sql_id,su.tablespace,sq.sql_text,se.sid,se.program,se.serial#,se.event,(su.blocks)*(tb.block_size/1048576) usedMB
from v$sort_usage su
inner join v$sqlarea sq on su.sql_id=sq.sql_id
inner join v$session se on su.session_addr=se.saddr
inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name
order by su.username;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6

USERNAME   SQL_ID        TABLESPACE      SQL_TEXT                 SID PROGRAM         SERIAL# EVENT                  USEDMB
---------- ------------- --------------- -------------------- ------- --------------- ------- --------------- ----------------------------------------
TEST       g4y6nw3tts7cc TEMP            BEGIN DBMS_APPLICATI      55 sqlplus.exe          63 SQL*Net message            11
                                         ON_INFO.SET_MODULE(:                                  from client
                                         1,NULL); END;

TEST       g4y6nw3tts7cc TEMP            BEGIN DBMS_APPLICATI      66 sqlplus.exe         353 SQL*Net message            17
                                         ON_INFO.SET_MODULE(:                                  from client
                                         1,NULL); END;

TEST1      g4y6nw3tts7cc TEMP            BEGIN DBMS_APPLICATI      72 sqlplus.exe         143 SQL*Net message            28
                                         ON_INFO.SET_MODULE(:                                  from client
                                         1,NULL); END;