temp tablespace usage check scripts

set pagesize 1000
set linesize 9999
col file_name for a60
col tablespace_name for a20
select file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE,INCREMENT_BY INC,MAXBYTES/1024/1024/1024 MAX_GB_CAN_EXTEND from dba_temp_files where TABLESPACE_NAME='&TBS_NAME'

---To check for held TEMP segments:
set pagesize 1000
set lines 9999
col TABLESPACE for a20
col USERNAME for a20
col OSUSER for a20
select
   srt.tablespace,
   srt.segfile#,
   srt.segblk#,
   srt.blocks,
   a.sid,
   a.serial#,
   a.username,
   a.osuser,
   a.status
from
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;
---select name,sum(bytes)/1024/1024/1024 gb from v$tempfile group by name;

---SELECT tablespace_name, bytes_used/1024/1024 used_mb, bytes_free/1024/1024 free_mb FROM v$temp_space_header;

---select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 GB,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 GB from dba_temp_files;

---select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS,MAX_BLOCKS from v$sort_segment;

----SELECT SUM (u.blocks * blk.block_size) / 1024 / 1024 "Mb. in sort segments"
, (hwm.MAX * blk.block_size) / 1024 / 1024 "Mb. High Water Mark"
FROM v$sort_usage u
, (SELECT block_size
FROM DBA_TABLESPACES
WHERE CONTENTS = 'TEMPORARY') blk
, (SELECT segblk# + blocks MAX
FROM v$sort_usage
WHERE segblk# = (SELECT MAX (segblk#)
FROM v$sort_usage)) hwm
GROUP BY hwm.MAX * blk.block_size / 1024 / 1024;

---------------Temp tablespace unable to extend
sqlplus '/as sysdba'
set linesize 9999
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024/1024, 0) "Size (Gb)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024/1024) "Used (Gb)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "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'

select extent_size,current_users,total_extents,used_extents,free_extents from v$sort_segment where tablespace_name='&temp_tbs_name';
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

set pagesize 1000
set lines 9999
col SQL_TEXT for a70
col USERNAME for a15
col OSUSER for a15
col TABLESPACE for a15
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks

set lines 9999
col USERNAME for a20
col TABLESPACE for a20
SELECT s.username, s.sid,  u.TABLESPACE, u.CONTENTS, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
==========================================

ALTER TABLESPACE TEMP_TS_dbname
   ADD TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname_01.dbf' SIZE 5g REUSE;
ALTER TABLESPACE TEMP_TS_dbname TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname_01.dbf' AUTOEXTEND ON NEXT 5g MAXSIZE 5g;

ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname.dbf' RESIZE 35g;
ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/temp_ts_dbname.dbf' autoextend on next 35g maxsiz2 35g;
ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/dbname_temp_01.dbf' RESIZE 27g;
ALTER DATABASE TEMPFILE '/oracle/temp1/dbname/dbname_temp_01.dbf' autoextend on next 27g maxsize 27g;