List the contents of the temporary tablespace(s)

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid 
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

Show segments that are approaching max_extents

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

Alter a databases default temporary tablespace

alter database default temporary tablespace temp
/

Create a temporary tablespace

create temporary tablespace temp
tempfile '<file_name>' size 500M
/

Show all tablespaces used by a user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/

List all objects in a tablespace

set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/

User quotas on all tablespaces

col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')
/

Tablespaces that are >=80% full, and how much to add to make them 80% again

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB"  for 999999999
col "%Used"  for 999
col "Add (80%)"  for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from    dba_data_files group by tablespace_name) tsu
,  (select ts.tablespace_name
 ,       nvl(sum(bytes)/1024/1024, 0) free_mb
 from    dba_tablespaces ts, dba_free_space fs
 where   ts.tablespace_name = fs.tablespace_name (+)
 group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

Show the files that comprise a tablespace

set lines 100
col file_name format a70
select file_name
,      ceil(bytes / 1024 / 1024) "size MB"
from   dba_data_files
where  tablespace_name like '&TSNAME'
/

Tablespace usage

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/