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

Detailed information about tablespaces, statistics on resizing of spaces.

​

base information

General information about the tablespace, the list of files included in it

​

CerebroSQL - Oracle tablespace info

select t.STATUS,
      t.CONTENTS,
      t.LOGGING,
      t.FORCE_LOGGING,
      t.RETENTION,
      t.BIGFILE
 from DBA_TABLESPACES t
where t.TABLESPACE_NAME = $$TABLESPACE_NAME
         
select file_name,
      round((bytes)/1073741824, 3) "Allocate",
      case when maxbytes>0 then
          round((maxbytes)/1073741824,3)  else
          round((bytes)/1073741824,3)
          end as "MaxSize"
 from DBA_DATA_FILES
where tablespace_name =$$TABLESPACE_NAME 
order by bytes ,lpad(file_id,4) desc

​

Resize tablespace info

Selected tablespace resizing statistics (using data from Oracle diagnostics pack )

​

CerebroSQL - Oracle resize tablespace.jp

SELECT BEGIN_INTERVAL_TIME,
      END_INTERVAL_TIME,
      B.NAME,
      ROUND((TABLESPACE_SIZE*8*1024)/1024/1024/1024,2)||' | '||
      ROUND((TABLESPACE_MAXSIZE*8*1024)/1024/1024/1024,2) "ALLOC",
      ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024/1024,2) USEDSIZE
 FROM DBA_HIST_TBSPC_SPACE_USAGE A
      JOIN V$TABLESPACE B ON (A.TABLESPACE_ID = B.TS#)
      JOIN DBA_HIST_SNAPSHOT C ON (A.SNAP_ID = C.SNAP_ID)
WHERE NAME = $$TABLESPACE_NAME
ORDER BY 1 DESC

    
select SIZE_MB_MIN,
      SIZE_MB_MAX,
      MAXSIZE_MB,
      MIN_USEDSIZE_MB,
      MAX_USEDSIZE_MB,
      DAYS,
      (MAXSIZE_MB-MAX_USEDSIZE_MB) "Free",
      Round((SIZE_MB_MAX-SIZE_MB_MIN)/nvl(DAYS,1)) "DAY_RES"
 from
(SELECT Min(ROUND((TABLESPACE_SIZE*8*1024)/1024/1024/1024)) SIZE_MB_MIN,
       Max(ROUND((TABLESPACE_SIZE*8*1024)/1024/1024/1024)) SIZE_MB_MAX,
       Max(ROUND((TABLESPACE_MAXSIZE*8*1024)/1024/1024/1024)) MAXSIZE_MB,
       min(ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024/1024)) MIN_USEDSIZE_MB,
       Max(ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024/1024)) MAX_USEDSIZE_MB,
       trunc(Max(END_INTERVAL_TIME))- trunc(min(BEGIN_INTERVAL_TIME)) DAYS
  FROM DBA_HIST_TBSPC_SPACE_USAGE A
       JOIN V$TABLESPACE B ON (A.TABLESPACE_ID = B.TS#)
       JOIN DBA_HIST_SNAPSHOT C ON (A.SNAP_ID = C.SNAP_ID)
 WHERE NAME = $$TABLESPACE_NAME)

​

Resize database info

Statistics on resizing of all tablespaces in the database (using data from Oracle diagnostics pack )

​

CerebroSQL - Oracle resize all tablespac

​

speakers

  • Name - the name of the tablespace

  • Max size - maximum tablespace size

  • Min alloc - minimum size of data files

  • Max alloc - maximum size of data files

  • Min data - minimum amount of data in space

  • Max data - the maximum amount of data in the space

  • Day mon - the number of days for which statistics are collected

  • Free - free in space (GB)

  • DayRes - average space size change per day

  • Day - the number of days for which, approximately, there will be enough free space

​

select Name,
      MAXSIZE_MB,
      SIZE_MB_MIN,
      SIZE_MB_MAX,
      MIN_USEDSIZE_MB,
      MAX_USEDSIZE_MB,
      DAYS,
      (MAXSIZE_MB-MAX_USEDSIZE_MB) "Free",
      Round((SIZE_MB_MAX-SIZE_MB_MIN)/nvl(DAYS,1)) "DAY_RES"
 from
(SELECT NAME,
      Min(ROUND((TABLESPACE_SIZE*8*1024)/1024/1024/1024,2)) SIZE_MB_MIN,
      Max(ROUND((TABLESPACE_SIZE*8*1024)/1024/1024/1024,2)) SIZE_MB_MAX,
      Max(ROUND((TABLESPACE_MAXSIZE*8*1024)/1024/1024/1024,2)) MAXSIZE_MB,
      min(ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024/1024,2)) MIN_USEDSIZE_MB,
      Max(ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024/1024,2)) MAX_USEDSIZE_MB,
      trunc(Max(END_INTERVAL_TIME))- trunc(min(BEGIN_INTERVAL_TIME)) DAYS
 FROM DBA_HIST_TBSPC_SPACE_USAGE A
      JOIN V$TABLESPACE B ON (A.TABLESPACE_ID = B.TS#)
      JOIN DBA_HIST_SNAPSHOT C ON (A.SNAP_ID = C.SNAP_ID)
group by name)
order by "DAY_RES" desc

Base information
Resize tablespace info
Resize database info
Страница "CREATE TABLESPACE"

Page "CREATE TABLESPACE"

 

Used to create tablespaces in an Oracle database

​

CerebroSQL - Create new tablespace page.

​

Tablespace Creation Sequence

  • In the " Tablespace type " block, select the type of tablespace to be created

    • Standard -  normal storage space

    • Temporary - temporary tablespace

    • UNDO - undo space

    • Big - "big" tablespace.

  • Tablespace name - the name of the tablespace, or " or list " the list of tablespaces to create. One line corresponds to the name of one created space

  • Folder - a folder on the server disk in which the files included in the tablespace will be created

when expanding the list, the path for creating database files is displayed. The file name is formed by the mask, where:

$TSNAME - space name from " or list " list

​

select file_name, file_id 
  from DBA_DATA_FILES
where file_id = (select max(file_id) 
                    from DBA_DATA_FILES
                 )

​

those. retrieved let create last data file 

  • Loging type - the default flag for all created objects indicating the prohibition of saving data in REDO logs

  • Compress - the default flag for all created tables in the space indicating the need to compress data 

  • Temporary group - used for spaces of type " Temporary ". Serves to combine several spaces into one group to improve the performance of working with temporary objects

  • Storage parameter - data file parameters

    • Size [MB] - initial size of the data file in megabytes​

    • Autoextend - enable automatic increase in data file size

      • Next [MB] - value by which to increase the size of the data file

      • Max [MB] - maximum data file size in megabytes

Buttons

  • Create tablespace - create tablespaces

  • Clear page - clear the page

  • Show sql - show SQL commands for creating tablespaces

​

ADD DATAFILE Page

Add datafile

 

The page is intended for mass creation of data files in table spaces.

​

CerebroSQL - add new datafile in Oracle tablespace

 

Procedure

  • Select the tablespace to which you want to add data files in the " Tablespace list " tree

If you need to create multiple data files:

  1. Set flag " Autoname "

    1. In the " Prefix " field, enter the prefix for the name of the files to be created

    2. In the " Current max number " field, enter the current maximum file number in the selected tablespace

    3. In the " Count file " field, specify the number of files to be created

  • In the " File name " field, enter the full file name and path.

If you plan to create multiple data files, then you must use a template in the name:

$PREF - name prefix, if the "Prefix" field is filled, then this value is taken, otherwise it is the name of the tablespace

If autoname=true

$NUM - file number, the value is formed in a loop according to the condition inc(current max number)

If autoname=false

$NUM = number of files in space increased by 1 

  • Specify the initial size of the created files in the " Size [MB] " field, for example 100 (value in MB)

  • If it is necessary to allow automatic expansion of data files, set the " Autoextend " radio button

    • Specify the amount by which the database will increase the size of data files in the field " Next [MB] "

    • Specify the maximum size of the data file in the field " Max [MB] " or leave 0 - maxsize

​

To view the list of generated commands, click the " Show SQL " button

To create files, press the " Create new datafile " button

​

Страница "ALTER DATAFILE"

ALTER DATAFILE Page

 

The page is intended for changing the parameters of current data files in table spaces

​

Resize datafile
​
CerebroSQL - alter datafile resize file.

 

Sequencing

  • Select a tablespace in the " Tablespace list " tree

  • In the " Datafile list " list, set the radio button next to the data files whose size you want to change

  • In the field " New size datafile " enter the new size

  • Press the " Apply " button  

The new size of the files, if the operation is successful, will be displayed in the " Datafile list " after the operation is completed.

If an error occurs, the process will stop. The text of the error will be indicated in the field " Status "

Resize datafile
Страница "Move"

Move page

 

The page is intended for bulk movement of objects both within one tablespace and to another tablespace to optimize the data storage structure and improve the performance of the I / O system. 

​

CerebroSQL - Oracle move objects

 

Sequencing:

  • On the " TABLESPACE " page, select table spaces, objects from which you want to transfer

or

  • On the " SCHEMA " page, select the schemas, the objects of which you want to transfer

  • Press the " Load list object " button to display a list of objects in the selected spaces

  • In the "A list of database objects" list, select the objects to be transferred

From the context menu of the window, it is possible to mass select objects of certain types

  • Select the tablespace to which you want to transfer the selected objects in the " Transfer to " field

  • Specify the level of parallelism of the operation in the " Parallel " field

  • Specify additional options

    • Compress - compress objects

    • Nologging - disable logging of the operation in the REDO logs

    • Compute statistics - update statistics

    • Online - perform an operation without blocking the object being moved.

Examples of generated commands:

ALTER TABLE "SYS"."AQ$_SCHEDULER$_REMDB_JOBQTAB_L" MOVE TABLESPACE SYSTEM   PARALLEL 10
ALTER TABLE "SYS"."AQ$_SCHEDULER$_REMDB_JOBQTAB_S" MOVE TABLESPACE SYSTEM   PARALLEL 10
ALTER TABLE "SYS"."AQ$_SCHEDULER_FILEWATCHER_QT_S" MOVE TABLESPACE SYSTEM   PARALLEL 10
ALTER TABLE "SYS"."AQ$_SCHEDULES" MOVE TABLESPACE SYSTEM   PARALLEL 10
ALTER TABLE "SYS"."AQ$_SUBSCRIBER_LWM" MOVE PARTITION "SYS_P7" TABLESPACE SYSTEM   PARALLEL 10
ALTER TABLE "SYS"."AQ$_SUBSCRIBER_LWM" MOVE PARTITION "SYS_P12" TABLESPACE SYSTEM   PARALLEL 10
ALTER TABLE "SYS"."AQ$_SUBSCRIBER_LWM" MOVE PARTITION "SYS_P16" TABLESPACE SYSTEM   PARALLEL 10
ALTER INDEX "SYS"."HS$_CLASS_INIT_PK" REBUILD ONLINE TABLESPACE SYSTEM  PARALLEL 10 COMPUTE STATISTICS
ALTER INDEX "SYS"."HS$_CLASS_INIT_UK1" REBUILD ONLINE TABLESPACE SYSTEM  PARALLEL 10 COMPUTE STATISTICS
ALTER INDEX "SYS"."HS$_FDS_CLASS_PK" REBUILD ONLINE TABLESPACE SYSTEM  PARALLEL 10 COMPUTE STATISTICS
ALTER INDEX "SYS"."HS$_FDS_CLASS_UK1" REBUILD ONLINE TABLESPACE SYSTEM  PARALLEL 10 COMPUTE STATISTICS
ALTER TABLE "SYS"."KOTAD$" MOVE LOB(SYS_NC_ROWINFO$) STORE AS (TABLESPACE SYSTEM)  PARALLEL 10
ALTER TABLE "SYS"."KOTTBX$" MOVE LOB(SYS_NC_ROWINFO$) STORE AS (TABLESPACE SYSTEM)  PARALLEL 10
ALTER TABLE "SYS"."TABCOMPART$" MOVE LOB(BHIBOUNDVAL) STORE AS (TABLESPACE SYSTEM)  PARALLEL 10

  • The process of transferring objects is started by pressing the " RUN " button

  • To stop the process, press the " STOP " button

  • To view the list of executed commands, click the " Show SQL " button

​

bottom of page