Some Useful DBA Queries Related to TEMP Usage, Free Blocks, Temp tablespace groups, Block Wise Check, Free Extents, Segments etc
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 !