Create database link oracle

Связь базы данных Oracle (database link) — это одностороннее соединение локальной базы данных с удаленной базой данных. Связь всегда односторонняя. Пользователи удаленной базы не могут применять ее для подключения к локальной базе — вместо этого они должны создать отдельную связь базы данных.

Database Link (cвязь) базы данных позволяет получать доступ к разным базам данных через учетную запись пользователя удаленной базы; вы сами не обязаны быть зарегистрированным пользователем удаленной базы данных. Привилегии в этой базе данных будут идентичны привилегиям пользовательской учетной записи, которая применялась для создания связи. Связи баз данных удобны, когда необходимо запросить таблицу в распределенной базе данных, или даже вставить данные из таблицы другой базы в собственную локальную таблицу. Связи баз данных позволяют пользователям обращаться к множеству баз данных как к единой логической базе данных.

Create database link — создание связей на примерах

Создавать можно приватные и общедоступные связи баз данных. Далее мы рассмотрим соответствующие примеры.

Создание приватной связи базы данных

Приватная связь базы данных принадлежит пользователю, который ее создал. В следующем операторе пользователь SYSTEM создает приватную связь базы данных. Связь базы данных позволяет устанавливать соединение с удаленной базой, используя имя и пароль пользователя hr этой удаленной базы.

После создания связи пользователь SYSTEM может выполнять запросы к таблице hr.employees в удаленной базе данных.

На заметку! Для создания связи базы данных пользователь должен иметь привилегию CREATE PRIVATE DATABASE LINK или CREATE PUBLIC DATABASE LINK в локальной базе данных

Обратите внимание, что в предыдущем операторе имя связи удаленной базы данных — MONITOR — совпадает с TNS-псевдонимом удаленной базы (псевдонимом Oracle Net Service), но вообще может быть любым, по вашему усмотрению. Конструкция CONNECT TO. IDENTIFIED BY означает, что пользователь этой связи базы данных применяет указанное имя и пароль для входа на удаленную базу данных. Конструкция USING ‘monitor’ просто специфицирует TNS-псевдоним удаленной базы данных.

Поскольку это приватная связь базы данных, только пользователь SYSTEM может использовать ее. Когда пользователь hr попытается воспользоваться этой связью удаленной базы данных, произойдет следующее:

Создание общедоступной связи базы данных

Общедоступная связь базы данных позволяет любому пользователю или любому модулю программы PL/SQL обращаться к объектам удаленной базы данных. Оператор создания такой связи очень похож на оператор создания приватной связи. К оператору CREATE DATABASE LINK просто добавляется ключевое слово PUBLIC :

Совет. Если нескольким пользователям требуется доступ к удаленной базе данных Oracle из локальной, можно создать общедоступную связь. В противном случае создавайте приватную связь базы данных, которая позволяет обращаться к объектам удаленной базы данных только владельцу.

Как только общедоступная связь MONITOR создана, любой пользователь может с ее помощью входить в удаленную базу. В следующем примере пользователь tester применяет общедоступную связь для выполнения запросов к удаленной базе MONITOR .

На заметку! Пользователь tester может обращаться к удаленной базе, даже не будучи ее зарегистрированным пользователем, потому что применяет общедоступную связь, что избавляет его от необходимости указывать комбинацию имени и пароля пользователя hr для доступа к объектам удаленной базы. Разумеется, с точки зрения безопасности общедоступная связь базы данных — не слишком хорошая идея, особенно с точки зрения аудиторов базы данных.

Использование Database Control для создания связей баз данных

С помощью OEM Database Control очень легко создать связь базы данных. Для этого на домашней странице Database Control нужно щелкнуть на вкладке Administration (Администрирование), а затем на ссылке Database Links (Связи базы данных) в группе Schema (Схема). На этой странице можно создать связь базы данных, ответив на несколько простых вопросов.

I am using Oracle 11g express edition. I have created tables, stored procedures and it works fine. I have my user "System" with password "xyz" (main user during installation).

Then i have created two databases "abc" and "pqr" with same user.

I wanted to create database link from abc to pqr.

I am getting error "Insufficient privileges". Please help me out.

1 Answer 1

It should be CONNECT TO username not the database name as shown in the following image which describes the syntax of CREATE DATABASE LINK . We define database instance/service under USING connect_string clause.

Prerequisites

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Summary: in this tutorial, you will learn about the Oracle database link and how to create a database link to a remote Oracle Database server.

What is an Oracle database link

A database link is a connection from the Oracle database to another remote database. The remote database can be an Oracle Database or any ODBC compliant database such as SQL Server or MySQL.

Why do you need a database link

A database link allows a user or program to access database objects such as tables and views from another database.

Once you create a database link, you can access the tables or views from the remote database using the following pattern:

For example, you can query data from a table in the remote database as if it was in the local server:

When accessing a remote table or view over the database link, the Oracle database is acting as an Oracle client.

Using a synonym to simplify the syntax for accessing objects via a database link

To simplify the syntax, you can create a synonym for the remote object accessed via the database link and use this object as if it was a local object.

This syntax shows how to create a synonym for a remote table:

And this query uses the synonym instead of the remote table name with the database link:

Oracle CREATE DATABASE LINK statement

There are two types of database links: public and private.

Private database links are visible to the owners while public database links are visible to all users in the database. For this reason, public database links may pose some potential security risks.

To create a private database link, you use the CREATE DATABASE LINK statement as follows:

• First, specify the name of the database link after the CREATE DATABASE LINK keywords.
• Second, provide user and password of the remote database after the CONNECT TO and IDENTIFIED BY keywords.
• Finally, specify the service name of the remote database. If you specify only the database name, Oracle will append the database domain to the connect string to form a complete service name.

Typically, you add an entry int the tnsnames.ora file and reference it as the remote_database in the USING clause.

The following statement shows how to create the private database link to a user in a remote database with a full connection string.

To create a public database link, just add the PUBLIC keyword:

Create a database link example

In this example, we will create a database link to a remote Oracle Database server located in the server 10.50.100.143 with the port 1521 and service name SALES .

First, add the following entry to tnsnames.ora file in the local Oracle Database server. Typically, the tnsnames.ora is located in the directory /NETWORK/ADMIN/ under ORACLE_HOME :

Next, use the CREATE DATABASE LINK statement to create a new private database link that connects to the SALES database via bob ‘s account:

Then, issue the SELECT statement to query data from the customers table on the SALES database:

Here is the output:

After that, insert a new row into the customers table:

Finally, query data from the customers table again:

The result set is as follows:

Oracle Database Link best practices

Here are some best practices using the database links:

1. Naming convention: the name of the database links should reflect the nature of data, not the database server. For example, instead of naming a database link SALES_PRD , you name it as SALES .
2. Remote database users: you should create a user dedicated for a database link. In addition, you should not give this user to anyone else. If you don’t follow this, the database will not work when someone changes the password of the user or even delete it.
3. Use a service-specific entry in the tnsnames.ora instead of the database-specific alias so that you copy between product, test, and development environments, you don’t have to recreate the database link.

In this tutorial, you have learned about the Oracle database link concept and how to use the Oracle CREATE DATABASE LINK to create a new database link to a remote Oracle Database server.