Владимир Пржиялковский

Таблицы Oracle при интенсивной динамической жизни (связанной в первую очередь с активным обновлением и удалением уже имеющихся строк) имеют тенденцию 'накапливать усталость'. Постоянная перестройка структур физического хранения со временем обычно приводит к деградации эксплуатационных характеристик таблицы. По меньшей мере можно говорить об усугубляющихся следующих негативных факторах:

• Фрагментации табличного пространства
• Утери последовательности хранения последовательных (в соответствии с первичным индексом) строк
• Образования избыточного числа и неоптимальная организация списка свободных блоков
• Образования записей, 'разорванных' по разным блокам

Пересоздать таблицу можно разными способами. Можно использовать программы exp и imp, а также программу SQL*Loader для загрузки предварительно выгруженных (например, программой SQL*Plus) данных. Здесь, однако, мы остановимся на более простом и доступном способе пересоздания с помощью предложения CREATE TABLE : AS : . Пропагандистом этого метода, например, является Donald Burleson, предлагающий его в одном из последних журналов Oracle Magazine.

Пусть имеется таблица x, которую требуется пересоздать. Идея этого метода тогда иллюстрируется последовательностью предложений:
CREATE TABLE y AS SELECT * FROM x;
DROP TABLE x;
RENAME TABLE y INTO x;

У этого метода, тем не менее, есть недостаток. Предложение CREATE TABLE y AS SELECT * FROM x действительно создаст таблицу y со структурой, идентичной структуре x, но оно не воссоздаст в y ограничений целостности, имеющихся в x.

Вся такая информация имеется в словаре-справочнике Oracle и вообще-то доступна. Но для извлечения ее в виде, удобном для нашего случая, целесообразно составить SQL-сценарий. Можно показать, как это сделать для перенесения на таблицу y ограничений DEFAULT и NOT NULL. В обоих случаях нам потребуется обратиться к таблице ALL_TAB_COLUMNS, владельцем которой является SYS. Используем запросы к ней для того, чтобы сформировать SQL-предложения, что позволят нам добавить эти ограничения в определение y.

Добавление нужных ограничений NOT NULL обеспечит следующий запрос:
SELECT 'ALTER TABLE &&1 MODIFY ' || column_name || ' NOT NULL;'
FROM all_tab_columns
WHERE table_name = UPPER('&&1')
AND owner = USER
AND nullable = 'N'
;

С ограничением DEFAULT ситуация несколько сложнее, так как интересующее нас поле DATA_DEFAULT таблицы ALL_TAB_COLUMNS имеет тип LONG. Видимо, так сложилось исторически (словарь-справочник в Oracle существует изначально); сегодня Oracle рекомендует использовать тип CLOB, с которым работать несколько проще, и не использовать менее удобный LONG (хотя, кажется, для этой цели в данном случае вполне хватило бы и еще более простого в работе VARCHAR2).

Для DEFAULT можно предложить такой сценарий с использование временной таблицы:
CREATE TABLE ttttt
(col_name VARCHAR2(30), col_default clob, col_len number);
INSERT INTO ttttt
SELECT column_name, TO_LOB(data_default), default_length
FROM all_tab_columns
WHERE table_name = UPPER('&&1')
AND owner = USER
AND data_default IS NOT NULL
;  
SELECT 'ALTER TABLE &&1 MODIFY ' || col_name || ' DEFAULT ' || DBMS_LOB.SUBSTR(col_default,col_len-1,1) || ';'
FROM ttttt;

Здесь мы вынуждены воспользоваться встроенным в Oracle 8i пакетом DBMS_LOB, предварительно прибегнув к преобразованию LONG в CLOB.

Таким образом полностью сценарий для добавления в создаваемую таблицу ограничений DEFAULT и NOT NULL может полностью выглядеть так:
set heading off
set verify off
set feedback off
set echo off
spool renew&&1..sql
SELECT 'ALTER TABLE &&1 MODIFY ' || column_name || ' NOT NULL;'
FROM all_tab_columns
WHERE table_name = UPPER('&&1')
AND owner = USER
AND nullable = 'N'
;
DROP TABLE transientfor&&1;
CREATE TABLE transientfor&&1
(col_name VARCHAR2(30), col_default clob, col_len number);
INSERT INTO transientfor&&1
SELECT column_name, TO_LOB(data_default), default_length
FROM all_tab_columns
WHERE table_name = UPPER('&&1')
AND owner = USER
AND data_default IS NOT NULL
;
SELECT 'ALTER TABLE &&1 MODIFY ' || col_name || ' DEFAULT ' || DBMS_LOB.SUBSTR(col_default,col_len-1,1) || ';'
FROM transientfor&&1;
DROP TABLE transientfor&&1;  
spool off    Если дать ему имя addconstraints.sql, то выдача следующего предложения обеспечит получение в файле renewemp1.sql сценарий добавления нужных ограничений для emp1:

sqlplus @addconstraints emp1