Oracle Tablespaces
| Tablespace Name | Contents |
|---|---|
|
A mandatory tablespace that consists of the data dictionary, including definitions of tables, views, and stored procedures needed by the database. Oracle Database automatically maintains information in this tablespace. |
|
|
A mandatory, auxiliary system tablespace that is used by many Oracle Database features and products. This tablespace contains content that was previously stored in the |
|
|
An user-created tablespace that consists of application data. As you create and enter data into tables, Oracle Database fills this space with your data. |
|
|
A mandatory tablespace that contains temporary tables and indexes created during SQL statement processing. You may have to expand this tablespace if you run SQL statements that involve significant sorting, such as |
|
|
System-managed tablespaces that contain undo data for each instance. Each Oracle RAC instance uses a different value for n in the tablespace name. These tablespaces are used for automatic undo management. |
|
|
A system tablespace that contains rollback segments. If you do not use automatic undo management, then you must configure the |
You can see what tablespaces exist in the database with the following query.
SQL> select TABLESPACE_NAME from dba_tablespaces;
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
MY_DATA
MY_INDEXES
MY_TEMP
8 rows selected.
In which files the tablespaces are stored.
SQL> select file_name, tablespace_name FROM DBA_DATA_FILES;
System tablespace
The system tablespace stores the “Oracle Data Dictionary”
Each Oracle database contains a set of read-only tables known as the data dictionary, which contains metadata (information about various database components). The Oracle data dictionary is the heart of the database management system.
The data dictionary is created when the database instance is created by executing instructions from the $ORACLE_HOME/rdbms/admin/catalog.sql file.
Oracle does not allow direct access to the data dictionary tables. It creates views based on these tables and public synonyms for those views that users can access. There are three sets of data dictionary views: USER, ALL, and DBA – each containing a similar set of views with a similar set of columns.
SQL> select * from dictionary;
View the contents of the system tablespace
SQL> select segment_name,owner, sum(bytes) from dba_segments where tablespace_name = 'SYSTEM' group by segment_name, owner order by 3,2 desc;
Sysaux tablespace
The sysaux tablespace serves as an auxiliary tablespace to the system tablespace.
SQL> set pagesize 0;
SQL> set linesize 200;
SQL> select occupant_desc,space_usage_kbytes from V$SYSAUX_OCCUPANTS order by space_usage_kbytes;
Server Manageability - Automatic Workload Repository 679360
Unified Job Scheduler 204992
Server Manageability - Advisor Framework 145600
Server Manageability - Optimizer Statistics History 90944
XDB 60096
Oracle Spatial 48704
Oracle Multimedia ORDDATA Components 15616
LogMiner 12544
Server Manageability - Other Components 7744
Workspace Manager 7488
PL/SQL Identifier Collection 5568
Transaction Layer - SCN to TIME mapping 5376
Expression Filter System 3968
Enterprise Manager Monitoring User 1920
SQL Management Base Schema 1728
OLAP API History Tables 1536
Analytical Workspace Object Table 1536
Logical Standby 1408
Oracle Streams 1024
Oracle Multimedia ORDSYS Components 576
Automated Maintenance Tasks 320
Enterprise Manager Repository 0
Oracle Text 0
Oracle Ultra Search 0
OLAP Catalog 0
DB audit tables 0
Oracle Transparent Session Migration User 0
Oracle Multimedia SI_INFORMTN_SCHEMA Components 0
Oracle Multimedia ORDPLUGINS Components 0
Statspack Repository 0
Oracle Ultra Search Demo User 0
31 rows selected.
The USERS tablespace is the tablespace where user data is stored by default.
The UNDO tablespace is used to store undo data, which is used to return modified data to its original state.
The TEMP tablespace is used to store objects that exist for the duration of a user session.
The remaining tablespaces MY_DATA, MY_INDEXES, MY_TEMP are created solely for convenience.
Size and free space for all tablespaces
SQL> SELECT a.tablespace_name, "Free, MB", "Total, MB" FROM
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS "Total, MB" FROM dba_data_files GROUP BY tablespace_name
UNION
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS "Total, MB" FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS "Free, MB" FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY a.tablespace_name;
Result:
TABLESPACE_NAME Free, MB Total, MB
------------------------------ ---------- ----------
MY_DATA 2046 2048
MY_INDEXES 2047 2048
MY_TEMP 2048
SYSAUX 56 820
SYSTEM 1 750
TEMP 54
UNDOTBS1 39 75
USERS 4 5
8 rows selected.
Or this option:
SQL> select a.TABLESPACE_NAME tablespace_name, b.BYTES total_bytes, a.BYTES free_bytes,
round(a.BYTES*100/b.BYTES,2) percent_free,
round((b.BYTES-a.BYTES)*100/b.BYTES,2) percent_used
from (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by a.TABLESPACE_NAME;
TABLESPACE_NAME TOTAL_BYTES FREE_BYTES PERCENT_FREE PERCENT_USED
------------------------------ ----------- ---------- ------------ ------------
MY_DATA 9674162176 9663217664 99.89 .11
MY_DATA2 1073741824 1072693248 99.9 .1
MY_INDEXES 2147483648 2146369536 99.95 .05
SYSAUX 891289600 62717952 7.04 92.96
SYSTEM 807403520 1900544 .24 99.76
UNDOTBS1 78643200 63700992 81 19
USERS 5242880 2228224 42.5 57.5
7 rows selected.
Size and free space for temporary tablespaces
SQL> SELECT a.tablespace_name, total_bytes/1024/1024 AS "Total, MB", used_mbytes AS "Used, MB",
(total_bytes/1024/1024 - used_mbytes) AS "Free, MB" FROM
(SELECT tablespace_name, SUM(bytes_used + bytes_free) AS total_bytes
FROM v$temp_space_header GROUP BY tablespace_name) a,
(SELECT tablespace_name, used_blocks*8/1024 AS used_mbytes FROM v$sort_segment) b
WHERE a.tablespace_name=b.tablespace_name;
Result:
TABLESPACE_NAME Total, MB Used, MB Free, MB
------------------------------- ---------- ---------- ----------
TEMP 54 0 54
MY_TEMP 2048 0 2048
From questions
How to correctly calculate used space in a tablespace?
Not tested on server (Needs verification):
https://t.me/oracledba_net/14189
select a.tablespace_name,
round(a.bytes_alloc / (1024 * 1024)) "TOTAL ALLOC (MB)",
round(a.physical_bytes / (1024 * 1024)) "TOTAL PHYS ALLOC (MB)",
round(nvl(b.tot_used, 0) / (1024 * 1024)) "USED (MB)",
round((nvl(b.tot_used, 0) / a.bytes_alloc) * 100) "% USED",
round((a.bytes_alloc / (1024 * 1024)) - (nvl(b.tot_used, 0) / (1024 * 1024))) "TOTAL FREE (MB)"
from (select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible, 'NO', bytes, 'YES', maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name
union all
select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible, 'NO', bytes, 'YES', maxbytes)) bytes_alloc
from DBA_TEMP_FILES
group by tablespace_name
) a,
(select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name
union all
SELECT tablespace_name,allocated_space-free_space FROM DBA_TEMP_FREE_SPACE
) b
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name not like 'UNDO%'
order by 5 desc;
Not tested on server (Needs verification):
https://t.me/oracledba_net/14206
This is a report, a slightly modified version of which is used as a metric in OEM13.
Select
'Табличное пространство: '||ts.tablespace_name||CHR(13)||
' Всего : '||size_info.megs_alloc||'MB ('||round(size_info.megs_alloc/1024,2)||'Gb)'||CHR(13)||
' Свободно : '||size_info.megs_free||'MB ('||round(size_info.megs_free/1024,2)||'Gb)'||CHR(13)||
' Использованно : '||size_info.megs_used||'MB ('||round(size_info.megs_used/1024,2)||'Gb)'||CHR(13)||
' Свободно/Занято: '|| size_info.pct_free||'%/'||size_info.pct_used||'%'||CHR(13)||
' Максимально возможный размер : '||size_info.max||'MB '||'('||round(size_info.max/1024,2)||'Gb)'||CHR(13)||
' Свободно с учетом авторасширения: '||
case to_char(size_info.max-size_info.megs_alloc)
when '0' then 'АВТОРАСШИРЕНИЕ ОТКЛЮЧЕНО!!! всего осталось: '||to_char(size_info.megs_free)||'MB ('||to_char(round(size_info.megs_free/1024,2))||'Gb)'
else to_char(size_info.max-size_info.megs_alloc)||'MB ('||to_char(round((size_info.max-size_info.megs_alloc)/1024,2))||'Gb) '
end as "INFO"
From
(
select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes/1048576) Max
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(
select ts.name tablespace_name, sum(fs.blocks) * ts.blocksize bytes_free
from DBA_LMT_FREE_SPACE fs, sys.ts$ ts
where ts.ts# = fs.tablespace_id
group by ts.name, ts.blocksize
) b
where a.tablespace_name = b.tablespace_name (+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max
from sys.v_$TEMP_SPACE_HEADER h, dba_temp_files f, sys.v_$Temp_extent_pool p
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
) size_info,
sys.dba_tablespaces ts, sys.dba_tablespace_groups tsg
where ts.tablespace_name = size_info.tablespace_name
and ts.tablespace_name = tsg.tablespace_name (+)
and size_info.max-size_info.megs_alloc<20480 and size_info.megs_free<20480 -- will output all smaller than 20 gigabytes, change in 2 places (in this case 20Gb - 20480)
and (ts.tablespace_name not like 'TEMP%' and ts.tablespace_name not like '%UNDO%' and ts.tablespace_name not in('CWMLITE','DRSYS','ODM')) -- exclude these tablespaces
order by ts.tablespace_name;