C:\> set ORACLE_SID=<your_sid>
C:\> sqlplus / as sysdba
SQL> select name from v$database;
SQL> select * from dba_tenp_files;

C:\Users\mwadmin>sqlplus sys/password@PLM61 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 23 14:45:58 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
PLM61

SQL> select * from dba_temp_files;

FILE_NAME
-----------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
W:\PLM61\TEMP01.DBF
         1 TEMP                           5194645504     634112 AVAILABLE
           1 YES 3.4360E+10    4194302       131072 5193596928      633984


SQL>

-- +----------------------------------------------------------------------------+                                                                            |
-- | DATABASE : Oracle        													|
-- | Author	  : Tuan Anh Tran - trantanh@vnpt.vn								|
-- | FILE     : tempusage.sql                                        			|
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Finding temporary tablespace and what're session, user schema   |
-- |            who has been being used. This script work with Oracle8i .     	|
-- |            or higher                          								|
-- +----------------------------------------------------------------------------+
select b.Total_MB,
       b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,
       round(used_blocks*8/1024)                Current_Used_MB,
       round(max_used_blocks*8/1024)             Max_used_MB
from v$sort_segment a,
 (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b
/

Prompt -- Press any key to continue
Pause
Prompt -- List sessions are using temporary tablespace --

col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000
SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;

Prompt -- Press anykey to continue --
Pause

Prompt -- List sql statements --
var schema varchar2(200);
var sid number;
col hash_value for 999999999999
select sql_text, hash_value, sorts, rows_processed/executions
 from v$sql
 where hash_value in (select hash_value from v$open_cursor where sid=&&sid)
 and sorts > 0
 and PARSING_SCHEMA_NAME='&&schema'
 order by rows_processed/executions;
undefine schema
undefine sid

teleadm@TELESMS> @tempusage

  TOTAL_MB CURRENT_FREE_MB CURRENT_USED_MB MAX_USED_MB
---------- --------------- --------------- -----------
      6505            6501               4        5128

-- Press any key to continue

-- List sessions are using temporary tablespace -

       SID USERNAME        TABLESPACE HASH_VALUE                               SEGTYPE   CONTENTS      BLOCKS
---------- --------------- ---------- ---------------------------------------- --------- --------- ----------
      2623 TELESMPP             TEMP       0/3724126796                             LOB_DATA  TEMPORARY     128
      4068 TELESMPP             TEMP       0/377997205                              DATA      TEMPORARY     128
      4068 TELESMPP             TEMP       0/377997205                              LOB_DATA  TEMPORARY     128
      4068 TELESMPP             TEMP       0/377997205                              INDEX     TEMPORARY     128

-- Press anykey to continue - Pause
-- List sql statements - var schema varchar2(200)
Enter value for sid: 4068
old   3:  where hash_value in (select hash_value from v$open_cursor where sid=&&sid)
new   3:  where hash_value in (select hash_value from v$open_cursor where sid=4068)
Enter value for schema: TELESMPP
old   5:  and PARSING_SCHEMA_NAME='&&schema'
new   5:  and PARSING_SCHEMA_NAME='TELESMPP'

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
   HASH_VALUE      SORTS ROWS_PROCESSED/EXECUTIONS
------------- ---------- -------------------------
SELECT PARTITION_NAME       FROM SYS.Dba_TAB_PARTITIONS       WHERE TABLE_OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME
ORDER BY PARTITION_NAME
    377997205         35                1.02857143

SELECT PARTITION_NAME       FROM SYS.Dba_TAB_PARTITIONS       WHERE TABLE_OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME
ORDER BY PARTITION_NAME
    377997205          5                       3.6

SELECT PARTITION_NAME       FROM SYS.Dba_TAB_PARTITIONS       WHERE TABLE_OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME
ORDER BY PARTITION_NAME
    377997205         31                4.74193548

SELECT COLUMN_NAME       FROM SYS.Dba_TAB_COLUMNS       WHERE OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME       ORDER BY COL
UMN_ID
   2828175926          6                11.3333333

SELECT COLUMN_NAME       FROM SYS.Dba_TAB_COLUMNS       WHERE OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME       ORDER BY COL
UMN_ID
   2828175926         35                11.8857143

select c.column_name,  case when data_type = :"SYS_B_00"     then      data_type||:"SYS_B_01"||c.char_length||decode(char_used,:"SYS
_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",null)||:"SYS_B_06"                when data_type = :"SYS_B_07"  then      data_type||:"SY
S_B_08"||c.char_length||decode(char_used,:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",null)||:"SYS_B_13"                when data
_type = :"SYS_B_14" then      data_type||:"SYS_B_15"||c.char_length||decode(char_used,:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19
",null)||:"SYS_B_20"                when data_type = :"SYS_B_21"    then      data_type||:"SYS_B_22"||c.char_length||decode(char_use
d,:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",null)||:"SYS_B_27"                when data_type = :"SYS_B_28" then
     case when c.data_precision is null and c.data_scale is null then          :"SYS_B_29"            when c.data_precision is nu
ll and c.data_scale is not null then          :"SYS_B_30"||c.data_scale||:"S
   3986790923         47                12.3617021

select c.column_name,  case when data_type = :"SYS_B_00"     then      data_type||:"SYS_B_01"||c.char_length||decode(char_used,:"SYS
_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",null)||:"SYS_B_06"                when data_type = :"SYS_B_07"  then      data_type||:"SY
S_B_08"||c.char_length||decode(char_used,:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",null)||:"SYS_B_13"                when data
_type = :"SYS_B_14" then      data_type||:"SYS_B_15"||c.char_length||decode(char_used,:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19
",null)||:"SYS_B_20"                when data_type = :"SYS_B_21"    then      data_type||:"SYS_B_22"||c.char_length||decode(char_use
d,:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",null)||:"SYS_B_27"                when data_type = :"SYS_B_28" then
     case when c.data_precision is null and c.data_scale is null then          :"SYS_B_29"            when c.data_precision is nu
ll and c.data_scale is not null then          :"SYS_B_30"||c.data_scale||:"S
   3986790923         20                     12.95


7 rows selected.

teleadm@TELESMS>