Some Useful DBA Queries Related to TEMP Usage, Free Blocks, Temp tablespace groups, Block Wise Check, Free Extents, Segments etc..
To check instance-wise total allocated,
total used TEMP for both rac and non-rac
total used TEMP for both rac and non-rac
set lines
152
152
col
FreeSpaceGB format 999.999
FreeSpaceGB format 999.999
col
UsedSpaceGB format 999.999
UsedSpaceGB format 999.999
col TotalSpaceGB
format 999.999
format 999.999
col
host_name format a30
host_name format a30
col
tablespace_name format a30
tablespace_name format a30
select
tablespace_name,
tablespace_name,
(free_blocks*8)/1024/1024
FreeSpaceGB,
FreeSpaceGB,
(used_blocks*8)/1024/1024
UsedSpaceGB,
UsedSpaceGB,
(total_blocks*8)/1024/1024
TotalSpaceGB,
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
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;
Total Used and Total Free Blocks
select
inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;
inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;
Another Query to check TEMP USAGE
col name for
a20
a20
SELECT
d.status “Status”, d.tablespace_name “Name”, d.contents
“Type”, d.extent_management
d.status “Status”, d.tablespace_name “Name”, d.contents
“Type”, d.extent_management
“ExtManag”,
TO_CHAR(NVL(a.bytes
/ 1024 / 1024, 0),’99,999,990.900′) “Size (M)”, TO_CHAR(NVL(t.bytes,
/ 1024 / 1024, 0),’99,999,990.900′) “Size (M)”, TO_CHAR(NVL(t.bytes,
0)/1024/1024,’99999,999.999′)
||’/’||TO_CHAR(NVL(a.bytes/1024/1024, 0),’99999,999.999′) “Used (M)”,
||’/’||TO_CHAR(NVL(a.bytes/1024/1024, 0),’99999,999.999′) “Used (M)”,
TO_CHAR(NVL(t.bytes
/ a.bytes * 100, 0), ‘990.00’) “Used %”
/ a.bytes * 100, 0), ‘990.00’) “Used %”
FROM
sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from
dba_temp_files group by
sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from
dba_temp_files group by
tablespace_name)
a,
a,
(select
tablespace_name, sum(bytes_cached) bytes from
tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool
group by tablespace_name) t
group by tablespace_name) t
WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
t.tablespace_name(+)
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
t.tablespace_name(+)
AND d.extent_management
like ‘LOCAL’ AND d.contents like ‘TEMPORARY’;
like ‘LOCAL’ AND d.contents like ‘TEMPORARY’;
Temporary Tablespace groups
SELECT *
FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
select
tablespace_name,contents from dba_tablespaces where tablespace_name like
‘%TEMP%’;
tablespace_name,contents from dba_tablespaces where tablespace_name like
‘%TEMP%’;
select *
from dba_tablespace_groups;
from dba_tablespace_groups;
Block wise Check
select
TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS,
FREE_BLOCKS from V$SORT_SEGMENT;
TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS,
FREE_BLOCKS from V$SORT_SEGMENT;
select
sum(free_blocks) from gv$sort_segment where tablespace_name = ‘TEMP’;
sum(free_blocks) from gv$sort_segment where tablespace_name = ‘TEMP’;
To Check
Percentage Usage of Temp Tablespace
Percentage Usage of Temp Tablespace
select
(s.tot_used_blocks/f.total_blocks)*100 as “percent used”
(s.tot_used_blocks/f.total_blocks)*100 as “percent used”
from (select
sum(used_blocks) tot_used_blocks
sum(used_blocks) tot_used_blocks
from
v$sort_segment where tablespace_name like ‘%TEMP%’) s,
v$sort_segment where tablespace_name like ‘%TEMP%’) s,
(select
sum(blocks) total_blocks
sum(blocks) total_blocks
from
dba_temp_files where tablespace_name like ‘%TEMP%’) f;
dba_temp_files where tablespace_name like ‘%TEMP%’) f;
To check Used Extents ,Free Extents
available in Temp Tablespace
available in Temp Tablespace
SELECT
tablespace_name, extent_size, total_extents, used_extents,free_extents,
max_used_size FROM v$sort_segment;
tablespace_name, extent_size, total_extents, used_extents,free_extents,
max_used_size FROM v$sort_segment;
To list all tempfiles of Temp Tablespace
col
file_name for a45
file_name for a45
select
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_temp_files order by file_name;
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_temp_files order by file_name;
SELECT
d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id,
d.bytes/1024/1024
d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id,
d.bytes/1024/1024
size_m
,
NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) *
100) pct_used
NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) *
100) pct_used
FROM
sys.dba_temp_files
d, v$temp_extent_pool t, v$tempfile v
d, v$temp_extent_pool t, v$tempfile v
WHERE
(t.file_id (+)= d.file_id)
(t.file_id (+)= d.file_id)
AND
(d.file_id = v.file#);
(d.file_id = v.file#);
Additional checks
select
distinct(temporary_tablespace) from dba_users;
distinct(temporary_tablespace) from dba_users;
select
username,default_tablespace,temporary_tablespace from dba_users order by
temporary_tablespace;
username,default_tablespace,temporary_tablespace from dba_users order by
temporary_tablespace;
SELECT *
FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
Changing the default temporary
Tablespace
Tablespace
SQL>
alter database default temporary tablespace TEMP;
alter database default temporary tablespace TEMP;
Database
altered.
altered.
To add tempfile to Temp Tablespace
alter
tablespace temp add tempfile ‘&tempfilepath’ size 1800M;
tablespace temp add tempfile ‘&tempfilepath’ size 1800M;
alter
tablespace temp add tempfile ‘/m001/oradata/SID/temp02.dbf’ size 1000m;
tablespace temp add tempfile ‘/m001/oradata/SID/temp02.dbf’ size 1000m;
alter
tablespace TEMP add tempfile ‘/SID/oradata/data02/temp04.dbf’ size 1800M
autoextend on maxsize 1800M;
tablespace TEMP add tempfile ‘/SID/oradata/data02/temp04.dbf’ size 1800M
autoextend on maxsize 1800M;
To resize the tempfile in Temp Tablespace
alter
database tempfile ‘/db01/oracle/SUPPT/db/apps_st/data/temp02.dbf’ resize 7000M;
database tempfile ‘/db01/oracle/SUPPT/db/apps_st/data/temp02.dbf’ resize 7000M;
alter
tablespace TEMP add tempfile ‘/SID/oradata/data02/temp05.dbf’ size 1800m reuse;
tablespace TEMP add tempfile ‘/SID/oradata/data02/temp05.dbf’ size 1800m reuse;
To find Sort Segment Usage by Users
select
username,sum(extents) “Extents”,sum(blocks) “Block”
username,sum(extents) “Extents”,sum(blocks) “Block”
from
v$sort_usage
v$sort_usage
group by
username;
username;
To find Sort Segment Usage by a
particular User
particular User
SELECT
s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM
v$session s, v$sort_usage u
v$session s, v$sort_usage u
WHERE
s.saddr=u.session_addr
s.saddr=u.session_addr
order by
u.blocks desc;
u.blocks desc;
To find Total Free space in Temp
Tablespace
Tablespace
select
‘FreeSpace ‘ ||
(free_blocks*8)/1024/1024 ||’ GB’ from
v$sort_segment where tablespace_name=’TEMP’;
‘FreeSpace ‘ ||
(free_blocks*8)/1024/1024 ||’ GB’ from
v$sort_segment where tablespace_name=’TEMP’;
select
tablespace_name , (free_blocks*8)/1024/1024
FreeSpaceInGB,
tablespace_name , (free_blocks*8)/1024/1024
FreeSpaceInGB,
(used_blocks*8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB
from
v$sort_segment where tablespace_name like ‘%TEMP%’
v$sort_segment where tablespace_name like ‘%TEMP%’
To find
Total Space Allocated for Temp Tablespace
Total Space Allocated for Temp Tablespace
select
‘TotalSpace ‘ || (sum(blocks)*8)/1024/1024 ||’ GB’ from dba_temp_files where
tablespace_name=’TEMP’;
‘TotalSpace ‘ || (sum(blocks)*8)/1024/1024 ||’ GB’ from dba_temp_files where
tablespace_name=’TEMP’;
Get 10 sessions with largest temp usage
cursor
bigtemp_sids is
bigtemp_sids is
select *
from (
from (
select
s.sid,
s.sid,
s.status,
s.sql_hash_value
sesshash,
sesshash,
u.SQLHASH
sorthash,
sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024)
mbused ,
mbused ,
sum(u.extents)
noexts,
noexts,
nvl(s.module,s.program)
proginfo,
proginfo,
floor(last_call_et/3600)||’:’||
floor(mod(last_call_et,3600)/60)||’:’||
mod(mod(last_call_et,3600),60)
lastcallet
lastcallet
from
v$sort_usage u,
v$sort_usage u,
v$session s,
v$parameter
p
p
where
u.session_addr = s.saddr
u.session_addr = s.saddr
and p.name =
‘db_block_size’
‘db_block_size’
group by
s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
s.sid,s.status,s.sql_hash_value,u.sqlhash,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)
order by 7
desc,3)
desc,3)
where rownum
< 11;
< 11;
Displays the amount of IO for each
tempfile
tempfile
SELECT
SUBSTR(t.name,1,50) AS file_name,
SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd
AS blocks_read,
AS blocks_read,
f.phyblkwrt
AS blocks_written,
AS blocks_written,
f.phyblkrd +
f.phyblkwrt AS total_io
f.phyblkwrt AS total_io
FROM v$tempstat f,v$tempfile t
WHERE t.file# = f.file#
ORDER BY
f.phyblkrd + f.phyblkwrt DESC;
f.phyblkrd + f.phyblkwrt DESC;
select *
from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program,
u.extents, ((u.blocks*8)/1024) as MB,
from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program,
u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM
gv$session s, gv$sort_usage u ,gv$instance i
gv$session s, gv$sort_usage u ,gv$instance i
WHERE
s.saddr=u.session_addr and u.inst_id=i.inst_id
order by MB DESC) a where rownum<10;
s.saddr=u.session_addr and u.inst_id=i.inst_id
order by MB DESC) a where rownum<10;
Thank you !
Recommended Posts
