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 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
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 page
Detailed information about tablespaces, statistics on resizing of spaces.
base information
General information about the tablespace, the list of files included in it
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 )
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 )
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
Page "CREATE TABLESPACE"
Used to create tablespaces in an Oracle database
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
The page is intended for mass creation of data files in table spaces.
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:
Set flag " Autoname "
In the " Prefix " field, enter the prefix for the name of the files to be created
In the " Current max number " field, enter the current maximum file number in the selected tablespace
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 Page
The page is intended for changing the parameters of current data files in table spaces
Resize datafile
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 "
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.
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