Who is using temporary tablespace :

The below script will give you the temporary tablespace used by Tablespace.

set pagesize 1000
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;


**************Output given below *******************************

TABLESPACE_NAME ALLOCATED FREE USED PERCENTFREE
------------------------------ ---------- ---------- ---------- -----------
USERS 5 1 3 35
EXAMPLE 100 22 77 23
TEST_TS 100 99 0 100
UNDOTBS1 160 136 23 86
SYSAUX 330 10 319 4
SYSTEM 490 4 485 1
Total 1185 275 909 0

7 rows selected.


***************

Note :

If you want to connect to a database without tnsnames.ora entry it will workout from 10g onwards.

sqlplus username/pwd@//hostname:1521/sidname