Создание, удаление и изменение структуры таблиц

Удаление таблиц

Удаление таблицы выполняется командой DROP TABLE имя_таблицы.

Сведения о таблице СУБД хранит в двух отдельных местах БД: в справочной части БД (в словаре-справочнике) — описание и в виде самостоятельной структуры (сегмента) в файлах табличного пространства — содержимое, данные.

До версии 10 единственным способом удаления по команде DROP TABLE было удаление из словаря-справочника сведений о таблице и удаление сегмента с данными из "рабочей части" БД. С версии 10 возможен (и действует автоматически по умолчанию) второй вариант, когда по команде DROP TABLE описание таблицы и сегмент с ее данными получают новое системное имя, после чего таблица под прежним именем перестает существовать, но фактически какое-то время может быть еще восстановлена. Этот второй способ удаления таблицы воплощает идею "мусорной корзины".

Простое удаление

Пример простой команды удаления таблицы:

DROP TABLE dept_copy2;

Если на столбцы таблицы определены ссылки внешними ключами других таблиц, СУБД не позволит выполнить DROP TABLE и вернет ошибку. (Замечания: (а) это связано не с наличием конкретных ссылающихся строк, а с наличием самого правила внешнего ключа; (б) внешний ключ, ссылающийся на значения столбцов "собственной" таблицы, не препятствует ее удалению). Конструкция CASCADE CONSTRAINTS в команде DROP TABLE позволит-таки удалить таблицу, но при этом СУБД удалит сначала "мешающее" правило внешнего ключа. Столбцы другой, оставшейся таблицы в результате сохранят свои значения, но они уже не будут обременены ограничением ссылочной целостности. Фактически использование CASCADE CONSTRAINTS равносильно последовательному удалению всех правил внешнего ключа, имеющих адресатом таблицу (таковых может быть несколько), и выполнению в завершение простой команды DROP TABLE.

Например, если на столбцы таблицы DEPT_COPY определены ссылки внешними ключами из других таблиц, настоять на удалении DEPT_COPY можно, выдав

DROP TABLE dept_copy2 CASCADE CONSTRAINTS;

Мусорная корзина

С версии 10 смысл команды DROP изменился. В основном случае после нее и описание, и данные таблицы продолжают храниться на своих местах, но под новыми, присвоенными системой автоматически именами. Для пользователя таблица, как и прежде, пропала, однако на деле все, что нужно для ее восстановления, если такая необходимость возникнет, продолжает храниться в БД. Тем самым для таблиц реализована техника мусорной корзины (recycle bin), хорошо известная по файловым системам.

Список содержимого мусорной корзины можно получить из системной таблицы USER_RECYCLEBIN (публичный синоним — RECYCLEBIN):

SELECT object_name, original_name, droptime FROM user_recyclebin;  

Восстановить таблицу по исходному имени (поле ORIGINAL_NAME из USER_RECYCLEBIN) можно, например, так:

FLASHBACK TABLE dept_copy2 TO BEFORE DROP;

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

Для удаления из мусорной корзины нужно использовать команду PURGE, например:

PURGE TABLE dept_copy3;
PURGE RECYCLEBIN;

Чтобы таблица удалялись сразу безвозвратно, следует использовать конструкцию PURGE в команде DROP, например:

DROP TABLE dept_copy3 PURGE;

Умолчательное помещение таблицы в мусорную корзину можно отменить и вернуться к старой обработке команды DROP. Для этого нужно задать значение OFF параметру СУБД RECYCLEBIN. Последний допускает динамическую установку на уровне СУБД (ALTER SYSTEM …) и на уровне сеанса (ALTER SESSION …), например:

ALTER SESSION SET RECYCLEBIN = OFF;

Как и в файловых системах, в Oracle мусорная корзина хранит содержимое до поры до времени. В основном она — инструмент восстановления данных после ошибочных действий пользователя, возникших в результате проведения опытов с базой или по неосторожности.

Изменение структуры таблиц

Изменение структуры и других свойств существующей таблицы необязательно требует удаления ее и воссоздания в переделанном виде. Часто оно осуществимо "по живому", что менее хлопотно для программиста и быстрее. Oracle не чинит чрезмерных препятствий таким изменениям и, как правило, допустит их, если они:

• не вступают в логические противоречия с описанием таблицы в БД, и
• не противоречат уже занесенным в таблицу данным.

В общем допустимы следующие структурные изменения: добавление и удаление столбцов, изменение типа столбца, добавление и убирание ограничений целостности данных. Все они осуществляются разновидностями команды ALTER TABLE.

С версии 11.2 Oracle предлагает к тому же особую технику редакций объектов хранения для внесения изменений в схему данных. Эта техника не рассматривается непосредственно здесь, но в одном из разделов ниже.

Кроме того, с версии 9 Oracle дает возможность переопределять структуру существующих таблиц не средствами SQL (в принципе достаточными для этой цели), а программно с помощью встроенного системного пакета DBMS_REDEFINITION. Делается это исключительно по технологическим соображениям с тем, чтобы время недоступности данных при перестройке было по возможности малым (формально переопределение происходит online, то есть без прекращения доступности вовсе). Главным образом это актуально для таблиц с большими объемами данных при существующих жестких требованиях к доступности. Такая техника в настоящем тексте, посвященном SQL, естественно, не затрагивается.

Добавление столбца

Добавление столбца не связано ни с какими возможными логическими противоречиями и осуществимо практически всегда.

Пример:

ALTER TABLE projx ADD ( pcode VARCHAR2 ( 1 ) );

Единственным логическим препятствием к добавлению столбца служит достижение предельного количества столбцов в таблице Oracle — 1000 (значение взято из документации по Oracle).

Следующее замечание касается не виртуальных (с версии 11), а реальных добавляемых столбцов.

В типичном случае такой добавляемый столбец не будет иметь значений, и его добавление не потребует правки в БД существующих строк таблицы, то есть совершится со скоростью внесения изменений в описание таблицы в словаре-справочнике. Однако если для нового столбца указано свойство DEFAULT, потребуется внести значение в новое поле у всех имеющихся строк. Если таблица велика, на это может уйти много времени. С версии 11 действует оптимизация такого исправления данных для случаев, когда вместе с DEFAULT для столбца одновременно указано NOT NULL. В самом деле, наличие этих свойств обоих сразу позволяет отказаться от фактического добавления значения в каждую строку таблицы, а вместо этого единожды сохранить значение выражения, указанного во фразе DEFAULT, в описании таблицы, а при последующих запросах к строке имитировать наличие этого значения в добавленном поле. С версии 11 СУБД Oracle так и поступает, экономя вдобавок дисковое пространство.

Изменение типа столбца

Выполняется с использованием слова MODIFY, например:

ALTER TABLE projx MODIFY ( pcode NUMBER ( 6 ) );

Логическим препятствием к выполнению такого действия может оказаться вступление в противоречие с заданным ранее ограничением (правилом) целостности, в определении которого участвует столбец. Например наличие внешнего ключа требует согласованности типов ссылающихся столбцов и столбцов-адресатов. Сослаться столбцом типа NUMBER на столбец типа VARCHAR2 нельзя.

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

• тип столбца можно поменять произвольно (TIMESTAMP на VARCHAR2 и так далее), когда столбец целиком пуст или строки в таблице отсутствуют;
• если данные в столбце есть, возможна замена только:
  • в пределах однородных типов (с одинаковым форматом хранения), например, всех разновидностей NUMBER друг на друга, VARCHAR2 на CHAR и обратно;
  • и если это допускают фактические данные:
    • всегда можно увеличить точность хранения в столбце, но
    • уменьшить же ее можно только, когда все существующие значения укладываются в новую точность.

Тип DATE хранится как TIMESTAMP ( 0 ) и однороден с ним в указанном смысле, а вот TIMESTAMP WITH TIME ZONE не однороден не только с DATE, но и TIMESTAMP и допускает взаимные замены с ними только на пустом столбце.

К сказанному имеется оговорка. В силу особенностей хранения данных типов LOB менять тип столбца на них или из них в остальные нельзя даже на пустом столбце. При необходимости такой столбец придется удалить и воссоздать с другим типом.