Топ-100
top of page
CerebroSQL

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.

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

TABLESPACE SIZE page

 

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

CerebroSQL - Oracle tablespace size page
Переключатель "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 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

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

2. The " Size object " list provides information on all data files

In the " Tablespace info " block

Switch " Quick (do not use dba_free_space) "

select df.FILE_ID,
      round(df.BYTES/1024/1024) "SizeMB",
      round(df.MAXBYTES/1024/1024) "MaxMB",
      round(df.USER_BYTES/1024/1024) "UsedMB",
      round( df.BYTES - df.USER_BYTES ) "FreeAlloc",
      round(df.MAXBYTES/1024/1024) - round(df.BYTES/1024/1024) "FreeDF",
      '-' "FreeTS"
 from DBA_DATA_FILES df
where file_name = $$FILE_NAME

Switch " Full (use dba_free_space) "

select df.FILE_ID,
      round(df.BYTES/1024/1024,4) "SizeMB",
      round(df.MAXBYTES/1024/1024,4) "MaxMB",
      round((df.BYTES - sum(fs.BYTES))/1024/1024,4) "UsedMB",
      round(sum(fs.BYTES/1024/1024),4) "FreeAlloc",
      round((df.MAXBYTES-(df.BYTES-sum(fs.BYTES)))/1024/1024,4)  "FreeDF",
      '-' "FreeTS"
 from DBA_DATA_FILES df,
      DBA_FREE_SPACE fs
where file_name = $$FILE_NAME
  and fs.FILE_ID= df.FILE_ID
 group by df.FILE_ID, df.BYTES, df.MAXBYTES

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

TABLESPACE INFO page