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 200select 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
;-- With Con_id set linesize 500 pagesize 300 DEF 1="TIMESTAMP'2024-04-30 18:00:00'" DEF 2="TIMESTAMP'2024-04-30 18:45:00'" 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#, con_id, 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 &1 AND &2 --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#, con_id, 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; /
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
set lines 300 pagesize 300
col END_TIME for a21
col PGA_ALLOCATED_GB format 999999999
col TEMP_ALLOCATED_GB format 999999999
col READ_IO_BYTES for 9999999999999999
select sql_id,sql_plan_hash_value,TOP_LEVEL_SQL_ID,
starting_time,
end_time,
(EXTRACT(HOUR FROM run_time) * 3600
+ EXTRACT(MINUTE FROM run_time) * 60
+ EXTRACT(SECOND FROM run_time)) run_time_sec,
READ_IO_BYTES,
PGA_ALLOCATED/1024/1024 PGA_ALLOCATED_MB,
TEMP_ALLOCATED/1024/1024 TEMP_ALLOCATED_MB
from (
select
sql_id,sql_plan_hash_value,TOP_LEVEL_SQL_ID,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time,
sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,
sum(DELTA_PGA) PGA_ALLOCATED,
sum(DELTA_TEMP) TEMP_ALLOCATED
from
(
select sql_id,
sql_plan_hash_value,
TOP_LEVEL_SQL_ID,
sample_time,
sql_exec_start,
DELTA_READ_IO_BYTES,
sql_exec_id,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,
greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP
from
dba_hist_active_sess_history
where
-- sample_time >= to_date ('2015/10/01 00:00:00','YYYY/MM/DD HH24:MI:SS')
-- and sample_time < to_date ('2015/10/16 03:10:00','YYYY/MM/DD HH24:MI:SS')
and sql_exec_start is not null
and IS_SQLID_CURRENT='Y'
)
group by sql_id,sql_plan_hash_value,TOP_LEVEL_SQL_ID,SQL_EXEC_ID,sql_exec_start
)
where 1=1-- and sql_id = '&sql_id' order by 3 asc;
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;
prompt "Top sql_id's that consumed PGA:"
DEF 3="TIMESTAMP'2024-04-27 10:00:00'"
DEF 4="TIMESTAMP'2024-04-27 10:15:00'"
select *
from (select instance_number, sql_id, max(pga_sum_mb) pga_max
from (select instance_number, sample_time, sql_id, round(sum(nvl(pga_allocated, 0))/1024/1024) pga_sum_mb
from dba_hist_active_sess_history
where 1=1
and sample_time > sysdate -15
--AND sample_time BETWEEN &3 AND &4
group by instance_number, sample_time, sql_id)
group by instance_number, sql_id
order by pga_max desc)
where rownum <= 10;
Prompt "Top sql_id's that consumed temporary segments"
select *
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
from dba_hist_active_sess_history
where 1=1
--AND sample_time BETWEEN &3 AND &4
and sample_time > sysdate -15
group by instance_number, sample_time, sql_id)
group by instance_number, sql_id
order by temp_max desc)
where rownum <= 10;
set linesize 500 pagesize 300
col INST_ID for a7
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*/
-- tu.INST_ID,
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
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 ||'''',
tu.USERNAME,
NVL(s.SQL_ID, s.prev_sql_id),
substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80),
tu.CONTENTS,
tu.SEGTYPE
having round(sum(tu.BLOCKS * t.BLOCK_SIZE) / 1024 / 1024) >2
order by sum(tu.BLOCKS) desc
/
set timing on time on linesize 150 pagesize 600
define define sql_id='3p3mf64j54uhx'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&sql_id',format=>'ALLSTATS LAST +outline'));
set linesize 160 pagesize 300
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'3p3mf64j54uhx',
format=>'ALLSTATS LAST +cost +bytes'));
define sql_id='3p3mf64j54uhx'
SET LINESIZE 300 PAGESIZE 1000
SELECT T.* FROM GV$SQL S, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER)) T
WHERE 1=1
--AND S.SQL_ID IN (SELECT SQL_ID FROM GV$SESSION WHERE SQL_ID IS NOT NULL AND USERNAME='USER' )
AND SQL_ID='&SQL_ID'
;
set linesize 300
col CON_NAME for a15
col DB_NAME for a15
col TYPE for a18
select
decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE ,
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;
34 comments:
Oracle Temp file info
set numf 999.99 linesize 200 pagesize 200
col FILE_NAME for a50
col AUTOEXTENSIBLE for a15
SELECT tablespace_name, file_name, bytes/1024/1024/1024 Gb ,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 Max_Gb FROM dba_temp_files
WHERE tablespace_name in (select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY')
http://anuj-singh.blogspot.co.uk/2012/05/oracle-temp-tablespace-info.html
set numf 999.99 linesize 200 pagesize 200
col FILE_NAME format a50
SELECT tablespace_name, file_name, bytes/1024/1024/1024 Gb FROM dba_temp_files WHERE tablespace_name like '%TEMP%' ;
explian plan from sga
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id',0,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +PREDICATE +COST +BYTES'));
Set event for sql info
ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';
To turn trace off
SQL> ALTER system SET EVENTS '1652 TRACE NAME ERRORSTACK OFF';
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,sql_text,sum((BLOCKS)*16)/1024 "MB" from temp_usage
group by kill,sql_id,username,sql_text
order by 4 desc
;
undefine sql_id
set linesize 200 long 4000
col sql_text for a150 WORD_WRAPPED
with sql_all as
( select sql_id,dbms_lob.substr(sql_text,3999,1) sql_text from dba_hist_sqltext where sql_id='&&sql_id'
union
select sql_id,dbms_lob.substr(SQL_FULLTEXT,3999,1) from gv$sql where sql_id='&&sql_id'
)
select sql_id ,sql_text from sql_all ;
Temp space usage from history ...
select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) 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 sql_id;
if error on diffrtent tablespace
ORA-1652: unable to extend temp segment by 128 in tablespace XXXX_INDEX
set pages 500 lines 1000
col file_name format a50
col AUTOEXTENSIBLE format a18
col BIGFILE format a15
col ENCRYPTED format a10
col TABLESPACE_NAME format a25
SELECT b.BLOCK_SIZE, a.TABLESPACE_NAME,a.FILE_NAME, trunc(a.BYTES/(1024*1024*1024),2) SIZE_GB, trunc(a.MAXBYTES/(1024*1024*1024),2) Max_GB , a.AUTOEXTENSIBLE,BIGFILE,ENCRYPTED,COMPRESS_FOR,CONTENTS,a.STATUS from DBA_DATA_FILES a ,DBA_TABLESPACES b
where 1=1
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME in ('XXXX_INDEX')
order by 2;
check objects detail
set linesize 200 pagesize 200
col SEGMENT_NAME for a25
SELECT * FROM ( SELECT segment_name, bytes/1024/1024/1024 AS size_g, extents
FROM dba_segments
WHERE tablespace_name = 'XXXX_INDEX'
ORDER BY 2 DESC) t
WHERE ROWNUM <10;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE temp ADD TEMPFILE '/oracle/product/TEMP01.dbf' SIZE 1g AUTOEXTEND ON MAXSIZE UNLIMITED;
11g New Feature temporary tablespace view.
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;
TABLESPACE_NAME TABLESPACE_SIZE_GB ALLOCATED_SPACE_GB FREE_SPACE_GB TotalUsed% ATTENTION
------------------------------ ------------------ ------------------ ------------- ---------- ------------------------
TEMP 56.99 56.9 51.65 9.22 Good :-)
Temp tablespace Metadata
set long 5000 linesize 300 pagesize 200
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb where CONTENTS='TEMPORARY' ;
ORA-25153: Temporary Tablespace is Empty
check temp files
set linesize 300
col TABLESPACE_NAME for a25
col FILE_NAME for a70
select tb.tablespace_name, bytes/1024/1024/1024 Gb,tf.file_name from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';
select temporary_tablespace,username from dba_users where temporary_tablespace='TEMP';
no rows selected
select temporary_tablespace,username from dba_users where temporary_tablespace<>'TEMP';
TEMPORARY_TABLESPACE USERNAME
------------------------------ ---------------------
TEMP1 SYS
TEMP1 SYSTEM
TEMP1 XS$NULL
TEMP1 OJVMSYS
TEMP1 LBACSYS
TEMP1 OUTLN
TEMP1 SYS$UMF
set linesize 300 pagesize 300
col program for a30
col username for a15
col osuser for a12
col module for a15
col tablespace for a15
col sort_ops for 999999
col kill for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , s.username, 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 1=1
and 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
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' , s.username, s.osuser, p.spid, s.module, s.program, tbs.block_size, t.tablespace
order by mb_used;
set pagesize 300
select 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;
set linesize 300
col USERNAME for a15
col HOST_NAME for a30
select tablespace_name,
trunc((free_blocks*8)/1024/1024,2) FreeSpaceGB,
trunc((used_blocks*8)/1024/1024,2) UsedSpaceGB,
trunc((total_blocks*8)/1024/1024,2) TotalSpaceGB,
i.instance_name,i.host_name,
-- CURRENT_USERS
USERNAME
from gv$sort_segment ss,gv$instance i ,dba_users
where ss.tablespace_name in (select tablespace_name from dba_tablespaces
where contents='TEMPORARY')
and CURRENT_USERS=USER_ID
and i.inst_id=ss.inst_id;
select TABLESPACE_NAME,FILE_ID,trunc(sum(BYTES_USED/1024/1024/1024),2) GB_USED,trunc(sum(BYTES_FREE/1024/1024/1024),2) Free_Gb
from gv$temp_space_header
group by TABLESPACE_NAME,FILE_ID;
Temporary segments in a tablespace which are not currently locked.
to drops them.
select ts# from sys.ts$ where name = 'TEMP' and online$ != 3; <<<< change temp tablespace name
TS#
----------
13 <<< + 1 =14
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';
alter session set events 'immediate trace name DROP_SEGMENTS level 14'; <<<<< in my case
http://anuj-singh.blogspot.com/2010/11/oracle-temp-segment-usage-per-session.html
set lines 200
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i
where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY')
and i.inst_id=ss.inst_id;
SELECT INST_ID , tablespace_name, trunc(SUM (bytes_used/(1024*1024*1024)),2) total_gb , SUM (bytes_free/(1024*1024*1024)) free_gb FROM gv$temp_space_header
GROUP BY tablespace_name, INST_ID;
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
order by 1,2
;
col kill for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.sql_id, segfile#, round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb
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 ||'''',s.sql_id, segfile#
order by 1,2
;
select distinct TEMPORARY_TABLESPACE from dba_users;
set linesize 300
select * from dba_tablespaces where tablespace_name like 'TEMP%' ;
set numf 9999999999999
select tablespace_name, file_id, bytes_used/1024/1024/1024 GB , bytes_free from v$temp_space_header;
col FILE_NAME for a30
select * from dba_temp_files where tablespace_name like 'TEMP%';
select tablespace_name, sum(bytes/1024/1024) GB from dba_temp_files
where tablespace_name like 'TEMP%' group by tablespace_name;
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 like '%TEMP%';
select sum(free_blocks) from gv$sort_segment where tablespace_name in (select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY');
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;
Temp datafile info !!!
set pages 500 lines 200 term off
set numf 9999999999999999
col fn new_value fname
select 'a'||(max(length(file_name))+1) fn from dba_temp_files;
col file_name for a40
col autoextensible for a18
col bigfile for a15
col encrypted for a10
col tablespace_name for a15
col compress_for for a10
col file_name for &fname heading "File Name"
select file_id, b.block_size, a.tablespace_name,a.file_name
--, a.bytes
, trunc(a.bytes/(1024*1024*1024),2) size_gb
, trunc(a.maxbytes/(1024*1024*1024),2) max_gb , a.autoextensible,bigfile,encrypted,compress_for,contents,a.status from dba_temp_files a ,dba_tablespaces b
where 1=1
and a.tablespace_name=b.tablespace_name
and a.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY' )
order by 1;
set linesize 300 pagesize 300
col sql_id for 999999999999999
col user_id for 999999999999999
col USERNAME for a20
col kill for a15
select ''''||a.SESSION_ID ||','|| a.SESSION_SERIAL#||',@'||a.INSTANCE_NUMBER ||'''' kill, a.user_id, b.username, a.sql_id,sum(temp_space_allocated/1024/1024) as TEMP_MB ,b.temporary_tablespace as TEMPTS from dba_hist_active_sess_history a, dba_users b
where trunc(SQL_EXEC_START) > trunc(sysdate -1)
and 1=1
--and temp_space_allocated/1024/1024/1024 > 1
and b.user_id = a.user_id
and USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by ''''||a.SESSION_ID ||','|| a.SESSION_SERIAL#||',@'||a.INSTANCE_NUMBER ||'''', a.user_id, a.sql_id, b.username, b.temporary_tablespace
order by TEMP_MB
/
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 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-1
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
-- and sql_id='&Sql_id'
and u.USER_ID=d.USER_ID
group by d.sql_id,u.USER_ID,u.USERNAME ,d.PROGRAM,d.MODULE,d.ACTION,d.MACHINE ;
add 10 tempfile
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA','+DATA','+DATA','+DATA','+DATA','+DATA','+DATA','+DATA','+DATA','+DATA','+DATA01' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited;
set pages 100 lines 500
col FILE_NAME format a50
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d, v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;
select d.temporary_tablespace,
d.local_temp_tablespace, -- 12.2 only
u.spare9,
count(*) CT
from dba_users d
inner join sys.user$ u on d.username = u.name
group by
d.temporary_tablespace,
d.local_temp_tablespace, -- 12.2 only
u.spare9
/
alter user TEST LOCAL TEMPORARY TABLESPACE TEMP
Or
select 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' from dba_users where username not in ('XS$NULL') and local_temp_tablespace = 'SYSTEM';"
Post a Comment