Топ-100
 
CerebroSQL

Oracle Database Space Manager

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

 
TABLESPACE SIZE page

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

CerebroSQL - Oracle tablespace size page.jpg
Query using radio button

Defines the queries used to form the "Tree tablespace" tree and information about free space in table spaces in the "Size object" list

Tree tablespace

The list is refreshed by pressing the "TABLESPACE SIZE" button or the "Refresh tree" button

Switch "Quick (do not use dba_free_space)"

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-----

View Free Space Information in Table Spaces

To view general information about all tablespaces, select the "TABLESPACE" node in the "Tree tablespace" tree

-> Request to retrieve data <-

SELECT a.tablespace_name,
ROUND (maxbytes / 1073741824) MAX,
ROUND (a.bytes_alloc / 1073741824,4) megs_alloc,
ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) / 1073741824,4) megs_used,
ROUND (NVL (b.bytes_free, 0) / 1073741824,4) 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

 

or

select a. "name" "tablespace_name",
a. "count",
round (a. "all" / 1024.4) "MAX",
round (a. "alloc" / 1024,4) "megs_alloc",
round (a. "used" / 1024,4) "megs_used",
'0' "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

-----

When you select in the tree "Tree tablespace" PERMANENT nodes (data space), TEMPORARY (table space for temporary data), UNDO (table space UNDO) in the list of "Size object" is displayed on the data files information and their size.

When the name of the tablespace is selected in the " Tree tablespace " tree, information about the data files of the specified space is displayed in the "Size object" list

Data format in the "Size object" list
  • Name - the name of the tablespace / data file

  • Max - the maximum possible size in MB

  • Free - free in MB

  • Progress bar - graphical display of free space as a percentage

Additional information about the object

1. The list "Size object" contains information on all tablespaces

In the "Tablespace info" block

-> Request to retrieve data <-

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 contains information on all data files

In the "Tablespace info" block

Switch "Quick (do not use dba_free_space)"

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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 Page

Detailed information about table spaces, statistics on resizing spaces.

Base information

General information about the table space, a list of files included in it

CerebroSQL - Oracle tablespace info.jpg

-> Request to retrieve data <-

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

Statistics on resizing the selected tablespace (using data from Oracle diagnostics pack )

CerebroSQL - Oracle resize tablespace.jp

-> Request to retrieve data <-

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

Resizing statistics for all tablespaces in the database (uses Oracle diagnostics pack data)

CerebroSQL - Oracle resize all tablespac

Loudspeakers

  • Name - the name of the tablespace

  • Max size - the maximum size of the table space

  • Min alloc - minimum size of data files

  • Max alloc - maximum size of data files

  • Min data - the minimum amount of data in space

  • Max data - the maximum amount of data in space

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

  • Free - free in space (GB)

  • DayRes - resizing space on average per day

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

-> Request to retrieve data <-

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

-----

 
 
 
 
CREATE TABLESPACE Page

Serves for creating table spaces in an Oracle database

CerebroSQL - Create new tablespace page.

Sequence of tablespace creation

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

    • Standard - regular storage space

    • Temporary - temporary tablespace

    • UNDO - undo space

    • Big is a "large" tablespace.

  • Tablespace name - the name of the tablespace or "or list" 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 table space will be created

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

$ TSNAME - the name of the space from the "or list"

The string is formed by the request

-> Request to retrieve data <-

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

-----

those. fetch let create the 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 space indicating the need to compress data

  • Temporary group - used for " Temporary " spaces. Used to combine multiple 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 expansion of the data file size

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

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

Buttons
  • Create tablespace - create tablespaces

  • Clear page - clear the page

  • Show sql - show SQL commands for creating tablespaces

 
ADD DATAFILE Page

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

CerebroSQL - add new datafile in Oracle tablespace.jpg

Procedure

  • Select a table space to which you want to add data files in the "Tablespace list" tree

If you need to create multiple data files:

  1. Set the "Autoname" flag

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

    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 many data files, then you must use the template in the name:

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

If autoname = true

$NUM - file number, the value is formed in a loop by 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 you need to enable automatic extension of data files, set the "Autoextend" radio button

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

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

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

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

 
ALTER DATAFILE Page
 

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

Resize datafile
CerebroSQL - alter datafile resize file.

Sequencing

  • In the "Tablespace list" tree, select a table space

  • In the list "Datafile list" set checkboxes opposite data files whose size needs to be changed

  • Enter a new size in the "New size datafile" field

  • Press the "Apply" button

If the operation is successful, the new file size will be displayed in the "Datafile list" after the operation is completed.

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

 
Move page

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

CerebroSQL - Oracle move objects.jpg

Sequencing:

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

or

  • On the "SCHEMA" page, select the schemas whose objects need to be transferred

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

  • In the list "A list of database objects" select the objects that you want to transfer

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

  • Select the table space 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, press the "Show SQL" button