На протяжении многих лет администраторы баз данных Oracle пользовались практически только утилитой SQL*Loader для выполнения загрузки данных в таблицы Oracle из внешних источников, применяя ее либо в обычном, либо в прямом режиме загрузки. Предлагаемый Oracle механизм внешних таблиц является шагом вперед и позволяет использовать функциональные возможности SQL*Loader для получения доступа к данным, хранящимся в файлах операционной системы, без выполнения загрузки в реальные таблицы Oracle.
Если исходные данные не нуждаются ни в каких преобразованиях перед загрузкой в базу, утилиты SQL*Loader для выполнения их загрузки в обычном или прямом режиме вполне достаточно. Внешние таблицы дополняют функциональные возможности SQL*Loader. Поэтому если необходимо выполнить серьезные преобразования перед загрузкой данных, рекомендуется использовать именно внешние таблицы.
За счет применения механизма внешних таблиц внешние данные можно визуализировать так, будто бы они хранятся в таблице Oracle. При создании внешней таблицы столбцы перечисляются тем же самым образом, как и при создании обычной таблицы. Однако поля данных из внешнего файла просто отображаются на столбцы внешней таблицы, но не загружаются в них на самом деле.
Внешние таблицы в действительности не существуют нигде ни внутри, ни за пределами базы данных Oracle. Под термином внешняя таблица (external table) подразумевается просто отображение заданной табличной структуры на файл данных, который находится в файле операционной системы. При создании внешней таблицы единственное, что происходит в базе данных, так это создание для новой таблицы в словаре данных новых записей с метаданными. Изменять содержимое файла данных во время получения доступа к его содержимому из базы данных никоим образом не допускается. Другими словами, при взаимодействии с внешними таблицами разрешается пользоваться только командой SELECT, но не командами INSERT, UPDATE и DELETE.
Фактически, внешняя таблица представляет собой своего рода интерфейс к внешнему файлу данных. Однако к ней можно выполнять запросы так, будто бы она является виртуальной таблицей, точно таким же образом, как и к любой обычной таблице Oracle, что делает ее очень мощным инструментом для осуществления ETL-операций в хранилищах данных. Кроме того, из внешних таблиц можно легко создавать другие обычные таблицы или представления, что может быть очень удобным при заполнении хранилищ данных.
Утилита SQL*Loader и механизм внешних таблиц в большинстве случаев работают одинаково в том, что касается скорости выполнения загрузки данных. Обе эти технологии представляют собой альтернативные методы для загрузки данных в базу из внешних источников. Ниже перечислены некоторые общие преимущества, которые предоставляет метод выполнения загрузки при помощи внешних таблиц по сравнению с методом выполнения загрузки с помощью утилиты SQL*Loader.
- К данным во внешних файлах можно получать доступ перед их загрузкой в таблицы.
- Над данными можно выполнять широкий спектр различных преобразований прямо во время самого процесса загрузки. Утилита SQL*Loader позволяет выполнять лишь очень ограниченный набор преобразований.
- При желании преобразование данных можно выполнять одновременно с их загрузкой в таблицы. Этот прием называется конвейеризацией (pipelining) двух этапов. В случае применения утилиты SQL*Loader для выполнения загрузки данных прямо в таблицы, в ходе процесса загрузки не допускается выполнять ничего, кроме самого минимального преобразования данных, из-за чего, следовательно, серьезные преобразования требуется производить на отдельном от загрузки данных этапе.
- Внешние таблицы подходят для загрузки больших объемов данных, которые могут иметь в базе данных одноразовое применение.
- Внешние таблицы экономят время, связанное с созданием реальных таблиц базы данных и затем агрегированием размеров данных для выполнения их загрузки в другие таблицы.
- Внешние таблицы исключают необходимость в создании вспомогательных или временных таблиц, которые являются практически обязательными в случае применения для выполнения загрузки данных из внешних источников утилиты SQL*Loader.
- Никакого физического пространства выделять не требуется, даже для самых больших внешних таблиц. После загрузки файлов данных в операционную систему можно создавать внешние таблицы и сразу же приступать к выполнению в отношении них необходимых SQL-запросов.
При наличии потребности в выполнении загрузки данных удаленным образом или отсутствии необходимости в подвергании данных серьезным преобразованиям, применение утилиты SQL*Loader, бесспорно, является наилучшим вариантом. Внешние таблицы никак не являются такими же универсальными, как и обычные таблицы базы данных, поскольку они доступны только для чтения. Более того, внешние таблицы страдают от ограничения, не позволяющего их индексировать, что делает выполнение насыщенной запросами работы с ними очень непрактичным занятием. Если имеется потребность в индексировании данных во вспомогательных таблицах по какой-то причине, применение утилиты SQL*Loader является единственной достойной внимания альтернативной. Настоящую пользу внешние таблицы приносят главным образом в средах с хранилищами данных или в ситуациях, когда требуется выполнять загрузку и преобразование огромных объемов данных при первой загрузке приложения.
Внимание! При желании иметь возможность создавать индексы на уровне вспомогательной таблицы, лучше применять для загрузки данных утилиту SQL*Loader. Индексировать внешние таблицы нельзя!
Например, предположим, что есть внешний файл данных по имени sales_data, в котором содержится детальная информация по произведенным фирмой продажам за последний год, и что фирма хочет провести на основании этих исходных данных анализ затрат по продуктам и времени. Для выполнения этого анализа создается специальная таблица затрат. Теперь в файле данных sales_data содержится масса детальной информации по затратам, но компания хочет, чтобы данные были объединены, скажем, по регионам. Внешние таблицы замечательно подходят для такой ситуации, когда есть много доступных исходных данных, но преобразовать требуется только какие-то конкретные их части.
Раньше администраторам баз данных, занимающимся хранилищами данных, приходилось создавать вспомогательные таблицы для выполнения преобразования данных перед их загрузкой в хранилище. В случае применения одной лишь утилиты SQL*Loader им требовалось сначала загружать исходные данные в базу и только затем выполнять над ними необходимые преобразования. В случае применения внешней таблицы операции по загрузке и преобразованию данных могут выполняться на одном этапе!
Создание внешней таблицы
Описание внешней таблицы еще также называется слоем (уровнем) внешней таблицы (external table layer) и, по сути, представляет собой описание входящих в состав внешней таблицы столбцов. Этот уровень, вместе драйвером доступа, обеспечивает отображение данных из внешнего файла на определение внешней таблицы.
В листинге 1 ниже приведен пример создания внешней таблицы.
SQL> CREATE TABLE sales_ext(
2 product_id NUMBER(6),
3 sale_date DATE,
4 store_id NUMBER(8),
5 quantity_sold NUMBER(8),
6 unit_cost NUMBER(10,2),
7 unit_price NUMBER(10,2))
8 ORGANIZATION EXTERNAL (
9 TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY ext_data_dir
11 ACCESS PARAMETERS
12 (RECORDS DELIMITED BY NEWLINE
13 BADFILE log_file_dir:'sales.bad_xt'
14 LOGFILE log_file_dir:'sales.log_xt'
15 FIELDS TERMINATED BY "|" LDRTRIM
16 MISSING FIELD VALUES ARE NULL)
17 LOCATION ('sales.data'))
18* REJECT LIMIT UNLIMITED;
Table created.
SQL>
Давайте проанализируем этот оператор более детально, чтобы разобраться в различных компонентах внешней таблицы.
Оператор CREATE TABLE...ORGANIZATION EXTERNAL
Оператор CREATE TABLE sales_ext (. . .) описывает структуру внешней таблицы, а идущая за ним конструкция ORGANIZATION EXTERNAL как раз и обозначает, что это будет не обычная таблица Oracle, а внешняя.
Оператор CREATE, который применяется для создания внешней таблицы, в принципе очень похож на тот, что применяется для создания обычной таблицы, за исключением разве что того, что в нем помимо определений столбцов требуется также предоставлять схему отображения этих столбцов на поля данных во внешнем файле данных, а также указывать место размещения внешнего файла данных в операционной системе.
Параметры доступа
Конструкция ACCESS PARAMETERS чем-то напоминает конструкцию OPTIONS, которая используется в управляющем файле SQL*Loader. Она тоже позволяет указывать различные выбираемые параметры, а также место размещения файла некорректных записей и файла журнала. Есть несколько параметров, которые можно использовать в этой конструкции для задания формата данных во внешней таблице. Наиболее важные из них перечислены ниже.
- RECORD_FORMAT_INFO. Эта конструкция является необязательной. По умолчанию для нее устанавливается значение RECORDS DELIMITED BY NEWLINE.
- FIXED. Эта конструкция позволяет определять фиксированную длину и тем самым указывать, что все записи во внешнем файле имеет одинаковую длину.
ACCESS PARAMETERS (RECORD FIXED 20 FIELDS (...))
- VARIABLE. Конструкция VARIABLE позволяет указывать, что все записи могут иметь разный размер, обозначаемый при помощи идущего в начале каждой из них ряда цифр.
ACCESS PARAMETERS (RECORDS VARIABLE 2)
- То есть в случае использования конструкции VARIABLE каждая запись в наборе данных будет иметь следующий вид, где первые два байта будут обозначать ее длину:
22samalapati1999dallastx
- DELIMITED BY. Эта конструкция позволяет указывать символ, которым завершается каждая запись. Чаще всего роль разделителей исполняют символы конвейера (|) или запятой (,).
- LOAD WHEN. Эта конструкция позволяет указывать условия, которые должны обязательно удовлетворяться перед загрузкой записи в таблицу.
LOAD WHEN (job != MANAGER)
- LOG FILE, BAD FILE и DISCARD FILE. Эти параметры являются необязательными, но файл журнала (LOG FILE) все равно всегда создается по умолчанию. Файлы некорректных (BAD FILE) и отвергнутых записей (DISCARD FILE) создаются только в случае отклонения данных или не удовлетворения ими условия, указанного в конструкции LOAD WHEN.
- Condition. Эта переменная позволяет сравнивать все поле или только какую-то его часть с произвольной постоянной строкой.
Драйвер доступа
Параметры доступа описывают внешние данные, содержащиеся в файлах данных. Драйвер доступа (access driver) заботится о том, чтобы обработка этих внешних данных соответствовала описанию внешней таблицы.
Драйверы доступа бывают двух типов, и каждых из них указывается за счет использования в операторе создания внешней таблицы атрибута TYPE. К первому типу относится драйвер доступа ORACLE_LOADER, который выбирается по умолчанию. В примере, приведенном в листинге 1, внешняя таблица создавалась с использованием именного такого драйвера. Драйвер доступа ORACLE_LOADER позволяет только загружать данные в таблицу из внешнего текстового файла.
Ко второму типу относится новый драйвер доступа ORACLE_DATAPUMP, и этот драйвер позволяет не только загружать, но и выгружать данные за счет применения внешних файлов дампа. С его помощью можно выполнять считывание данных в таблицы базы данных из внешнего файла данных, а также извлекать данные из таблиц Oracle в файл дампа внешней таблицы.
Объекты каталогов и их размещение
Конструкция DEFAULT DIRECTORY указывает на принятое по умолчанию место размещения всех файлов, из которых внешним таблицам необходимо считывать или в которые им необходимо записывать данные. Размещать внешние файлы данных в случайном каталоге операционной системы нельзя по вполне очевидным связанным с безопасностью причинам. Для успешного выполнения оператора создания внешней таблицы нужно сначала создавать объект каталога (directory object) и затем предоставлять конкретным пользователям права на доступ к нему.
Параметр LOCATION, расположенный ближе к концу показанного в листинге 1 оператора, указывает, где находятся файлы данных, необходимые для создания внешней таблицы. В параметре LOCATION может указываться и объект каталога, и имя файла. В общем, синтаксис этого параметра выглядит как каталог: файл, где на месте каталог указывается объект каталога, созданный в базе данных, а не путь к настоящему каталогу в операционной системе. Если объект каталога не задан, тогда по умолчанию предполагается, что файл или файлы данных находятся в том каталоге, на который указывает конструкция DEFAULT DIRECTORY. При желании синтаксисом каталог: файл можно также пользоваться и для указания файлов данных прямо в конструкции ACCESS PARAMETERS.
Утилита SQL*Loader использует объект (или объекты) каталога для обозначения места размещения файлов данных, а также для хранения своих выходных файлов, наподобие файлов некорректных или отвергнутых записей. Пользователь должен обязательно обладать привилегиями на выполнение чтения в объекте каталога, содержащем файлы данных, и привилегиями на выполнение записи в объекте каталога, содержащем выходные файлы. При желании разместить файлы данных и выходные файлы одном и том же объекте каталога можно предоставлять пользователю привилегии на выполнение в этом объекте каталога как чтения, так и записи. Ниже приведен один такой пример:
SQL> CREATE DIRECTORY ext_data_dir AS '/u01/oradata/ext_data'; Directory created. SQL> GRANT READ, WRITE ON DIRECTORY ext_data_dir TO samalapati; Grant succeeded. SQL>
После создания объекта каталога ext_data_dir и предоставления надлежащих прав доступа к нему, можно далее начинать использовать его как принятый по умолчанию каталог для размещения всех внешних файлов данных, а также любых выходных файлов. Параметр LOCATION в операторе создания внешней таблицы, показанном в листинге 1, указывает лишь на внешний файл данных, находиться который будет в принятом по умолчанию каталоге, на который ссылается ext_data_dir.
В целях демонстрации давайте создадим новую таблицу по имени costs, чтобы впоследствии выполнить в нее загрузку из внешнего файла данных (внешней таблицы) агрегированных данных (а именно — совокупных значений столбцов unit_cost и unit_price):
SQL> CREATE TABLE costs 2 (sale_date DATE, 3 product_id NUMBER(6), 4 unit_cost NUMBER (10,2), 5 unit_price NUMBER(10,2)); Table created.
Теперь можно приступать непосредственно к вставке в эту новую таблицу costs необходимых агрегированных данных из внешней таблицы (фактически, представляющей собой внешний файл) с именем sales_ext. Этот процесс подразумевает выполнение сначала считывания данных из внешней таблицы и только потом их загрузку в обычную таблицу базы данных и называется загрузкой данных. В листинге 2 показан пример осуществления вставки данных из внешней таблицы в обычную. В этом примере предполагается, что внешняя таблица имеет имя sales_ext, а обычная таблица Oracle — costs.
SQL> INSERT INTO costs (sale_date, product_id, unit_cost, unit_price) SELECT sale_date, product_id, sum(unit_cost), sum(unit_price) FROM sales_ext GROUP BY time_id, prod_id; SQL>
Обратите внимание на то, что при желании можно вставлять только некоторые из столбцов внешней таблицы и что выполнять преобразование данных можно еще до их загрузки в таблицы Oracle. В этом как раз и заключается главное отличие между применением внешних таблиц и применением утилиты SQL*Loader. Утилита SQL*Loader позволяет выполнять преобразование данных, но ее возможности в этой области чрезвычайно ограничены, как было показано ранее в этой статье. При создании внешней таблицы для данных можно применять практически любые SQL-преобразования.
Заполнение внешних таблиц
Термины загрузка (loading) и выгрузка (unloading) в контексте внешних таблиц могут вызывать путаницу, поэтому давайте остановимся на них более подробно и устраним любую связанную с ними неоднозначность. В частности, при работе с внешними таблицами эти термины означают следующее.
- Под загрузкой данных подразумевается считывание данных из внешней таблицы и затем их загрузка в обычную таблицу. Oracle сначала считывает поток данных из тех файлов, которые указываются, а потом переводит их из внешнего представления в вид внутренних данных Oracle и передает далее интерфейсу внешней таблицы.
- Под выгрузкой данных подразумевается считывание данных из обычной таблицы Oracle и затем помещение их во внешнюю таблицу. По сути, под этим процессом понимается выполнение загрузки данных во внешний файл. В Oracle Database 11g появилась возможность загружать и преобразовывать большие объемы данных в не зависящие от платформы собственные плоские файлы Oracle для распространения или хранения данных.
Драйвер доступа ORACLE_DATAPUMP умеет как загружать, так и извлекать данные, т.е. он может и загружать внешнюю таблицу из плоского файла, и извлекать данные из обычной таблицы базы данных во внешний плоский файл. Данные в такой внешний плоский файл записываются в специальном формате, который может понимать только драйвер доступа ORACLE_DATAPUMP. Далее этот созданный новый файл можно использовать для создания внешней таблицы как в той же, так и в другой базе данных.
При загрузке таблицы Oracle из внешней таблицы (загрузка данных) применяется конструкция INSERT INTO...SELECT, как было показано в листинге 2. А при заполнении внешней таблицы данными из обычной таблицы Oracle (выгрузка данных) применяется конструкция CREATE TABLE...AS SELECT (также сокращенно называемая CTAS), как будет показано позже в этой статье, в листинге 4.
Ниже перечислены некоторые преимущества наличия возможности заполнять таблицы данными при помощи внешних таблиц.
- Загрузка табличных данных в плоские файлы означает, что далее их можно легко хранить или перемещать в другие базы данных. Внешние таблицы помогают перемещать большие объемы данных между платформами, поскольку они не зависят от используемой платформы.
- В средах с хранилищами данных часто возникает необходимость в выполнении сложных заданий ETL. Данными во внешних таблицах можно легко манипулировать за счет применения различных SQL-трансформаций перед их загрузкой снова в ту же или другую базу данных.
Обратите внимание на то, что когда говорится о выполнении записи во внешние таблицы, на самом деле имеется в виду выполнение записи во внешний файл. Для извлечения данных в этот находящийся в операционной системе файл применяется оператор SELECT. Драйвер доступа ORACLE_DATAPUMP записывает в него данные в двоичном и понятном только Oracle формате Data Pump, после чего его можно использовать для загрузки еще одной внешней таблицы в другой базе данных.
Создание внешней таблицы с использованием драйвера доступа ORACLE_DATAPUMP
В листинге 3 приведен пример, демонстрирующий создание внешней таблицы и заполнение ее данными из внешнего плоского файла с использованием драйвера доступа ORACLE_DATAPUMP, а не ORACLE_LOADER.
SQL> CREATE TABLE test_xt(
2 product_id NUMBER(6),
3 warehouse_id NUMBER(3),
4 quantity_on_hand NUMBER(8))
5 ORGANIZATION EXTERNAL(
6 TYPE ORACLE_DATAPUMP
7 DEFAULT DIRECTORY ext_data_dir
8 LOCATION ('test_xt.dmp'));
Table created.
SQL>
Для выполнения загрузки данных этой внешней таблицы в существующую таблицу в базе данных можно применить конструкцию INSERT INTO...SELECT, как было показано ранее в листинге 2
Запись данных во внешнюю таблицу
В листинге 4 представлен пример, показывающий, как выполнять запись данных во внешнюю таблицу.
SQL> CREATE TABLE test_xt
ORGANIZATION EXTERNAL(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_data_dir
LOCATION ('test_xt.dmp'))
AS
SELECT * FROM scott.dept;
Обратите внимание на то, что в операторе создания внешней таблицы для выполнения во внешнюю таблицу (файл) записи данных из таблицы scott.dept используется конструкция SELECT * FROM. Если в новую внешнюю таблицу нужно включить лишь некоторые, а не все столбцы из таблицы scott.dept, вместо оператора SELECT*FROM применялся бы соответствующий оператор SELECT.
На заметку! Запомните, что при загрузке таблицы Oracle данными из внешней таблицы (загрузка данных) применяется конструкция INTO...SELECT, а при заполнении внешней таблицы данными из таблицы Oracle (выгрузка данных) — конструкция CREATE TABLE...AS SELECT.
Если теперь заглянуть в место, указанное для принятого по умолчанию каталога (ext_data_dir), то можно будет увидеть следующее:
SQL> ls -altr Total 24 drwxr-xr-x 5 root root 4096 March 4 14:08 .. -rw-r--r-- 1 oracle oinstall 41 March 5 10:08 TEST_XT_28637.log -rw-r------- 1 oracle oinstall 12288 March 5 10:08 test_xt.dmp
Первый файл (test_xt_28637.log) предназначен для сведений о самом процессе создания данной внешней таблицы, а во второй (test_xt.dmp) — для данных из таблицы. Таблица test_xt создается как внешняя таблица. Табличная структура и данные для нее берутся из обычной таблицы Oracle по имени scott.dept. При желании далее полученный файл дампа может использоваться как в той же, так и в другой базе данных для загрузки других таблиц. Обратите внимание на то, что для успешного выполнения данного оператора создания внешней таблицы, принятый по умолчанию каталог (ext_data_dir) должен быть обязательно создан заранее. Само выполнение этого оператора CTAS приведет к загрузке данных из таблицы scott.dept в новую внешнюю таблицу dept_xt_dmp, а точнее — просто к сохранению данных этой таблицы во внешнем файле по имени dept_xt_dmp. Следовательно, получается, что внешняя таблица на самом деле образуется из плоских файлов, имеющих специальный формат и не зависящих от используемой операционной системы.
Драйвер доступа ORACLE_DATAPUMP также можно применять и для извлечения данных таблицы Oracle в несколько файлов, как показано ниже:
SQL> CREATE TABLE extract_cust
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_data_dir ACCESS PARAMETERS
(NOBADFILE NOLOGFILE)
LOCATION ('extract_cust1.exp', 'extract_cust2.exp', 'extract_cust3.exp',
'extract_cust4.exp'))
PARALLEL 4 REJECT LIMIT UNLIMITED AS
SELECT c.*, co.country_name, co.country_subregion, co.country_region
FROM customers c, countries co where co.country_id=c.country_id;
Параметр PARALLEL ускорит процесс выгрузки данных в четыре указанных файла данных. Обратите внимание, что количество указываемых файлов накладывает ограничение на степень параллелизма. Например, в случае установки для параметра PARALLEL значения 8 (PARALLEL=8) и указания только четырех файлов, степень параллелизма будет равняться четырем, а не восьми.
Сжатие и шифрование данных
Для обеспечения сжатия и шифрования данных при их записи в набор файлов дампа можно применять параметры COMPRESSION и ENCRYPTION. По умолчанию база данных не сжимает и не шифрует данные. В следующих подразделах рассказывается о том, как именно применять каждый из этих двух параметров.
Параметр COMPRESSION
Параметр COMPRESSION позволяет указывать, должны ли данные сжиматься перед их записью в набор файлов дампа. По умолчанию функция сжатия отключена (COMPRESSION DISABLED). Включать ее на время всей операции выгрузки можно, установив для параметра COMPRESSION значение ENABLED. В следующем примере показано, как это сделать:
SQL> CREATE TABLE table TEST
ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY def_dir1
ACCESS PARAMETERS (COMPRESSION ENABLED) LOCATION ('test.dmp'));
Наличие конструкции COMPRESSION ENABLED в приведенной выше команде гарантирует, что все данные будут записываться в файл test.dmp в сжатом формате.
Параметр ENCRYPTION
Параметр ENCRYPTION позволяет указывать, должна ли база данных шифровать данные перед их записью в набор файлов дампа. По умолчанию функция шифрования отключена и включить ее можно, установив для параметра ENCRYPITON значение ENABLED, как показано в следующем примере:
SQL> CREATE TABLE TEST
ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY test_dir1
ACCESS PARAMETERS (ENCRYPTION ENABLED) LOCATION ('test.dmp'));
Наличие в приведенной выше команде конструкции ENCRYPTION ENABLED гарантирует, что перед записью в файл дампа test.dmp все данные будут обязательно шифроваться.
Использование внешней таблицы
После создания новой внешней таблицы за счет заполнения внешнего файла данными из таблицы Oracle к ней можно выполнять запросы, точно так же, как к любой обычной таблице Oracle. Например, запрос к внешней таблице test_xt приведет к получению тех же данных, что и запрос к исходной таблице scott.dept:
SQL> SELECT * FROM test_xt;
В качестве владельца этой новой таблицы test_xt будет тоже отображаться пользователь samalapati, как показано ниже:
SQL> SELECT owner FROM dba_tables WHERE table_name='TEST_XT'; OWNER ----------- SAMALAPATI
Важно обратить внимание на то, что, как и в версии Oracle9i, в которой механизм внешних таблиц появился впервые, данные можно только выбирать (select) из внешней таблицы. Вставлять, удалять или обновлять данные во внешней таблице нельзя. Поэтому термин “записываемые внешние таблицы” (writable external tables) имеет очень ограниченный смысл: выполнять запись во внешние таблицы допускается только при их первоначальном создании. Ниже приведен пример, показывающий, что бы произошло при попытке вставить в новую внешнюю таблицу test_xt какие-то данные:
SQL> INSERT INTO test_xt (product_id) VALUES (222222); INSERT INTO test_xt * ERROR at line 1: ORA-30657: operation not supported on external organized table операция не поддерживается на внешних таблицах SQL>
При попытке выполнить операцию DELETE или UPDATE тоже было бы выдано похожее сообщение об ошибке.
Еще важно обратить внимание на то, что в случае применения механизма внешних таблицы для извлечения табличных данных в файл экспортируются только сами данные. Экспортировать метаданные при применении внешних таблиц нельзя. При желании извлечь метаданные для какого-нибудь объекта, нужно просто использовать DBMS_METADATA, как показано ниже:
SET LONG 2000
SELECT DBMS_METADATA.GET_DDL('TABLE','EXTRACT_CUST') FROM DUAL;
Использование SQL*Loader для генерации операторов создания внешних таблиц
Как уже можно было видеть в предыдущих разделах, правильное создание внешних таблиц и выбор подходящих параметров доступа может оказаться довольно утомительным занятием. К счастью, для выполнения всего этого существует и более простой способ, который заключается в генерировании всего необходимого для создания внешних таблиц DDL-кода и всех необходимых для загрузки их данными SQL-операторов утилитой SQL*Loader.
Генерировать DDL-код, необходимый для создания всех внешних таблиц, в SQL*Loader позволяет параметр командной строки EXTERNAL_TABLE. По умолчанию для него используется значение NOT_USED, при котором утилита SQL*Loader выполняет стандартную загрузку данных либо в обычном, либо в прямом режиме. В случае использования этого параметра со значением GENERATE_ONLY утилита SQL*Loader никакой загрузки данных не выполняет; вместо этого она генерирует все SQL-операторы, которые требуются для загрузки описанных в управляющем файле внешних таблиц, и размещает их в своем журнальном файле. В случае применения параметра EXTERNAL_TABLE со значением EXECUTE она пытаться выполнить эти SQL-операторы, чтобы создать внешние таблицы и загрузить их данными.
В частности, в результате применения опции EXTERNAL_TABLE=GENERATE_ONLY в журнальном файле SQL*Loader генерируются следующие операторы.
- Оператор CREATE DIRECTORY.
- Полный оператор CREATE TABLE, требуемый для создания внешней таблицы, со всеми необходимыми параметрами доступа.
- Все операторы INSERT, которые необходимы для выполнения загрузки данных из внутренних таблиц.
- Все операторы DELETE, которые требуются для каталога и внешней таблицы.
Давайте рассмотрим пример, показывающий, как генерировать связанные с созданием внешней таблицы операторы с помощью утилиты SQL*Loader. В этом примере предполагается, что внутренняя таблица называется test_emp. Эта таблица должна либо уже существовать, либо быть создана перед использованием SQL*Loader. Имя генерируемой SQL*Loader внешней таблицы выглядит как sys_sqlldr_x_ext_test_emp. Используемый для SQL*Loader управляющий файл носит имя test.ctl и имеет следующий вид:
LOAD DATA INFILE * INTO TABLE test_emp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (employee_id,first_name,last_name,hire_date,salary,manager_id) BEGINDATA 12345,"sam","alapati",sysdate,50000,99999 23456,"mark","potts",sysdate,50000,99999
Первое, что нужно сделать — это вызвать утилиту SQL*Loader с указанием test.ctl в качестве управляющего файла. Не следует забывать о том, что производится лишь генерация операторов CREATE TABLE и INSERT, а не фактическая загрузка таблиц.
$ sqlldr USERID=system/sammyy1 CONTROL=test.ctl \
EXTERNAL_TABLE=GENERATE_ONLY
SQL*Loader: Release 10.2.0.0.0 - Beta on Sun Mar 6 13:49:39 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript. [/u01/app/oracle/dba]
$
Поскольку для журнального файла не был указан никакой каталог, он будет создан в том же каталоге, из которого была запущена утилита SQL*Loader. В данном случае он будет называться test.log и содержать всю необходимую информацию, а именно — и операторы для создания внешней таблицы и каталога для нее, и фактические операторы INSERT для загрузки этой таблицы данными. После этого с помощью этих SQL-операторов внешнюю таблицу можно будет создать и загрузить напрямую данными уже без использования SQL*Loader. В листинге 5 ниже показано, как будет выглядеть содержимое журнального файла, сгенерированного в результате указания параметра EXTERNAL_TABLE=GENERATE_ONLY.
SQL*Loader: Release 10.2.0.0.0 - Beta on Sun Mar 9 13:49:39 2008
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Control File: test.ctl
Data File: test.ctl
Bad File: test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table TEST_EMP, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------- ---------- ----- ---- ---- ---------
EMPLOYEE_ID FIRST * , O(") CHARACTER
FIRST_NAME NEXT * , O(") CHARACTER
LAST_NAME NEXT * , O(") CHARACTER
HIRE_DATE NEXT * , O(") CHARACTER
SALARY NEXT * , O(") CHARACTER
MANAGER_ID NEXT * , O(") CHARACTER
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u01/app/oracle/dba'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_TEST_EMP"
(
"EMPLOYEE_ID" NUMBER,
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(20),
"HIRE_DATE" DATE,
"SALARY" NUMBER,
"MANAGER_ID" NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
LOGFILE 'test.log_xt'
READSIZE 1048576
SKIP 6
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPLOYEE_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"FIRST_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LAST_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"HIRE_DATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SALARY" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MANAGER_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'test.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
--------------------------------------------------------------
INSERT /*+ append */ INTO TEST_EMP
(
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
SALARY,
MANAGER_ID
)
SELECT
"EMPLOYEE_ID",
"FIRST_NAME",
"LAST_NAME",
"HIRE_DATE",
"SALARY",
"MANAGER_ID"
FROM "SYS_SQLLDR_X_EXT_TEST_EMP"
Run began on Sun Mar 06 13:49:39 2009
Run ended on Sun Mar 06 13:49:40 2009
Elapsed time was: 00:00:01.22
CPU time was: 00:00:00.27
Не трудно заметить, что генерировать операторы CREATE TABLE для внешних таблиц таким способом гораздо легче, чем создавать их с нуля.
