H.3. Oracle FDW#
H.3. Oracle FDW #
H.3.1. О oracle_fdw #
Версия: 2.7.0
Части Copyright (c) 2017-2023, CYBERTEC PostgreSQL International GmbH
H.3.2. Обёртка внешних данных для Oracle #
oracle_fdw - это расширение Tantor SE, которое предоставляет обертку внешних данных для легкого и эффективного доступа к базам данных Oracle, включая передачу условий WHERE и необходимых столбцов, а также полную поддержку EXPLAIN.
oracle_fdw был написан Лауренцем Альбе, с заметным вкладом Винсента Мора из Oslandia и Тацуро Ямада из NTT OSS Center.
Особая благодарность Кристиану Улльриху (Christian Ullrich) за постоянную помощь с Windows.
H.3.3. Кулинарная книга #
Это простой пример использования oracle_fdw. Более подробная информация будет предоставлена в разделах Опции и Использование. Вам также следует прочитать Раздел 5.12 и команды, упомянутые там.
Для этого примера предположим, что вы можете подключиться как
пользователь операционной системы postgres
(или кто-либо, кто запускает
сервер Tantor SE) с помощью следующей команды:
sqlplus orauser/orapwd@//dbserver.mydomain.com:1521/ORADB
Это означает, что клиент Oracle и окружение настроены правильно. Я также предполагаю, что oracle_fdw был скомпилирован и установлен (см. раздел Установка).
Мы хотим получить доступ к таблице, определенной следующим образом:
SQL> DESCRIBE oratab Name Null? Type ------------------------------- -------- ------------ ID NOT NULL NUMBER(5) TEXT VARCHAR2(30) FLOATING NOT NULL NUMBER(7,2)
Затем настройте oracle_fdw как суперпользователь Tantor SE следующим образом:
pgdb=# CREATE EXTENSION oracle_fdw; pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//dbserver.mydomain.com:1521/ORADB');
(Вы можете использовать другие методы именования или локальные соединения, см. описание опции dbserver ниже.)
Это хорошая идея использовать суперпользователя только там, где это действительно необходимо, поэтому давайте позволим обычному пользователю использовать внешний сервер (это не требуется для работы примера, но я рекомендую это):
pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
Затем вы можете подключиться к Tantor SE как pguser
и
определить:
pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb OPTIONS (user 'orauser', password 'orapwd');
(Вы можете использовать внешнюю аутентификацию, чтобы избежать хранения паролей Oracle; см. ниже.)
pgdb=> CREATE FOREIGN TABLE oratab ( id integer OPTIONS (key 'true') NOT NULL, text character varying(30), floating double precision NOT NULL ) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');
(Помните, что имя таблицы и схемы – последнее является необязательным – обычно должно быть в верхнем регистре.)
Теперь вы можете использовать таблицу как обычную таблицу Tantor SE.
H.3.4. Объекты, созданные расширением #
FUNCTION oracle_fdw_handler() RETURNS fdw_handler FUNCTION oracle_fdw_validator(text[], oid) RETURNS void
Эти функции являются обработчиком и функцией проверки, необходимыми для создания внешнего источника данных.
FOREIGN DATA WRAPPER oracle_fdw HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator
Расширение автоматически создает обертку внешних данных с именем oracle_fdw
.
Обычно этого достаточно, и вы можете приступить к определению внешних серверов. Вы можете создать дополнительные обертки внешних данных Oracle, например, если вам нужно установить опцию nls_lang (вы можете изменить существующую обертку oracle_fdw
, но все изменения будут потеряны после дампа/восстановления).
FUNCTION oracle_close_connections() RETURNS void
Эта функция может быть использована для закрытия всех открытых соединений с Oracle в этой сессии. См. раздел Использование для дальнейшего описания.
FUNCTION oracle_diag(name DEFAULT NULL) RETURNS text
Эта функция полезна только для диагностических целей. Она вернет версии oracle_fdw, Tantor SE сервера и Oracle клиента. Если вызвать без аргумента или с NULL, она дополнительно вернет значения некоторых переменных окружения, используемых для установления соединений с Oracle. Если вызвать с именем внешнего сервера, она дополнительно вернет версию Oracle сервера.
FUNCTION oracle_execute(server name, stmt text) RETURNS void
Эта функция может использоваться для выполнения произвольных SQL-запросов на удаленном сервере Oracle. Это будет работать только с запросами, которые не возвращают результаты (как правило, DDL-запросы).
Будьте осторожны при использовании этой функции, так как она может нарушить управление транзакциями oracle_fdw. Помните, что выполнение оператора DDL в Oracle приведет к неявному COMMIT. Рекомендуется использовать эту функцию вне многооператорных транзакций.
H.3.5. Опции #
H.3.5.1. Опции обертки внешних данных #
(Внимание: Если вы измените обертку внешних данных по умолчанию
oracle_fdw
, любые изменения будут потеряны при
дампе/восстановлении. Создайте новую обертку внешних данных, если хотите,
чтобы параметры сохранялись. SQL-скрипт, поставляемый с программным обеспечением,
содержит оператор CREATE FOREIGN DATA WRAPPER, который вы можете использовать.)
nls_lang (optional)
Устанавливает переменную окружения NLS_LANG для Oracle в это значение. NLS_LANG имеет форму “language_territory.charset” (например, AMERICAN_AMERICA.AL32UTF8). Это должно соответствовать кодировке вашей базы данных. Если это значение не установлено, oracle_fdw автоматически выполнит правильное действие, если сможет, и выдаст предупреждение, если не сможет. Устанавливайте это значение только если вы точно знаете, что делаете. См. раздел Проблемы.
H.3.5.2. Опции внешнего сервера #
dbserver (required)
Строка подключения к базе данных Oracle для удаленной базы данных. Это может быть в любой из форм, поддерживаемых Oracle, при условии, что ваш клиент Oracle настроен соответствующим образом. Установите это значение в пустую строку для локальных (“BEQUEATH”) подключений.
уровень_изоляции (необязательно, по умолчанию
serializable
)Уровень изоляции транзакций для использования в базе данных Oracle. Значение может быть
serializable
,read_committed
илиread_only
.Обратите внимание, что таблица Oracle может быть запрошена более одного раза в течение одного оператора Tantor SE (например, во время соединения с вложенным циклом). Чтобы убедиться, что не возникает несоответствий, вызванных условиями гонки с параллельными транзакциями, уровень изоляции транзакции должен гарантировать стабильность чтения. Это гарантируется только уровнями изоляции SERIALIZABLE или READ ONLY в Oracle.
К сожалению, реализация SERIALIZABLE в Oracle довольно плохая и вызывает ошибки сериализации (ORA-08177) в неожиданных ситуациях, таких как вставка в таблицу. Использование транзакций с уровнем изоляции READ COMMITTED обходит эту проблему, но существует риск несоответствий. Если нужно использовать это, проверьте свои планы выполнения, если внешнее сканирование может быть выполнено более одного раза.
nchar (boolean, необязательный, по умолчанию
off
)Установка этого параметра в
on
выбирает более дорогостоящую конвертацию символов на стороне Oracle. Это необходимо, если таблицы Oracle содержат столбцы NCHAR или NVARCHAR2, которые содержат символы, не представимые в наборе символов базы данных Oracle.Установка
nchar
вon
имеет заметное влияние на производительность и вызывает ошибки ORA-01461 с операторами UPDATE, которые устанавливают строки длиной более 2000 байт (или 16383, если у вас MAX_STRING_SIZE = EXTENDED). Эта ошибка, по-видимому, является ошибкой Oracle.set_timezone (логический, необязательный, по умолчанию
off
)Установка этого параметра в
on
устанавливает временную зону сеанса Oracle в текущее значение параметра Tantor SEtimezone
при установлении соединения с Oracle. Это полезно только в том случае, если вы планируете использовать столбцы Oracle типа TIMESTAMP WITH LOCAL TIME ZONE и хотите преобразовать их вtimestamp without time zone
в Tantor SE.Обратите внимание, что если вы измените
timezone
после установления соединения с Oracle, oracle_fdw не изменит часовой пояс сеанса Oracle. В этом случае вы можете вызватьoracle_close_connections()
, чтобы новое соединение было открыто при следующем доступе к внешней таблице.Если Oracle не распознает часовой пояс, соединения завершатся с ошибкой, подобной
ORA-01882: timezone region not found
В этом случае либо используйте другой
timezone
или оставьте опцию в положенииoff
и установите переменную окружения ORA_SDTZ на соответствующее значение в окружении Tantor SE сервера.
H.3.5.3. Параметры отображения пользователя #
user (required)
Имя пользователя Oracle для сеанса. Установите это значение в пустую строку для внешней аутентификации, если вы не хотите хранить учетные данные Oracle в базе данных Tantor SE (один из простых способов - использовать внешнее хранилище паролей).
password (required)
Пароль для пользователя Oracle.
H.3.5.4. Опции внешней таблицы #
table (required)
Имя таблицы Oracle. Это имя должно быть написано точно так же, как оно указано в системном каталоге Oracle, поэтому обычно состоит только из заглавных букв.
Чтобы определить внешнюю таблицу на основе произвольного запроса Oracle, установите этот параметр в запрос, заключенный в круглые скобки, например
OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')
Не устанавливайте опцию schema в этом случае. INSERT, UPDATE и DELETE будут работать с внешними таблицами, определенными на простых запросах; если нужно этого избежать (или избежать запутанных сообщений об ошибках Oracle для более сложных запросов), используйте опцию таблицы readonly.
dblink (optional)
Ссылка на базу данных Oracle, через которую осуществляется доступ к таблице. Это имя должно быть написано точно так, как оно указано в системном каталоге Oracle, поэтому обычно состоит только из заглавных букв.
schema (optional)
Схема (или владелец) таблицы. Полезно для доступа к таблицам, которые не принадлежат подключенному пользователю Oracle. Это имя должно быть написано точно так же, как оно указано в системном каталоге Oracle, поэтому обычно состоит только из заглавных букв.
max_long (опционально, по умолчанию “32767”)
Максимальная длина любых столбцов LONG, LONG RAW и XMLTYPE в таблице Oracle. Возможные значения — целые числа от 1 до 1073741823 (максимальный размер
bytea
в Tantor SE). Это количество памяти будет выделено как минимум дважды, поэтому большие значения будут потреблять много памяти. Если max_long меньше длины самого длинного извлеченного значения, вы получите сообщение об ошибкеORA-01406: fetched column value was truncated
.только для чтения (необязательно, по умолчанию “ложь”)
INSERT, UPDATE и DELETE разрешены только для таблиц, где эта опция не установлена на yes/on/true.
sample_percent (необязательно, по умолчанию “100”)
Эта опция влияет только на обработку ANALYZE и может быть полезна для выполнения ANALYZE на очень больших таблицах в разумное время.
Значение должно быть в диапазоне от 0.000001 до 100 и определяет процент блоков таблицы Oracle, которые будут случайным образом выбраны для вычисления статистики таблицы Tantor SE. Это осуществляется с помощью предложения
SAMPLE BLOCK (x)
в Oracle.ANALYZE завершится с ошибкой ORA-00933 для таблиц, определенных с запросами Oracle, и может завершиться с ошибкой ORA-01446 для таблиц, определенных с использованием сложных представлений Oracle.
предварительная выборка (опционально, по умолчанию “50”)
Устанавливает количество строк, которые будут извлечены за один обмен между Tantor SE и Oracle во время сканирования внешней таблицы. Значение должно быть в пределах от 1 до 1000.
Более высокие значения могут ускорить производительность, но будут использовать больше памяти на сервере Tantor SE.
Обратите внимание, что предварительная выборка не выполняется, если таблица Oracle содержит столбцы типа
MDSYS.SDO_GEOMETRY
.lob_prefetch (опционально, по умолчанию “1048576”)
Устанавливает количество байт, которые предварительно выбираются для значений BLOB, CLOB и BFILE. LOB, превышающие этот размер, потребуют дополнительных обменов между Tantor SE и Oracle, поэтому установка этого значения больше, чем размер вашего типичного LOB, будет полезна для производительности. Выбор больших значений для этой опции может выделить больше памяти на стороне сервера, но повысит производительность для больших LOB.
H.3.5.5. Опции столбца #
ключ (необязательно, по умолчанию “false”)
Если установлено значение yes/on/true, соответствующий столбец в удаленной таблице Oracle считается столбцом первичного ключа. Для работы UPDATE и DELETE необходимо установить этот параметр для всех столбцов, которые принадлежат первичному ключу таблицы.
strip_zeros (опционально, по умолчанию “false”)
Если установлено значение yes/on/true, символы ASCII 0 будут удалены из строки во время передачи. Такие символы допустимы в Oracle, но не в Tantor SE, поэтому они вызовут ошибку при чтении с помощью oracle_fdw. Этот параметр имеет смысл только для
character
,character varying
иtext
столбцов.
H.3.6. Использование #
H.3.6.1. Разрешения Oracle #
Пользователю Oracle, очевидно, потребуется привилегия CREATE SESSION и право на выборку из соответствующей таблицы или представления. Обратите внимание, что oracle_fdw обращается к таблице Oracle во время планирования запроса, чтобы получить ее определение. Это происходит до проверки разрешений на внешнюю таблицу. Следовательно, вы можете получить ошибку Oracle, если попытаетесь получить доступ к внешней таблице, на которую у вас нет разрешений в Tantor SE. Это ожидаемое поведение и не является проблемой безопасности.
Для EXPLAIN VERBOSE пользователю также понадобятся привилегии SELECT на V$SQL и V$SQL_PLAN.
H.3.6.2. Соединения #
oracle_fdw кэширует подключения к Oracle, потому что создание сеанса Oracle для каждого отдельного запроса является дорогостоящим. Все подключения автоматически закрываются, когда сессия Tantor SE завершается.
Функция oracle_close_connections()
может быть использована для закрытия всех кэшированных подключений к Oracle. Это может быть полезно для длительных сессий, которые не обращаются к внешним таблицам все время и хотят избежать блокировки ресурсов, необходимых для открытого подключения к Oracle.
Вы не можете вызвать эту функцию внутри транзакции, которая изменяет данные Oracle.
H.3.6.3. Столбцы #
Когда вы определяете внешнюю таблицу, столбцы таблицы Oracle сопоставляются со столбцами Tantor SE в порядке их определения.
oracle_fdw будет включать в запрос Oracle только те столбцы, которые действительно необходимы для запроса Tantor SE.
Таблица Tantor SE может иметь больше или меньше столбцов, чем таблица Oracle. Если она имеет больше столбцов, и эти столбцы используются, вы получите предупреждение, и будут возвращены значения NULL.
Если нужно выполнить UPDATE или DELETE, убедитесь, что
key
опция установлена для всех столбцов, которые принадлежат
первичному ключу таблицы. В противном случае это приведет к ошибкам.
H.3.6.4. Типы данных #
Вы должны определить столбцы Tantor SE с типами данных, которые oracle_fdw может преобразовать (см. таблицу преобразования ниже). Это ограничение применяется только в том случае, если столбец действительно используется, поэтому вы можете определить “фиктивные” столбцы для непереводимых типов данных, пока вы к ним не обращаетесь (этот трюк работает только с SELECT, но не при изменении внешних данных). Если значение Oracle превышает размер столбца Tantor SE (например, длину столбца varchar или максимальное значение целого числа), вы получите ошибку времени выполнения.
Эти преобразования автоматически обрабатываются oracle_fdw:
Oracle type | Possible Tantor SE types
-------------------------+--------------------------------------------------
CHAR | char, varchar, text
NCHAR | char, varchar, text
VARCHAR | char, varchar, text
VARCHAR2 | char, varchar, text, json
NVARCHAR2 | char, varchar, text
CLOB | char, varchar, text, json
LONG | char, varchar, text
RAW | uuid, bytea
BLOB | bytea
BFILE | bytea (read-only)
LONG RAW | bytea
NUMBER | numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8,
| boolean, char, varchar, text
FLOAT | numeric, float4, float8, char, varchar, text
BINARY_FLOAT | numeric, float4, float8, char, varchar, text
BINARY_DOUBLE | numeric, float4, float8, char, varchar, text
DATE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH | date, timestamp, timestamptz, char, varchar, text
LOCAL TIME ZONE |
INTERVAL YEAR TO MONTH | interval, char, varchar, text
INTERVAL DAY TO SECOND | interval, char, varchar, text
XMLTYPE | xml, char, varchar, text
MDSYS.SDO_GEOMETRY | geometry (see "PostGIS support" below)
Если NUMBER преобразуется в boolean, 0 означает
false
, все остальное true
.
Вставка или обновление XMLTYPE работает только с значениями, которые не превышают максимальную длину типа данных VARCHAR2 (4000 или 32767, в зависимости от параметра MAX_STRING_SIZE
).
NCLOB в настоящее время не поддерживается, потому что Oracle не может автоматически преобразовать его в кодировку клиента.
Если нужно преобразовать TIMESTAMP WITH LOCAL TIME ZONE в
timestamp
, рассмотрите возможность установки опции
set_timezone
на удаленном сервере.
Если вам нужны преобразования, превышающие указанные выше, определите соответствующее представление в Oracle или Tantor SE.
H.3.6.5. Условия WHERE и предложения ORDER BY #
Tantor SE будет использовать все применимые части предложения WHERE в качестве фильтра для сканирования. Запрос Oracle, который строит oracle_fdw, будет содержать предложение WHERE, соответствующее этим критериям фильтрации, всякий раз, когда такое условие может быть безопасно переведено на Oracle SQL. Эта функция, также известная как передача условий WHERE, может значительно сократить количество строк, извлекаемых из Oracle, и может позволить оптимизатору Oracle выбрать хороший план для доступа к необходимым таблицам.
Аналогично, предложения ORDER BY будут переданы в Oracle, где это возможно. Обратите внимание, что никакое условие ORDER BY, которое сортирует по строке символов, не будет передано, так как порядок сортировки в Tantor SE и Oracle не может быть гарантированно одинаковым.
Чтобы воспользоваться этим, постарайтесь использовать простые условия для внешней таблицы. Выберите типы данных столбцов Tantor SE, которые соответствуют типам Oracle, потому что в противном случае условия не могут быть переведены.
Выражения now()
,
transaction_timestamp()
,
current_timestamp
,
current_date
и
localtimestamp
будут переведены правильно.
Вывод EXPLAIN покажет используемый запрос Oracle, так что вы сможете увидеть, какие условия были переведены на Oracle и как.
H.3.6.6. Соединения между внешними таблицами #
Начиная с Tantor SE 14, oracle_fdw может передавать соединения на сервер Oracle, то есть соединение между двумя внешними таблицами приведет к одному запросу Oracle, который выполняет соединение на стороне Oracle.
Есть некоторые ограничения, когда это может произойти:
Обе таблицы должны быть определены на одном и том же внешнем сервере.
Соединения между тремя или более таблицами не будут переданы.
Соединение должно быть в операторе SELECT.
oracle_fdw должен иметь возможность передавать все условия соединения и WHERE-клаузулы.
Перекрестные соединения без условий соединения не опускаются.
Если соединение передано на нижний уровень, предложения ORDER BY не будут переданы на нижний уровень.
Важно, чтобы статистика таблицы для обеих внешних таблиц была собрана с помощью ANALYZE для Tantor SE, чтобы определить лучшую стратегию соединения.
H.3.6.7. Изменение внешних данных #
oracle_fdw поддерживает INSERT, UPDATE и DELETE на внешних таблицах. Это разрешено по умолчанию (также в базах данных, обновленных с более раннего выпуска Tantor SE) и может быть отключено путем установки опции таблицы readonly.
Для работы команд UPDATE и DELETE столбцы, соответствующие столбцам первичного ключа таблицы Oracle, должны иметь установленную опцию столбца key. Эти столбцы используются для идентификации строки внешней таблицы, поэтому убедитесь, что опция установлена на всех столбцах, которые принадлежат первичному ключу.
Если вы опускаете столбец внешней таблицы при выполнении INSERT, этот столбец устанавливается в значение, определенное в предложении DEFAULT на Tantor SE внешней таблице (или NULL, если предложение DEFAULT отсутствует). Предложения DEFAULT на соответствующих столбцах Oracle не используются. Если Tantor SE внешняя таблица не включает все столбцы таблицы Oracle, предложения DEFAULT Oracle будут использоваться для столбцов, не включенных в определение внешней таблицы.
Клаузула RETURNING в операциях INSERT, UPDATE и DELETE поддерживается, за исключением столбцов с типами данных Oracle LONG и LONG RAW (Oracle не поддерживает эти типы данных в клаузуле RETURNING).
Триггеры на внешних таблицах поддерживаются начиная с Tantor SE 14. Триггеры, определенные с AFTER и FOR EACH ROW, требуют, чтобы во внешней таблице не было столбцов с типами данных Oracle LONG или LONG RAW. Это связано с тем, что такие триггеры используют упомянутую выше конструкцию RETURNING.
Хотя изменение внешних данных работает, производительность не особенно хорошая, особенно когда затрагивается много строк, потому что (из-за того, как работают обертки внешних данных) каждая строка должна обрабатываться индивидуально.
Транзакции пересылаются в Oracle, поэтому BEGIN, COMMIT, ROLLBACK и SAVEPOINT работают как ожидалось. Подготовленные операторы, включающие Oracle, не поддерживаются. См. раздел Внутреннее устройство для подробностей.
Поскольку oracle_fdw по умолчанию использует сериализованные транзакции, возможно, что операторы изменения данных приведут к ошибке сериализации:
ORA-08177: can't serialize access for this transaction
Это может произойти, если параллельные транзакции изменяют таблицу, и вероятность этого увеличивается в долгосрочных транзакциях. Такие ошибки можно определить по их SQLSTATE (40001). Приложение, использующее oracle_fdw, должно повторить транзакции, которые завершились с этой ошибкой.
Возможно использовать другой уровень изоляции транзакций, см. Параметры внешнего сервера для обсуждения.
H.3.6.8. EXPLAIN #
Tantor SE EXPLAIN
покажет запрос, который фактически отправляется
в Oracle. EXPLAIN VERBOSE покажет план выполнения Oracle (это
не будет работать с сервером Oracle 9i или более старыми версиями, см.
Проблемы).
H.3.6.9. ANALYZE #
Вы можете использовать ANALYZE для сбора статистики по внешней таблице. Это поддерживается oracle_fdw.
Без статистики, Tantor SE не имеет возможности оценить количество строк для запросов к внешней таблице, что может привести к выбору неэффективных планов выполнения.
Tantor SE не будет автоматически собирать статистику для внешних таблиц с помощью демона autovacuum, как это делается для обычных таблиц, поэтому особенно важно запускать ANALYZE для внешних таблиц после их создания и всякий раз, когда удаленная таблица значительно изменилась.
Имейте в виду, что анализ внешней таблицы Oracle приведет к полному последовательному сканированию таблицы. Вы можете использовать параметр таблицы sample_percent, чтобы ускорить этот процесс, используя только выборку из таблицы Oracle.
H.3.6.10. Поддержка PostGIS #
Тип данных geometry
доступен только при установке
PostGIS.
Единственные поддерживаемые типы геометрии: POINT, LINE, POLYGON, MULTIPOINT, MULTILINE и MULTIPOLYGON в двух и трех измерениях. Пустые геометрии PostGIS не поддерживаются, так как у них нет эквивалента в Oracle Spatial.
Значения NULL для Oracle SRID будут преобразованы в 0 и наоборот.
Для других преобразований между Oracle SRID и PostGIS SRID создайте
файл srid.map
в каталоге share
Tantor SE.
Каждая строка этого файла должна содержать Oracle SRID и соответствующий PostGIS SRID,
разделенные пробелом. Держите файл небольшим для хорошей производительности.
H.3.6.11. Поддержка IMPORT FOREIGN SCHEMA #
Начиная с Tantor SE 14, поддерживается IMPORT FOREIGN SCHEMA для массового импорта определений таблиц для всех таблиц в схеме Oracle. В дополнение к документации по IMPORT FOREIGN SCHEMA, рассмотрите следующее:
IMPORT FOREIGN SCHEMA создаст внешние таблицы для всех объектов, найденных в ALL_TAB_COLUMNS. Это включает таблицы, представления и материализованные представления, но не синонимы.
Эти параметры поддерживаются для IMPORT FOREIGN SCHEMA:
регистр: управляет преобразованием регистра для имен таблиц и столбцов во время импорта
Возможные значения:
keep
: оставьте имена такими, какие они есть в Oracle, обычно в верхнем регистре.lower
: переводит все имена таблиц и столбцов в нижний регистр.smart
: переводите только имена, которые написаны полностью заглавными буквами в Oracle (это значение по умолчанию).
сортировка: сортировка, используемая для приведения к нижнему регистру для опций
lower
иsmart
в caseЗначение по умолчанию —
default
, что означает использование коллации по умолчанию для базы данных. Поддерживаются только коллации в схемеpg_catalog
. См. значенияcollname
в каталогеpg_collation
для списка возможных значений.dblink: ссылка на базу данных Oracle, через которую осуществляется доступ к схеме
Это имя должно быть написано точно так же, как оно указано в системном каталоге Oracle, поэтому обычно состоит только из заглавных букв.
только для чтения: устанавливает только для чтения опцию для всех импортированных таблиц
См. раздел Параметры для подробностей.
skip_tables (по умолчанию
false
): не импортировать таблицыskip_views (по умолчанию
false
): не импортировать представленияskip_matviews (по умолчанию
false
): не импортировать материализованные представленияmax_long: устанавливает опцию max_long для всех импортированных таблиц
См. раздел Параметры для подробностей.
sample_percent: устанавливает параметр sample_percent для всех импортируемых таблиц
См. раздел Параметры для подробностей.
предварительная выборка: устанавливает опцию предварительной выборки для всех импортированных таблиц
См. раздел Параметры для подробностей.
lob_prefetch: устанавливает опцию lob_prefetch для всех импортированных таблиц
См. раздел Параметры для подробностей.
nchar: устанавливает опцию nchar для всех импортированных таблиц
См. раздел Параметры для подробностей.
set_timezone: устанавливает set_timezone опцию на всех импортированных таблицах
См. раздел Параметры для подробностей.
Имя схемы Oracle должно быть написано точно так же, как в Oracle, поэтому обычно в верхнем регистре. Поскольку Tantor SE переводит имена в нижний регистр перед обработкой, вы должны защитить имя схемы двойными кавычками (например,
"SCOTT"
).Названия таблиц в предложении LIMIT TO или EXCEPT должны быть написаны так, как они будут отображаться в Tantor SE после преобразования регистра, описанного выше.
Обратите внимание, что IMPORT FOREIGN SCHEMA не работает с сервером Oracle 8i; см. раздел Проблемы для получения подробной информации.
H.3.7. Требования к установке #
oracle_fdw должен компилироваться и работать на любой платформе, поддерживаемой Tantor SE и клиентом Oracle, хотя я мог протестировать его только на Linux и Windows.
Tantor SE 14 или выше требуется.
Как всегда, вы должны использовать последнюю минорную версию для любой версии Tantor SE, которую вы используете.
oracle_fdw написан для стандартного открытого исходного кода Tantor SE. Форки Tantor SE, такие как “PostgresPro” и “Postgres-XL”, вероятно, будут несовместимы. Если вы все же хотите попробовать, вам придется собрать oracle_fdw из исходного кода. Если вы столкнетесь с проблемами при использовании такого сервера, основанного на Tantor SE, пожалуйста, попробуйте оригинальную версию перед тем, как сообщать о проблеме.
Требуется версия клиента Oracle 11.2 или выше. oracle_fdw может быть собран и использован с Oracle Instant Client, а также с установками Oracle Client и Server, установленными с помощью Universal Installer. Бинарные файлы, скомпилированные с Oracle Client 11, могут быть использованы с более поздними версиями клиента без перекомпиляции или повторной компоновки.
Поддерживаемые версии сервера Oracle зависят от используемой версии клиента (см. матрицу совместимости клиент/сервер Oracle в документе Oracle Support 207303.1). Tantor SE и Oracle должны иметь одинаковую архитектуру. Например, вы не можете использовать 32-битное программное обеспечение для одного и 64-битное программное обеспечение для другого.
Рекомендуется использовать последний набор исправлений как на клиенте Oracle, так и на сервере, особенно с неподдерживаемыми версиями Oracle. Для списка ошибок Oracle, которые, как известно, влияют на удобство использования oracle_fdw, см. раздел Проблемы. Обратитесь к Wiki oracle_fdw для получения советов по установке и настройке Oracle и поделитесь там своими знаниями.
H.3.8. Установка #
Если вы используете бинарное распределение oracle_fdw, перейдите к “Установка расширения” ниже. Дополнительные советы по установке oracle_fdw можно найти на Wiki.
H.3.8.1. Установка расширения: #
Убедитесь, что общая библиотека oracle_fdw установлена в
каталоге библиотек Tantor SE и что
oracle_fdw.control
и SQL файлы находятся в
каталоге расширений Tantor SE.
Поскольку общая библиотека клиента Oracle, вероятно, не находится в
стандартном пути библиотек, вы должны убедиться, что Tantor SE
сервер сможет её найти. Как это сделать, зависит от
операционной системы; в Linux вы можете установить
LD_LIBRARY_PATH или использовать /etc/ld.so.conf
.
Убедитесь, что все необходимые переменные окружения Oracle установлены в окружении процесса сервера Tantor SE (ORACLE_HOME, если вы не используете Instant Client, TNS_ADMIN, если у вас есть файлы конфигурации и т.д.)
Чтобы установить расширение в базу данных, подключитесь как суперпользователь и
CREATE EXTENSION oracle_fdw;
Это определит необходимые функции и создаст обертку для внешних данных.
Для обновления с версии oracle_fdw до 1.0.0, используйте
ALTER EXTENSION oracle_fdw UPDATE;
Обратите внимание, что версия расширения, отображаемая командой psql
\dx
или системным каталогом
pg_available_extensions
,
не является установленной версией oracle_fdw. Чтобы
получить версию oracle_fdw, используйте функцию
oracle_diag
.
H.3.8.2. Запуск регрессионных тестов: #
Если вы не разрабатываете oracle_fdw или не хотите тестировать его функциональность на экзотической платформе, вам не нужно этого делать.
Чтобы регрессионные тесты работали, у вас должен быть запущен кластер Tantor SE и сервер Oracle (версии 10.2 или выше с Locator или Spatial), а бинарные файлы oracle_fdw должны быть установлены.
Регрессионные тесты создадут базу данных под названием contrib_regression
и выполнят ряд тестов. Для успешного выполнения регрессионных тестов PostGIS должны быть установлены бинарные файлы PostGIS.
База данных Oracle должна быть подготовлена следующим образом: - Должен существовать пользователь
scott
с паролем tiger
(если вы не хотите редактировать скрипты регрессионного тестирования).
Пользователю необходимы системные привилегии CREATE SESSION, CREATE TABLE, CREATE VIEW и
CREATE MATERIALIZED VIEW, а также достаточная квота
на его таблицы по умолчанию, а также привилегии SELECT на V$SQL и
V$SQL_PLAN.
Установите окружение для сервера Tantor SE, чтобы он мог установить соединение с Oracle без строки подключения: если сервер Oracle находится на той же машине, установите переменные окружения ORACLE_SID и ORACLE_HOME соответствующим образом, для удаленного сервера установите переменную окружения TWO_TASK (или LOCAL на Windows) на строку подключения.
Регрессионные тесты выполняются следующим образом:
$ make installcheck
Тесты на регрессию поддерживаются в актуальном состоянии с последней версией разработки Tantor SE. Если вы запускаете их с более старыми версиями Tantor SE, вы можете ожидать незначительные различия, такие как измененные сообщения об ошибках или другой формат вывода psql.
H.3.9. Внутреннее устройство #
oracle_fdw устанавливает MODULE сессии Oracle в
postgres
и ACTION в номер фонового процесса.
Это может помочь идентифицировать сессию Oracle и позволяет
отслеживать её с помощью DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.
oracle_fdw использует интерфейс массивов Oracle, чтобы избежать ненужных обменов между клиентом и сервером. Размер пакета можно настроить с помощью параметра таблицы prefetch, который по умолчанию установлен на 50.
Вместо использования PLAN_TABLE для объяснения запроса Oracle (что потребовало бы создания такой таблицы в базе данных Oracle), oracle_fdw использует планы выполнения, хранящиеся в библиотечном кэше. Для этого запрос Oracle явно описывается, что заставляет Oracle анализировать запрос. Сложная часть заключается в том, чтобы найти SQL_ID и CHILD_NUMBER оператора в V$SQL, потому что столбец SQL_TEXT содержит только первые 1000 байт запроса. Поэтому oracle_fdw добавляет комментарий к запросу, который содержит хеш текста запроса. Это используется для поиска в V$SQL. Фактический план выполнения или информация о стоимости извлекается из V$SQL_PLAN.
oracle_fdw использует уровень изоляции транзакций SERIALIZABLE на стороне Oracle, что соответствует Tantor SE REPEATABLE READ. Это необходимо, потому что один оператор Tantor SE может привести к выполнению нескольких запросов Oracle (например, во время соединения с вложенным циклом), и результаты должны быть согласованными. К сожалению, реализация SERIALIZABLE в Oracle имеет определенные особенности; см. раздел Проблемы для получения дополнительной информации.
Транзакция Oracle фиксируется непосредственно перед фиксацией локальной транзакции, так что завершенная транзакция Tantor SE гарантирует, что транзакция Oracle завершена. Однако существует небольшая вероятность того, что транзакция Tantor SE не сможет завершиться, даже если транзакция Oracle зафиксирована. Это нельзя избежать без использования двухфазных транзакций и менеджера транзакций, что выходит за рамки того, что может разумно предоставить обертка для внешних данных. Подготовленные операторы, связанные с Oracle, не поддерживаются по той же причине.
H.3.10. Проблемы #
H.3.10.1. Кодировка #
Символы, хранящиеся в базе данных Oracle, которые не могут быть преобразованы в кодировку базы данных Tantor SE, будут без предупреждения заменены замещающими символами, обычно это нормальный или перевернутый вопросительный знак, Oracle. Вы не получите никаких предупреждений или сообщений об ошибках.
Если вы используете кодировку базы данных Tantor SE, которую Oracle не знает (в настоящее время это EUC_CN, EUC_KR, LATIN10, MULE_INTERNAL, WIN874 и SQL_ASCII), символы, не относящиеся к ASCII, не могут быть переведены корректно. В этом случае вы получите предупреждение, и символы будут заменены на символы замены, как описано выше.
Вы можете установить опцию nls_lang внешнего источника данных, чтобы принудительно использовать определенную кодировку Oracle, но полученные символы, скорее всего, будут некорректными и приведут к сообщениям об ошибках Tantor SE. Это, вероятно, полезно только для кодировки SQL_ASCII, если вы точно знаете, что делаете. См. раздел Опции.
H.3.10.2. Ограниченная функциональность в старых версиях Oracle #
Определение системных каталогов Oracle V$SQL и V$SQL_PLAN изменилось с Oracle 10.1. Использование EXPLAIN VERBOSE с более старыми версиями сервера Oracle приведет к ошибкам, таким как:
ERROR: error describing query: OCIStmtExecute failed to execute remote query for sql_id DETAIL: ORA-00904: "LAST_ACTIVE_TIME": invalid identifier
Нет планов исправлять это, так как Oracle 9i не поддерживается с 2010 года, и эта функциональность не является необходимой.
IMPORT FOREIGN SCHEMA вызывает следующую ошибку с сервером Oracle 8i:
ERROR: error importing foreign schema: OCIStmtExecute failed to execute column query DETAIL: ORA-00904: invalid column name
Это потому, что представление ALL_TAB_COLUMNS не содержит столбца CHAR_LENGTH, который был добавлен в Oracle 9i.
H.3.10.3. Библиотеки LDAP #
Библиотека совместного использования клиента Oracle поставляется с собственной реализацией клиента LDAP, соответствующей
RFC
1823, поэтому эти функции имеют те же имена, что и у OpenLDAP.
Это приведет к конфликту имен, когда сервер Tantor SE был
настроен с параметром --with-ldap
.
Конфликт имен не будет обнаружен, потому что oracle_fdw загружается во время выполнения, но проблемы возникнут, если кто-то вызовет функцию LDAP. Обычно OpenLDAP загружается первым, поэтому если Oracle вызывает функцию LDAP (например, если вы используете разрешение имен в каталоге), серверный процесс завершится аварийно. Это может привести к появлению сообщений, подобных следующему (наблюдается на Linux) в журнале сервера Tantor SE:
../../../libraries/libldap/getentry.c:29: ldap_first_entry: Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.
Лучше всего настроить Tantor SE --without-ldap
. Это единственный безопасный способ избежать этой проблемы. Даже когда Tantor SE собран с --with-ldap
, он может работать, пока вы не используете какую-либо функциональность LDAP клиента в Oracle. На некоторых платформах вы можете заставить клиентскую общую библиотеку Oracle загружаться до запуска сервера Tantor SE (LD_PRELOAD на Linux). Тогда должны использоваться функции LDAP Oracle. В этом случае Oracle может использовать функциональность LDAP, но использование LDAP из Tantor SE приведет к сбою бэкенда.
Вы не можете использовать функциональность LDAP как в Tantor SE, так и в Oracle, точка.
H.3.10.4. Ошибки сериализации #
В Oracle 11.2 или выше, вставка первой строки в только что созданную таблицу Oracle с помощью oracle_fdw приведет к ошибке сериализации.
Это связано с функцией Oracle, называемой отложенное создание сегмента, которая откладывает выделение места для новой таблицы до вставки первой строки. Это вызывает сбой сериализации с сериализуемыми транзакциями (см. документ 1285464.1 в базе знаний Oracle).
Это не серьезная проблема; вы можете обойти ее, либо игнорируя первую ошибку, либо создавая таблицу с SEGMENT CREATION IMMEDIATE.
Гораздо более неприятная проблема заключается в том, что параллельные вставки могут иногда вызывать ошибки сериализации, когда страница индекса разделяется одновременно с модифицирующей сериализуемой транзакцией (см. документ Oracle 160593.1).
Oracle утверждает, что это не ошибка, и предложенное решение заключается в повторной попытке выполнения транзакции, которая вызвала ошибку сериализации.
H.3.10.5. Ошибки Oracle #
Это список ошибок Oracle, которые влияют или влияли на oracle_fdw.
Ошибка 2728408 может вызвать ORA-8177 невозможно сериализовать доступ для этой транзакции
даже если попытка модификации удаленных данных не производится.
Это может произойти с сервером Oracle 8.1.7.4 (установите одноразовый патч 2728408) или сервером Oracle 9.2 (установите Patch Set 9.2.0.4 или лучше).
Клиент Oracle 21c, как известно, не работает с колонками CLOB (они отображаются пустыми). Нет окончательного доказательства, что это ошибка Oracle, но другие версии работают нормально.
H.3.10.6. Отсутствует DLL клиента Oracle (только для Windows) #
Следующее сообщение об ошибке (из любого запроса, связанного с внешней таблицей Oracle) указывает на то, что Tantor SE не может найти библиотеку клиента Oracle:
ERROR: Oracle client library (oci.dll) not found DETAIL: No Oracle client is installed, or your system is configured incorrectly. HINT: Verify that the PATH variable includes the Oracle client.
Убедитесь, что путь к oci.dll
находится в
переменной окружения PATH пользователя, запускающего сервер Tantor SE.
Если он работает как служба Windows, это системная
среда, и вы должны перезапустить службу после изменения.
Если обновление окружения не работает, возможно, вы используете дистрибутив Tantor SE, который предоставляет свои собственные переменные окружения, скрывая клиент Oracle. См. отчет об ошибке на вопрос #160 на GitHub для получения дополнительной информации.
H.3.11. Поддержка #
Посмотрите Wiki для получения помощи с настройкой на определенных платформах и другими проблемами. Также, поиск по вопросам на GitHub может помочь - возможно, вы не первый с этой проблемой.
Если нужно сообщить о проблеме с oracle_fdw, и имя внешнего сервера (например) “ora_serv”, пожалуйста, включите вывод
SELECT oracle_diag('ora_serv');
в вашем отчете о проблеме. Если это вызывает ошибку, пожалуйста, также включите вывод
SELECT oracle_diag();
Если у вас есть проблема, вопрос или любой вид обратной связи, предпочтительный вариант - открыть проблему на GitHub issues Это требует учетной записи GitHub.
Коммерческая поддержка доступна от CYBERTEC PostgreSQL International GmbH.