Description:
Tablespace Name: name of tablespace, leading '*' indicates a good locally managed tablespace, leading blank means it is a bad dictionary managed tablespace.
Second character of A implies ASSM managed storage,
second character of M implies manually managed (pctused, freelists, etc are used to control space utilization)
Kbytes: allocated space of the tablespace, sum of kbytes consumed by all datafiles associated with tablespace.
Used: space in the tablespace that is used by some segment.
Free: space in the tablespace not allocated to any segment.
%Used: ratio of free to allocated space
largest: mostly useful with dictionary managed tablespaces, the size of the largest contigously set of blocks available.
If this number in a dictionary managed tablespace is smaller than the next extent for some object,
that object could fail with "out of space" even if the FREE column says there is lots of free space.
MaxPoss Kbytes: the autoextend max size (note CAN be smaller than the allocated size!!!! you can set
the maxsize to be less than the current size of a file)
%Max Used: how much of the maximum autoextend size has been used so far
Check the example below:
------------------------------------------------------------------------------------------------------
clear screen
set linesize 250
column dummy noprint
column name format a25 heading "Tablespace Name"
column megs_alloc format 999g999g999 heading "Mbytes|Total"
column megs_used format 999g999g999 heading "Mbytes|Used"
column megs_free format 999g999g999 heading "Mbytes|Free"
column pct_free format 999.9 heading "Free"
column pct_used format 999.9 heading "Used"
column pct format a1 heading ""
column largest format 999g999g999 heading "Freespace|Largest"
column Max format 999g999g999 heading "Mbytes|MaxPoss"
column tot_file format 999g999g999 heading "Mbytes|FreeFile"
column pct_file format 999.9 heading "FreeFile"
break on report
compute sum of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report
prompt
prompt Tablespace Name = Tablespace Name
prompt Mbytes Total = Totale ruimte gealloceerd door applicatie
prompt Mbytes Used = Totale gebruikte ruimte van gealloceerde ruimte
prompt Mbytes Free = Totale ONgebruikte ruimte van gealloceerde ruimte
prompt Free = Percentage ongebruikte ruimte
prompt Used = Percentage gebruikte ruimte
prompt Mbytes MaxPoss = Maximale omvang van alle datafiles
prompt Freespace Largest = Maximale Freespace chunck
prompt Mbytes FreeFile = Totale resterende file ruimte
prompt FreeFile = Percentage ONgebruikte file ruimte
prompt
prompt Omvang in Mb's.
prompt 1000-tal scheidingsteken is ','.
prompt
select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKOWN')) Name,
round(a.bytes_alloc / 1048576, 2) megs_alloc,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1048576, 2) megs_used,
round(nvl(b.bytes_free, 0) / 1048576, 2) megs_free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)*100,2) Pct_used,
'%' pct,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
'%' pct,
round(maxbytes / 1048576,2) Max,
round(DECODE(SIGN(round(maxbytes / 1048576,2) - round(a.bytes_alloc / 1048576,2)),-1,'0',round(maxbytes / 1048576,2) - round(a.bytes_alloc / 1048576,2))) tot_file,
100 * ((round(maxbytes / 1048576,2) - round(a.bytes_alloc / 1048576, 2)) / round(maxbytes / 1048576,2)) pct_file,
'%' pct,
nvl(b.largest,0) largest
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free,
max(f.bytes) / 1048576 largest
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = f.tablespace_name) || nvl(f.tablespace_name, nvl(f.tablespace_name,'UNKOWN')) Name,
round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
round(sum(nvl(p.bytes_used, 0)) / 1048576, 2) megs_used,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
100 - (round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free))*100,2)) pct_used,
'%' pct,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free))*100,2) Pct_Free,
'%' pct,
round(f.maxbytes / 1048576, 2) max,
round(DECODE(SIGN(round(f.maxbytes / 1048576,2) - round(sum(h.bytes_free + h.bytes_used) / 1048576, 2)),-1,'0',round(f.maxbytes / 1048576,2) - round(sum(h.bytes_free + h.bytes_used) / 1048576, 2))) tot_file,
100 * (DECODE (SIGN(round(f.maxbytes/1048576, 2) - round(sum(h.bytes_free + h.bytes_used) / 1048576, 2)),-1,'0',round(f.maxbytes/1048576,2) / round(f.maxbytes/1048576, 2))) pct_file,
'%' pct,
max(f.bytes)/1048576 largest
from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by f.tablespace_name, f.maxbytes
ORDER BY 1
/
prompt
prompt A leading asterisk (*) in the tablespace name indicates that the tablespace is a locally managed one,
prompt whereas a leading blank means that it is an old-fashioned dictionary-managed tablespace.
prompt second character a, the ts is Automatic Segment Space Management (ASSM),
prompt second character m, the ts is manually managed (pctused, freelists etc. are used)
clear columns