Табличные пространства Oracle


Tablespace Name Contents

SYSTEM

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.

SYSAUX

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 DRSYS, CWMLITE, XDB, ODM, OEM_REPOSITORY, and SYSTEM tablespaces.

USERS

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.

TEMP

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 ANALYZE COMPUTE STATISTICS on a very large table, or the constructs GROUP BY, ORDER BY, or DISTINCT.

UNDOTBSn

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.

RBS

A system tablespace that contains rollback segments. If you do not use automatic undo management, then you must configure the RBS tablespace. The RBS tablespace should only be used when needed for compatibility with earlier versions of Oracle Database.


Посмотреть, какие табличные пространства имеются в базе данных можно следующим запросом.

SQL> select TABLESPACE_NAME from dba_tablespaces;


SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
MY_DATA
MY_INDEXES
MY_TEMP

8 rows selected.


В каких файлах хранятся табличные пространства.


SQL> select file_name, tablespace_name FROM DBA_DATA_FILES;


Табличное пространство system

В табличном пространстве system хранится «Словарь данных Oracle»

Каждая база данных Oracle содержит набор таблиц, доступных только для чтения и известных как словарь данных (data dictionary), который содержит метаданные (информацию о различных компонентах базы данных). Словарь данных Oracle – сердце системы управления базой данных.

Словарь данных создается при создании экземпляра базы данных выполнением инструкций в файле $ORACLE_HOME/rdbms/admin/catalog.sql

Oracle не позволяет обращаться к таблицам словаря данных напрямую. Он создает представления на базе этих таблиц и общедоступные синонины для тих представлений, к которым могут обращаться пользователи. Существует три набора представлений словаря данных: USER, ALL и DBA – каждый из которых содержит сходный набор представлений со сходным набором столбцов.


SQL> select * from dictionary;


Посмотреть содержимое табличного пространства system


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

Табличное пространство sysaux служит вспомогательным табличным пространством по отношению к табличному пространству system.


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.


Табличное пространство USERS – табличное пространство, где по умолчанию хранятся пользовательские данные.

Табличное пространство UNDO служит для хранения данных отмены, которые используются для возврата измененных данных в исходное состояние.

Табличное пространство TEMP – служит для хранения объектов, существующих на протяжении существования пользовательского сеанса.

Остальные табличные пространства MY_DATA, MY_INDEXES, MY_TEMP – созданы исключительно для удобства.


Размер и свободное место для всех табличных пространств

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.


Или такой вариант:


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.


Размер и свободное место для временных табличных пространств

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


Из вопросов

Как правильно считать используемое место в tablespace?


Не запускалось на сервере (Нужно проверить):

https://t.me/oracle_dba_ru/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;


Не запускалось на сервере (Нужно проверить):

https://t.me/oracle_dba_ru/14206

Это отчет, такой же переделанный немного стоит в качестве метрики в 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 -- выведет все которые меньще 20 гигабайт меянть в 2х местах (в данном случае 20Гб - 20480)
and (ts.tablespace_name not like 'TEMP%' and ts.tablespace_name not like '%UNDO%' and ts.tablespace_name not in('CWMLITE','DRSYS','ODM')) -- не учитывать эти табличные пространства
order by ts.tablespace_name;