Oracle изменить размер datafile

Обновлено: 06.07.2024

В прошлый раз я упустил один момент, давайте еще не надолго вернемся к дефрагментации. Это достаточно обширная тема, но думаю последнее, на что стоит обратить внимание это размер блока Oracle. Он содержатся в файле init.ora в секции db_block_size и имеет, как правило, оптимальное значение выбранное по умолчанию. Но эффект от увеличения размера блок просто поражает! В большинстве случаев используют блоки двух размеров 2 и 4 Кбт. (Хотя я почти всегда ставлю 8 Кбт!). Переход на больший размер блока может повысить производительность на 50%! И достигается это без значительных затрат! Учтите, что менять секцию db_block_size просто так нельзя! Для увеличения размера блока БД лучше пересоздать весь экземпляр заново с новым значением! Повышение производительности связано со способом работы сервера Oracle с заголовком блока. Как следствие для данных используется больше места, что улучшает возможность обращения к одному и тому же блоку данных, от нескольких пользователей. Удвоение размера блока Oracle практически не влияет на его заголовок. Это значит, что в процентном отношении для заголовка расходуется меньше места! Но учтите, что, например, удвоение размера блока Oracle так же будет влиять на кэш буфера данных и может вызвать проблемы с управлением памятью на сервере!

Теперь давайте рассмотрим момент, когда табличное пространство необходимо модифицировать в ту или иную сторону. Например, рассмотрим случай когда табличное пространство и связанный с ним файл данных необходимо усечь в размерах! Сделать это можно, например, с помощью команды ALTER DATABASE. Но учтите, что нельзя изменить размер файла данных, если пространство, которое вы пытаетесь освободить, в настоящий момент занято объектами БД. Например, если объекты БД занимают объем 200 Мб, а размер файла данных 300 Мб, то можно отсечь только 100 Мб у файла данных! Сама команда будет выглядеть вот так:

При этом учтите, если табличное пространство сильно дефрагментировано, то Oracle может выдать ошибку при попытке усечь табличное пространство! Далее давайте посмотрим как можно производить сокращение таблиц и индексов в БД. Но, для начала проделаем следующее. Создадим таблицу SPEED в схеме MILLER:

Теперь запишем вот такой блок для того, чтобы наполнить таблицу данными! Для примера:

Время, которое потратил Oracle в моем случае составило 2,5 Сек. (Это оценивает PL/SQL Developer). Когда Oracle записывает данные в сегмент, обновляется так называемая - верхняя отметка (high - water mark - высшая точка) сегмента. Верхняя отметка сегмента - это наибольший номер блока сегмента, в котором вы когда-либо хранили данные. Если вы добавили скажем 5000 строк верхняя отметка будет увеличиваться! Дайте к таблице SPEED вот такой запрос:

Время на исполнение у меня было 0.016 сек. Хорошо. Запрос прошел все блоки таблицы до верхней отметки. А теперь удалим записи:

Время на удаление чуть больше, уже 0.235 сек! А теперь повторите прошлый запрос:

Снова 0.016 сек! Но почему? А в следствии того, что при удалении записей из таблицы ее high - water mark не снижается и запрос прошел все блоки снова! Вот как! Если не считать удаление таблицы и ее воссоздание, верхняя отметка сегмента переустанавливается только после команды TRUNCATE TABLE (к ней мы еще вернемся!) Давайте проделаем следующее. Снова наполним таблицу:

А теперь дадим команду нашего запроса:

Время снова примерно 0.017 сек. Хорошо, даем вот такую команду:

Затраченное время 0 сек! Указатель high - water mark был перемещен! Что и требовалось доказать! Здесь так же кроется некий подводный камень, при работе с таблицами БД и особенно большими таблицами! Знание этого нюанса думаю в дальнейшем поможет вам справляться с распределением табличного пространства под объекты БД. Найти верхнюю отметку для таблицы CUSTOMERS для схемы MILLER нашей учебной БД поможет такой сценарий (для того, чтобы все получилось необходимо зайти в экземпляр пользователем SYS или SYSTEM!):

Здесь используется пакет SYS.dbms_space и его метод unused_space! Получаем:

Здесь верхняя отметка таблицы (в байтах) представляет собой разницу между значениями TOTAL_BYTES и UNUSED_BYTES. Значение UNUSED_BLOCKS соответствует числу блоков выше высшей точки. TOTAL_BLOCKS это общее количество блоков связанное с данной таблицей! Улавливаете! Если нужно сжать таблицу и значение UNUSED_BLOCKS не равно нулю, с помощью команды ALTER TABLE можно забрать пространство выше верхней отметки. Чтобы освободить занимаемое таблицей пространство можно дать команду:

И действительно зачем ей лишние 8 блоков! У меня это получается (16 * 8192) - (8 * 8192) = 65536! Вот так лишнее долой! Кстати, если не указать конструкцию keep, то значение параметров сохранения minextents и initial таблицы останутся прежними. Если использовать keep, то можно освобождать свободное пространство из любого экстента! Даже из initial, если в других экстентах данных нет! Так, что пользуйтесь возможностью борьбы с неиспользуемым свободным местом табличных пространств! Но, осторожно! Удачи!

Когда ваше табличное пространство заполняется данными таблиц или индексов, необходимо увеличить его размер. Это делается добавлением файлов в команде ALTER TABLESPACE:

Можно также увеличивать или уменьшать размер табличного пространства, увеличивая или уменьшая размер файлов данных табличного пространства опцией RESIZE.Обычно опция RESIZE применяется для исправления ошибок, допущенных при задании размера файла данных. Обратите внимание, что размер файла данных нельзя сделать меньше того, что уже занят объектами, хранящимися в нем.

Следующий пример показывает, как изменить размер файла данных вручную.Изначально файл имеет размер 250 Мбайт, а следующая команда удваивает его размер до 500 Мбайт. Заметьте, что для изменения размера файла данных необходимо использовать команду ALTER DATABASE, а не ALTER TABLESPACE.

При создании табличного пространства или при добавлении к табличному пространству файлов данных можно указывать конструкцию AUTOEXTEND, чтобы заставить Oracle автоматически расширять размер файлов данных в табличном пространстве до заданного максимума.

Вот как выглядит синтаксис использования средства AUTOEXTEND:

В предыдущем примере экстенты в 10 Мбайт будут добавляться к табличному пространству, когда понадобится дополнительное место, как указано в параметре AUTOEXTEND. Параметр MAXSIZE ограничивает табличное пространство размером в 1000 Мбайт. При желании можно также специфицировать MAXSIZE UNLIMITED; в этом случае не устанавливается максимальный размер данного файла данных, а следовательно и всего табличного пространства. Однако необходимо убедиться в наличии достаточного пространства на диске операционной системы.

Oracle также предоставляет средство Resumable Space Allocation, временно приостанавливающее операции, которые могут в противном случае завершиться сбоем из-за нехватки места, а затем возобновляет их выполнение после того, как вы добавите место для объекта базы данных. Это делает использование средства AUTOEXTEND менее привлекательным.

После Google не могу найти простой способ вернуть свободное место после удаления таблицы.

Я нашел много объяснений, рассказывающих, как файл данных становится фрагментированным, большой стек скучных запросов, которые вам нужно выполнить, чтобы переместить «пустое пространство» в конец файла данных (таблица за таблицей . даже когда вы есть 200 столов!?).

Затем вам нужно уменьшить размер файла данных, «угадав», насколько вы можете его уменьшить, или вы должны точно знать, каков ваш «размер блока» . И, наконец, вы не должны забывать «перестраивать индексы».

Существует ли простая процедура PL / SQL, которая, учитывая имя табличного пространства или имя файла данных, будет выполнять эту работу? Или любой подобный инструмент Oracle?

Краткий ответ - нет . К сожалению, способ сделать это в Oracle требует «большого количества скучных запросов». Статьи, на которые вы ссылаетесь, являются одними из лучших на эту тему. Файл данных действительно фрагментируется, поэтому даже если свободное пространство находится ниже самого высокого сегмента, Oracle не будет автоматически консолидировать его, когда RESIZE будет выполнено a .

Чтобы «дефрагментировать» табличное пространство, вам нужно переместить эти сегменты в начало файла данных, а не в конец. Для таблиц это автономный процесс, то есть таблица будет недоступна во время перемещения. Индексы можно перемещать либо в автономном режиме, либо с помощью Enterprise Edition их можно перемещать в режиме онлайн. Поскольку у вас есть окно отключения, я рекомендую вам выполнить следующие шаги.

A. Сжатие файлов данных со свободным пространством за отметкой максимальной воды. Это можно сделать следующим образом (запрос аналогичен процедуре Frosty Z):

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

C. Для каждого из этих табличных пространств определите, какие сегменты необходимо переместить. (Замените USERS именем вашего табличного пространства или присоедините его к предыдущему запросу)

D. Переместите каждую таблицу и перестройте индексы и статистику.

E. повторите шаг A.

Я только что построил большинство этих запросов, так что вы захотите тщательно протестировать их перед использованием. Я полагаю, вы могли бы создать процедуру, которая использовалась бы EXECUTE IMMEDIATE для создания фактических операторов для динамического выполнения, но поскольку запросы будут получать ORA-08103: объект больше не существует, пока выполняется перемещение, я думаю, что лучше контролировать этот процесс вручную, даже если если это действительно означает немного больше времени / усилий.

Табличные пространства это хранилище для данных схемы, включая словарь данных (который находится в схеме SYS). У всех баз данных обязаны быть табличные пространства SYSTEM и SYSAUX и (для работы с БД) временное табличное пространство и пространство undo. Обычно эти четыре табличных пространства создаются на этапе создания БД. В дальнейшем DBA может создавать много других табличных пространств для пользовательских данных, и возможно дополнительные пространства для undo и временных данных.

Создание табличного пространства

Для создания табличного пространства с помощью Enterpise Manager Database Control с домашней страницы базы данных перейдите на вкладку Server и нажмите на ссылку Tablespaces в разделе Storage. На рисунке 5-3 отображается результат для БД по умолчанию.

Рисунок 5-3 – Табличные пространства в БД ocp11g

Для каждого табличного пространства отображаются:

Allocatedsize –размер файла/ов данных табличного пространства. Это текущее значение, а не максимальное до которого может быть расширено
Spaceused – это пространство использованное сегментами
Allocatedspaceused (%) – графическое представление предыдущих значений
Allocatedfreespace – пространство доступное на данный момент в табличном пространстве
Status – зелёная галочка отображает что табличное пространтсво в рабочем состоянии и объекты этого пространства доступны для использования. Выключенное табличное пространство (offline) будут отображаться как красный крестик
Datafiles – колчиество файлов данных (или временных файлов для временного табличного пространства) которые входят в табличное пространство
Type – типы объектов, которые могут храниться в этом табличном пространстве. Постоянные пространства (permanent) могут хранить обычные объекты схемы, такие как таблицы и индексы. Временные табличные пространства могут хранить управляемые системой временные а сегменты, а пространства undo только системно управляемые сегменты undo
Extentmanagement – метод выделения экстентов для сегмента. LOCAL значение по умолчанию и всегда должно использоваться
Segmentmanagement – метод выделения блоков доступных для вставки данных. AUTO значение по умолчанию и рекомендуется для всех табличных пространств содержащих пользовательские данные.

Эту же информацию можно получить написав запрос к предсатвлениям словаря данных DBA_TABLESPACES, DBA_DATA_FILES, DBA_SEGMENTS и DB_FREE_SPACE. К примеру результатом выполнения запроса

t.status, d.cnt, contents, t.extent_management extman,

from dba_tablespaces t,

(select sum(bytes) allocated, count(file_id) cnt from dba_data_files

(select sum(bytes) free from dba_free_space

(select sum(bytes) used from dba_segments

На этой же странице нажмите кнопку CREATE для создания нового табличного пространства. Новое окно создания запросит название нового табличного пространства, значения для Extent Management, Type и Status. В большинстве случаев значения по умолчанию валидны: Local, Permanent и Read Write. Затем кнопка ADD позволяет указать один или более файлов данных для нового табличного пространства. Для каждого файла необходимо указать имя файла и размер, также опционально можно включить autoextend до максимального допустимого размера файла. Возможность autoxtend позволяет Oracle серверу самому увеличиваться размер файлов данных при необходимости, что позволит избежать ошибок из-за нехватки места. На рисунках 5-4 и 5-5 отображены окна Database Control для создания табличного пространства NEWTS с одним файлом данных.

Рисунок 5-4 Окно создания табличного пространства Рисунок 5-5 Окно добавления файла данных

Нажав кнопку CONTINUE вы вернётесь к окну создания табличного пространства. Нажатие кнопки SHOW SQL отобразит сгенерированный запрос

Рассмотрим эту команду построчно

3 SIZE 100M AUTOEXTEND ON NEXT 100K MAXSIZE 200M – Файл данных будет создан размеров 100 Мб, при заполнении будет автоматически выделяться 100Кб до максимально допустимого значения в 200 Мб. По умолчанию автоматическое выделения места не включено.

4 LOGGING – Все операции над сегментами в табличном пространстве будут генерировать данные для повтора изменений (redo): это значение по умолчанию. Возможно отключить генерацию redo только для нескольких операция (таких как создание индекса)

5 EXTENT MANAGEMENT LOCAL – табличное пространство будет использовать битовые карты для выделения экстентов; значение по умолчанию

6 SEGMENT SPACE MANAGEMENT AUTO – Сегменты в табличном пространстве будут использовать битовые карты для отслеживания использования блоков; значение по умолчанию

Если выбрать вкладку Storage в окне создания табличного пространства то можно управлять параметрами управления экстентами и сжатия – Рисунок 5-6.

Рисунок 5-6 Дополнительные параметры создания табличного пространства

Когда используется локальное управление экстентами, возможно включить функционал согласно которому все экстенты в табличном пространстве будут одного размера. Также доступны опции сжатия, логирования.

Пример команды создания табличного пространства выполненной в SQL* Plus показан на рисунке 5-7.

Табличное пространство STORETABS состоит из двух файлов данных, оба без автоинкремента. Единственное различие от команды по умолчанию это указание размера extent-а в 5 МБ. Первый запрос на рисунке показывает что файлы не большие иначе нельзя было бы создать два файла данных. Второй запрос на рисунке отображает информацию о табличном пространстве TEMP, используемом для хранения временных объектов. Важно понимать что временное табличное пространство использует временные файлы, а не файлы данных. Временные файлы перечислены в пресдатвлении V$TEMPFILE и DBA_TEMP_FILES, когда файлы данных перечислены в V$DATAFILE и DBA_DATA_FILES. Также важно отметить что V$ и DBA показывают разную информацию. В V$TABLESPACE находится информация является ли табличное пространтсво «большим» и в V$TEMPFILE (или V$DATAFILE) размер файлов. Этой информации нет в представлениях DBA. Но представления DBA содержат детальную информацию о экстентах и сегментах. Разная информация доступна либо там либо там, так как некоторая информация хранится в файле контроле (и доступна только в V$ представлениях), а другая хранится в словаре данных (и видима в DBA представлениях). Остальная информация дублируется.

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

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

Переименование
Включение выключение (online/offline)
Переключение в режим только-чтение и обратно
Изменение размера
Изменение уровня препупреждений

Переименование табличного пространтсва и его файлов

ALTER TABLESPACE tablespaceoldname RENAME TO tablespacenewname;

Первая команда изменяет имя табличного пространства. Это самая легкая часть. Затем табличное пространство выключается, и выполняются команды операционной среды переименовать файлы. Затем две команды ALTER DATABASE изменяют имена файлов в файле контроля, и Oracle сможет их найти. И наконец табличное пространство включается.

Включение и выключение табличного пространтсва (Online/Offline)

Включенным табличным пространством является пространство готовое к использованию; выключенное табличное пространство определено в словаре данны и файле контроля, но его нельзя использовать. Табличное пространтсво может быть включенным, но один или несколько файлов данных внутри него могут быть выключены. Такая ситуация может привести к неожиданным результатам и следует избегать её возникновения.

Синтаксис для выключения табличного пространства

ALTER TABLESPACE tablespacename OFFLINE [NORMAL|IMMEDIATE|TEMPORARY]

Обычное выключение (NORMAL – значение по умолчанию) запустит создание контрольной точки для всех файлов данных этого пространства. Все грязные буфферы в кэше которые содержат блоки из табличного пространтсва будут записаны в файлы данных и табличное пространство и файлы данных будут выключены.

Режим IMMEDIATE выключает табличное пространтсво и файлы данных сразу, без очищения буфера. Это значит что файлы данных станут повреждёнными (не содержат подтверждённые изменения) и необходимо будет восстанавливать их состояние (путём чтения и применения лога изменений) перед тем как можно будет их включить. Этот режим стоит использовать только если файлы были повреждены и контрольная точка не может быть создана.

Режим TEMPORARY создаст контрольную точку для всех файлов для которых она может быть создана и затем по порядку выключит файлы и табличное пространство. Любые повреждённые файлы будут выключены сразу. Если только один некоторые файлы были повреждёны, этот режим поможет ограничить количество файлов которые надо восстанавливать.

Режим только-чтения (Read only)

Чтобы понять эффект режима только чтения изучите рисунок 5-9. Синтаксис команды говорит сам за себя

ALTER TABLESPACE tablespacename [READONLY|READ WRITE];

После перевода в режим только-чтения, объекты не могут быть созданы используя DML команды. Но они могут быть удалены. Выглядит нелогично но давайте подумаем. Удаление таблицы на самом деле не выполняет каких-либо действий над таблицей. Это транзакция над словарём данных, которая удаляет строки описывающие таблицу и её столбцы; словарь данных находится в табличном пространстве SYSTEM и оно не только для чтения. Создание таблицы в пространстве находящемся в режиме только для чтения также будет неуспешно, так как кроме DDL запроса должно выделиться физическое место для первого экстента таблицы (если не включен deferred segment creation. Если включен то место не будет выделяться и запрос выполниться успешно, однако при попытке добавить строки в созданную таблицу будет ошибка).

Изменение размера табличного пространста

Размер табличного пространства может быть изменён как добавлением файлов данных в него так и изменением размера существующих файлов. Файлы данных могут быть увеличены автоматически при необходимости если была указана опция AUTOEXTEND при создании. Иначе вам придётся делать это вручную используя команду ALTER DATABASE

ALTER DATABASE DATAFILE filename RESIZE n [M|G|T]

M G и T это мегабайт, гигабайт и терабайт соответсвенно.

alter dtabase datafile ‘/oradata/users_02.dbf’resize 10m;

Из команды вы не знаете станет ли файл больше или меньше. Размер может быть увеличен только если в файловой системе достаточно свободного места. Уменьшение размера возможно только если внутри файла сущесвует достаточно места не выделенного под сегменты.

Изменение уровня предупреждений

Чтобы посмотреть или изменить эти значения в Database Control можно выбрать Tablespace на вкладкe Server и нажать EDIT. Затем в окне управления пространством перейти на вкладку Thresholds. На рисунке 5-10 показан пример для пространства EXAMPLE.

Рисунок 5-10 Окно управления предупреждениями

На этом рисунке “Available Space” указано как 32Гб. Что полностью неверно, так как выделенное место как видно на рисунке 5-3 всего 100МБ. Это происходит так как включен AUTOEXTENSION. Если AUTOEXTEND указан для файла и не установленм MAXSIZE, тогда файл может увеличиваться до платформо-зависимого ограничения, в нашем случае 32 ГБ. Конечно это не значит что у системы есть место для таких файлов. Система предупреждения рассчитыает лимиты и использует максимально допустимый размер файла как основу для вычисления, и это абсолютно быссмесленно если у вас включен AUTOEXTEND и не указан MAXSIZE.

Становится понятно что при использовании автоматического управления размером желательно указывать максимальное значение. Это можно сделать и Database Control или командой ALTER DATABASE.

Удаление табличных пространств

Для удаления табличного пространтсва используется команда

DROP TABLESPACE tablespacename [INCLUDING CONTENTS [AND DATAFILES]];

Если не указано INCLUDING CONTENS то удаление не выполнится если существуют объекты в табличном пространстве. Используя этот параметр вы указываете Oracle вначале удалить все объекты, а затем удалить табличное пространство. И даже эта команда может быть выполнена неуспешно если например в табличном пространстве создана таблица родитель для внешнего ключа таблицы созданной в другом табличном пространстве.

Если не указано AND DATAFILES тогда табличное пространство и его содержимое будет удалено однако файлы данных не будут удалены с диска. Oracle не будет знать ничего об этих файлах после удаления табличного пространства и файлы можно будет удалить только командами операционной системы.

Oracle Managed Files (OMF)

Использование OMF должно избавить DBA от необходимости знать что-либо о файловой системе. Создание файлов БД может быть полностью автоматическим. Для включения OMF необходимо установить параметры экземпляра

Параметр DB_CREATE_FILE_DEST определяет путь по умолчанию дял файлов данных. Параметры DB_CREATE_ONLINE_LOG_DEST_n указывают путь файла текщих логов. Параметр DB_RECOVERY_FILE_DEST определяет путь к файлам архивных логовов и резервных копий. OMF будет использовать эти пути и создавать файлы со сгенерированными именами и (по умолчанию) устанавливать размер файлов. При включенном OMF всё равно можно указать имя вручную при создании табличного пространства.

Читайте также: