Scripts related to temp tablespace

Scripts related to temp tablespace

• 1.
  scripts related totemp tablespace 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, 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; 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 "ExtManag", 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 * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%'; select * 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; 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"
• 2.
  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; SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_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 '/u01/app/oracle/oradata/prim/temp02.dbf' size 1000m; alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/prim/temp04.dbf' size 1800M autoextend on maxsize 1800M; To resize the tempfile in Temp Tablespace:- alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' resize 250M; alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' autoextend on maxsize 1800M; alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/prim/temp05.dbf' size 1800m reuse; To find Sort Segment Usage by Users select username,sum(extents) "Extents",sum(blocks) "Block" from v$sort_usage
• 3.
  group by username; Tofind 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,
• 4.
  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; I used these queries to check some settings: -- 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 ; -- List all schemas temp tablespaces and default tablespaces?: 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 ; TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN ------------------------------ --------- --------- ---------- SYSTEM ONLINE PERMANENT LOCAL SYSAUX ONLINE PERMANENT LOCAL UNDOTBS1 ONLINE UNDO LOCAL TEMP ONLINE TEMPORARY LOCAL USERS ONLINE PERMANENT LOCAL EXAMPLE ONLINE PERMANENT LOCAL SOUMYATBS ONLINE PERMANENT LOCAL Show number of tables in the TEMP tablespace:- 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 10g onwards 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
• 5.
  AND c.address= a.sql_address ANDc.hash_value = a.sql_hash_value AND (b.blocks*d.block_size)/1048576 > 1024 ORDER BY b.tablespace, 6 desc;
• 6.
  AND c.address= a.sql_address ANDc.hash_value = a.sql_hash_value AND (b.blocks*d.block_size)/1048576 > 1024 ORDER BY b.tablespace, 6 desc;