Andrew Fraser DBA

Tablespace space with sm$ views + database level space

The sm$ views are an easy way of seeing tablespace space usage. There is also an sm$ts_free view.

set pages 9999
col tot_mb form 999,999
col use_mb form 999,999
col pct_used form 999
select t.tablespace_name, t.bytes/1024/1024 tot_mb, u.bytes/1024/1024 use_mb, 100*u.bytes/t.bytes pct_used
from sys.sm$ts_avail t, sys.sm$ts_used u
where t.tablespace_name = u.tablespace_name(+)
order by 4 desc
/
-- two queries below for ancient dictionary managed tablespaces only
select owner, segment_name, extents, pct_increase, max_extents, next_extent/1024/1024, tablespace_name
from dba_segments
where extents > max_extents - 5
/
select owner, segment_name, extents, pct_increase, max_extents, next_extent/1024/1024, tablespace_name
from dba_segments s
where next_extent > ( select max(f.bytes) from dba_free_space f where f.tablespace_name = s.tablespace_name)
/

— *Update* – and also database level space:

select sum(bytes)/1024/1024/1024 allocated_gb
from (
  select sum(bytes) bytes from dba_data_files
  union all
  select sum(bytes) bytes from dba_temp_files
  union all
  select sum(l.bytes) bytes from v$log l, v$logfile f where l.group# = f.group#
)
/
select sum(bytes)/1024/1024/1024 used_gb
from sys.sm$ts_used
/

— *Update2* – generate script to add 1000m to each datafile:

set pages 9999 lines 112
select 'alter database datafile '''||file_name||''' resize '||(bytes+1000*1024*1024)/1024/1024||' m ;'
from dba_data_files where tablespace_name in ('TS_INDEX1')
order by 1
/