Search This Blog

Total Pageviews

Wednesday 26 September 2012

Oracle Temp tablespace usage info


Oracle Temp
Oracle Temp space information Oracle Space usage 
set linesize 200 pagesize 200
col description for a50
col property_value for a20
col property_name for a30
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';


PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace



select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB' Total from dba_temp_files 
where tablespace_name=(select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE')


set linesize 200 pagesize 200
select TABLESPACE_NAME,trunc(TABLESPACE_SIZE/1024/1024/1024,2) TABLESPACE_SIZE_GB, trunc(ALLOCATED_SPACE/1024/1024/1024,2) ALLOCATED_SPACE_GB, trunc(FREE_SPACE/1024/1024/1024,2 ) FREE_SPACE_GB,
(ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)>85.00) then '---(85.00)% full :-( ***'
else 'Good :-)'
end as ATTENTION
from dba_temp_free_space;


set linesize 500 pagesize 500
col username for a20
col sql_text for a50
col OSUSER for a15
col PROGRAM for a20
col kill for a18
SELECT distinct
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,
a.con_id,
          a.username  username ,
           a.osuser,
           a.program,
           a.sql_id,
           a.sql_child_number,
           a.sql_exec_start,
           q.plan_hash_value,
           b.tablespace,
           b.segtype,
           sum(b.blocks) as BLOCKS,
           substr(q.sql_text,1,50) sql_text
      FROM gv$session a, gv$tempseg_usage b, gv$sql q
     WHERE a.saddr = b.session_addr
       and q.address = a.sql_address
       and q.inst_id = a.inst_id
       and q.hash_value = a.sql_hash_value
       and q.CHILD_NUMBER = a.SQL_CHILD_NUMBER
       and a.INST_ID = b.INST_ID
     GROUP by GROUPING SETS((''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' , a.con_id,a.username, a.osuser, a.program, a.sql_id, a.sql_child_number, a.sql_exec_start, q.sql_text, q.plan_hash_value, b.tablespace, b.segtype, sysdate),(sysdate));
  




-- >11g for con_id 
set linesize 300 numf 9999.99
col CREATION_TIME for a27
col NAME for a70
col tb_name for a15
select a.con_id,b.name tb_name, a.name, a.bytes/1024/1024/1024 Gb ,CREATE_BYTES/1024/1024/1024 CREATED_SIZE,to_char(CREATION_TIME,'dd-mm-yyyy hh24:mi') CREATION_TIME ,STATUS,ENABLED
from v$tempfile a, v$tablespace b 
where a.ts#=b.ts#
and  a.con_id= b.con_id
and  a.con_id = (SELECT sys_context('USERENV', 'CON_ID') FROM dual)
;

- -tempfile info 
set linesize 300
col TABLESPACE_NAME for a25
col FILE_NAME for a70
select tb.tablespace_name, bytes/1024/1024/1024 Gb,MAXBYTES/1024/1024/1024 Max_Gb,tf.file_name,AUTOEXTENSIBLE from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';


set linesize 300 numf 9999.99
col CREATION_TIME for a27
col NAME for a70
col tb_name for a15
select b.name tb_name, a.name, a.bytes/1024/1024/1024 Gb ,CREATE_BYTES/1024/1024/1024 CREATED_SIZE,to_char(CREATION_TIME,'dd-mm-yyyy hh24:mi') CREATION_TIME ,STATUS,ENABLED
from v$tempfile a, v$tablespace b 
where a.ts#=b.ts#


alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
set pages 999 lines 400
col FILE_NAME format a70
col INCREMENT_BY_GB for 9999.99
col MAXSIZE_GB for 999.99
select v.FILE#,d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024/1024 SIZE_GB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024/1024 MAXSIZE_GB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024/1024 INCREMENT_BY_GB
,CREATION_TIME
from dba_temp_files d, v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;


-- %  !!!!!!!!!
col TABLESPACE_NAME for A15
SELECT D.tablespace_name,   SPACE/1024/1024/1024   	"SUM_SPACE(GB)",
                blocks    				"SUM_BLOCKS", 
               used_space /1024/1024/1024           	"USED_SPACE(GB)", 
             (SPACE - used_space ) /1024/1024/1024    "FREE_SPACE(GB)" ,
                 Round(Nvl(used_space, 0)/SPACE * 100, 2) "USED(%)"
        FROM   (SELECT tablespace_name,  sum(decode(maxbytes, 0, bytes, maxbytes))  SPACE, SUM(blocks)      BLOCKS         
                FROM   dba_temp_files 
                GROUP BY tablespace_name) D, 
               (SELECT tablespace,   SUM(blocks * 8192) USED_SPACE   FROM   v$sort_usage 
                GROUP BY tablespace) F 
        WHERE D.tablespace_name = F.tablespace(+)
       --   AND D.tablespace_name='TEMP'
AND D.tablespace_name in (select tb.tablespace_name from dba_tablespaces tb  where tb.contents = 'TEMPORARY')
;




set pagesize 10000 linesize 300 tab off
 
col tablespace_name format A28              heading "Tablespace"
col ts_type         format A13              heading "TS Type"
col segments        format 999999           heading "Segments"
col files           format 9999
col allocated_mb    format 999,999,990.00   heading "Allocated Size|(Mb)"
col used_mb         format 999,999,990.00   heading "Used Space|(Mb)"
col Free_mb         format 999,999,990.00   heading "Free Space|(Mb)"
col used_pct        format 999              heading "Used|%"
col max_ext_mb      format 999,999,990.00   heading "Max Size|(Mb)"
col max_free_mb     format 999,999,990.00   heading "Max Free|(Mb)"
col max_used_pct    format 999              heading "Max Used|(%)"
 
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT
 
WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), 
     tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), 
     tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
     ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
-- TEMP TS
SELECT d.tablespace_name, 
       d.status, 
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, 
       a.cnt, 
       0,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, 
       ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB, 
       ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
       ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb, 
   ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Total_free_MB,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t 
 WHERE d.tablespace_name = a.tablespace_name(+) 
   AND d.tablespace_name = t.tablespace_name(+) 
   AND d.tablespace_name = m.tablespace_name(+) 
   AND d.extent_management = 'LOCAL'
   AND d.contents = 'TEMPORARY'  ;


set linesize 300
col name for a12


col "Size (gb)" for a20
col "Used (gb)" for a20
col "Used %" for a20
SELECT   d.status "Status", d.tablespace_name "Name", 
         d.contents "Type", 
         d.extent_management "ExtMgmnt",
         TO_CHAR(NVL(a.bytes / 1024/1024/1024, 0), '99,999,990')   "Size (gb)", 
         TO_CHAR(NVL(t.bytes,0)/1024/1024/1024,  '99999,999') "Used (gb)",
         TO_CHAR(NVL(t.bytes / a.bytes * 100, 0),  '99990.00') "Used %"
FROM sys.dba_tablespaces d, 
     (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
     (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) 
AND   d.tablespace_name = t.tablespace_name(+)
AND   d.extent_management like 'LOCAL' 
AND   d.contents like 'TEMPORARY';




select TABLESPACE_NAME,
trunc(TABLESPACE_SIZE/1024/1024/1024,2) 		TABLESPACE_SIZE_GB,
trunc(ALLOCATED_SPACE/1024/1024/1024,2) 		ALLOCATED_SPACE_GB,
trunc(FREE_SPACE/1024/1024/1024,2 ) 			FREE_SPACE_GB,
(ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)) 	"TotalUsed%", 
case when (ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)>85.00) then '---(85.00)% full :-( ***'
else 'Good :-)'
end as ATTENTION
from dba_temp_free_space;


col FILE_NAME for a30
select * from dba_temp_files where tablespace_name like 'TEMP%';

 

col sql_text for a30
col error_msg for a30
select
   user_id,
   session_id,
   status,
   start_time,
   suspend_time,
   sql_text,
   error_number,
   error_msg
from    dba_resumable;


set line 300 pagesize 200
col lastcallet 	for a10
col proginfo 	for a50
col username 	for a20
col tablespace 	for a15
col serial# 	for 99999999
col kill 	for a15
col event 	for a30

cursor temp_use is
select * from (
select 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' kill ,
s.status,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60) lastcallet,s.sql_id,PREV_SQL_ID,event
from gv$sort_usage u,gv$session s,v$parameter p
where u.session_addr = s.saddr
and u.inst_id= s.inst_id
and p.name = 'db_block_size'
--and s.USERNAME not in ( 'SYS' ,'SYSTEM','DBSNMP','SYSMAN' ,'OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS', 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' ,s.status,s.username,u.tablespace,
nvl(s.module,s.program),floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60),s.sql_id ,PREV_SQL_ID,event
having sum(u.blocks*p.value/1024/1024)  >1 -- MB change based on requirement
order by 5 desc,3   )
where 1=1
-- and rownum < 11
;




set linesize 500 pagesize 200 
col username for a15
col sid_serial for a15
col sql_text for a100 word_wrapped
col hash_value for 999999999999
col tablespace for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.username,t.blocks * tbs.block_size/1024/1024 mb_used, t.tablespace,t.sql_id sql_id, q.hash_value, dbms_lob.substr(q.sql_text,2000,1) sql_text
from gv$sort_usage t, gv$session s, gv$sqlarea q, dba_tablespaces tbs
where t.session_addr = s.saddr
and t.sqladdr = q.address (+)
and t.tablespace = tbs.tablespace_name
and s.sql_id is not null 
order by 3 desc;


kill username mb_used tablespace sql_id hash_value sql_text
--------------- --------------- ---------- --------------- ------------- ------------- ----------------------------------------------------------------------------------------------------
'742,59484,@1' ANUJ 314 TEMP g9pw0rahq8jw8 2707703688 select * from TEST1 order by n1,n2 desc



set linesize 300 pagesize 200 
col kill for a15
col sid_serial for a15
col osuser for a12
col module for a30
col username for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.username, s.sql_id,s.osuser, p.spid, s.module, s.program, t.tablespace, count(*) sort_ops, sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
from gv$sort_usage t, gv$session s, dba_tablespaces tbs, gv$process p
where t.session_addr = s.saddr 
and t.inst_id = s.inst_id
and s.paddr = p.addr 
and s.inst_id = p.inst_id
and t.tablespace = tbs.tablespace_name
and s.sql_id is not null 
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', s.username,s.sql_id, s.osuser, p.spid, s.module,s.program, tbs.block_size, t.tablespace
order by mb_used desc;
KILL USERNAME SQL_ID OSUSER SPID MODULE PROGRAM TABLESPACE SORT_OPS MB_USED
--------------- --------------- ------------- ------------ ------------------------ ------------ ------------------------------------------------ -------------------- ---------- ----------
'742,59484,@1' ANUJ g9pw0rahq8jw8 oracle 19945 SQL*Plus sqlplus@************************* * (TNS V1-V3) TEMP 1 314




--with  s.CON_ID>1  !!!!!
set linesize 200 pagesize 200 col kill for a15 col sid_serial for a15 col osuser for a12 col module for a12 col username for a15 select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.CON_ID,s.username, s.sql_id,s.osuser, p.spid, s.module, s.program, t.tablespace, count(*) sort_ops, sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used from gv$sort_usage t, gv$session s, dba_tablespaces tbs, gv$process p where t.session_addr = s.saddr and t.inst_id = s.inst_id and s.paddr = p.addr and s.inst_id = p.inst_id and t.tablespace = tbs.tablespace_name and s.sql_id is not null --and s.CON_ID>1 group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', s.CON_ID,s.username,s.sql_id, s.osuser, p.spid, s.module,s.program, tbs.block_size, t.tablespace order by mb_used desc;



col tablespace for a20
col username for a15
col PRGM for a25
col TEMPSIZE for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.username,rpad(s.program,15,' ') prgm,rpad(su.blocks*16384/(1024*1024)||' mb ',10,' ') tempsize,su.segtype, su.tablespace,s.sql_id
from gv$session s,gv$sort_usage su
where s.saddr=su.session_addr
and s.sql_id is not null 
order by su.blocks ;


KILL USERNAME PRGM TEMPSIZE SEGTYPE TABLESPACE SQL_ID
--------------- --------------- ------------------------------------------------------------ ---------------------------------------- --------- -------------------- -------------
'742,59484,@1' ANUJ sqlplus@ibrac01 628 MB SORT TEMP g9pw0rahq8jw8


set linesize 200 pagesize 200 
col sql_text for a70
with temp_usage as
(
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username, a.osuser, b.tablespace, b.blocks, c.sql_text from gv$session a, gv$tempseg_usage b, gv$sqlarea c
where a.saddr = b.session_addr
and c.address = a.sql_address
and c.hash_value = a.sql_hash_value
and a.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by b.tablespace, b.blocks
)
select kill ,sql_id, username,sum((BLOCKS)*16)/1024 "MB" ,sql_text from temp_usage
group by kill,sql_id,username,sql_text
order by 4 desc
; 


KILL SQL_ID USERNAME MB SQL_TEXT
--------------- ------------- --------------- ---------- ----------------------------------------------------------------------
'742,59484,@1' g9pw0rahq8jw8 ANUJ 628 select * from TEST1 order by n1,n2 desc


set line 200 pagesize 200
col lastcallet for a10
col proginfo for a25
col username for a20
col tablespace for a15
col serial# for 99999999
col kill for a15
col event for a30
cursor temp_use is
select * from (
select 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' kill ,
s.status,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60) lastcallet,s.sql_id,event
from gv$sort_usage u,gv$session s,v$parameter p
where u.session_addr = s.saddr
and u.inst_id= s.inst_id
and p.name = 'db_block_size'
and s.USERNAME not in ( 'SYS' ,'SYSTEM','DBSNMP','SYSMAN' ,'OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS', 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' ,s.status,s.username,u.tablespace,
nvl(s.module,s.program),floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60),s.sql_id ,event
order by 5 desc,3)
where rownum < 11;



KILL STATUS USERNAME TABLESPACE MBUSED NOEXTS PROGINFO LASTCALLET SQL_ID EVENT
--------------- -------- -------------------- --------------- ---------- ---------- ------------------------- ---------- ------------- ------------------------------
'742,59484,@1' INACTIVE ANUJ TEMP 314 314 SQL*Plus 0:0:0 g9pw0rahq8jw8 SQL*Net message from client




set linesize 200 pagesize 200 
col kill for a15
col sql_text for a70
col username for a15
with temp_usage as
(
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username, a.osuser, b.tablespace, b.blocks, c.sql_text from gv$session a, gv$tempseg_usage b, gv$sqlarea c
where a.saddr = b.session_addr
and c.address = a.sql_address
and c.hash_value = a.sql_hash_value
and a.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by b.tablespace, b.blocks
)
select kill ,sql_id, username,sql_text,sum((BLOCKS)*16)/1024 "MB" from temp_usage
group by kill,sql_id,username,sql_text
order by 4 desc
; 



KILL SQL_ID USERNAME SQL_TEXT MB
--------------- ------------- --------------- ---------------------------------------------------------------------- ----------
'742,59484,@1' g9pw0rahq8jw8 ANUJ select * from TEST1 order by n1,n2 desc 628




set line 200 pagesize 200
col tablespace for a20
col kill for a15
col host_name for a30
col username for a15
select * from (SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, u.tablespace, s.username, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,i.inst_id,i.host_name,s.sql_id FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr 
and u.inst_id=i.inst_id 
and s.username is not null 
order by MB DESC) a 
where rownum<11;


KILL TABLESPACE USERNAME LOGON_TIM PROGRAM EXTENTS MB INST_ID HOST_NAME SQL_ID
--------------- -------------------- --------------- --------- ------------------------------------------------ ---------- ---------- ---------- ------------------------------ -------------
'742,59484,@1' TEMP ANUJ 27-AUG-17 sqlplus@**************************** (TNS V1-V3) 314 314 1 ******************************** g9pw0rahq8jw8

 

set pagesize 50 linesize 200
select 
 a.inst_id,
 a.tablespace_name tablespace, 
 d.mb_total,
 sum (a.used_blocks * d.block_size)/1024/1024 mb_used
 -- , d.mb_total - sum (a.used_blocks * d.block_size)/1024/1024 mb_free
from gv$sort_segment a,
( select 
 b.name, 
 c.block_size, 
 sum (c.bytes) / 1024 / 1024 mb_total
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.inst_id,a.tablespace_name, d.mb_total
/



 INST_ID TABLESPACE MB_TOTAL MB_USED
---------- --------------- ---------- ----------
 2 TEMP 639 2
 1 TEMP 639 316

select min(snap_id),max(snap_id) from dba_hist_snapshot;

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER

exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set linesize 300 col DURATION for a25 col MODULE for a15 col ACTION for a15 select * from ( select instance_number as inst_id, sql_id, to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START, max(sample_time) - SQL_EXEC_START as duration, sql_exec_id, sql_plan_hash_value, module, action, round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb, round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb, max(px_used) as max_px_used from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sum(temp_space_allocated) as temp_space_allocated, sum(pga_allocated) as pga_allocated, count(distinct session_serial#) - 1 as px_used, sample_time from dba_hist_active_sess_history where 1=1 and snap_id between :BgnSnap and :EndSnap --- <<<<<< change this
and sql_exec_id > 0 group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time having sum(temp_space_allocated) is not null) group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB order by 9 desc ) where rownum <= nvl('&3',10) / INST_ID SQL_ID SQL_EXEC_START DURATION SQL_EXEC_ID SQL_PLAN_HASH_VALUE MODULE ACTION MAX_TEMP_GB MAX_PGA_GB MAX_PX_USED ---------- ------------- ------------------- ------------------------- ----------- ------------------- --------------- --------------- ----------- ---------- ----------- 1 c8drdcj8g767v 07.01.2019 13:23:15 +000000000 08:45:03.377 16777216 421534165 SQL Developer 93.828 1.025 0 select * from ( select instance_number as inst_id, sql_id, to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START, max(sample_time) - SQL_EXEC_START as duration, sql_exec_id, sql_plan_hash_value, module, action, round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb, round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb, max(px_used) as max_px_used from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sum(temp_space_allocated) as temp_space_allocated, sum(pga_allocated) as pga_allocated, count(distinct session_serial#) - 1 as px_used, sample_time from dba_hist_active_sess_history where 1=1 -- and snap_id > (select/*+ NO_UNNEST*/ min(snap_id) from dba_hist_snapshot where begin_interval_time > sysdate - nvl('&1',30)) and sql_exec_id > 0 group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time having sum(temp_space_allocated) is not null) group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB order by 9 desc ) where rownum <= nvl('&2',10)

===========
var snap_id1 number ;
var snap_id2 number;
var vsqlid varchar2(30);
 begin :snap_id1 := '11111'; end;  
/
begin :snap_id2:= '11112'; end; 
/
begin :vsqlid:= 'XXXXXXXXX'; end;
/

set linesize 500 pagesize 300
col SQL_PROFILE 		for a20
col DURATION 			for a25
col MODULE 			for a20
col ACTION 			for a23
select to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss')           as SQL_EXEC_START,
       max(sample_time) - SQL_EXEC_START                         as duration,
       sql_id ,
       sql_exec_id,
       sql_plan_hash_value,
       module,
       action,
       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
       max(px_used)                                             as max_px_used
  from (select sql_id,sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
               sum(temp_space_allocated)           as temp_space_allocated,
               sum(pga_allocated)                  as pga_allocated,
               count(distinct session_serial#) - 1 as px_used,
               sample_time
          from dba_hist_active_sess_history
         where 1=1
        --and sql_id = :vsqlid
        --   and snap_id between &2 and nvl('&&3', &&2)
           and sql_exec_id > 0
       --  and sample_time >sysdate -1
         group by sql_id,sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
          having sum(temp_space_allocated) is not null)
group by SQL_EXEC_START, sql_id,sql_exec_id, sql_plan_hash_value, module, action
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 1 ;
===============
SET LINESIZE 200  PAGESIZE 200
UNDEF num_days
COL startup_time  FOR a30
COL db_name   FOR a10
COL snap_start   FOR 9999999
COL snap_end   FOR 9999999
COL start_interval  FOR a25
COL end_interval  FOR a25
COL range_interval  FOR a40
COL qtd_snaps  FOR 999
 
SELECT
    s.startup_time,
    di.instance_name,
    MIN(snap_id) snap_start,
    MAX(snap_id) snap_end,
    MIN(end_interval_time) start_interval,
    MAX(end_interval_time) end_interval,
    EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Days(s) ' || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Hour(s) '
    || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )  || ' Minute(s) ' range_interval,   MAX(snap_id) - MIN(snap_id) qtd_snaps
FROM    dba_hist_snapshot s,    dba_hist_database_instance di
WHERE    di.dbid = s.dbid
    AND   di.instance_number = s.instance_number
    AND   end_interval_time > DECODE(&num_days,0,TO_DATE('08-JAN-2019','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) )
GROUP BY  s.startup_time,    di.instance_name
ORDER BY  startup_time ASC;





set linesize 300 pagesize 300
col kill for a15
col USERNAME  for a20
col SQL_ID  for a13
col TEXT80  for a80 wrap
col CONTENTS  for a20 just l
col SEGTYPE  for a20
select /*+ ORDERED*/
       ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
       tu.USERNAME,
       NVL(s.SQL_ID, s.prev_sql_id) SQL_ID,
       tu.CONTENTS,
       tu.SEGTYPE,
       count(tu.SEGBLK#)                     SEG_COUNT,
       count(distinct tu.SESSION_ADDR) PROC_COUNT,
       round(sum(tu.BLOCKS * t.BLOCK_SIZE) / 1024 / 1024) size_MB,
      substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80) as TEXT80
  from gv$tempseg_usage tu, gv$session s, dba_tablespaces t, gv$sqlarea sa
 where tu.SESSION_ADDR = s.SADDR
   and tu.INST_ID = s.INST_ID
   and s.SQL_ID = sa.SQL_ID
   and s.INST_ID = sa.INST_ID
   and tu.TABLESPACE = t.TABLESPACE_NAME
-- and s.SQL_ID = '1'
 group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' , tu.USERNAME, NVL(s.SQL_ID, s.prev_sql_id), substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80),  tu.CONTENTS,  tu.SEGTYPE
 order by sum(tu.BLOCKS) desc
/


define top=10
 col STAR for a20
select SQL_ID,TEMP_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_TEMP_MB) TEMP_MB ,(ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,temp_space_allocated,
greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
from gv$active_session_history
where 1=1
and IS_SQLID_CURRENT='Y'
and sample_time > sysdate -1
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_TEMP_MB) > 0
)
where rank < (&top+1)
order by rank
/

===
-- with Con_id > 11g
alter session set nls_date_format='dd-mon-yy hh24:mi' ;
set linesize 500 pagesize 300
col sql_text for a70 
col username for a20
col osuser for a15
col kill for a16
 select 
 ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''  as kill,
 a.CON_ID,
 sysdate,
  	   a.username,
           a.osuser,
           a.program,
           a.sql_id,
           a.sql_child_number,
           a.sql_exec_start,
           q.plan_hash_value,
           b.tablespace,
           b.segtype,
           sum(b.blocks) as BLOCKS,
           q.sql_text
      from gv$session a, gv$tempseg_usage b, gv$sql q
     where a.saddr = b.session_addr
       and q.address = a.sql_address
       and q.inst_id = a.inst_id
       and q.hash_value = a.sql_hash_value
       and q.child_number = a.sql_child_number
       and a.inst_id = b.inst_id
     group by grouping SETS((''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''',a.con_id, a.username,  a.osuser, a.program, a.sql_id, a.sql_child_number, a.sql_exec_start, q.sql_text, q.plan_hash_value, b.tablespace, b.segtype, sysdate),(sysdate));
 

====

select
 * from (
                select instance_number as inst_id,
                       ash.sql_id,
                       sql_opname,
                       to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss')           as SQL_EXEC_START,
                       max(sample_time) - SQL_EXEC_START                         as duration,
                       sql_exec_id,
                       sql_plan_hash_value,
                       module,
                       action,
                       machine,
                       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
                       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
                       max(px_used)                                             as max_px_used
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ') as SQL_TEXT
                  from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, machine,
                               sum(temp_space_allocated)           as temp_space_allocated,
                               sum(pga_allocated)                  as pga_allocated,
                               count(distinct session_serial#) - 1 as px_used,
                               sample_time
                          from dba_hist_active_sess_history ash
                         where snap_id > (select/*+ NO_UNNEST*/ min(snap_id) from dba_hist_snapshot where begin_interval_time > sysdate - nvl('&1',30))
                           and sql_exec_id > 0
                         group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, sample_time, machine
                          having sum(temp_space_allocated) is not null) ash
left join dba_hist_sqltext t  on t.sql_id  = ash.sql_id
                group by instance_number, ash.sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, machine
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ')
                -- having max(temp_space_allocated) / 1024 / 1024 / 1024 > 100 -- GB
				having max(temp_space_allocated) / 1024 / 1024 / 1024 > 1
                order by 11 desc
) where rownum <= nvl('&2',10)
/
===
set linesize 500 pagesize 200
col sql_text 	for a50 wrap
col service 	for a15
col action 	for a10
col module 	for a10
col username 	for a10
col kill 	for a15
select 
''''||sid ||','|| serial#||',@'||gv$session.inst_id ||'''' kill ,username, logon_time, round((sysdate-logon_time)*1440) timeonline_min,  c.tempsize_gb ,a.*
from gv$session, (  select inst_id, sql_id, round(sum(tempseg_size) /1024/1024/1024) tempsize_gb   from gv$sql_workarea_active
  where 1=1 
-- and tempseg_size > 1000000000  ---- for 1Gb 
  group by inst_id, sql_id) c, (
    select inst_id instance_number,sql_id, 
    round((conc_wait_sec_exec / elap_sec_exec)*100) 		con_perc,
    round((clu_wait_sec_exec / elap_sec_exec)*100) 		clust_perc,
    round((user_io_wait_sec_exec / elap_sec_exec)*100) 		io_perc, 
    conc_wait_sec_exec, clu_wait_sec_exec, user_io_wait_sec_exec, cpu_time_sec_exec, elap_sec_exec, buffer_gets, 
    round((buffer_gets*32678)/1024/1024/1024) 			buffer_gb,
    disk_reads, rows_processed, module,service, action,sql_text
    from ( 
      select inst_id, sql_id, sql_text,
      round((concurrency_wait_time/1000000)/decode(executions,null,1,0,1, executions),2) 	conc_wait_sec_exec,
      round((cluster_wait_time/1000000)/decode(executions,null,1,0,1,executions),2) 		clu_wait_sec_exec,
      round((user_io_wait_time/1000000)/decode(executions,null,1,0,1,executions),2) 		user_io_wait_sec_exec,
      round((direct_writes/decode(executions,null,1,0,1,executions)),2) 					direct_writes_exec,
      round((cpu_time/1000000)/decode(executions,null,1,0,1,executions),2) 					cpu_time_sec_exec,
      round(( elapsed_time/1000000)/decode(executions,null,1,0,1,executions),2) 			elap_sec_exec,
      round((io_interconnect_bytes/decode(executions,null,1,0,1,executions)),2) 			io_inter_by_exec,
      concurrency_wait_time, cluster_wait_time, user_io_wait_time, direct_writes,  cpu_time, elapsed_time, io_interconnect_bytes,
      round(sorts/decode(executions,null,1,0,1,executions),2) sort_exec, fetches, rows_processed, executions, parse_calls,
      round(disk_reads/decode(executions,null,1,0,1,executions),2) disk_exec,
      round(buffer_gets/decode(executions,null,1, 0,1,executions),2) buff_exec, 
      service, module, action, buffer_gets, disk_reads
      from gv$sql
      where users_opening > 0
      and elapsed_time/decode(executions, null, 1, 0, 1, executions) >= 30000000)
    ) a
where a.sql_id		= gv$session.sql_id
and a.instance_number 	= gv$session.inst_id
and a.sql_id 		= c.sql_id
and a.instance_number 	= c.inst_id;

select to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss')           as SQL_EXEC_START,
       max(sample_time) - SQL_EXEC_START                         as duration,
       sql_exec_id,
       sql_plan_hash_value,
       module,
       action,
       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
       max(px_used)                                             as max_px_used
  from (select sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
               sum(temp_space_allocated)           as temp_space_allocated,
               sum(pga_allocated)                  as pga_allocated,
               count(distinct session_serial#) - 1 as px_used,
               sample_time
          from dba_hist_active_sess_history
         where sql_id = 'gtfzbk4ajr15q'   --- !!!!
        --   and snap_id between &2 and nvl('&&3', &&2)
           and sql_exec_id > 0
         group by sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
          having sum(temp_space_allocated) is not null)
group by SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
--having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 1
===

set pages 500 lines 500
col MIN_SAMPLE_TIME for a16
col MAX_SAMPLE_TIME for a16
col sql_text for a200

select * from (
select inst_id,
       to_char(min(sample_time),'dd.mm.yyyy hh24:mi')          as min_sample_time,
       to_char(max(sample_time),'dd.mm.yyyy hh24:mi')          as max_sample_time,
       module,
       sql_id,
       sql_plan_hash_value                                     as plan_hash_value,
       count(distinct sql_exec_id)                             as execs,
       to_char(max(temp_space_allocated),'999,999,999,999,999')as max_temp_space,
       round(max(temp_space_allocated)   /   (select sum(user_bytes) from dba_temp_files where tablespace_name = 'TEMP')  *100) as "TEMP%",
       to_char(max(pga_allocated),'999,999,999,999')           as max_pga,
       max(px) - 1                                             as px
--(select substr(sql_text,1,200) as sql_text from gv$sqlarea a where a.sql_id = sql_id union select dbms_lob.substr(sql_text,200) from dba_hist_sqltext a where a.sql_id = sql_id) as sql_text
--,(select NVL(substr(a.sql_text,1,200), dbms_lob.substr(h.sql_text,200)) as sql_text from gv$sqlarea a, dba_hist_sqltext h where a.sql_id = s.sql_id and  a.sql_id = h.sql_id) as sql_text
  from (select inst_id                             as inst_id,
               sample_time,
               sql_id,
               sql_plan_hash_value,
               sql_exec_id,
               module,
               sum(temp_space_allocated)           as temp_space_allocated,
               sum(pga_allocated)                  as pga_allocated,
               count(distinct session_serial#) - 1 as px
          from gv$active_session_history
         where sql_exec_id > 0
           and sample_time > sysdate -2
         group by inst_id,
                  sample_time,
                  sql_id,
                  sql_plan_hash_value,
                  sql_exec_id,
                  module
        ) s
 group by inst_id, sql_id, sql_plan_hash_value, module
 order by max(temp_space_allocated) desc nulls last)
where rownum <= nvl('&1', 10)
/

 INST_ID MIN_SAMPLE_TIME  MAX_SAMPLE_TIME  MODULE                                             SQL_ID        PLAN_HASH_VALUE      EXECS MAX_TEMP_SPACE            TEMP% MAX_PGA                  PX
-------- ---------------- ---------------- -------------------------------------------------- ------------- --------------- ---------- -------------------- ---------- ---------------- ----------
        1 15.01.2022 06:00 15.01.2022 06:00 DBMS_SCHEDULER                                     4kvt2r09bz39r      2912237509          2            8,388,608          0       38,593,536         -1
       2 13.01.2022 14:20 15.01.2022 12:36 MMON_SLAVE                                         8mdz49zkajhw3      2609763588         24            6,291,456          0       51,159,040         -1
       2 13.01.2022 22:00 13.01.2022 22:00 DBMS_SCHEDULER                                     4kvt2r09bz39r      2912237509          2            6,291,456          0       36,496,384         -1
       1 15.01.2022 03:40 15.01.2022 12:36 MMON_SLAVE                                         8mdz49zkajhw3      2609763588         54            5,242,880          0       98,362,368         -1
       2 13.01.2022 22:11 13.01.2022 22:12 DBMS_SCHEDULER                                     bb9kv1sq4fq9x      1186311642          7            5,242,880          0       34,399,232         -1
       2 13.01.2022 22:12 13.01.2022 22:12 DBMS_SCHEDULER                                     531x22ur6bxc1      3026497435          1            5,242,880          0       39,117,824         -1
       2 13.01.2022 22:12 13.01.2022 22:12 DBMS_SCHEDULER                                     531x22ur6bxc1               0          1            5,242,880          0       34,399,232         -1
       2 13.01.2022 22:03 13.01.2022 22:03 DBMS_SCHEDULER                                     f705bwx3q0ydq      2485401174          1            5,242,880          0       28,435,456         -1
       1 15.01.2022 04:02 15.01.2022 09:47 MMON_SLAVE                                         fg4skgcja2cyj      1186311642          4            5,242,880          0       34,202,624         -1
SQL>


===

set linesize 300 pagesize 300
col inst_id for 99
col username for a20
col sql_id for a13
col text80 for a80
col contents for a20 just l
col segtype for a20

select tu.inst_id,
       tu.username,
       nvl(s.sql_id, s.prev_sql_id) sql_id,
       tu.contents,
       tu.segtype,
       count(tu.segblk#)                     seg_count,
       count(distinct tu.session_addr) proc_count,
       round(sum(tu.blocks * t.block_size) / 1024 / 1024) size_mb,
    substr(rpad(sa.sql_text, 80, '*'), 1, 80) as text80
  from gv$tempseg_usage tu, gv$session s, dba_tablespaces t, gv$sqlarea sa
 where tu.session_addr = s.saddr
   and tu.inst_id = s.inst_id
   and s.sql_id = sa.sql_id
   and s.inst_id = sa.inst_id
   and tu.tablespace = t.tablespace_name
--and s.sql_id = '&1'
 group by tu.inst_id,
          tu.username,
          nvl(s.sql_id, s.prev_sql_id),
          substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80),
          tu.contents,
          tu.segtype
 order by sum(tu.blocks) desc
/
====

   


set linesize 300 pagesize 300 
col sql_text for a30
select t.inst_id,to_char(t.sample_time,'DD-MON-YYYY HH24:MI:SS') sample_time, t.sql_id, t.temp_mb, t.temp_diff, substr(s.sql_text,1,30) sql_text
  from (
        select 
inst_id,
--session_id,session_serial#,
               --'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
               trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
               , row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
          from (
                select inst_id,sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb, 
                       temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
                 --from dba_hist_active_sess_history 
                 from gv$active_session_history
                where 1 = 1 
                -- session_id=par1 
                -- and session_serial#=par2
               )
         group by inst_id,
--session_id,session_serial#,
                  trunc(sample_time),
                  sql_id
       ) t
  left join gv$sqlarea s
    on s.sql_id = t.sql_id and s.inst_id = t.inst_id
 where 1 = 1
   and rn <=5
   and sample_time >= trunc(sysdate) - 1                 
 order by temp_mb desc, sample_time desc;
 
 
 
 
set linesize 300 pagesize 300 
col sql_text for a30
select t.INSTANCE_NUMBER,to_char(t.sample_time,'DD-MON-YYYY HH24:MI:SS') sample_time, t.sql_id, t.temp_mb, t.temp_diff, substr(s.sql_text,1,30) sql_text
  from (
        select 
INSTANCE_NUMBER,
--session_id,session_serial#,
               --'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
               trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
               , row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
          from (
                select INSTANCE_NUMBER,sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb, 
                       temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
                 from dba_hist_active_sess_history 
                 --from gv$active_session_history
                where 1 = 1 
                -- session_id=par1 
                -- and session_serial#=par2
               )
         group by INSTANCE_NUMBER,
--session_id,session_serial#,
                  trunc(sample_time),
                  sql_id
       ) t
  left join gv$sqlstats s
    on s.sql_id = t.sql_id and s.inst_id = t.instance_number
 where 1 = 1
   and rn <=5
   and sample_time >= trunc(sysdate) - 7               
 order by temp_mb desc, sample_time desc;
 
 





col kill for a15
set linesize 140 pagesize 400 head on
col iid for 990
col sid_serial for a11
column username for a10
col module for a20
col action for a15
col state for a10
column segfile# for 9,999 heading 'file|id'
col orapid for 999999 heading "orapid||oracle|pid"
col dbpid for a6 heading "spid|unix|pid"
col apppid for a10
column program for a55
column segblk# for 999,999,999 heading 'block|id'
set timi off
clear computes
break on iid
select distinct
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
p.pid orapid,
p.spid dbpid,
s.username,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,
s.status,
s.state,
u.sql_id,
segfile#,
s.module,
s.action
from gv$sort_usage u, gv$session s, gv$process p, v$parameter par
where 1=1
and s.inst_id=u.inst_id
and s.saddr(+) = u.session_addr
and s.paddr = p.addr
and par.name='db_block_size'
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', p.pid, p.spid, s.username, s.status, s.state, u.sql_id, segfile#, s.module, s.action
having round(sum (((u.blocks*par.value)/1024/1024)),2) >10  ---MB
order by 1,2
;




set linesize 400 pagesize 300
col CLIENT_ID  for a20
col MODULE   for a25                                                        
col ACTION for a20
select sql_id ,sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID,
       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
       max(px_used)                                             as max_px_used
  from (select sql_id ,sql_exec_id, sql_plan_hash_value, REGEXP_SUBSTR(client_id, '.+\#') as CLIENT_ID, module, action, sample_id,
               sum(temp_space_allocated)           as temp_space_allocated,
               sum(pga_allocated)                  as pga_allocated,
               count(distinct session_serial#) - 1 as px_used
          from gv$active_session_history
         where 1=1
        --   and sql_id = '&&1'
           and sql_exec_id > 0
           and sql_plan_hash_value > 0
         group by sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, REGEXP_SUBSTR(client_id, '.+\#')
          having sum(temp_space_allocated) is not null)
group by sql_id ,sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 1
order by 2




set linesize 700 pagesize 500

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;


col SQL_EXEC_START for a20
col DURATION for a25
select * from (
                select instance_number as inst_id,
                       sql_id,
                       to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss')           as SQL_EXEC_START,
                       max(sample_time) - SQL_EXEC_START                         as duration,
                       sql_exec_id,
                       sql_plan_hash_value,
                       module,
                       action,
                       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
                       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
                       max(px_used)                                             as max_px_used
                  from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
                               sum(temp_space_allocated)           as temp_space_allocated,
                               sum(pga_allocated)                  as pga_allocated,
                               count(distinct session_serial#) - 1 as px_used,
                               sample_time
                          from dba_hist_active_sess_history
                         where snap_id between :bid  and :eid
                           and sql_exec_id > 0
                         group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
                          having sum(temp_space_allocated) is not null)
                group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
                having max(temp_space_allocated) / 1024 / 1024 / 1024 > .5 -- GB
                order by 9 desc
) where rownum <= nvl('&3',10)
/



col SAMPLE_TIME for a27
select * from (
                select instance_number as inst_id,
                       sample_id,
                       sample_time,
                       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
                       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb
                  from (select instance_number, sample_time, sample_id,
                               sum(temp_space_allocated)           as temp_space_allocated,
                               sum(pga_allocated)                  as pga_allocated
                          from dba_hist_active_sess_history
                         where snap_id between :bid  and :eid
                         group by instance_number, sample_id, sample_time
                          having sum(temp_space_allocated) is not null)
                group by instance_number, sample_time, sample_id
                having max(temp_space_allocated) / 1024 / 1024 / 1024 > .5 -- GB
                order by 4 desc
) where rownum <= nvl('&3',10)
/



define seconds= 1000
define top=10
col percent head '%' for 99990.99
col star for A10 head ''


select SQL_ID,TEMP_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_TEMP_MB) TEMP_MB ,(ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,temp_space_allocated,greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
from gv$active_session_history
where 1=1
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1000' second
and sample_time>sysdate- INTERVAL '1' HOUR
and IS_SQLID_CURRENT='Y'
order by 1,2,3,4
)
group by sql_id
-- having sum(DELTA_TEMP_MB) > 0
)
where rank < (&top+1)
order by rank
/


SQL_ID           TEMP_MB         %
------------- ---------- --------- ----------
0t3pbwd42tt9t       3438     42.93 ****



===

- -tempfile info 
set linesize 300
col TABLESPACE_NAME for a25
col FILE_NAME for a70
select tb.tablespace_name, bytes/1024/1024/1024 Gb,MAXBYTES/1024/1024/1024 Max_Gb,tf.file_name,AUTOEXTENSIBLE from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';



set linesize 300 numf 9999.99
col CREATION_TIME for a27
col NAME for a70
col tb_name for a15
select b.name tb_name, a.name, a.bytes/1024/1024/1024 Gb ,CREATE_BYTES/1024/1024/1024 CREATED_SIZE,to_char(CREATION_TIME,'dd-mm-yyyy hh24:mi') CREATION_TIME ,STATUS,ENABLED
from v$tempfile a, v$tablespace b 
where a.ts#=b.ts#

set pagesize 50 linesize 200
col file_name for a40
select t.inst_id,substr(t.name,1,50) as file_name,f.phyblkrd as blocks_read,f.phyblkwrt as blocks_written,f.phyblkrd + f.phyblkwrt as total_io
from gv$tempstat f,gv$tempfile t
where t.file# = f.file#
and t.inst_id=f.inst_id
order by f.phyblkrd + f.phyblkwrt desc;

 INST_ID FILE_NAME BLOCKS_READ BLOCKS_WRITTEN TOTAL_IO
---------- ---------------------------------------- ----------- -------------- ----------
 1 +DATA/rac/temp01.dbf 525400 573493 1098893
 2 +DATA/rac/temp01.dbf 77424 70562 147986


 
 add temp file ..

alter database tempfile '+DATA/prd/tempfile/temp_02.dbf' resize 10G ;
alter database tempfile '+DATA/prd/tempfile/temp_02.dbf' autoextend on maxsize 10G;


To shrink ..
alter tablespace TEMP shrink tempfile '+DATA/prd/tempfile/temp_02.dbf' KEEP 2G;


set linesize 120 pagesize 0
select 'ALTER TABLESPACE ' || tablespace_name || ' SHRINK TEMPFILE ''' || file_name || ''' KEEP 10M;' stmt from dba_temp_files 
-- where tablespace_name = 'TEMP'
;


----

select * from V$TEMPORARY_LOBS


col PROGRAM for a50
col kill for a16

SELECT 
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill, a.sql_id,b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,a.inst_id as Instance,NVL(a.username, '(oracle)') AS username,a.program, a.status
FROM gv$session a, gv$sort_usage b, 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
and b.contents = 'TEMPORARY' 
--and b.segtype='LOB_DATA'
ORDER BY temp_size desc;




col PROGRAM for a50
col kill for a16

SELECT 
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill, a.sql_id,b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,a.inst_id as Instance,NVL(a.username, '(oracle)') AS username,a.program, a.status
FROM gv$session a, gv$sort_usage b, 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
and b.contents = 'TEMPORARY' 
and b.segtype='LOB_DATA'
ORDER BY temp_size desc;




col PROGRAM for a50
col kill for a16
SELECT
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,
a.sql_id,
 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
      FROM   gv$session a,
       gv$sort_usage b,
       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
ORDER BY b.tablespace, b.blocks




select * from v$temporary_lobs
order by
  cache_lobs desc 


SELECT A.inst_id,
  A.tablespace_name TABLESPACE,
  D.mb_total,
  SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  D.mb_total         - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM gv$sort_segment A,
  (SELECT B.INST_ID,
    B.name,
    C.block_size,
    SUM (C.bytes) / 1024 / 1024 mb_total
  FROM gv$tablespace B,
    gv$tempfile C
  WHERE B.ts#  = C.ts#
  AND c.inst_id=b.inst_id
  GROUP BY B.INST_ID,
    B.name,
    C.block_size
  ) D
WHERE A.tablespace_name = D.name
--and a.tablespace_name='TEMP'
AND A.inst_id           =D.inst_id
GROUP BY a.inst_id,
  A.tablespace_name,
  D.mb_total
ORDER BY 1,2;  



set pagesize 300
select /*+ parallel(8) */ sql_id,trunc(max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024),2) GB from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
and sample_time > sysdate-2
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
group by sql_id
order by 2 desc;


---with con_id  
set pagesize 300
select /*+ parallel(8) */ CON_ID,sql_id,trunc(max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024),2) GB from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
and sample_time > sysdate-2
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
group by CON_ID,sql_id
order by 2 desc;

====

from History 

  
define when="19/10/2022 10:00"
define div=1/1024/1024/1024

col "size (Gb)"      format 999,999,999.99
col "used size (Gb)" format 999,999,999.99
col "max size (Gb)"  format 999,999,999.99
col "free size (Gb)" format 999,999,999.99


set pagesize 300
set linesize 300

with A as ( select name tablespace_name,
                   ts#  tablespace_id
            from v$tablespace ),
     B as ( select tablespace_name,
                   block_size*&div factor
            from dba_tablespaces ),
     C as ( select snap_id,
                   to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') end_interval_time
            from dba_hist_snapshot
            where 1=1
			--and to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') like '&when%' 
			and end_interval_time > sysdate -1
			)
select C.end_interval_time "end interval time",
       B.tablespace_name,
       D.tablespace_size                        *factor "size (Gb)",
       D.tablespace_usedsize                    *factor "used size (Gb)",
       (D.tablespace_size-D.tablespace_usedsize)*factor "free size (Gb)",
       D.tablespace_maxsize                     *factor "max size (Gb)"
from
  A, B, C, dba_hist_tbspc_space_usage D
where
      D.snap_id = C.snap_id
  and D.tablespace_id = A.tablespace_id
  and A.tablespace_name = B.tablespace_name
  and A.tablespace_name='TEMP'
  and CON_ID=5
order by 1;


====



set linesize 300 pagesize 300

col CLIENT_ID for a15
col MODULE for a30
col ACTION for a15
select con_id,sql_id, sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID,
       round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
       round(max(pga_allocated)        / 1024 / 1024 / 1024, 3) as max_pga_gb,
       max(px_used)                                             as max_px_used
  from (select con_id,sql_id,sql_exec_id, sql_plan_hash_value, REGEXP_SUBSTR(client_id, '.+\#') as CLIENT_ID, module, action, sample_id,
               sum(temp_space_allocated)           as temp_space_allocated,
               sum(pga_allocated)                  as pga_allocated,
               count(distinct session_serial#) - 1 as px_used
        --  from gv$active_session_history
		  from dba_hist_active_sess_history
         where 1=1
--and sql_id = '&&1'
         --  and sql_exec_id > 0
           and sql_plan_hash_value > 0
		   and SAMPLE_TIME >sysdate -7
         group by con_id,sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, REGEXP_SUBSTR(client_id, '.+\#')
          having sum(temp_space_allocated) is not null
         )
group by con_id,sql_id,sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 6 -- GB
order by 2
/



alter session set nls_date_format='dd-mm-YYYY hh24:Mi';
set linesize 300 pagesize 300
col PROGRAM for a50
col MODULE for a20
col ACTION for a25
col MACHINE for a15
col USERNAME for a15
select con_id,d.sql_id,u.USER_ID,u.USERNAME,trunc(max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024),2) GB ,d.PROGRAM,d.MODULE,d.ACTION,MACHINE,max(d.SQL_EXEC_START) SQL_START_time
from DBA_HIST_ACTIVE_SESS_HISTORY d,dba_users u
where 1=1
and d.sample_time > sysdate-5
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
-- and sql_id='&Sql_id'
and u.USER_ID=d.USER_ID
group by con_id,d.sql_id,u.USER_ID,u.USERNAME ,d.PROGRAM,d.MODULE,d.ACTION,d.MACHINE ;







col kill for a17
col OSUSER for a20
break on con_id skip 4
compute sum of pga_alloc_mem on con_id
SELECT 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill,
p.con_id,
s.sql_id,
       p.spid,
       p.pid,
   --    s.sid,
   --    s.serial#,
       s.sql_id,
       s.status,
       ROUND(p.pga_alloc_mem/1024/1024) as pga_alloc_mem,
       ROUND(p.pga_used_mem/1024/1024) as pga_used_mem,
       ROUND(p.PGA_MAX_MEM/1024/1024) as pga_max_mem,
       s.username,
       s.osuser,
      substr(s.program,1,20) program,
	  s.event 
FROM gv$process p, gv$session s
WHERE 1=1 
and s.paddr( + ) = p.addr
--AND p.background is null /* Remove prevent listing background processes */
AND    s.inst_id = p.inst_id
and ROUND(p.pga_alloc_mem/1024/1024) >30 --- <<<<
ORDER BY con_id,pga_alloc_mem;
break on off




	  
SET PAGESIZE 50 LINESIZE 300
COLUMN tablespace FORMAT A20
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A50
SELECT 
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''  kill,
a.con_id,
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,
a.event
FROM gv$session a,
gv$sort_usage b,
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
and ROUND(((b.blocks*p.value)/1024/1024),2) >1  --- 1 mb 
ORDER BY b.tablespace, b.blocks
/





set linesize 400 pagesize 400
col username for A20
col program  for A20
col kill_cmd for A55
col EVENT for a30
SELECT username,
		con_id, 
        sql_id,
       substr(program,1,20) program,
	   event ,
      'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' as kill_cmd
FROM gV$SESSION s
WHERE ( s.inst_id,s.saddr) IN (SELECT u.inst_id,u.session_addr 
								FROM   gv$session a,gv$sort_usage u,gv$parameter p
								WHERE  p.name  = 'db_block_size'
								AND    a.saddr = u.session_addr
								AND    a.inst_id=u.inst_id
								AND    a.inst_id=p.inst_id
								--and ROUND(((u.blocks*p.value)/1024/1024),2)>1  -- > 1mb 
								);




set linesize 300
col OPERATION_TYPE for a20

col ACTUAL_MEM_USED_MB for 99999.99
col WORK_AREA_SIZE_MB for 99999.99
col TEMPSEG_SIZE_MB for 99999.99
select inst_id,sid
--,CON_ID
,sql_id,operation_type,sum(actual_mem_used)/1024/1024 actual_mem_used_MB,sum(work_area_size)/1024/1024 WORK_AREA_SIZE_MB, sum(tempseg_size)/1024/1024 tempseg_size_MB 
from gv$sql_workarea_active  
group by inst_id,sid,sql_id,operation_type
--group by inst_id,sid,CON_ID,sql_id,operation_type
;






https://anuj-singh.blogspot.com/2010/03/oracle-script-to-monitor-for-pga-per.html





set linesize 500

 SELECT
     sql_id
	 ,CON_ID
   , policy
   , ROUND(SUM(actual_mem_used)/1048576) actual_pga_mb
   , ROUND(SUM(work_area_size)/1048576)  allowed_pga_mb
   , ROUND(SUM(tempseg_size)/1048576)    temp_mb
   , MAX(number_passes)                  num_passes
   , COUNT(DISTINCT qcinst_id||','||qcsid)   num_qc
   , COUNT(DISTINCT inst_id||','||sid)   num_sessions
 FROM
     gv$sql_workarea_active
 WHERE 1=1
 --and username='xx'
  --   1
 GROUP BY  sql_id ,CON_ID, policy
 ORDER BY   temp_mb DESC NULLS LAST
/


set pagesize 500
col sid for 99999 
col operation for a20
col start_time for a20
col expected_size_MB for 99,999.9 
col Act_Used_MB for 99,999.9 
col Max_Used_MB for 99,999.9
col TEMP_Seg_MB for 999,999
select 
sql_id
,CON_ID
,to_char(sql_exec_start, 'dd-mm-yyyy hh24:mi:ss') start_time, sql_id,
       to_number(decode(sid, 65535, NULL, sid)) sid,
       operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,
       trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,
       number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB
FROM gv$sql_workarea_active
where 1=1
--and SQL_ID='&SQL_ID'
--and  sql_exec_start> sysdate - interval '10' minute
ORDER BY 1,2;




http://anuj-singh.blogspot.com/2013/11/oracle-top-10-sql-waits.html




col INST_ID for 99
col USERNAME for a20
col SQL_ID for a13
col TEXT80 for a80
col CONTENTS for a20 just l
col SEGTYPE for a20
col kill for a17
select /*+ ORDERED*/
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill,
s.con_id ,
    --   tu.INST_ID,
       tu.USERNAME,
       NVL(s.SQL_ID, s.prev_sql_id) SQL_ID,
       substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80) as TEXT80,
       tu.CONTENTS,
       tu.SEGTYPE,
       count(tu.SEGBLK#)                     SEG_COUNT,
       count(distinct tu.SESSION_ADDR) PROC_COUNT,
       round(sum(tu.BLOCKS * t.BLOCK_SIZE) / 1024 / 1024) size_MB
  from gv$tempseg_usage tu, gv$session s, dba_tablespaces t, gv$sqlarea sa
 where tu.SESSION_ADDR = s.SADDR
   and tu.INST_ID = s.INST_ID
   and s.SQL_ID = sa.SQL_ID
   and s.INST_ID = sa.INST_ID
   and tu.TABLESPACE = t.TABLESPACE_NAME
-- and s.SQL_ID = '1'
 group by 
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' ,
 s.con_id ,
-- tu.INST_ID,
          tu.USERNAME,
          NVL(s.SQL_ID, s.prev_sql_id),
          substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80),
          tu.CONTENTS,
          tu.SEGTYPE
 order by sum(tu.BLOCKS) desc
/




define sql_id='XXXXX'
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'&sql_id', cursor_child_no => 0, FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));





   set linesize 300 
   col sql_text for a30
   col kill for a18
   select --''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||''''
   kill,t.con_id,to_char(t.sample_time,'DD-MON-YYYY HH24:MI:SS') sample_time, t.sql_id, t.temp_mb, t.temp_diff, substr(s.sql_text,1,30) sql_text
  from (
        select ''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||'''' kill,con_id,
               --'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
               trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
               , row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
          from (
                select sample_time,session_id,session_serial#,sql_id,inst_id,con_id,temp_space_allocated/1024/1024 temp_mb, 
                       temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
                 --from dba_hist_active_sess_history 
                 from gv$active_session_history
                where 1 = 1 
                -- session_id=par1 
                -- and session_serial#=par2
				and sql_id is not null
               )
         group by ''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||inst_id ||'''' ,con_id,
                  trunc(sample_time),
                  sql_id
       ) t
  left join gv$sqlarea s
    on s.sql_id = t.sql_id
 where 1 = 1
   and rn <=10
   and sample_time >= trunc(sysdate) - 2                 
 order by temp_mb desc, sample_time desc;
 
===


col gb for 99999.99
col USERNAME for a20
SELECT /*+ parallel(8) */ username, sql_id,
       MAX(temp_space_allocated) / (1024 * 1024 * 1024) GB
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
  JOIN DBA_USERS
    on DBA_HIST_ACTIVE_SESS_HISTORY.user_id = DBA_USERS.user_id
 WHERE sample_time > SYSDATE - 7
   AND temp_space_allocated >  1*(1024 * 1024 * 1024) /* 1 GB */
 GROUP BY username, sql_id
 ORDER BY GB DESC;

===


COLUMN module format A20
COLUMN sql_opname format A20
COLUMN etime_secs FORMAT 999,999.9
COLUMN etime_mins FORMAT 999,999.9
COLUMN user_id FORMAT 999999
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 99999
COLUMN username FORMAT A25
COLUMN inst_id FORMAT 99
COLUMN sql_opname FORMAT A10
COLUMN sql_id FORMAT A13
COLUMN sql_exec_id FORMAT 9999999999
COLUMN max_temp_mb FORMAT 999,999,999
COLUMN sql_start_time FORMAT A26
COLUMN sql_end_time FORMAT A26
 col MAX_TEMP_GB for 9999.99
 
SELECT ASH.inst_id,
  ASH.user_id,
  ASH.session_id sid,
  ASH.session_serial# serial#,
  ASH.sql_id,
  ASH.sql_exec_id,
  ASH.sql_opname,
  ASH.module,
  MIN(sample_time) sql_start_time,
  MAX(sample_time) sql_end_time,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
  MAX(temp_space_allocated)/(1024*1024*1024) max_temp_gb
FROM gv$active_session_history ASH
WHERE ASH.session_type = 'FOREGROUND'
AND ASH.sql_id        IS NOT NULL
--AND sample_time BETWEEN to_timestamp('11-04-2023 00:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('11-04-2023 02:00', 'DD-MM-YYYY HH24:MI')
  --and  ASH.sql_id = 
GROUP BY ASH.inst_id,
  ASH.user_id,
  ASH.session_id,
  ASH.session_serial#,
  ASH.sql_id,
  ASH.sql_opname,
  ASH.sql_exec_id,
  ASH.module
HAVING MAX(temp_space_allocated)/(1024*1024*1024)  >2  ---Gb
;


******************************************************

 
column  today noprint new_value strtoday
select  to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual;
column  database_name noprint new_value strdatabasename
select name database_name from v$database ;
ttitle left '____________________________________________________________________________________________________________________' -
skip 2 center    strtoday -
skip   center    strdatabasename -
skip   center   'tablespace report threshold 85%' -
skip left   '______________________________________________________________________________________________________________________' -
skip left   ''


VARIABLE value NUMBER
SELECT  value into :value FROM   v$parameter WHERE   name = lower('DB_BLOCK_SIZE');

print value


VARIABLE value1 NUMBER
SELECT  to_number(value) into :value1 FROM   v$parameter WHERE   name = lower('DB_BLOCK_SIZE');
		
define value1=8192
set head on  pagesize 300 linesize 200 numf 999999.99
col tablespace_name for a28
col status          for a20
col pdb_name        for a15
col CON_ID for 9999
select 
tb.con_id,
nvl(pdb_name,'CDB$ROOT') PDB_NAME,
pdb.status,
tablespace_name tablespace_name ,
round(tb.TABLESPACE_SIZE * &value1 /(1024*1024*1024),2)  					TABLESPACE_SIZE_GB,
round(tb.USED_SPACE * &value1 /(1024*1024*1024),2)       					USED_SPACE_GB,
round((tb.TABLESPACE_SIZE - tb.USED_SPACE) * &value1 /(1024*1024*1024),2) 	TABLESPACE_FREE_SIZE_GB,
trunc(used_percent)                     "USED_PERCENT%", 
case when ((used_percent) > 85.00) then '---(>85.00)% full ##'
                                   else 'good' end  as "status"
from cdb_tablespace_usage_metrics tb,cdb_pdbs pdb
where 1=1  
and tb.con_id= pdb.con_id(+)
-- and pdb.con_id=3
-- and used_percent >1
and tablespace_name like 'TEMP%'
order by 1,7 desc  ;



	
alter session set nls_date_format='dd-mm-yyyy hh24:mi'; 
set linesize 500 pagesize 300	
col sql_text for a50 
col username for a20
col OSUSER for a15
col kill for a17
	 SELECT
	  ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''  kill,
	  a.con_id,
	 sysdate,
           NVL(a.username, 'SUMMARY') username,
          -- a.sid,
         --  a.serial#,
		   a.osuser,
           a.program,
           a.sql_id,
           a.sql_child_number,
           a.sql_exec_start,
           q.plan_hash_value,
           b.tablespace,
           b.segtype,
           sum(b.blocks) as BLOCKS,
           substr(q.sql_text,1,50) sql_text
      FROM gv$session a, gv$tempseg_usage b, gv$sql q
     WHERE a.saddr = b.session_addr
       and q.address = a.sql_address
       and q.inst_id = a.inst_id
       and q.hash_value = a.sql_hash_value
       and q.CHILD_NUMBER = a.SQL_CHILD_NUMBER
       and a.INST_ID = b.INST_ID
     GROUP by GROUPING SETS((''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''',a.con_id, a.username,  a.osuser, a.program, a.sql_id, a.sql_child_number, a.sql_exec_start, q.sql_text, q.plan_hash_value, b.tablespace, b.segtype, sysdate),(sysdate));
	
	

==

ash_io_waits_temp.sql


 
 define 1='waits'
 --define 1='reqs'
 --define 1='blocks'
 define 2=20
 define 3="sample_time > sysdate - 1/24"

set linesize 500
set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col SQL_PROCESS for a13
col "event(waits:requests:blocks)" for a100
col "waits%"  for a7
col "reqs%"   for a7
col "blocks%" for a7
col inst_id for 9999 hea #i
col PROGRAMS for a60 HEADING 'PROGRAMS BY TYPES                                           '
col t0 for 999
col MIN_SAMPLE_TIME for a22
col MAX_SAMPLE_TIME for a22

with ash as (select * from Gv$active_session_history where &3)
, log_block as (select value / (select max(blocksize) from v$log) as ratio from v$parameter where name = 'db_block_size')
select * from (
    select --inst_id,
           SQL_PROCESS,
           SUM(WAIT_COUNT),
           to_char(RATIO_TO_REPORT(SUM(WAIT_COUNT)) OVER() * 100, '990.99') AS "waits%",
           SUM(REQUESTS),
           to_char(RATIO_TO_REPORT(SUM(REQUESTS)) OVER()   * 100, '990.99') AS "reqs%",
           SUM(BLOCKS),
           to_char(RATIO_TO_REPORT(SUM(BLOCKS)) OVER()     * 100, '990.99') AS "blocks%",
           decode(nvl(upper('&&1'), 'BLOCKS')
                                  , 'WAITS' , rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by WAIT_COUNT desc), '; ')
                                  , 'REQS'  , rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by REQUESTS desc), '; ')
                                  , 'BLOCKS', rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by BLOCKS desc), '; ')
                                            , rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by BLOCKS desc), '; '))
--           rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by WAIT_COUNT desc), '; ')
            as "event(waits:requests:blocks)"
--,
--            EXECS,          
--            MIN_SAMPLE_TIME,
--            MAX_SAMPLE_TIME 
      from (select --inst_id,
                   SQL_PROCESS,
                   event,
                   sum(WAIT_COUNT)                   as WAIT_COUNT,
                   sum(WAIT_COUNT * REQS_PER_WAIT)   as REQUESTS,
                   round(sum(WAIT_COUNT * BLOCKS_PER_WAIT) / decode(substr(event, 1, 8), 'log file', log_block.ratio, 1)) as BLOCKS
--,
--                   EXECS,          
--                   MIN_SAMPLE_TIME,
--                   MAX_SAMPLE_TIME 
              from (select count(*) as WAIT_COUNT,
                           nvl(sql_id,case when REGEXP_INSTR(program, '\([A-Z]...\)') = 0 then '(USER)'
                                           when REGEXP_INSTR(program, '\(ARC.\)')     > 0 then '(ARC.)'
                                           when REGEXP_INSTR(program, '\(O...\)')     > 0 then '(O...)'
                                           when REGEXP_INSTR(program, '\(P...\)')     > 0 then '(P...)'
                                           else REGEXP_REPLACE(REGEXP_SUBSTR(program, '\([^\)]+\)'), '([[:digit:]])', '.')
                                           end) as SQL_PROCESS,
                           event,
                           case when p2text = 'blocks' then p2
                                when p3text in ('blocks','block cnt') then p3
                                when p1text = 'requests' then p1  
                                else 1
                           end                                                                                            as BLOCKS_PER_WAIT,
                           case when p3text='requests' then p3 when p1text='requests' then p1 else 1 end                  as REQS_PER_WAIT,
                           --inst_id,
                           count(distinct sql_exec_id)                                                                    as EXECS,
                           to_char(min(SAMPLE_TIME),'DD.MM.YYYY HH24:MI:SS')                                              as MIN_SAMPLE_TIME,
                           to_char(max(SAMPLE_TIME),'DD.MM.YYYY HH24:MI:SS')                                              as MAX_SAMPLE_TIME
                     -- from ash --Gv$active_session_history
                  from Gv$active_session_history     --
                     where 
                        wait_class in ('User I/O','System I/O') and
                        session_state = 'WAITING'
                     group by nvl(sql_id,case when REGEXP_INSTR(program, '\([A-Z]...\)') = 0 then '(USER)'
                                         when REGEXP_INSTR(program, '\(ARC.\)')     > 0 then '(ARC.)'
                                         when REGEXP_INSTR(program, '\(O...\)')     > 0 then '(O...)'
                                         when REGEXP_INSTR(program, '\(P...\)')     > 0 then '(P...)'
                                         else REGEXP_REPLACE(REGEXP_SUBSTR(program, '\([^\)]+\)'), '([[:digit:]])', '.')
                                         end),
                              event,
                              case when p2text = 'blocks' then p2
                                   when p3text in ('blocks','block cnt') then p3
                                   when p1text = 'requests' then p1  
                                   else 1
                              end,
                              case when p3text='requests' then p3 when p1text='requests' then p1 else 1 end
                              --, inst_id
)
                 , log_block
             group by --inst_id,
                      SQL_PROCESS,
                      event,
                      log_block.ratio
--,
--                      EXECS,                                
--                      MIN_SAMPLE_TIME,
--                      MAX_SAMPLE_TIME
             )
     group by --inst_id,
              SQL_PROCESS
--, EXECS, MIN_SAMPLE_TIME, MAX_SAMPLE_TIME
     order by decode(nvl(upper('&&1'),'BLOCKS'), 'WAITS', SUM(WAIT_COUNT), 'REQS', SUM(REQUESTS), 'BLOCKS', SUM(BLOCKS), SUM(BLOCKS)) desc
) where rownum <= nvl('&2', 10)
/
set feedback on echo off VERIFY ON


=====



set linesize 500 pagesize 300
col STAR for a20
select SQL_ID,
       TEMP_MB,
       percent,
       rpad('*',percent*10/100,'*') star
from(select SQL_ID,
            sum(DELTA_TEMP_MB) TEMP_MB ,
            (ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
     from(select SESSION_ID,
                 SESSION_SERIAL#,
                 sample_id,
                 SQL_ID,
                 SAMPLE_TIME,
                 IS_SQLID_CURRENT,
                 SQL_CHILD_NUMBER,
                 temp_space_allocated,
                 greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
          from gv$active_session_history
         where IS_SQLID_CURRENT='Y'
         --  and sample_time > sysdate-1
		   and  sample_time > sysdate - interval '60' minute
         order by 1,2,3,4
         )
group by sql_id
having sum(DELTA_TEMP_MB) > 0
)
where rank < 11
order by rank;
/






CTAS Query Performance on Wait Event 'Direct Path Read Temp'/'Direct Path Write Temp' (Doc ID 2571038.1)

 May need to increase _smm_isort_cap

If increasing PGA does not help this parameter, "_smm_isort_cap", may need to be increased from 100mb to 180mb in order to
 prevent a large sort from writing 10 GB to temporary tablespace.
alter system set "_smm_isort_cap" = 184320;





Oracle DBA

anuj blog Archive