Для управления табличными пространствами в базе данных Oracle Database нужно познакомиться с несколькими ключевыми табличными пространствами:
DBA_TABLESPACES
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES
DBA_TABLESPACE_GROUPS
В дополнение к этим табличным пространствам для управления и мониторинга табличных пространств в базе данных также очень пригодятся динамические представления производительности V$DATAFILE
и V$FILESTAT
. Ниже мы кратко опишем ключевые представления словаря, касающиеся табличных пространств.
DBA_TABLESPACES
Представление DBA_TABLESPACES
— очень важное представление словаря для управления табличными пространствами. С помощью этого представления можно много узнать о табличных пространствах, например, отключены или подключены ли они в данный момент (offline/online); являются ли они undo, постоянными или временными; тип управления экстентами, тип расширения, тип управления пространством сегментов;состоят они из bigfile или smallfile. В статье наших блогов “Создание табличных пространств” уже были даны примеры применения этого представления. Представление словаря DBA_TABLESPACES
служит для поиска важной информации о табличном пространстве, в том числе:
- размер начального экстента;
- размер следующего экстента;
- максимальное количество экстентов по умолчанию;
- статус (онлайновое, отключенное, только для чтения);
- содержимое (постоянное, временное или undo);
- тип управления экстентами (
DICTIONARY
илиLOCAL
); - управление пространством сегмента (
AUTO
илиMANUAL
).
DBA_FREE_SPACE
Представление DBA_FREE_SPACE
сообщает о том, сколько свободного места в базе данных имеется на каждый данный момент. Запрос из листинга 1 ниже позволяет узнать,сколько свободного места есть в табличных пространствах. Обратите внимание, что место, относящееся к удаленной таблице, которая находится в “корзине”, отображается в этом представлении как свободное. Однако его нельзя использовать для размещения любого другого объекта. Вы получаете это пространство обратно только после того, как окончательно удалите элемент оператором ALTER TABLE...PURGE
.
SQL> SELECT tablespace_name, SUM(bytes)
2 FROM DBA_FREE_SPACE
3* GROUP BY tablespace_name;
TABLESPACE_NAME SUM(BYTES)
--------------- ----------
CWMLITE 11141120
DRSYS 10813440
EXAMPLE 262144
INDX 26148864
ODM 11206656
SYSTEM 4325376
TOOLS 4128768
UNDOTBS1 202047488
USERS 26148864
XDB 196608
10 rows selected.
SQL>
DBA_SEGMENTS
Как вам известно, база данных Oracle содержит несколько видов сегментов: таблиц,индексов, undo и т.д. Представление словаря данных DBA_SEGMENTS
, помимо прочего,показывает имя и тип сегмента вместе с табличным пространством, к которому он относится. Это представление отображает детальную информацию о различных сегментах базы данных, что видно из листинга 2.
SQL> SELECT
2 tablespace_name,
3 segment_name,
4 segment_type,
5 extents, /*Количество экстентов в сегменте*/
6 blocks, /*Количество блоков БД в сегменте*/
7 bytes /*Количество байтов в сегменте*/
8 FROM dba_segments
9* WHERE owner = 'HR';
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE EXTENTS BLOCKS BYTES
--------------- ------------ ------------ ------- ------ ------
EXAMPLE REGIONS TABLE 1 8 65,536
EXAMPLE LOCATIONS TABLE 1 8 65,536
EXAMPLE DEPARTMENTS TABLE 1 8 65,536
EXAMPLE JOBS TABLE 1 8 65,536
EXAMPLE EMPLOYEES TABLE 1 8 65,536
EXAMPLE JOB_HISTORY TABLE 1 8 65,536
EXAMPLE REG_ID_PK INDEX 1 8 65,536
EXAMPLE COUNTRY_PK INDEX 1 8 65,536
EXAMPLE LOC_ID_PK INDEX 1 8 65,536
EXAMPLE DEPT_ID_PK INDEX 1 8 65,536
EXAMPLE DEPT_LOC_IX INDEX 1 8 65,536
. . .
25 rows selected.
SQL>
DBA_DATA_FILES
Представление словаря данных DBA_DATA_FILES
— еще одно исключительно полезное представление, к которому вы будете часто обращаться при управлении пространством хранилища базы данных. Это представление запрашивается для нахождения имен всех файлов данных, табличных пространств, к которым они относятся, и информации об этих файлах, такой как количество байтов и блоков, а также относительный номер файла. Простой запрос к представлению DBA_DATA_FILES
отображает все файлы данных, как показано в листинге 3.
SQL> SELECT file_name, tablespace_name FROM DBA_DATA_FILES;
FILE_NAME TABLESPACE_NAME
------------------------------------------ -------------
C:\ORACLENT\ORADATA\MANAGER\SYSTEM01.DBF SYSTEM
C:\ORACLENT\ORADATA\MANAGER\UNDOTBS01.DBF UNDOTBS
C:\ORACLENT\ORADATA\MANAGER\CWMLITE01.DBF CWMLITE
C:\ORACLENT\ORADATA\MANAGER\DRSYS01.DBF DRSYS
C:\ORACLENT\ORADATA\MANAGER\EXAMPLE01.DBF EXAMPLE
C:\ORACLENT\ORADATA\MANAGER\INDX01.DBF INDX
C:\ORACLENT\ORADATA\MANAGER\TOOLS01.DBF TOOLS
C:\ORACLENT\ORADATA\MANAGER\USERS01.DBF USERS
8 rows selected.
SQL>
Представление
DBA_DATA_FILES
особенно полезно, когда вы соединяете его с другим представлением словаря данных, как в примере, который иллюстрирует листинг ниже. Этот запрос производит отчет, показывающий размеры табличных пространств, свободное и занятое место, а также процент занятого места в каждом табличном пространстве. В конце вы также получаете сумму всего пространства хранилища, выделенного всем табличным пространствам, а также распределение свободного и занятого пространства в базе данных.
BREAK ON REPORT
COMPUTE SUM OF tbsp_size ON REPORT
compute SUM OF used ON REPORT
compute SUM OF free ON REPORT
COL tbspname FORMAT a20 HEADING 'Tablespace Name'
COL tbsp_size FORMAT 999,999 HEADING 'Size|(MB)'
COL used FORMAT 999,999 HEADING 'Used|(MB)'
COL free FORMAT 999,999 HEADING 'Free|(MB)'
COL pct_used FORMAT 999 HEADING'% Used'
SQL> SELECT df.tablespace_name tbspname
2 sum(df.bytes)/1024/1024 tbsp_size,
3 nvl(sum(e.used_bytes)/1024/1024,0) used,
4 nvl(sum(f.free_bytes)/1024/1024,0) free,
5 nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used,
6 FROM DBA_DATA_FILES df
7 (SELECT file_id
8 SUM(nvl(bytes,0)) used_bytes
9 FROM dba_extents
10 GROUP BY file_id) e,
11 (SELECT MAX(bytes) free_bytes, file_id
12 FROM dba_free_space
14 GROUP BY file_id) f
15 WHERE e.file_id(+) = df.file_id
16 AND df.file_id = f.file_id(+)
17 GROUP BY df.tablespace_name
18* ORDER BY 5 DESC
Size Used Free
Tablespace Name (MB) (MB) (MB) % Used
--------------- ------ ------ ----- -------
PERSON_INFO_I 2,299 2,245 54 98
PERSONS_I 26348 6,185 162 97
LABELS_I 2,038 1,980 58 97
. . .
CBC_I 501 7 490 1
QUEST 10 0 10 1
TEST2 10 0 1 0
-------- ------- -------
Grand Total 291,528 224,473 43,602
SQL>
Представление DBA_TEMP_FILES
— аналог представления DBA_DATA_FILES
, дающего информацию о временных файлах временных табличных пространств.
DBA_TABLESPACE_GROUPS
Вы можете собирать набор временных табличных пространств в группы временных табличных пространств. Представление DBA_TABLESPACE_GROUPS
показывает все группы табличных пространств, имеющиеся в базе данных. В этом представлении можно также увидеть индивидуальное имя каждого табличного пространства в каждой такой группе.
V$DATAFILE
Представление V$DATAFILE
содержит информацию об имени файла данных, номер табличного пространства, статус, временную метку последнего изменения и т.п. Представление V$TEMPFILE
показывает ту же информацию о файлах временных табличных пространств. Представление V$DATAFILE
отображает важную информацию, когда его соединить с представлением V$FILESTAT
.
V$FILESTAT
Представление V$FILESTAT
снабжает детальной информацией о статистике чтения/записи файла, включая количество физических операций чтения и записи, время,потребовавшееся на эти операции, а также среднее время одной операции чтения и записи в миллисекундах. Представление V$TABLESPACE
содержит информацию о табличных пространствах. Листинг 5 демонстрирует, как можно соединить представления V$DATAFILE,
V$TABLESPACE
и V$FILESTAT
для получения полезной информации о дисковых операциях ввода-вывода.
SQL> SELECT d.name, t.name, f.phyrds, f.phywrts,
2 f.readtim, f.writetim
3 FROM V$DATAFILE d,
4 V$FILESTAT f,
5 V$TABLESPACE t
6 WHERE f.file# = d.file#
7* AND d.ts# = t.ts#;
NAME T.NAME PHYRDS PHYWRTS READTIM WRITETIM
---------- ------ ------ ------- ------- ---------
C:\ORACLEN SYSTEM 46180 98697 29637 473716
T\ORADATA\
MANAGER\SY
STEM01.DBF
C:\ORACLEN UNDOTBS 330 140887 801 165629
T\ORADATA\
MANAGER\UN
DOTBS01.DBF
C:\ORACLEN DRSYS 649 23 515 0
T\ORADATA\
MANAGER\DR
SYS01.DBF
C:\ORACLEN INDX 34 23 4 0
T\ORADATA\
MANAGER\IN
DX01.DBF
SQL>