The below query gives the details of the Tablespaces in a Oracle Database. It shows the details of Size allocated, Used size and Free size of the tablespaces.
NOTE: Temporary tablespace is not included in this.
set lines 200 pages 1000
column tablespace_name format a30
column allocated format 999999.99
column free format 999999.99
column used format 999999.99
col Contiguous format 999999.99
break on report
compute sum of allocated on report
compute sum of used on report
compute sum of free on report
compute sum of contiguous on report
select rpad(a.tablespace_name,30,'.')tablespace_name,
sum(a.bytes)/(1024*1024) Allocated,
sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
max(nvl(b.space,0)) Free,round(((max(nvl(b.space,0)))/(sum(a.bytes)/(1024*1024))),4)*100 perctfree,
max(nvl(b.cont,0))/(1024*1024) Contiguous
from dba_data_files a,
(select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
group by a.tablespace_name
order by a.tablespace_name
/
Example:
Excelente.
ReplyDelete