Oracle DBA Support info@awator.net

Scripts related to TEMP tablespaces

To check instance-wise total allocated, total used TEMP for both rac and non-rac
set lines 152

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,


from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents=’TEMPORARY’) and

Total Used and Total Free Blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;
Another Query to check TEMP USAGE
col name for a20

SELECT d.status “Status”, d.tablespace_name “Name”, d.contents “Type”, d.extent_management


TO_CHAR(NVL(a.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(t.bytes / a.bytes * 100, 0), ‘990.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’;
Temporary Tablespace groups

select tablespace_name,contents from dba_tablespaces where tablespace_name like ‘%TEMP%’;

select * from dba_tablespace_groups;

Block wise Check
select sum(free_blocks) from gv$sort_segment where tablespace_name = ‘TEMP’;

To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as “percent used”

from (select sum(used_blocks) tot_used_blocks

from v$sort_segment where tablespace_name=’TEMP’) s,

(select sum(blocks) total_blocks

from dba_temp_files where tablespace_name=’TEMP’) f;
To check Used Extents ,Free Extents available in Temp Tablespace
SELECT 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

select 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

size_m, 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

WHERE (t.file_id (+)= d.file_id)

AND (d.file_id = v.file#);
Additional checks
select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;


Changing the default temporary Tablespace

SQL> alter database default temporary tablespace TEMP;

Database altered.
To add tempfile to Temp Tablespace
alter tablespace  temp  add tempfile ‘&tempfilepath’ size 1800M;
alter 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;
To resize the  tempfile in Temp Tablespace
alter database tempfile ‘/u02/oradata/TESTDB/temp01.dbf’ resize 250M

alter database tempfile ‘/SID/oradata/data02/temp12.dbf’ autoextend on maxsize 1800M;

alter 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”

from v$sort_usage

group by username;
To find Sort Segment Usage by a particular User
SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks

FROM v$session s, v$sort_usage u

WHERE s.saddr=u.session_addr

order by u.blocks desc;
To find Total Free space in Temp Tablespace
select ‘FreeSpace  ‘ || (free_blocks*8)/1024/1024 ||’ GB’  from v$sort_segment where tablespace_name=’TEMP’;

select 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%’
To find  Total Space Allocated for Temp Tablespace
select ‘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

select * from (select s.sid, s.status, s.sql_hash_value sesshash, u.SQLHASH sorthash, 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 from v$sort_usage u, v$session s, v$parameter p

where u.session_addr = s.saddr and p.name = ‘db_block_size’

group by 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) where rownum < 11;
Displays the amount of IO for each tempfile
SELECT 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   v$tempstat f,v$tempfile t

WHERE  t.file# = f.file# ORDER BY 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, i.inst_id,i.host_name

FROM 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;


List all database files and their tablespaces:

select  file_name, tablespace_name, status ,bytes   /1000000  as MB ,maxbytes/1000000  as MB_max

from dba_data_files ;
What temporary tablespace is each user using?:

select username, temporary_tablespace, default_tablespace from dba_users ;
List all tablespaces and some settings:

select tablespace_name, status, contents, extent_management

from dba_tablespaces ;


Show number of tables in the TEMP tablespace – SHOULD be 0:

select count(*)  from dba_all_tables

where tablespace_name = ‘TEMP’ ;
Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly – note that you’ll probably need to connect internal in order to see the sys_objects view:
Shows all objects which exist in the TEMP tablespace – should get — NO rows for this:

column owner        format a20

column object_type  format a30

column object_name  format a40

select o.owner  ,o.object_name ,o.object_type

from sys_objects s ,dba_objects o ,dba_data_files df

where df.file_id = s.header_file and o.object_id = s.object_id and df.tablespace_name = ‘TEMP’ ;
Identifying WHO is currently using TEMP Segments
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;

For More information

contact info@awator.net



Copyright (c) 1998-2014