Use of Oracle Dblink

Oracle has its own Dblink function, which is to logically treat multiple Oracle databases as a single database, meaning that objects in another database can be manipulated in one database, for example, we create a new data database1, We need to manipulate the tables in the database database2, or we need to manipulate the tables in the database DATABASE3 on the remote machine, and we can use this powerful feature of Dblink!

1, if we want to create a global dblink, which means that whatever role can be used, then we need to determine whether the user has Dblink permissions, if not, you need to use the SYSDBA role to authorize the user:

To see if a user has Dblink permissions:

Select *  from where  like Upper ('%database link%');

No, the SYSDBA authorization is used:

Grant Create  Public Database  to Dbusername;

2. Create a Dblink using a statement:

Create Database  to by'(DESCRIPTION = (Address_list = (ADDRESS = (      PROTOCOL = TCP) (host = connection database host IP address) (port = port number))    )    (Connect_data =      (service_name = Connection database service name)))     ';

If you create a global dblink, you must use the SYSTM or SYS user to add public before database:

Create  Public database....

For example, create Test_dblink in TESTDB2 to manipulate the TESTDB1 database in the host 192.168.1.254:

Create Database  to by'(DESCRIPTION = (Address_list = (ADDRESS = (      PROTOCOL = TCP) (HOST = 192.168.1.254) (PORT = 1521))    )    (Connect_data =      (service_name = testdb1)))    
    ';

3. Using Dblink:

Query the data in another database, other modifications, deletions are the same use, is another database table name @ This database creates the Dblink name:

Select  name @dblink from table name;

For example, we use Dblink in TESTDB2 to view data from the Tb_user table in TESTDB1:

Select *  from Tb_user@tset_dblink;

To view the Dblink created in the data:

Select owner,object_namefromwhere object_type='  DATABASE LINK'; Select *  from Dba_db_links;

Delete the corresponding Dblink:

Drop database link Dblink name;

To close the Dblink connection:

Alter Close Database ' Dblink_name '

To create and delete a view:

Create or Replace View  as (Select from user. Table name @dblink1);   Drop  view name;

After the list is created的Database links中


Use of Oracle Dblink