Oracle Tablespace Manager

Oracle storage manager - the module allows you to manage tablespaces, data files. Track the utilization of space in them, including actively, collecting statistics on the dynamics of utilization and evaluating the need to add files.

Страница "TABLESPACE SIZE"

TABLESPACE SIZE page

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

Переключатель "Query using"

"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"

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

Cвободное место в табличных пространствах

Free space in tablespaces

Формат данных в списке "Size object"

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