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
/

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'
/

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
/

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')
/

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
/

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
/

Create a temporary tablespace

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

Alter a databases default temporary tablespace

alter database default temporary tablespace temp
/

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 <segment_type> <segment_name> storage(maxextents 150);

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
/