Shareware software
TABLESPACE SIZE page
The page displays information about the size of tablespaces / data files of the Oracle database, free space in them

"Query using" switch
Specifies the queries used to form the tree " Tree tablespace " and information about free space in table spaces in the list " Size object "
Tree tablespace
The list is updated by pressing the button " TABLESPACE SIZE " or the button " Refresh tree "
Switch " Quick (do not use dba_free_space) "
select t.tablespace_name,
t.contents,
t.status
from DBA_TABLESPACES t
order by t.contents, t.tablespace_name
Switch " Full (use dba_free_space) "
select t.tablespace_name,
t.contents,
t.status,
case when t.CONTENTS<>'TEMPORARY' then
(select count(*)||' | '||
round(sum(bytes)/1024/1024/1024,2)||' GB'
from DBA_DATA_FILES d
where d.TABLESPACE_NAME=t.TABLESPACE_NAME)
else
(select count(*)||' | '||
round(sum(bytes)/1024/1024/1024,2)||' GB'
from DBA_TEMP_FILES d
where d.TABLESPACE_NAME=t.TABLESPACE_NAME)
end "COUNT|ALLOC"
from DBA_TABLESPACES t
order by t.contents, t.tablespace_name
Free space in tablespaces
To view general information about all tablespaces, in the " Tree tablespace " tree, select the " TABLESPACE " node
Quick (do not use dba_free_space)
select a."name" "tablespace_name",
a."count",
round(a."all",2) "MAX",
round(a."alloc"/1024,4) "megs_alloc",
round(a."used"/1024,4) "megs_used",
a."all" - a."used" "megs_free",
round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100,2) "FREE", round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100) "FREE1",
a."counts",
a."StatusTS"
from (
select tablespace_name "name",
count(file_name) "count",
round(sum(DECODE(autoextensible,'YES',maxbytes,'NO',bytes))/1024/1024) "all",
round(sum(bytes)/1024/1024) "alloc",
round(sum(user_bytes)/1024/1024) "used",
count(*) "counts"
,(select status from DBA_TABLESPACES dt where dt.TABLESPACE_NAME = d.tablespace_name) "StatusTS"
from DBA_DATA_FILES d
group by tablespace_name
union all
SELECT h.tablespace_name "name",
count(f.FILE_NAME ) "count",
ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) / 1048576) "all",
ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) "alloc",
ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) "used",
count(*) "Counts"
,(select status from DBA_TABLESPACES dt where dt.TABLESPACE_NAME = h.tablespace_name) "StatusTS"
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 h.tablespace_name) a
order by 7
Full (use dba_free_space!)
SELECT a.tablespace_name,
ROUND (maxbytes / 1024/1024) MAX,
ROUND (a.bytes_alloc /1073741824,4) megs_alloc,
ROUND ( (a.bytes_alloc - NVL (b.bytes_free,0)) / 1073741824,4) megs_used,
round((maxbytes - (a.bytes_alloc - NVL(b.bytes_free, 0)) + NVL(b.bytes_free, 0))/1024/1024,2) megs_free,
ROUND(100-(((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024)/ (maxbytes / 1024 / 1024))*100) "FREE",
ROUND(100-(((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024)/ (maxbytes / 1024 / 1024))*100,2) "FREE1",
a."counts"
,(select status from DBA_TABLESPACES dt where dt.TABLESPACE_NAME = a.tablespace_name) "StatusTS"
FROM ( SELECT f.tablespace_name,
SUM (f.bytes) bytes_alloc,
SUM ( DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes))maxbytes,
count(*) "counts"
FROM dba_data_files f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name, SUM (f.bytes) bytes_free
FROM dba_free_space f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
SELECT h.tablespace_name,
ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) / 1073741824,4) MAX,
ROUND (SUM (h.bytes_free + h.bytes_used) / 1073741824,4) megs_alloc,
ROUND (SUM (NVL (p.bytes_used, 0)) / 1073741824,2) megs_used,
ROUND (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))/ 1073741824,4)megs_free,
ROUND(100-(ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576))/(ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) / 1048576))*100,4) "FREE",
ROUND(100-(SUM (NVL (p.bytes_used, 0)) / 1048576)/(SUM (h.bytes_free + h.bytes_used) / 1048576)*100,4) "FREE1",
count(*) "counts"
,(select status from DBA_TABLESPACES dt where dt.TABLESPACE_NAME = h.tablespace_name) "StatusTS"
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 h.tablespace_name ORDER BY 7
When you select the PERMANENT (data space), TEMPORARY (tablespaces for temporary data), UNDO ( UNDO tablespaces) nodes in the " Tree tablespace " tree, information about data files and their size is displayed in the " Size object " list.
When a tablespace name is selected in the " Tree tablespace " tree, the " Size object " list displays information about the data files of the specified space
Data structure in "Size object" list
Name - tablespace/data file name
Max - maximum possible size in MB
Free - free in MB
Progress bar - graphical display of free space in percent
Detailed information about the object
1. When a table space is selected in the " Size object " list, the following information is displayed in the " Tablespace info " block from the DBA_TABLESPACES view:
TABLESPACE_NAME
BLOCK_SIZE
STATUS
CONTENTS
LOGGING
FORCE_LOGGING
EXTENT_MANAGEMENT
SEGMENT_SPACE_MANAGEMENT
DEF_TAB_COMPRESSION
RETENTION
select t.TABLESPACE_NAME "tablespace_name",
t.BLOCK_SIZE "block_size",
t.STATUS "Status",
t.CONTENTS "Contents",
t.LOGGING "Logging",
t.FORCE_LOGGING "Force",
t.EXTENT_MANAGEMENT "EXTENT_MANAGEMENT",
t.SEGMENT_SPACE_MANAGEMENT "SEGMENT_SPACE_MANAGEMENT",
t.DEF_TAB_COMPRESSION "Compression",
t.retention "retention"
from DBA_TABLESPACES t where tablespace_name=$$tablespace_name
When you select the PERMANENT (data space), TEMPORARY (tablespaces for temporary data), UNDO ( UNDO tablespaces) nodes in the " Tree tablespace " tree, information about data files and their size is displayed in the " Size object " list.
When a tablespace name is selected in the " Tree tablespace " tree, the " Size object " list displays information about the data files of the specified space
Data structure in "Size object" list
Name - tablespace/data file name
Max - maximum possible size in MB
Free - free in MB
Progress bar - graphical display of free space in percent
Additional information about the object
1. The " Size object " list provides information on all tablespaces
In the " Tablespace info " block