Dblink remote database access

Dblink: When you want to access data in another database table across local databases, dblink of the remote database must be created in the local database, dblink allows you to access data in a remote database table just like a local database. The following describes how to create a dblink in a local database. There are generally two ways to create a dblink. However, before creating a dblink, you must have the permission to create a dblink. To learn about dblink permissions, log on to the local database as sys: select * from user_sys_privs t where t. privilege like upper ('% link % '); 1 sys create database link no 2 sys drop public database link no 3 sys create public database link no we can see that in the DATABASE, dblink has three permissions: create database link (the created dblink can only be the Creator) available, other users cannot use it), create public database link (public indicates that all users of the created dblink can use it), and drop public database link. Under the sys user, grant the create public database link and drop public database link permissions to your user www.2cto.com grant create public database link and drop public database link to scott; log on to the local database as scott. the first way to create dblink is to create tnsnames in the local database. the ora file is configured with the database to be remotely accessed. Create public database link to_bylw connect to scott identified by tiger using 'bylw'; where to_bylw is the name of the dblink you created, and bylw is the Instance name of the remote database, scott/tiger is the user/password used to log on to the remote database. Then, access scott in the remote database 'bylw' through dblink in the local database. for the tb_test table, the SQL statement is as follows: select * from scott. tb_test @ to_bylw; 2. the second method for creating dblink is to create tnsnames in the local database. no remote database is configured in the ora file. www.2cto.com create database link to_test connect to scott identified by tiger using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.5) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bylw) '; the second is to set the first The information configured in the tnsnames. ora file is directly placed after the dblink statement is created. The first case is tnsnames. the information in the ora file is as follows: bylw = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.5) (PORT = 1521 ))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bylw) 1. Create an oracle client to connect to sxyuniformance locally (Oracle and phd master servers, the oracle database used to connect to the remote machine. Create a table in the database of the remote machine for testing and create the RTDB_TAG table. Create table uniformance. RTDB_TAG (NAME VARCHAR2 (30) not null, DES VARCHAR2 (60), UNIT VARCHAR2 (15), HI VARCHAR2 (20), LO VARCHAR2 (10) Add a record in it: insert into uniformance. RTDB_TAG (NAME, DES, UNIT, HI, LO) values ('tag1', 'tagtest1', 'M', '123', '-100 '); www.2cto.com 2. log on to plsql on the oralce and phd servers, and create a remote database connection dblink create database link dblink connect to uniformance identified by uniformance using 'sxyun Iformance '; dblink: database link name uniformance: remote oracle database login password sxyuniformance: Local Service name established to remote end note: when you create a database link, the name is not obtained at will. If the value of the global_names parameter in the local database is true, the dblink name must be the same as the global_names parameter in the remote database. How can I view the GLOBAL_NAME of a database? Run SQL> SELECT * FROM GLOBAL_NAME; www.2cto.com. How can I check whether the Global_name parameter is true or False? Run SQL> show parameter global_name; how do I modify the value of Global_name? Execute SQL> alter system set global_names = false; 3. test whether the connection is successful select * from dual @ dblink. If a result is returned, the connection is successful. 4. query the created remote connection name in the local DATABASE: SQL> select owner, object_name from dba_objects where object_type = 'database link'; OWNER OBJECT_NAME www.2cto.com Public dblink 5. So far, create a DBLINK on the local oracle and phd servers to connect to the remote database and query the table information in the remote database in the local database: SQL> select * from RTDB_TAG @ dblink