Валерий Юринский,
ORACLE Certified Professional

Сервер Oracle8i предоставляет новую возможность быстрого обмена большими объемами информации между базами данных Oracle - перемещаемые табличные пространства (transportable tablespaces). Некоторым аспектам практического применения этой возможности посвящена эта статья.

Как всегда возникают вопросы:

• А что мы с этого будем иметь?
• Кому, что и как это облегчает и усложняет?

Вот лишь некоторые ответы. Перемещаемые табличные пространства обеспечивают:

• Оперативное предоставление разработчикам свежей копии данных производственной системы.
• Использование информации, публикуемой на неперезаписываемых носителях (CD-ROM, DVD и т.п.).
• Быстрое перемещение данных из оперативной системы в хранилище или в витрину данных.

Ускорение переноса информации достигается за счет замены ресурсоемких процессов экспорта-импорта или выгрузки-загрузки значительно более быстрым копированием файлов данных с одной вычислительной установки на другую.

Существует ряд условий ограничивающих применение описываемой возможности.

1) "Донором" может быть только Oracle8i Enterprise Edition. "Приёмником" - любой вариант сервера Oracle8i: Enterprise/Standard/Personal Edition.

SELECT * FROM v$option

WHERE UPPER(parameter) =
'EXPORT TRANSPORTABLE TABLESPACES'

/

Все в порядке, если в результате будет получено:

PARAMETER VALUE

-------------------------------- -----

Export transportable tablespaces TRUE

2) Можно перемещать табличные пространства только между такими базами данных Oracle8i, которые:

• имеют одинаковый размер блока (db_block_size),
• созданы с одинаковой кодировкой (character set),
• в файлах инициализации (INIT.ORA) исходной и целевых баз данных параметр COMPATIBLE должен быть установлен в значение 8.1,
• работают на совместимых платформах одного и того же производителя оборудования.

Удовлетворение первых трех требований можно проверить, выполнив в исходной и целевой БД (результаты должны быть идентичны) запрос:

SELECT name, value FROM v$parameter

WHERE name IN ('db_block_size','compatible')

UNION ALL

SELECT parameter, value FROM nls_database_parameters

WHERE parameter LIKE '%CHARACTERSET'

/

NAME VALUE

--------------------- ------------

db_block_size 4096

compatible 8.1.0

NLS_CHARACTERSET CL8MSWIN1251

NLS_NCHAR_CHARACTERSET CL8MSWIN1251

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

4) Не поддерживается транспортировка:

• снапшотов и тиражируемых таблиц (Snapshot/replication),
• Функциональных индексов (Function-based indexes),
• локальных ссылок на объекты (Scoped REFs),
• доменных индексов (Domain indexes) (новый тип индексов, предоставляющих возможность расширенного индексирования),
• совместимых с v8.0 улучшенных очередей (advanced queues) с множественными получателями (recipients).

Предположим, что нам нужно перенести данные таблицы EMP, созданной следующим образом:

DROP TABLE emp

/

DROP TABLE dept

/

CREATE TABLE dept (

deptno NUMBER(2)

, dname VARCHAR2(14) CONSTRAINT dept#N#dname NOT NULL

, loc VARCHAR2(13) CONSTRAINT dept#N#loc NOT NULL

, CONSTRAINT dept#p PRIMARY KEY(deptno)

USING INDEX TABLESPACE indx

)

/

GRANT SELECT, INSERT, UPDATE, DELETE ON
dept TO emp_manager

/

CREATE TABLE emp (

empno NUMBER(4)

, ename VARCHAR2(10) CONSTRAINT emp#N#ename NOT NULL

, job VARCHAR2(9) CONSTRAINT emp#N#job NOT NULL

, mgr NUMBER(4)

, hiredate DATE CONSTRAINT emp#N#hiredate NOT NULL

, sal NUMBER(7,2) CONSTRAINT emp#N#sal NOT NULL

, comm NUMBER(7,2)

, deptno NUMBER(2) CONSTRAINT emp#N#deptno NOT NULL

, CONSTRAINT emp#p PRIMARY KEY (empno)

USING INDEX TABLESPACE indx

, CONSTRAINT emp#r#dept FOREIGN KEY (deptno)

REFERENCES dept(deptno)

, CONSTRAINT emp#r#emp FOREIGN KEY (mgr)

REFERENCES emp(empno)

)

/

GRANT SELECT, INSERT, UPDATE, DELETE
ON emp TO emp_manager

/

CREATE TRIGGER emp#BIU#R

BEFORE INSERT OR UPDATE ON EMP FOR EACH ROW

BEGIN

:NEW.ename := UPPER(:NEW.ename);

END;

/

Самодостаточный (self-contained) набор табличных пространств - это совокупность табличных пространств, объекты которых не ссылаются на какие-либо объекты, не содержащиеся в данном наборе. В качестве примеров нарушений самодостаточности можно привести случаи, когда:

• хотя бы один из индексов расположен в табличном пространстве, входящем в набор, а сама таблица создана в табличном пространстве, невходящем в набор. (Обратная ситуация не является нарушением - таблицы можно транспортировать без принадлежащих им индексов),
• хотя бы одна секция секционированной таблицы расположена в табличном пространстве, невходящем в набор,
• столбец таблицы, имеющий тип LOB, расположен в табличном пространстве, невходящем в набор.

Для проверки самодостаточности удобно использовать процедуру TRANSPORT_SET_CHECK из пакета SYS.DBMS_TTS (общего синонима нет). Пользователю должна быть предоставлена роль EXECUTE_CATALOG_ROLE (изначально такая роль предоставлена SYS).

BEGIN

sys.dbms_tts.transport_set_check (

-- Список табличных пространств

ts_list => 'USER_DATA'

-- Учитывать ссылочные ограничения

, incl_constraints => TRUE);

END;

/

Результаты ее работы записываются во временную таблицу и их можно посмотреть через системное представление SYS.TRANSPORT_SET_VIOLATIONS (общего синонима также нет):

SELECT * FROM sys.transport_set_violations

/

VIOLATIONS

---------------------------------

[1] Constraint EMP#N#ENAME between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

[2] Constraint EMP#N#JOB between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

[3] Constraint EMP#N#HIREDATE between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

[4] Constraint EMP#N#SAL between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

[5] Constraint EMP#N#DEPTNO between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

[6] Constraint EMP#P between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

[7] Constraint EMP#R#DEPT between table SCOTT.DEPT
in tablespace

USERS and table SCOTT.EMP
in tablespace USER_DATA

[8] Constraint EMP#R#EMP between table SCOTT.DEPT
in tablespace USERS and table SCOTT.EMP
in tablespace USER_DATA

Выявленные нарушения 1-5 и 8 вызывают некоторое недоумение, поскольку связаны с ограничениями типа NOT NULL таблицы EMP, ее первичным ключом и ссылкой самой на себя, что вроде бы никак не связанно с табличными пространствами, отличными USER_DATA (может быть это оттого, что сведения о них хранятся в словаре данных?

Если вы получите сообщение вида:

Master table SCOTT.EMP in tablespace USER_DATA
not allowed in transportable set

ищите снапшот, использующий указанную таблицу.

Диагностика подобная:

Partitioned table SCOTT.ORDER_DETAILS is partially
contained in the transportable set

свидетельствует о том, что транспортируемый набор содержит не все разделы секционированной таблицы SCOTT.ORDER_DETAILS.

Нарушение 7 напоминает о том, что EMP имеет ссылку на таблицу DEPT, поэтому добавим в список табличное пространство USERS, в котором создана DEPT:

BEGIN

sys.dbms_tts.transport_set_check (

ts_list => 'USER_DATA, USERS',

incl_constraints => TRUE);

END;

/

Выборка из SYS.TRANSPORT_SET_VIOLATIONS не возвращает строк, то есть все в порядке. Однако, если вы хотите перенести и индексы (возможно не все), то для выявления таблиц, имеющих индексы, расположенные в табличных пространствах, не входящих в набор, подлежащий транспортировке выполните:

SELECT table_name, tablespace_name FROM dba_indexes

WHERE table_name IN

(SELECT table_name FROM dba_tables

WHERE tablespace_name IN ('USER_DATA','USERS'))

MINUS

SELECT table_name, tablespace_name FROM dba_tables

WHERE tablespace_name NOT IN ('USER_DATA','USERS')

/

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

TABLE_NAME TABLESPACE_NAME

---------- ---------------

EMP INDX

DEPT INDX

Внимание! Для секционированных (partitioned) таблиц и индексов, а также для индекс-таблиц (index organized tables), запрос следует модифицировать с учетом соответствующих представлений словаря данных!

На этом этапе будет также уместным определить:

• файлы данных, подлежащие переподключению:

SELECT tablespace_name, file_name

FROM dba_data_files

WHERE tablespace_name IN ('USER_DATA','USERS', 'INDX')

ORDER BY tablespace_name, file_name

/

TABLESPACE_NAME FILE_NAME

--------------- -----------------------------------

INDX D:\ORACLE81\ORADATA\VSUIMB\INDX01.ORA

USERS D:\ORACLE81\ORADATA\VSUIMB\USERS01.ORA

USER_DATA D:\ORACLE81\ORADATA\VSUIMB\USER_DATA_01.ORA

• имена схем данных, которым принадлежат таблицы и индексы, расположенные в транспортируемом наборе:

SELECT owner, segment_type, COUNT(*) AS cnt

FROM dba_segments

WHERE tablespace_name IN ('USER_DATA','USERS', 'INDX')

GROUP BY owner, segment_type

/

OWNER SEGMENT_TYPE CNT

----- ------------ -------

SCOTT INDEX 2

SCOTT TABLE 2

SYSTEM TABLE 1

TOAD INDEX 4

TOAD TABLE 3

• роли и/или пользователей, которым предоставлены привилегии доступа к таблицам, расположенным в перемещаемых табличных пространствах:

SELECT DISTINCT P.owner AS table_owner

, P.grantee

, DECODE(U.type#, 0, 'ROLE', 1, 'USER') as grantee_type

FROM dba_tab_privs P, sys.user$ U

WHERE (P.owner, P.table_name) IN

(SELECT owner, segment_name

FROM dba_segments

WHERE tablespace_name IN ('USER_DATA','USERS', 'INDX'))

AND P.grantee = U.name

/

TABLE_OWNER GRANTEE GRANTEE_TYPE

----------- ------------ ------------

SCOTT EMP_MANAGER ROLE

SYSTEM PUBLIC ROLE

TOAD PUBLIC ROLE

Кроме того, может быть полезным выявление триггеров, не принадлежащих собственникам таблиц:

SELECT DISTINCT owner AS trigger_owner

, table_owner

FROM dba_triggers

WHERE (table_owner, table_name) IN

(SELECT owner, segment_name

FROM dba_segments

WHERE tablespace_name IN ('USER_DATA','USERS', 'INDX'))

AND owner <> table_owner

/

что может в результате дать, например:

TRIGGER_OWNER TABLE_OWNER

------------- -----------

FILER SCOTT

1. Сначала следует перевести табличные пространства набора в состояние ТОЛЬКО ЧТЕНИЕ (READ ONLY):

ALTER TABLESPACE user_data READ ONLY;

ALTER TABLESPACE users READ ONLY;

ALTER TABLESPACE indx READ ONLY;

2. Затем с помощью утилиты EXP экспортируются метаданные словаря. Например:

EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=(USERS, USER_DATA, INDX) TRIGGERS=Y
CONSTRAINTS=Y GRANTS=Y FILE=exp_tts log=exp_tts

Параметры:

• TRANSPORT_TABLESPACE=Y - указывает, что выполняется экспорт метаданных транспортируемых табличных пространств,
• TABLESPACES=(USERS, USER_DATA, INDX) - задает список транспортируемых табличных пространств,
• TRIGGERS=Y - Экспортировать табличные триггеры (если указать N, то триггеры экспортироваться не будут)
• CONSTRAINTS=Y - Экспортировать ограничения целостности (при N не экспортируются ограничения типов PRIMARY KEY, UNIQUE, FOREIGN KEY и CHECK, однако ограничения NOT NULL экспортируются),
• GRANTS=Y - Экспортировать привилегии доступа к таблицам (N отменяет экспорт привилегий),
• FILE=exp_tts и log=exp_tts имя файла с данными и имя файла журнала экспорта.

При запросе имени, согласно документации, следует подключаться к базе данных как SYSDBA:

Username: SYS@VSUIMB AS SYSDBA

Password:

Впрочем, USERID=SYS@VSUIMB AS SYSDBA, как и все прочие параметры можно задать в отдельном файле параметров, указав его при вызове утилиты (EXP PARFILE=file_name.par). Кроме того, натурные испытания показали, что на данном этапе подключаться как SYSDBA вовсе не обязательно - достаточно роли DBA.

3. Сделать копии файлов данных в другое место на той же вычислительной установке или на транспортируемый носитель или на машину, где расположена целевая БД.

4. Не забудьте перевести табличные пространства назад в состояние ЧТЕНИЕ-ЗАПИСЬ (Если это необходимо для продолжения нормальной работы пользователей исходной БД):

ALTER TABLESPACE user_data READ WRITE;

ALTER TABLESPACE users READ WRITE;

ALTER TABLESPACE indx READ WRITE;

Этот шаг предельно прост.

Переносим копии файлов данных и файл, полученный утилитой экспорта, в место, доступное целевой базе данных. Это можно сделать любым доступным способом: утилитой ОС, по FTP, публикацией на неперезаписываемом носителе и т.п.

Содержимое файла экспорта метаданных можно посмотреть с помощью команды:

IMP TRANSPORT_TABLESPACE=Y FILE=exp_tts log=list_tts
indexfile=list_tts show=Y

В фрагменте полученного файла list_tts.sql, содержащем информацию о таблице DEPT обнаруживаем неописанные в документации служебные метапараметры SEG_FILE, SEG_BLOCK и OBJNO_REUSE:

REM CREATE TABLE "SCOTT"."DEPT" ("DEPTNO" NUMBER(2, 0),

REM "DNAME" VARCHAR2(14) CONSTRAINT "DEPT#N#DNAME"

REM NOT NULL ENABLE, "LOC" VARCHAR2(13)

REM CONSTRAINT "DEPT#N#LOC" NOT NULL ENABLE)

REM PCTFREE 10 PCTUSED 40 INITRANS 1

REM MAXTRANS 255 LOGGING

REM STORAGE(SEG_FILE 3 SEG_BLOCK 2992 OBJNO_REUSE 14780

REM INITIAL 53248 NEXT 53248 MINEXTENTS 1

REM MAXEXTENTS 2147483645

REM PCTINCREASE 1 FREELISTS 1 FREELIST GROUPS 1

REM BUFFER_POOL DEFAULT) TABLESPACE "USERS" ;

Перед тем, как приступить собственно к подключению (plug-in) может потребоваться создание:

• схем данных, которым будут принадлежать таблицы, индексы, триггеры перемещаемого набора,
• пользователей и/или ролей, которым будут предоставлены привилегии доступа к таблицам.

Впрочем, всего или кое-чего из этого можно не делать, если вы планируете разместить данные в других уже существующих схемах и/или не собираетесь предоставлять те же привилегии и создавать триггеры, идентичные тем, которые были в исходной базе данных.

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

IMP PARFILE=imp_tts.par

Файл imp_tts.par:

USERID=SYS@SVORA AS SYSDBA

TRANSPORT_TABLESPACE=Y

DATAFILES=('G:\ORACLE816\ORADATA\SVORAPLUG\INDX01.ORA',

'G:\ORACLE816\ORADATA\SVORAPLUG\USERS01.ORA',

'G:\ORACLE816\ORADATA\SVORAPLUG\USER_DATA_01.ORA')

TABLESPACES=(USERS,USER_DATA,INDX)

TTS_OWNERS=(SCOTT,SYSTEM,TOAD)

FROMUSER=(SCOTT,SYSTEM,TOAD)

TOUSER=(TIGER,MANAGER,PANDORA)

FILE=exp_tts.dmp

LOG=imp_tts.log

Здесь:

• TRANSPORT_TABLESPACE=Y - указывает, что импортируются метаданные набора подключаемых табличных пространств,
• DATAFILES=(:) - список, определяющий имена и место расположения подключаемых файлов данных,
• TABLESPACES=(USERS,USER_DATA,INDX) - список табличных пространств. Если параметр не задан, то список берется из файла экспорта. Если задан, то сверяется со списком, содержащимся в файле экспорта (в случае несовпадения будет выдано сообщение об ошибке).
• TTS_OWNERS=(SCOTT,SYSTEM,TOAD) - список схем, которым принадлежат объекты в подключаемых табличных пространствах. Если параметр не задан, то список берется из файла экспорта. Если задан, то сверяется со списком, содержащимся в файле экспорта (в случае несовпадения будет выдано сообщение об ошибке).
• FROMUSER=(SCOTT,SYSTEM,TOAD) , TOUSER=(TIGER,MANAGER,PANDORA) - имена исходных и соответствующих им целевых схем. В этом примере объекты, принадлежавшие в исходной БД схеме SCOTT, будут импортированы в схему TIGER, принадлежавшие SYSTEM в схему MANAGER, а TOAD в PANDORA. Если параметры опущены, то данные будут импортироваться в одноименные схемы (SCOTT в SCOTT и т.д.)

Транспортируемые табличные пространства предоставляют разработчикам возможность получить копию части данных производственной системы, тождественных не только по содержанию, но и по физическим характеристикам (фрагментация). Однако для этого в дополнение к перечисленному выше потребуется выполнить некоторые дополнительные действия. Дело в том, что при транспортировке метаданных не экспортируются представления (view), процедуры, функции, пакеты и др. Поэтому их нужно переносить отдельно. Для этого удобнее всего воспользоваться всё тем же экспортом-импортом. Допустим, что все интересующие нас объекты принадлежат схеме SCOTT. Тогда команда экспорта в из исходной БД могла бы выглядеть так:

EXP SCOTT/TIGER@VSUIMB FILE=scott_proc log=scott_proc ROWS=N

Воссоздадим недостающие объекты в целевой базе данных, воспользовавшись при импорте в схему TIGER командой:

IMP TIGER/KING@SVORA FILE=scott_proc log=scott_proc_IMP IGNORE=Y

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

После успешного подключения набора табличных пространств к целевой базе данных не забудьте, как уже говорилось выше, перевести табличные пространства исходной БД в состояние READ WRITE, если это необходимо для работы её пользователей.

• После подключения табличного пространства ROWID строк содержащихся в нем таблиц больше в целом не уникален в рамках БД назначения, но по-прежнему уникален в рамках таблицы. Вероятно, то же происходит с идентификатором объекта БД (см. выше OBJNO_REUSE).
• При импорте Oracle не контролирует объектные ссылки и поэтому возможно появление зависших (dangling) REFs.
• Аналогично не контролируется наличие внешних двоичных файлов, на которые указывают ссылки BFILE.
• Если используемые в коде триггера объекты отсутствуют, то при импорте триггеров возможны ошибки компиляции.
• Ну и никогда не следует забывать, что любая новая возможность, кроме всего прочего, всегда предоставляет пищу для пытливого ума и непоседливых рук. Поэтому, перефразируя известную пословицу и клятву, повторим главную заповедь "администрата": "Не навреди! Семь пробных баз угробь, а потом на рабочую ставь!"

Такие дела.