Size (Gb/Mb)

SQL> SELECT NVL(SUM(BYTES),0)/1024/1024/1024 "GB" FROM DBA_TEMP_FILES;

SQL> SELECT NVL(SUM(BYTES),0)/1024/1024 "MB" FROM DBA_TEMP_FILES;

TS, Free and Used space (Gb/Mb)

SQL> SELECT TABLESPACE_NAME, SUM (BYTES_USED)/1024/1024/1024 USED_GB, SUM (BYTES_FREE)/1024/1024/1024 FREE_GB FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME;

SQL> SELECT TABLESPACE_NAME, SUM (BYTES_USED)/1024/1024 USED_MB, SUM (BYTES_FREE)/1024/1024 FREE_MB FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME;

Size, used, free (Gb/Mb)

Gb
SQL>
SELECT   A.tablespace_name tablespace, D.Gb_total,
         SUM (A.used_blocks * D.block_size)/1024/1024/1024 Gb_used,
         D.Gb_total - SUM (A.used_blocks * D.block_size)/1024/1024/1024 Gb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 Gb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.Gb_total;

Mb
SQL>
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Which of users are working with Temp tablespace

SQL>
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

SQL>
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr and tablespace='MY_TEMP';

SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE; 
SQL> ALTER SYSTEM KILL SESSION '131,8' IMMEDIATE;

SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#INST_ID' IMMEDIATE; 
SQL> ALTER SYSTEM KILL SESSION '131,8,@6' IMMEDIATE;

Add temp tablespace and Tempfile

SQL> CREATE TEMPORARY TABLESPACE "TEMP_01" TEMPFILE '/SNAP/tmpredo/ORCL/temp/temp_01.dbf' SIZE 32767M;

Add Tempfile

SQL> ALTER TABLESPACE "MY_TEMP" ADD TEMPFILE '/SNAP/tmpredo/ORCL/temp/my_tmp01.dbf' SIZE 1G AUTOEXTEND ON NEXT 128M; 

Resize Tempfile

SQL> ALTER DATABASE TEMPFILE '/SNAP/tmpredo/ORCL/temp/temp_01.dbf' RESIZE 128M; 

Set default temp tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MY_TEMP;

SQL> ALTER USER TIGER TEMPORARY TABLESPACE MY_TEMP;

 

Delete Tempfile

SQL> ALTER DATABASE TEMPFILE '/SNAP/tmpredo/ORCL/temp/temp01.dbf' DROP;

$ rm /SNAP/tmpredo/ORCL/temp/temp01.dbf

(!) Delete Temp tablespace (!)

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;