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;