How to Create DBLink in Oracle

1. To create a global DBLink, you must first confirm that you have the permission to create a dblink:

1. Select * from user_sys_privs where privilege like upper ('% Database link %');

If not, you must use the sysdba role to grant permissions to users:

1. Grant createPublicDatabase link to dbusername;

2. log on to PL/SQL using the following command:

1. -- Method 1: Database Server A is required to have database B ing in tnsnames. ora
2. -- Create database link database connection name connect to username identified by passwordUsing 'Instance name of the locally configured data';

The graphic configuration interface is as follows:

 

1. -- Method 2: directly configure
2. -- If you create a global dblink, you must use the mongom or sys user and addPublic.
3. Create/* Public */Database link dblink1
4. Connect to dbusername identified by dbpassword
5. 　　Using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = orcl )))';
7. -- Database parameter global_name =TrueThe database connection name must be the same as that of the remote database. You can use the following command to check the global name of the database:
8. -- Select * from global_name;

3. query data:

1. -- Data Query, deletion, and insertion are the same as operations on the local database, except that the table name must be written as "table name @ dblink server.
2. Select xxx FROM table name @ Database Link name;

4. Delete DBLink

1. Drop/* Public */Database link dblink1;

5. Create and delete Synonyms

1. Create or replace synonym nameForTable Name;
2. Create or replace synonym nameForUser. Table Name;
3. Create or replace synonym nameForTable Name @ Database Link name;
4. Drop synonym name;

6. Create and delete views

1. Create or replace view nameAs(Select field from user. Table Name @ dblink1 );
2. Drop view name;

7. Note:

It is very easy to create a DBLink, but there is a lock in the background in use. You can view the lock in the console or query the database. Each time you use dblink for query, a connection will be created with a remote database. dblink will not automatically release this connection. If you use a large number of dblink queries, the number of connections of web projects will be insufficient, as a result, the system cannot run normally and the system does not run properly.