Remote access via DBLINK to an Oracle database
The easiest way to access tables and views from another Oracle database from one Oracle database is by using a DBLink (link to the database). However, in many cases, the use of DBLinks may not be recommended, since access to remote tables and views can cause significant performance problems in both databases, both remote and local. In most cases these performance problems are due to the inability to use the indexes on the remote tables.
DBLinks are created in the local database using the PL / SQL or SQL CREATE DATABASE LINK command. Obviously, the user who executes said command must have the necessary permissions to be able to do it. The syntax of the mentioned SQL command is as follows:
CREATE [PUBLIC] DATABASE LINK dblink_name
CONNECT TO Username INDENTIFIED BY Password
USING 'String_connection';
Where:
Dblink_name is the name of the DBLink.
Username and Password are the identifiers that the DBLink will use to connect to the remote database. Therefore, access to the tables and views of the remote database will be done with the permissions that said user has over them in said database. If these values ​​are omitted, then access to the remote database will be done with the same username and password with which the user in question is connected to the local database.
Connection_string identifies the remote database. It can be the name of the instance if it is defined in the tnsnames.ora file of the source database, or also, for version 8 of the Oracle database or later, we can directly use the TNS definition (this enables that we don’t have to modify the tnsnames.ora file).
On the other hand, if we define the DBLink as PUBLIC, all users will have access to it.
Here are some examples of the CREATE DATABASE LINK command:
CREATE DATABASE LINK dbl_bd001
CONNECT TO repadmin IDENTIFIED BY reppass1
USING 'bd001';
CREATE PUBLIC DATABASE LINK dbl_bd001
CONNECT TO repadmin IDENTIFIED BY reppass1
USING '(description =
(address =
(protocol = tcp)
(host = db.miempresa.com)
(Port = 1521))
(connect_data =
(sid = bd001))
) ';
Once the DBLink has been created, to name a table or view of the remote database, the name of the table or view must be indicated, concatenated with the “@” character and the name given to the DBLink. Tables and remote views can be used in queries (SELECT statements) and, if the Oracle database uses the distributed database system, then SQL or PLSQL statements of type DELETE, INSERT, UPDATE or LOCK TABLE can also be executed. In addition, synonyms can be created to shorten the way to reference remote tables and views. Here are some examples of sentences:
SELECT * FROM Remote_table_name @dblink_Name;
INSERT INTO Remote_Table_Name @Blink_Name (field1)
VALUES ('ORAGEEK');
UPDATE Remote_TableName @dblink_Name;
SET field1 = 'updated'
WHERE field1 = 'test';
CREATE SYNONYM my_res
FOR Remote_table_name @dblink_name;
SELECT * FROM my_res;
In Short:
There is another concept that I have not explained here and it is that of shared DBLinks (CREATE SHARED DATABASE LINK command), the one that needs more information on this concept can look in the Oracle manual on distributed database systems (Oracle Distributed Database Systems). On the other hand, it is also possible to access through DBLink remote databases other than Oracle. To create such a DBLink, and before anyone asks me, you can do an Internet search for the words: “Oracle Heterogeneous Services”.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.