SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
-- Program/user/sessions etc are currently using your temp space
SELECT b.TABLESPACE, b.segfile#, b.segblk#, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb, a.SID, a.serial#, a.username,
a.osuser, a.program, a.status FROM v$session a, v$sort_usage b, v$process c, v$parameter p WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;
select * from V$TEMPSEG_USAGE;
-- Max used size
select * from V$SORT_SEGMENT ;
NOTE : Oracle 9i does not release allocated TEMP segments until you shutdown the database.DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
-- How much is being used by Queries
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
--AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;
--Who all are using temporary tablespaces
select u.tablespace
, u.segfile#
, u.segblk#
, u.blocks
, s.sid
, s.serial#
-- , p.spid
, s.username
, s.osuser
, s.status
from v$session s
, v$sort_usage u
, v$process p
where s.saddr = u.session_addr
and s.paddr = p.addr
order by u.blocks desc;
---Actual/Current space used in temporary tablespace
select 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 v$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.tablespace_name, d.mb_total
---Sessions storing in Temp segments :
select (select username from v$session where saddr = session_addr) uname, v.* from v$sort_usage v;
***********
Total temporary space usage :
SELECT 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 v$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.tablespace_name, D.mb_total;
Temporary Space used by Session :
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
Temporary Space used by SQL
================================
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
select usage.snap_id,to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
from dba_hist_tbspc_space_usage usage,
v$tablespace tablespace,
dba_hist_snapshot snapshot,
v$parameter block_size
where usage.snap_id = snapshot.snap_id
and usage.tablespace_id = tablespace.ts#
and tablespace.name = 'TEMP'
and block_size.name = 'db_block_size'
order by snapshot.begin_interval_time
select 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
where
session_id=&1
and session_serial#=&2
order by sample_time asc
/
select 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 v$active_session_history
where
session_id=&1
and session_serial#=&2
order by sample_time asc
/
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.inst_id=D.inst_id
GROUP by a.inst_id,A.tablespace_name, D.mb_total;
SELECT S.INST_ID,S.sid || ',' || S.serial# sid_serial, S.username, S.osuser,
P.spid pid,
s.service_name,
--S.module,
--P.program,
T.segtype ,
SUM (T.blocks)* TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM gv$tempseg_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND s.inst_id=p.inst_id
and t.inst_id=p.inst_id
and s.inst_id=t.inst_id
AND T.tablespace = TBS.tablespace_name
having SUM (T.blocks) * TBS.block_size / 1024 / 1024>10
GROUP BY
s.inst_id,
S.sid,
S.serial#, S.username,
S.osuser, P.spid,
S.Service_name,
--S.module,
--P.program,
TBS.block_size, T.tablespace,segtype
ORDER BY mb_used;
11.2 onwards :
select * from (
select trunc(TEMP_SPACE_ALLOCATED/1024/1024) temp_mb,
trunc(PGA_ALLOCATED/1024/1024) pga_mb, cnt ,count(1s) ,
inst_id, session_id, session_serial#,
SQL_EXEC_START, SQL_EXEC_ID, TOP_LEVEL_SQL_ID, SQL_ID, IS_SQLID_CURRENT, SQL_PLAN_LINE_ID plan_line,
to_char(cast(SAMPLE_TIME as date), 'YYYY/MM/DD HH24:MI:SS') sample_time
from (
select ash.*,
count(*) over (partition by inst_id, session_id, session_serial#) cnt,
row_number() over (partition by inst_id, session_id, session_serial# order by TEMP_SPACE_ALLOCATED desc) rn
from gv$active_session_history ash
where TEMP_SPACE_ALLOCATED > 0)
where rn = 1
order by 1 desc
) where rownum <= 10;
select
trunc(TEMP_SPACE_ALLOCATED/1024/1024) temp_mb,
trunc(PGA_ALLOCATED/1024/1024) pga_mb, cnt ,count(10s),
SQL_EXEC_START, SQL_EXEC_ID, TOP_LEVEL_SQL_ID, SQL_ID, IS_SQLID_CURRENT, SQL_PLAN_LINE_ID plan_line,
instance_number, session_id, session_serial#
to_char(cast(SAMPLE_TIME as date), 'YYYY/MM/DD HH24:MI:SS') sample_time
from (
select ash.*,
count(*) over (partition by instance_number, session_id, session_serial#) cnt,
row_number() over (partition by instance_number, session_id, session_serial# order by TEMP_SPACE_ALLOCATED desc) rn
from dba_hist_active_sess_history ash
where TEMP_SPACE_ALLOCATED >= 1024 * 1024 * 1024 * 10 -- 10gb
and cast(SAMPLE_TIME as date) > trunc(sysdate - 5) -- last 5 days
)
where rn = 1
order by 1;
Other :
select a.name, b.value
from
v$statname a,
v$sesstat b,
v$session s,
v$sort_usage su
where a.statistic#=b.statistic#
and b.sid=s.sid
and s.saddr=su.session_addr;
-- Program/user/sessions etc are currently using your temp space
SELECT b.TABLESPACE, b.segfile#, b.segblk#, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb, a.SID, a.serial#, a.username,
a.osuser, a.program, a.status FROM v$session a, v$sort_usage b, v$process c, v$parameter p WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;
select * from V$TEMPSEG_USAGE;
-- Max used size
select * from V$SORT_SEGMENT ;
NOTE : Oracle 9i does not release allocated TEMP segments until you shutdown the database.DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
-- How much is being used by Queries
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
--AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;
--Who all are using temporary tablespaces
select u.tablespace
, u.segfile#
, u.segblk#
, u.blocks
, s.sid
, s.serial#
-- , p.spid
, s.username
, s.osuser
, s.status
from v$session s
, v$sort_usage u
, v$process p
where s.saddr = u.session_addr
and s.paddr = p.addr
order by u.blocks desc;
---Actual/Current space used in temporary tablespace
select 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 v$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.tablespace_name, d.mb_total
---Sessions storing in Temp segments :
select (select username from v$session where saddr = session_addr) uname, v.* from v$sort_usage v;
***********
Total temporary space usage :
SELECT 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 v$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.tablespace_name, D.mb_total;
Temporary Space used by Session :
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
Temporary Space used by SQL
================================
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
select usage.snap_id,to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
from dba_hist_tbspc_space_usage usage,
v$tablespace tablespace,
dba_hist_snapshot snapshot,
v$parameter block_size
where usage.snap_id = snapshot.snap_id
and usage.tablespace_id = tablespace.ts#
and tablespace.name = 'TEMP'
and block_size.name = 'db_block_size'
order by snapshot.begin_interval_time
select 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
where
session_id=&1
and session_serial#=&2
order by sample_time asc
/
select 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 v$active_session_history
where
session_id=&1
and session_serial#=&2
order by sample_time asc
/
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.inst_id=D.inst_id
GROUP by a.inst_id,A.tablespace_name, D.mb_total;
SELECT S.INST_ID,S.sid || ',' || S.serial# sid_serial, S.username, S.osuser,
P.spid pid,
s.service_name,
--S.module,
--P.program,
T.segtype ,
SUM (T.blocks)* TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM gv$tempseg_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND s.inst_id=p.inst_id
and t.inst_id=p.inst_id
and s.inst_id=t.inst_id
AND T.tablespace = TBS.tablespace_name
having SUM (T.blocks) * TBS.block_size / 1024 / 1024>10
GROUP BY
s.inst_id,
S.sid,
S.serial#, S.username,
S.osuser, P.spid,
S.Service_name,
--S.module,
--P.program,
TBS.block_size, T.tablespace,segtype
ORDER BY mb_used;
11.2 onwards :
select * from (
select trunc(TEMP_SPACE_ALLOCATED/1024/1024) temp_mb,
trunc(PGA_ALLOCATED/1024/1024) pga_mb, cnt ,count(1s) ,
inst_id, session_id, session_serial#,
SQL_EXEC_START, SQL_EXEC_ID, TOP_LEVEL_SQL_ID, SQL_ID, IS_SQLID_CURRENT, SQL_PLAN_LINE_ID plan_line,
to_char(cast(SAMPLE_TIME as date), 'YYYY/MM/DD HH24:MI:SS') sample_time
from (
select ash.*,
count(*) over (partition by inst_id, session_id, session_serial#) cnt,
row_number() over (partition by inst_id, session_id, session_serial# order by TEMP_SPACE_ALLOCATED desc) rn
from gv$active_session_history ash
where TEMP_SPACE_ALLOCATED > 0)
where rn = 1
order by 1 desc
) where rownum <= 10;
select
trunc(TEMP_SPACE_ALLOCATED/1024/1024) temp_mb,
trunc(PGA_ALLOCATED/1024/1024) pga_mb, cnt ,count(10s),
SQL_EXEC_START, SQL_EXEC_ID, TOP_LEVEL_SQL_ID, SQL_ID, IS_SQLID_CURRENT, SQL_PLAN_LINE_ID plan_line,
instance_number, session_id, session_serial#
to_char(cast(SAMPLE_TIME as date), 'YYYY/MM/DD HH24:MI:SS') sample_time
from (
select ash.*,
count(*) over (partition by instance_number, session_id, session_serial#) cnt,
row_number() over (partition by instance_number, session_id, session_serial# order by TEMP_SPACE_ALLOCATED desc) rn
from dba_hist_active_sess_history ash
where TEMP_SPACE_ALLOCATED >= 1024 * 1024 * 1024 * 10 -- 10gb
and cast(SAMPLE_TIME as date) > trunc(sysdate - 5) -- last 5 days
)
where rn = 1
order by 1;
Other :
select a.name, b.value
from
v$statname a,
v$sesstat b,
v$session s,
v$sort_usage su
where a.statistic#=b.statistic#
and b.sid=s.sid
and s.saddr=su.session_addr;