Лабораторная работа №6. Импорт, экспорт, загрузка данных.

Цель работы: изучить дополнительные утилиты поставляемые ORACLE: EXP, IMP и SQLLDR.

СУБД ORACLE имеет в своем составе инструментарий для копирования информации, как в БД, так и во внешние источники, например, БД других производителей (MS SQL Server, Interbase). Рассмотрим назначение утилит, которые входят в состав СУБД ORACLE.

Таблица 3. Операции с БД

Операция	Назначение	Название утилиты
Экспорт	Копирует данные во внешние файлы, которые предназначены только для последующего импорта в другую БД СУБД ORACLE. Данные сохраняются в двоичном формате ORACLE.	EXP
Импорт	Копирует данные в БД СУБД ORACLE из внешних файлов, которые были созданы утилитой экспорта ORACLE.	IMP
Загрузка	Копирует данные в БД из внешних, текстовых файлов, которые имеют либо стандартных формат разделителей, либо любой другой формат, поддерживаемый утилитой.	sqlldr

Import и Export - дополнительные утилиты, поставляемые корпорацией ORACLE. В основном, эти утилиты применяются для резервного копирования и миграции БД (между серверами либо из более старой версии ORACLE в более новую). Ниже приведены другие возможности утилит Import и Export:

1. хранение данных в файлах ОС для архивирования;

2. выборочное резервное копирование частей БД;

3. перемещение данных из одной пользовательской схемы в другую;

4. перемещение данных с одной аппаратной платформы или ОС в другую.

5. экономия пространства и повышение производительности за счет уменьшения фрагментации.

Утилита Export записывает информацию о таблицах и других объектах БД (операторы создания индексов, привилегии на экспортируемые объекты и т.д.), а также данные самих таблиц. Затем утилита Export сохраняет эту информацию в именованных файлах ОС. Файлы ОС, создаваемые утилитой Export, известны как файлы дампа. Файлы дампа, которые представлены в двоичном формате ORACLE, могут применяются только в утилите Import. Можно назвать файл дампа любым именем, допустимым в ОС. Если вы не укажите имя выходного файла для утилиты Export, то по умолчанию файл примет название “EXPDAT.DMP”.

После экспорта созданные утилитой файлы дампа можно записать на съемный носитель для дальнейшего хранения, перемещения или восстановления. Чтобы запустить утилиту экспорта необходимо выполнить:

Кнопка «ПУСК» => «ВЫПОЛНИТЬ» => в открывшемся окне набрать «Exp» и нажать «ОК».

Основные параметры утилиты экспорта перечислены в таблице ниже.

Таблица 4. Основные параметры утилиты экспорта

Параметр	Значение по умолчанию	Описание
CONSTRAINTS	N	Указывает, экспортируются ли табличные ограничения.
FILE	expdat.dmp	Имя файла, в который будут импортироваться данные. По умолчанию именем файла будет expdat.dmp (сокращенно от EXPort DATa.DuMP). Если требуется другое имя файла, то измените параметр FILE.
FULL	N	Если FULL=Y, экспортироваться будет вся БД, включая сведения о табличных пространствах.
GRANTS	Y	Указывает, будут ли экспортироваться привилегии для экспортируемых объектов.
HELP	N	Если задано HELP=Y, то другие параметры не требуются. На компьютер выводится справочная информация.
INDEXES	Y	Указывает, экспортируются ли определенные пользователем индексы. Системные индексы, созданные посредством определения ограничений (первичный ключ, уникальный ключ) экспортируются, независимо от значения параметра INDEXES
LOG		Имя файла, в который будет записан журнал экспорта. Если не указано иное, Oracle дает файлу расширение.LOG
ROWS	Y	Указывает, будут ли экспортироваться данные таблиц. Если ROWS=N, то экспортируются только определения объектов
TABLES		Указывает список таблиц (с запятой в качестве разделителя), которые должны быть экспортированы. Этот параметр используется совместно с параметром FROMUSER. В не-UNIX среде, например, в Windows, список таблиц необходимо заключать в круглые скобки
TABLESPACES		Список табличных пространств, которые должны быть экспортированы.
OWNER		Список имен пользователей БД, объекты которых будут экспортированы.
USERID		Указывает имя и пароль пользователя, который осуществляет процесс экспорта. Формат параметра — «имя пользователя/пароль@сервер».

Рассмотрим несколько сценариев экспорта:

1. Экспорт таблиц. Режим экспорта таблиц используется для экспорта одной таблицы или нескольких таблиц БД. Пользователи, имеющие доступ к таблицам других пользователей, могут экспортировать эти таблицы, указав перед таблицей имя схемы. Пример команды:

exp userid=reldb/ret@LOCALHOST file=c:\kbk.dmp log=c:\kbk.log tables=ver11.kbk.

2. Экспорт схемы пользователя. Режим экспорта схемы пользователя используется для экспорта всех объектов, принадлежащих схеме. Этот режим работает хорошо при создании пользователя, который является владельцем всех объектов приложения. Например, если существует пользователь с именем SALES, который является владельцем всех объектов в схеме SALES, экспорт схемы может выглядеть следующим образом:

exp userid=reldb/ret@proddb file=c:\USER_exp.dmp log=c:\USER_EXP.log owner= SALES.

3. Экспорт БД. Режим экспорта БД используется для экспорта всех объектов БД, за исключением объектов, которые обычно создаются и поддерживаются учетной записью SYS. Экспортировать БД могут только пользователи, которым назначена роль EXP_FULL_DATABASE.

exp userid=reldb/ret@proddb file=c:\DB_exp.dmp log=c:\DB_exp.log full=y.

Утилита Import противоположна по действию утилите Export. Она отвечает за чтение файлов дампа в целях воссоздания объектов БД, а также любого состояния, в котором они экспортировались первоначально. Утилита Import может также преобразовывать данные, предоставленные с разных платформ, например, с UNIX машины в ASCII кодах, на мейнфрейм с кодировкой EBCDIC и наоборот, что позволяет перемещать данные с одной платформы на другую. Утилита Import может работать в интерактивном режиме или в режиме командной строки.

Основные параметры утилиты импорта перечислены в таблице ниже.

Таблица 5. Основные параметры утилиты импорта

Параметр	Значение по умолчанию	Описание
FILE	expdat.dmp	Имя файла, из которого будут импортироваться данные. По умолчанию именем файла, из которого осуществляется импорт, будет expdat.dmp.
FROMUSER		Если указан этот параметр, то импортируются только те объекты, владельцем которых является пользователь с иден­тификационным кодом FROMUSER
FULL	N	Если FULL=Y, то импортироваться будет вся БД.
GRANTS	Y	Указывает, будут ли заданы все полномочия для экспортированных объектов.
HELP	N	Если задано HELP=Y, то другие параметры не требуются. На экране будет выведена справочная информация.
IGNORE	NO	Если задано IGNORE=Y, то ошибки при создании объектов игнорируются и строки вставляются в таблицу. Будьте внимательны, поскольку, если для таблицы не определено ограничение уникальности значений, то могут появиться дублирующие записи. Отметим, что о других ошибках, не связанных с созданием объектов (например, проблемах с ОС), пользователь будет информирован в обычном режиме.
INDEXES	Y	Указывает, экспортируются ли определяемые пользователем индексы. Системные индексы, созданные посредством опре­деления ограничений (первичный ключ, уникальный ключ) импортируются независимо от значения параметра ISDEXES.
LOG		Имя файла, в который будет записан журнал импорта. Если не указано иное, Oracle даст файлу расширение LOG.
TABLES		Указывает список таблиц (с запятой в качестве разделителя), которые должны быть импортированы. Этот параметр используется совместно с параметром FROMUSER. В не-UNIX среде, как, например, Windows, необходимо заключать список таблиц в круглые скобки.
TOUSER		Параметр TOUSER указывает имя пользователя, который будет владельцем импортируемых объектов. Данный параметр необходимо использовать совместно с параметром FROMUSER.
USERID		Указывает имя и пароль пользователя, который осуществляет процесс импорта. Формат параметра — «имя пользователя/пароль@сервер».

Примеры использования утилиты экспорта:

Импорт таблицы с данными.

IMP USERID=HR/1@LOCALHOST FILE=C:\IMP.DAT LOG=C:\IMP.LOG TABLES=ABC IGNORE=Y.

Импорт схемы пользователя.

IMP USERID=HR/1@LOCALHOST FILE=C:\IMP.DAT LOG=C:\IMP.LOG FROMUSER=STUDENT TOUSER=UTEST IGNORE=Y.

Импорт БД.

IMP USERID=SYS/1@LOCALHOST FILE=C:\DB.DAT LOG=C:\DBIMP.LOG FULL=Y.

SQL*Loader. Одной из типичных проблем, с которой часто сталкиваются администраторы БД, является перемещение данных из внешних источников в БД ORACLE. Сложность этой задачи возрастает с появлением хранилищ данных, приходится перемещать уже не мегабайты данных, а гигабайты, а в некоторых случаях и терабайты. ORACLE предусмотрел для решения этой задачи специальную утилиту. SQL*Loader - универсальное инструментальное средство, которое загружает внешние данные в таблицы БД ORACLE. Утилита SQL*Loader является гибкой и настраиваемой до такой степени, что обычно удается обойтись без процедур на языках третьего поколения с внедренными операторами SQL.

Для работы утилиты SQL*Loader необходимы входные параметры двух типов:

1. внешние данные, которые находятся на диске;

2. управляющая информация, которая содержится в управляющем файле и описывает характеристики внешних данных.

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

Таблица 6. Основные параметры утилиты SQL*Loader

Параметр	Описание
USERID	Указывает имя и пароль пользователя, который осуществляет процесс загрузки. Формат параметра — «имя пользователя/пароль@сервер».
CONTROL	Управляющий файл.
BAD	Параметр указывает путь к файлу, в который будут записаны незагруженные записи.
DATA	Параметр указывает на файл с внешними данными для загрузки в БД.
ERRORS	Максимальное количество допустимых ошибок при выполнении загрузки.
LOG	Файл протокола, который после выполнения загрузки содержит подробное описание процесса загрузки.

Внешние данные. Утилита SQL*Loader может обрабатывать файлы данных практически любого типа и поддерживает собственные типы данных почти для любой платформы. Данные обычно считываются из одного или нескольких файлов данных. Допускается располагать данные для загрузки непосредственно в управляющем файле.

SQL*Loader позволяет загружать данные либо в двоичном формате, либо в текстовом. Данные могут находиться в файлах как фиксированного, так и переменного форматов. При фиксированном формате поля данных всегда имеют одинаковую длину, независимо от содержащихся значений. В файлах с переменным форматом данные находятся в записях, которые изменяются по длине в зависимости от размера значений. Поля имеют длину, необходимую для размещения данных. Поля в файлах с переменным форматом могут быть разделены завершающими символами, например, запятыми, пробелами, или заключены в ограничительные символы.

Управляющий файл. Прежде, чем утилита SQL*Loader сможет обработать внешние данные, необходимо задать определения этих данных. Управляющий файл - это файл произвольного формата, который содержит информацию, указывающую SQL*Loader, как обрабатывать внешние данные.

Таблица 7. Основные параметры управляющего файла

Параметр	Описание
LOAD DATA CHARACTERSET	Кодировка символов, в которой производится загрузка данных. Для кириллицы значение этого параметра следует задать CL8MSWIN1251.
INFILE	INFILE "Путь\Имя файла" указывает файл с данными INFILE * данные для загрузки находятся в управляющей файле.
REPLACE (APPEND)	Заменить существующие записи таблицы данными из файла (Добавить данные к существующим записям таблицы).
INTO TABLE ИМЯ ТАБЛИЦЫ	Указывают таблицу для загрузки данных.
FIELDS TERMINATED BY 'разделитель'	Указывает на разделитель между полями в файле данных
(СПИСОК_КОЛОНОК) (A,B,C,D)	Список колонок, в которые будут загружаться данные.
BEGINDATA	Начало данных в управляющем файле

Пример 1.

Файл данных:

1,3,5

2,4,2

3,21,02

Управляющий файл:

load data

infile "c:\load.txt"

append

into table abc

fields terminated by ',' (a,b,c)

Вызов SQL*Loader:

SQLLdr userid=student/istas@localhost control=c:\upr.txt log=c:\loader.log bad=c:\bad.txt.

Пример 2.

Управляющий файл:

load data

CHARACTERSET CL8MSWIN1251

infile *

append

into table Sotr

fields terminated by ','

(FirstName, LastName, Age)

begindata

Семен,Иванов,24

Иван,Петров,42

Олег,Сидоров,23 (a,b,c)

Вызов SQL*Loader:

SQLLdr userid=student/istas@localhost control=c:\upr.txt log=c:\loader.log bad=c:\bad.txt.

 

Задания для самостоятельной работы:

1. Подключитесь к учебной БД под учетной записью student. Создайте двух новых пользователей (USER1) и (USER2). Создайте новую роль. Присвойте роли права подключаться, создавать таблицы, создавать последовательности, создавать триггеры и роль DBA.

2. Создайте таблицу-справочник стран: ID (первичный ключ), название страны (символьное, уникальное). Добавьте в таблицу две-три записи.

3. Создайте таблицу-справочник городов: ID (первичный ключ), страна (внешний ключ к таблице стран), название города (символьный). Создайте последовательность. Создайте триггер к таблице, который перед вставкой записи заполняет первичный ключ. Добавьте в таблицу пять записей.

4. Экспортируйте таблицу стран вместе с данными. Импортируйте таблицу из файла в схему пользователя USER1. Подключитесь к учебной БД под учетной записью USER1. Напишите запрос, который бы возвращал все записи таблицы стран. Добавьте три записи в таблицу. Удалите таблицу стран.

5. Подключитесь под учетной записью student. Создайте хранимую процедуру, которая бы возвращала список городов с привязанными странами, т.е. город и страна. Для этого примените курсорный цикл. Вызовите хранимую процедуру.

6. Экспортируйте всю схемe пользователя student в файл. Импортируйте схему пользователя student из файла в схему пользователя USER2. Подключитесь к учебной БД под учетной записью USER2. Напишите запрос, который бы возвращал все записи таблицы городов. Добавьте три записи в таблицу. Удостоверьтесь, что триггер заполнил первичный ключ.

7. Создайте текстовый файл или новый документ в EXCEL. Заполните десять строк для переноса в таблицу городов с помощью SQL*Loader. Создайте в текстовом редакторе управляющий файл и импортируйте в таблицу городов данные. Напишите запрос, который бы возвращал все записи таблицы городов. Убедитесь, что после импорта появились новые записи.

Библиографический список

 

1. Oracle Database 10g Release 2 - Administrator's Guide, 2005.

2. Oracle Database 10g Release 2 - Application Developer's Guide – Fundamentals, 2005.

3. Oracle Database 10g Release 2 - Concepts, 2005.

4. Oracle Database 10g Release 2 – SQL Reference, 2005.

5. Oracle Database 10g Release 2 – PL/SQL User's Guide and Reference, 2005.

6. Аллен, Кристофер. 101: Oracle PL/SQL: Как писать мощные и гибкие программы на PL/SQL / Кристофер Аллен; [Пер. с англ. Т. Москалев]. - [М.]: Лори, [2001]. - 350 с.: ил.; 23 см. - ISBN 5-85582-139-0.

7. Грабер, Мартин. Справочное руководство по SQL: [Перевод] / Мартин Грабер. - М.: Лори, Б. г. (1998). - 291 с.; 24 см.

8. Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт; [пер. с англ. и ред. К. А. Птицына]. - 8-е изд. - Москва [и др.]: Вильямс, 2008. - 1327 с.: ил., табл.; 24 см. - ISBN 978-5-8459-0788-2.

9. Фейерштейн, Стивен. Oracle PL/SQL для профессионалов / С. Фейерштейн, Б. Прибыл; [Пер. с англ. О. Здир]. - 3-е изд. - М. [и др.]: Питер, 2003. - 940 с.: ил.; 25 см. - (Программирование для Oracle) (Серия "Для профессионалов"). - ISBN 5-318-00528-4.