PL/SQL через dblink
Приходилось ли Вам реализовывать нестандартные решения? А в Oracle? Мне бы хотелось рассмотреть использование техник, позволяющих лучше узнать принципы работы СУБД, а в совокупности предоставляющие удобство для разработчика.
Намного удобнее, выполнять разработку приложений баз данных в едином пространстве, а результаты переносить по ландшафту системы в фоновом режиме, автоматически регистрируя производимые изменения. 
Пример выполнения обновления на сервере разработки
Пролог
Вы встречали вредных DBA? А работали с такими? На самом деле, обе стороны (Developer vs. DBA), добиваются одного результата, работоспособности системы, но с разных сторон. Впрочем, когда система расширяется, децентрализуется, но сохраняет целостность в реализации, то поддержка консистентного состояния программной оснастки может начать доставлять серьезные неудобства. Появляются серверы разработки, тестирования, «продуктива» — и все это замечательно, но всех их нужно обновлять.
В Oracle есть инструменты казалось бы похожие на рассматриваемый:
• Audit
• Oracle Streams
• Alert
Но все они выполняют другие функции. Одни, обеспечивают аудит изменений, другие синхронизируют данные. А мне бы хотелось действовать более прозрачно, вот например:
Теперь все мои действия продублированы на сервере ‘prod’. А может быть, даже так:
И, скажем, семь серверов создали таблицу «A». Здорово? Тогда – поехали.
Подготовка
Выполним соединение с базой данных от имени пользователя имеющего достаточные привилегии для последующих действий:
Предполагается что пользователь, выполняющий обновления, не должен иметь доступа к самой системе обновления, впрочем, как и сама система не привязывается к целевой схеме, следовательно, может использоваться универсально. Поэтому создадим нового пользователя:
Поскольку статья не об ограничении прав новых пользователей:
Опустив рассуждения о проводимых изысканиях, скажу, что самым сложным оказалось получение анонимного PL/SQL блока, который приводился в примере выше. Естественно, одни действия в конечном итоге порождают другие, так например, всё тот же блок из примера, выполнит insert, но на самом деле может быть и не выполнит! Ведь выполняться он будет на другом сервере. Поэтому нас будет интересовать именно анонимный PL/SQL блок, а не последствия. Паблик синоним V$SQL или представление V_$SQL на которое он ссылается, хранит все запросы, выполнявшиеся на сервере. Попробуем найти в нём нашу цель:
Действительно, именно мой анонимный блок находится там где положено. Конечно же, SQL_ID выполняя мой пример, будет другой, но принадлежит ли он мне? Проверим:
Нет, не принадлежит, оптимизатор видит, что ранее такое выражение уже выполнялось, и возвращает уже зарегистрированный SQL_ID. Пометим на полях свои изыскания, и продолжим изучение:
Выполнившийся блок, удалось найти, но мне бы хотелось узнать, кем он выполнен, а точнее узнать, что именно я выполнял его в определенный момент времени. Другое представление V_$SESSION, сможет мне в этом помочь:
select sql_id, prev_sql_id from v$session;
Тут нужно пояснить, что синоним v$session предоставляет доступ к VIEW, а доступ для пользователя организуется командой:
grant select on v_$session to upd;
Дело тут в том, что тип представление v_$session является FIXED VIEW, поэтому давать права на его синоним – запрещено. Впрочем, если выдавать права на синоним, скажем таблицы, сами права выдаются на таблицу, а НЕ на синоним.
Так что же там с запросом? Ах да, нужно ограничить выборку текущей сессией:
Как это у Вас, не получается? Ни SQL_ID ни PREV_SQL_ID – не содержат найденного ранее идентификатора 753c9f808k8hh? Естественно! SQL_ID содержит идентификатор только что выполненного запроса, а PREV_SQL_ID скорее всего хранит идентификатор запроса:
Я надеюсь, что читатель последовательно выполнял запросы, как я их приводил и поэтому сразу не нашел того что ожидалось. Для того что бы убедиться, что результат будет как и указано, нужно выполнить последовательно анонимный блок и запрос к представлению. Как бы то ни было, считаю, что еще один этап изысканий пройден. Теперь мы имеем исходный текст анонимного блока, и знаем, что он был выполнен именно нами.
К сожалению, решение, которое автоматизирует связывание, мне не нравится, ведь необходимо после определенного момента, запомнить все возможные анонимные блоки выполняющиеся пользователем, а представления хранящего историю сессии пользователя не существует? Или существует? Впрочем я не нашел и на данный момент, предлагаю следующий подход. Создадим таблицу, которая будет хранить идентификатор сессии, которая заинтересована в прослушивании и джоб, опрашивающий эту таблицу и сохраняющий историю сессии.
«Что за названия полей второй таблицы?» — спросил бы я. Несмотря на то, что это не имеет веского оправдания, но пытавшись минимизировать нагрузку создаваемую джобом, я добрался до представления более высокого уровня sys.x_$ksuse которое содержит достаточную информацию о целевой сессии. Делая закладку на будущее, в таблицу будут сохраняться еще несколько полезных полей, помимо необходимых: KSUSENUM (SID) и KSUSESQI (SQL_ID). Хорошо будет вынести тело джоба во внешнюю процедуру, и не добавлять ее в пакет, дабы избежать ошибок, если пакет будет не валиден:
Идея обработки, заключается в том, что бы записывать в историю сессии только тогда, и только то что выполняется пользователем в режиме обновления. Теперь можно создать джоб, прослушать сессию пользователя и проверить результат:
Как видно из результата запроса к V$SQL анонимный блок попал в таблицу лога записанный туда джобом. Для теста, я обращался к столбцу KSUSEPSI лога (предыдущему запросу) ввиду того, что мне приходилось выполнять команды очистки таблицы сессии в момент прослушивания. В дальнейшем, это так же окажется некоторым недостатком, но «обрывание» прослушивания мы исключим из результирующего набора выполняемого на удаленном сервере.
Теперь необходимо собрать DLL команды, которые так же могут выполняться при обновлении. Но здесь происходит противоречие, зачем собирать DDL – если их соберет джоб? К сожалению, он их не соберет, так как DDL не является запросом, а следовательно в v$session не отразится. Для этих целей Oracle предоставляет триггеры уровня СУБД, которыми можно воспользоваться. Выполняемые DDL, запишем в новую таблицу, а по аналогии с джобом, создадим процедуру и триггер выполняющей её:
Дополнительная таблица, и её тип (GLOBAL TEMPORARY хранить данные до дисконнекта), выбраны из следующих соображений: джоб собирающий информацию о сессии, работает в сессии отличной от той, которая выполняет скрипты обновления, следовательно запросы записанные в нее стали бы недоступны сессии исполнителя; предоставить Oracle очистку таблицы после обновления; DDL триггер, срабатывает в той же сессии, в которой выполняется DDL, следовательно в этом случае записывать можно сразу в таблицу буфера; сохранение данных таблицы после коммита обусловлено тем, что DDL выполняет молчаливый коммит.
Важно обратить внимание на то, что процедура объявлена с директивой AUTHID DEFINER, которая позволит записывать действия с правами пользователя UPD, которые могут быть большими, чем у вызывающего. Далее производится определение длинны DDL и сохранение буферов в поле CLOB.
Триггер выполняется после (AFTER) DDL, что подразумевает успешное выполнение команды, до записи в буфер.
Подводя итоги изысканий, теперь имеются все возможные типы операций, подлежащие выполнению на обновляемой базе и можно приступить к завершающему этапу – инструменту выполнения обновлений.
Реализация
Мне не нравятся публикации, которые после длительного рассуждения и подготовки заканчиваются чем то вроде: «А теперь, (если не дурак) тебе должно быть ясно как доделать оставшуюся фигню». Конечно же тут дураков – нет, все давно поняли что нужно сделать дальше. Но я приведу свою текущую реализацию, несмотря на то, что её можно считать бета версией. Теперь много кода, а затем пояснения:
К ранее созданным таблицам, добавились еще две, одна из которых используется для визирования успешно выполненных обновлений, а вторая для настройки соединения с удаленной базой Oracle.
Пакет объявлен с директивой AUTHID CURRENT_USER – что приведет к выполнению процедур пакета, с правами пользователя вызывающего пакет. Теперь, о всех процедурах пакета:
procedure SetSession(u_sid number, u_remove boolean default false) – используя автономную транзакцию, записывает текущий идентификатор сессии в таблицу инициирующую прослушивание.
function JobNumber return number – получает идентификатор джоба прослушивателя.
procedure JobRun – проверяет существование джоба.
procedure SetChannel(u_alias varchar2) – получает настройки удаленного соединения и записывает их в локальные переменные пакета.
procedure CancelUpdate – стирает настройки и очищает временные таблицы.
procedure BeginUpdateChannel(u_alias varchar2) – объединяет вызовы подготовительных процедур и начинает прослушивание.
procedure PrepareUpdateChannel – завершает прослушивание и дописывает в таблицу буфер собранные джобом запросы сессии. Я для собственных нужд, не слишком стараясь, отбрасываю при этом DML, select и встреченные в процессе тестирования служебные команды, а так же вызов процедуры PrepareUpdateChannel который тоже записывается в лог сессии.
procedure DropObject – вспомогательная процедура для очистки.
procedure ExecRemote – выполнение блока на удаленном сервере. Эта процедура реализует один из ключевых моментов механизма. Тут пакет dbms_sql вызывается на удаленном сервере.
procedure EndUpdateChannel – применение обновления. И об этом отдельно.
Оговорюсь, что первый вариант реализации, был несколько проще, того который приведен здесь. Дело в том, что динамический sql не предоставляет возможности выполнять блоки длинной более varchar2 (32767 символов или байт, в зависимости от объявления). Хотя это не совсем так. Локально, dbms_sql позволяет это, но LOB поле невозможно передать на удаленный сервер. Большое спасибо Тому Кайту (https://asktom.oracle.com/pls/apex/f?p=100:11:0. P11_QUESTION_ID:950029833940), который знает, как пробрасывать LOB между удаленными серверами. Я был приятно удивлен, тем что первый способ, который он приводит, у меня был реализован, через dbms_lob.substr, которым я в цикле обрезал поле CLOB из таблицы UPD$BUF. Второй способ, который он предлагает, для этой задачи выглядит как: создать на текущем хосте таблицу, с правами вызывающего обновление пользователя и выполнить удаленное создание таблицы по обратному соединению с текущей базой данных. Здесь можно указать на несколько недочетов, в приведенной реализации, а именно: допущение возможной ошибки, если пользователь вызывающий обновление, не равен авторизовавшемуся по dblink, ведь он не будет иметь прав на select из временной таблицы; создание и удаление таблиц динамически. Еще одной проблемой, с которой я уже сталкивался, при «перебрасывании» CLOB между серверами — была ошибка «ORA-02046: distributed transaction already begun». По всей видимости, во время тестирования, возникла подвисшая сессия, или идентификатор удаленного соединения остался открытым. Я повторно не смог смоделировать эту ситуацию, но во избежание повторений, нужно подумать о размещении вызова: dbms_session.close_database_link(pkg_dblink);
Для выполнения кода из скопированной таблицы, я пробовал генерировать анонимный блок, содержащий по сути тот же самый код, но это приводило к ошибке выполнения на рекурсивном уровне (номер ошибки я не сохранил, что то вроде Error on SQL level 2), но создание процедуры позволило решить и эту последнюю проблему.
Для конечного пользователя можно создать процедуры обертки, с директивой AUTHID DEFINER и раздать права вызывать их нужным пользователям:
Dblink что это
dblink — выполняет запрос в удалённой базе данных
Синтаксис
Описание
dblink выполняет запрос (обычно SELECT , но это может быть и любой другой оператор SQL, возвращающий строки) в удалённой базе данных.
Когда этой функции передаются два аргумента типа text , первый сначала рассматривается как имя постоянного подключения; если такое подключение находится, команда выполняется для него. Если не находится, первый аргумент воспринимается как строка подключения, как для функции dblink_connect , и заданное подключение устанавливается только на время выполнения этой команды.
Аргументы
Имя используемого подключения; опустите этот параметр, чтобы использовать безымянное подключение. connstr
Строка подключения, описанная ранее для dblink_connect sql
SQL-запрос, который вы хотите выполнить в удалённой базе данных, например select * from foo . fail_on_error
Если равен true (это значение по умолчанию), в случае ошибки, выданной на удалённой стороне соединения, ошибка также выдаётся локально. Если равен false, удалённая ошибка выдаётся локально как ЗАМЕЧАНИЕ, и функция не возвращает строки.
Возвращаемое значение
Эта функция возвращает строки, выдаваемые в результате запроса. Так как dblink может выполнять произвольные запросы, она объявлена как возвращающая тип record , а не некоторый определённый набор столбцов. Это означает, что вы должны указать ожидаемый набор столбцов в вызывающем запросе — в противном случае Postgres Pro не будет знать, чего ожидать. Например:
В части « псевдонима » предложения FROM должны указываться имена столбцов и типы, которые будет возвращать функция. (Указание имён столбцов в псевдониме таблицы предусмотрено стандартом SQL, но определение типов столбцов является расширением Postgres Pro .) Это позволяет системе понять, во что должно разворачиваться обозначение * , и на что ссылается proname в предложении WHERE , прежде чем пытаться выполнять эту функцию. Во время выполнения произойдёт ошибка, если действительный результат запроса из удалённой базы данных не будет содержать столько столбцов, сколько указано в предложении FROM . Однако имена столбцов могут не совпадать, так же, как dblink не настаивает на точном совпадении типов. Функция завершится успешно, если возвращаемые строки данных будут допустимыми для ввода в тип столбца, объявленный в предложении FROM .
Замечания
Использовать dblink с предопределёнными запросами будет удобнее, если создать представление. Это позволит скрыть в его определении информацию о типах столбцов и не выписывать её в каждом запросе. Например:
Database Links
The central concept in distributed database systems is a database link . A database link is a connection between two physical database servers that allows a client to access them as one logical database.
This section contains the following topics:
What Are Database Links?
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.
A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.
A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.
Figure 31-3 shows an example of user scott accessing the emp table on the remote database with the global name hq.acme.com :
Figure 31-3 Database Link
Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access.
One principal difference among database links is the way that connections to a remote database occur. Users access a remote database through the following types of links:
| Type of Link | Description |
|---|---|
| Connected user link | Users connect as themselves, which means that they must have an account on the remote database with the same user name and password as their account on the local database. |
| Fixed user link | Users connect using the user name and password referenced in the link. For example, if Jane uses a fixed user link that connects to the hq database with the user name and password scott/ password, then she connects as scott , Jane has all the privileges in hq granted to scott directly, and all the default roles that scott has been granted in the hq database. |
| Current user link | A user connects as a global user. A local user can connect as a global user in the context of a stored procedure, without storing the global user’s password in a link definition. For example, Jane can access a procedure that Scott wrote, accessing Scott’s account and Scott’s schema on the hq database. Current user links are an aspect of Oracle Advanced Security. |
Create database links using the CREATE DATABASE LINK statement. After a link is created, you can use it to specify schema objects in SQL statements.
Oracle Database SQL Language Reference for syntax of the CREATE DATABASE statement
What Are Shared Database Links?
A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.
When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode. The following table illustrates the possibilities:
| Local Database Mode | Remote Database Mode |
|---|---|
| Dedicated | Dedicated |
| Dedicated | Shared server |
| Shared server | Dedicated |
| Shared server | Shared server |
A shared database link can exist in any of these four configurations. Shared links differ from standard database links in the following ways:
Different users accessing the same schema object through a database link can share a network connection.
When a user needs to establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server. The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions.
When you use a shared database link in a shared server configuration, a network connection is established directly out of the shared server process in the local server. For a non-shared database link on a local shared server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher.
Why Use Database Links?
The great advantage of database links is that they allow users to access another user’s objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.
For example, assume that employees submit expense reports to Accounts Payable (A/P), and further suppose that a user using an A/P application needs to retrieve information about employees from the hq database. The A/P users should be able to connect to the hq database and execute a stored procedure in the remote hq database that retrieves the desired information. The A/P users should not need to be hq database users to do their jobs; they should only be able to access hq information in a controlled way as limited by the procedure.
«Users of Database Links» for an explanation of database link users
«Viewing Information About Database Links» for an explanation of how to hide passwords from non-administrative users
Global Database Names in Database Links
To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name. The database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter.
For example, Figure 31-4 illustrates a representative hierarchical arrangement of databases throughout a network.
Figure 31-4 Hierarchical Arrangement of Networked Databases
The name of a database is formed by starting at the leaf of the tree and following a path to the root. For example, the mfg database is in division3 of the acme_tools branch of the com domain. The global database name for mfg is created by concatenating the nodes in the tree as follows:
While several databases can share an individual name, each database must have a unique global database name. For example, the network domains us.americas.acme_auto.com and uk.europe.acme_auto.com each contain a sales database. The global database naming system distinguishes the sales database in the americas division from the sales database in the europe division as follows:
«Managing Global Names in a Distributed System» to learn how to specify and change global database names
Names for Database Links
Typically, a database link has the same name as the global database name of the remote database that it references. For example, if the global database name of a database is sales.us.example.com , then the database link is also called sales.us.example.com .
When you set the initialization parameter GLOBAL_NAMES to TRUE , the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for hq is hq.acme.com , and GLOBAL_NAMES is TRUE , then the link name must be called hq.acme.com . Note that the database checks the domain part of the global database name as stored in the data dictionary, not the DB_DOMAIN setting in the initialization parameter file (see «Changing the Domain in a Global Database Name»).
If you set the initialization parameter GLOBAL_NAMES to FALSE , then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to hq.acme.com as foo .
Oracle recommends that you use global naming because many useful features, including Replication, require global naming.
After you have enabled global naming, database links are essentially transparent to users of a distributed database because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database to remote database sales :
Oracle Database Reference for more information about specifying the initialization parameter GLOBAL_NAMES
Types of Database Links
Oracle Database lets you create private , public , and global database links. These basic link types differ according to which users are allowed access to the remote database:
Note : In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.
Determining the type of database links to employ in a distributed database depends on the specific requirements of the applications using the system. Consider these features when making your choice:
| Type of Link | Features |
|---|---|
| Private database link | This link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the link to access the remote database. |
| Public database link | When many users require an access path to a remote Oracle Database, you can create a single public database link for all users in a database. |
| Global database link | When an Oracle network uses a directory server, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple. |
«Specifying Link Types» to learn how to create different types of database links
«Viewing Information About Database Links» to learn how to access information about links
Users of Database Links
When creating the link, you determine which user should connect to the remote database to access the data. The following table explains the differences among the categories of users involved in database links:
| User Type | Description | Sample Link Creation Syntax |
|---|---|---|
| Connected user | A local user accessing a database link in which no fixed username and password have been specified. If SYSTEM accesses a public link in a query, then the connected user is SYSTEM , and the database connects to the SYSTEM schema in the remote database. |
Note: A connected user does not have to be the user who created the link, but is any user who is accessing the link.
«Specifying Link Users» to learn how to specify users when creating links
Connected User Database Links
Connected user links have no connect string associated with them. The advantage of a connected user link is that a user referencing the link connects to the remote database as the same user, and credentials don’t have to be stored in the link definition in the data dictionary.
Connected user links have some disadvantages. Because these links require users to have accounts and privileges on the remote databases to which they are attempting to connect, they require more privilege administration for administrators. Also, giving users more privileges than they need violates the fundamental security concept of least privilege: users should only be given the privileges they need to perform their jobs.
The ability to use a connected user database link depends on several factors, chief among them whether the user is authenticated by the database using a password, or externally authenticated by the operating system or a network authentication service. If the user is externally authenticated, then the ability to use a connected user link also depends on whether the remote database accepts remote authentication of users, which is set by the REMOTE_OS_AUTHENT initialization parameter.
The REMOTE_OS_AUTHENT parameter operates as follows:
| REMOTE_OS_AUTHENT Value | Consequences |
|---|---|
| TRUE for the remote database | An externally-authenticated user can connect to the remote database using a connected user database link. |
| FALSE for the remote database | An externally-authenticated user cannot connect to the remote database using a connected user database link unless a secure protocol or a network authentication service supported by the Oracle Advanced Security option is used. |
The REMOTE_OS_AUTHENT initialization parameter is deprecated. It is retained for backward compatibility only.
Fixed User Database Links
A benefit of a fixed user link is that it connects a user in a primary database to a remote database with the security context of the user specified in the connect string. For example, local user joe can create a public database link in joe ‘s schema that specifies the fixed user scott with password password . If jane uses the fixed user link in a query, then jane is the user on the local database, but she connects to the remote database as scott/ password .
Fixed user links have a user name and password associated with the connect string. The user name and password are stored with other link information in data dictionary tables.
Current User Database Links
Current user database links make use of a global user. A global user must be authenticated by an X.509 certificate or a password, and be a user on both databases involved in the link.
The user invoking the CURRENT_USER link does not have to be a global user. For example, if jane is authenticated (not as a global user) by password to the Accounts Payable database, she can access a stored procedure to retrieve data from the hq database. The procedure uses a current user database link, which connects her to hq as global user scott. User scott is a global user and authenticated through a certificate over SSL, but jane is not.
Note that current user database links have these consequences:
If the current user database link is not accessed from within a stored object, then the current user is the same as the connected user accessing the link. For example, if scott issues a SELECT statement through a current user link, then the current user is scott .
When executing a stored object such as a procedure, view, or trigger that accesses a database link, the current user is the user that owns the stored object, and not the user that calls the object. For example, if jane calls procedure scott.p (created by scott ), and a current user link appears within the called procedure, then scott is the current user of the link.
If the stored object is an invoker-rights function, procedure, or package, then the invoker’s authorization ID is used to connect as a remote user. For example, if user jane calls procedure scott.p (an invoker-rights procedure created by scott ), and the link appears inside procedure scott.p , then jane is the current user.
You cannot connect to a database as an enterprise user and then use a current user link in a stored procedure that exists in a shared, global schema. For example, if user jane accesses a stored procedure in the shared schema guest on database hq , she cannot use a current user link in this schema to log on to a remote database.
«Distributed Database Security» for more information about security issues relating to database links
Oracle Database PL/SQL Language Reference for more information about invoker-rights functions, procedures, or packages.
Creation of Database Links: Examples
Create database links using the CREATE DATABASE LINK statement. The table gives examples of SQL statements that create database links in a local database to the remote sales.us.americas.acme_auto.com database:
| SQL Statement | Connects To Database | Connects As | Link Type |
|---|---|---|---|
| CREATE DATABASE LINK sales.us.americas.acme_auto.com USING ‘sales_us’; | sales using net service name sales_us | Connected user | Private connected user |
| CREATE DATABASE LINK foo CONNECT TO CURRENT_USER USING ‘am_sls’; | sales using service name am_sls | Current global user | Private current user |
| CREATE DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY password USING ‘sales_us’; | sales using net service name sales_us | scott using password password | Private fixed user |
| CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY password USING ‘rev’; | sales using net service name rev | scott using password password | Public fixed user |
| CREATE SHARED PUBLIC DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY password AUTHENTICATED BY anupam IDENTIFIED BY password1 USING ‘sales’; | sales using net service name sales | scott using password password , authenticated as anupam using password password1 | Shared public fixed user |
«Creating Database Links» to learn how to create link
Oracle Database SQL Language Reference for information about the CREATE DATABASE LINK statement syntax
Schema Objects and Database Links
After you have created a database link, you can execute SQL statements that access objects on the remote database. For example, to access remote object emp using database link foo , you can issue:
You must also be authorized in the remote database to access specific remote objects.
Constructing properly formed object names using database links is an essential aspect of data manipulation in distributed systems.
Naming of Schema Objects Using Database Links
Oracle Database uses the global database name to name the schema objects globally using the following scheme:
schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.
schema_object is a logical data structure like a table, index, view, synonym, procedure, package, or a database link.
global_database_name is the name that uniquely identifies a remote database. This name must be the same as the concatenation of the remote database initialization parameters DB_NAME and DB_DOMAIN , unless the parameter GLOBAL_NAMES is set to FALSE , in which case any name is acceptable.
For example, using a database link to database sales.division3.acme.com , a user or application can reference remote data as follows:
If GLOBAL_NAMES is set to FALSE , then you can use any name for the link to sales.division3.acme.com . For example, you can call the link foo . Then, you can access the remote database as follows:
Authorization for Accessing Remote Schema Objects
To access a remote schema object, you must be granted access to the remote object in the remote database. Further, to perform any updates, inserts, or deletes on the remote object, you must be granted the SELECT privilege on the object, along with the UPDATE , INSERT , or DELETE privilege. Unlike when accessing a local object, the SELECT privilege is necessary for accessing a remote object because the database has no remote describe capability. The database must do a SELECT * on the remote object in order to determine its structure.
Synonyms for Schema Objects
Oracle Database lets you create synonyms so that you can hide the database link name from the user. A synonym allows access to a table on a remote database using the same syntax that you would use to access a table on a local database. For example, assume you issue the following query against a table in a remote database:
You can create the synonym emp for emp@hq.acme.com so that you can issue the following query instead to access the same data:
«Using Synonyms to Create Location Transparency» to learn how to create synonyms for objects specified using database links
Schema Object Name Resolution
To resolve application references to schema objects (a process called name resolution ), the database forms object names hierarchically. For example, the database guarantees that each schema within a database has a unique name, and that within a schema each object has a unique name. As a result, a schema object name is always unique within the database. Furthermore, the database resolves application references to the local name of the object.
In a distributed database, a schema object such as a table is accessible to all applications in the system. The database extends the hierarchical naming model with global database names to effectively create global object names and resolve references to the schema objects in a distributed database system. For example, a query can reference a remote table by specifying its fully qualified name, including the database in which it resides.
For example, assume that you connect to the local database as user SYSTEM :
You then issue the following statements using database link hq.acme.com to access objects in the scott and jane schemas on remote database hq :
Database Link Restrictions
You cannot perform the following operations using database links:
Grant privileges on remote objects
Execute DESCRIBE operations on some remote objects. The following remote objects, however, do support DESCRIBE operations:
Analyze remote objects
Define or enforce referential integrity
Grant roles to users in a remote database
Obtain nondefault roles on a remote database. For example, if jane connects to the local database and executes a stored procedure that uses a fixed user link connecting as scott , jane receives scott ‘s default roles on the remote database. Jane cannot issue SET ROLE to obtain a nondefault role.
Execute hash query joins that use shared server connections
Use a current user link without authentication through SSL, password, or NT native authentication