Database Link

DB Link, How and Why

What is DB Link?

A database link is a kind of schema object in a database that enables users to access and manipulate objects on a remote database. For better understanding the features of a database link which may play an important role in data migration, we should study what a database link can and cannot do in a distributed database system in order to develop our data migration strategy.

Generally speaking, you can perform Data Query Language (DQL) and Data Manipulation Language (DML) via a database link, but you cannot perform Data Definition Language (DDL) in this way. Which means, you cannot do CREATE TABLE, ALTER TABLE, CREATE INDEX or ALTER INDEX on the remote database through a database link.

Beside DDL, let’s see what Oracle says about the restrictions of using a database link.

General Restrictions on DB Link

There’re several restrictions apply to database links. 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: Tables, Views, Procedures, Functions
  • Analyze remote objects
  • Define or enforce referential integrity
  • Grant roles to users in a remote database
  • Obtain non-default roles on a remote database.
  • Use a current user link without authentication through SSL, password, or Microsoft Windows native authentication.

User-Defined Type over DB Link

Furthermore, some usage restrictions are specifically related to user-defined types over database links. You CANNOT:

  • Connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.
  • Use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer’s Guide.
  • Use database links within PL/SQL code to declare a local variable of a remote user-defined type.
  • Convey a user-defined type argument or return value in a PL/SQL remote procedure call.

Examples on Cans and Cannots of DB Link

First of all, let’s create a database link for our examples.

[oracle@ora12c ~]$ sqlplus /nolog
...
SQL> conn hr/hr
Connected.
SQL> create database link ora11g_hr connect to hr identified by hr using 'ORA11G';

Database link created.

In which, ORA11G is a connect identifier defined in tnsnames.ora.

What You Can Do …

1. DQL – SELECT FROM a Remote Table via a DB link

SQL> select first_name from employees@ora11g_hr where last_name = 'Chen';

FIRST_NAME
--------------------
Ed

2. DQL – Local Table Joins Remote Table via a DB link

SQL> select b.department_name from employees_copy a inner join departments@ora11g_hr b using (department_id) where first_name = 'Ed' and last_name = 'Chen';

DEPARTMENT_NAME
------------------------------
Finance

3. DQL – Remote Table Joins Remote Table via a DB link

SQL> select b.department_name from employees@ora11g_hr a inner join departments@ora11g_hr b using (department_id) where first_name = 'Ed' and last_name = 'Chen';

DEPARTMENT_NAME
------------------------------
Finance

4. DQL – DESCRIBE a Remote Object via a DB link

Here I described a remote object is a function.

SQL> desc show_department_name@ora11g_hr;
FUNCTION show_department_name@ora11g_hr RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 V_EMPLOYEE_ID                  NUMBER                  IN

Please note that, only 4 types of remote schema object can be described, they are Tables, Views, Procedures, Functions.

5. DML – UPDATE a Remote Table via a DB link

SQL> update employees set first_name = 'Eddie' where last_name = 'Chen';

1 row updated.

6. DCL – COMMIT Changes on a Remote Table via a DB link

SQL> commit;

Commit complete.

SQL> select first_name from employees@ora11g_hr where last_name = 'Chen';

FIRST_NAME
--------------------
Eddie

7. DDL – Create a Table Based on a Remote Table via a DB link

SQL> create table employees_copy as select * from employees@ora11g_hr;

Table created.

8. DDL – Create a View Based on a Remote Table via a DB link

SQL> create view employees_remote as select * from employees@ora11g_hr;

View created.

9. Programming Units – Execute a Remote Procedure via a DB link

SQL> exec do_something@ora11g_hr;

PL/SQL procedure successfully completed.

10. Programming Units – Execute a Remote Function via a DB link

Using a remote function is a little tricky, you should NOT put the input parameter before the database link.

SQL> select show_department_name(101)@ora11g_hr from dual;
select show_department_name(101)@ora11g_hr from dual
                                *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Instead, you should put the input parameter right after the database link.

SQL> select show_department_name@ora11g_hr(101) dep_name from dual;

DEP_NAME
--------------------------------------------------------------------------------
Executive

A more complicated case of using the remote function is as following:

SQL> select show_department_name@ora11g_hr(employee_id) dep_name from employees@ora11g_hr where first_name = 'Ed' and last_name = 'Chen';

DEP_NAME
--------------------------------------------------------------------------------
Finance

What You Can Not Do …

1. DDL – Create a Remote Table

Creating a remote table is not allowable, where ORA-02021 is thrown.

SQL> create table employees_copy@ora11g_hr as select * from employees@ora11g_hr;
create table employees_copy@ora11g_hr as select * from employees@ora11g_hr
                           *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

2. DDL – Alter a Remote Table

SQL> alter table employees@ora11g_hr move;
alter table employees@ora11g_hr move
                      *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

3. DDL – References a Remote Table

SQL> alter table employees_copy add constraint emp_dep_id_ref foreign key (department_id) references hr.departments@ora11g_hr (department_id);
alter table employees_copy add constraint emp_dep_id_ref foreign key (department_id) references hr.departments@ora11g_hr (department_id)
                                                                                                              *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

4. DDL – Create a Remote Index

SQL> create index emp_idx_01 on hr.employees@ora11g_hr (employee_id, department_id);
create index emp_idx_01 on hr.employees@ora11g_hr (employee_id, department_id)
                                        *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

5. DDL – Alter a Remote Index

SQL> alter index emp_emp_id_pk@ora11g_hr rebuild;
alter index emp_emp_id_pk@ora11g_hr rebuild
                         *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

Alternatives to Create DB links

In case that you have no right modify the local naming file tnsnames.ora, you can still use a full connect descriptor to define your own database link. SQL> create database link ora11g_hr connect to hr identified by hr using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ORCL)))';

Database link created.

Leave a Reply

Your email address will not be published. Required fields are marked *