Wednesday, October 28, 2020
dbametrix
More
    Home Sql Tutorial Remote access via DBLINK

    Remote access via DBLINK

    Blog post explains how to create DBLINK and usage of dblink to remote data selection with DML.

    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 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:

    - Advertisement -
    dbametrix

    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.

    - Advertisement -
    dbametrix

    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 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 be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in 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 be enjoy more advance topics from our partner resource.

    Consider reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments