Shulou(Shulou.com)05/31 Report--

This article mainly shows you "oracle how to achieve cross-database query", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "oracle how to achieve cross-database query" this article.

Execute the weighted dbuser account on the Oracle local database side

SQL > grant create database link to dbuser

1. Configure the tnsnames.ora file for the local database server

$vi $ORACLE_HOME/network/admin/tnsnames.ora

Add the following line, where DBLINK is the connection name (customizable), HOST and PORT are the IP and port for database listening, and SERVICE_NAME is the SID of the database

MEDIADBLINK = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = db)

two。 Log in to the local database and create a database link

Execute the following query statement, where MEDIADB is the database link name (customizable) and MEDIADBLINK is the connection name previously defined in tnsnames.ora

Dbuser is the user name and password is the password

-- Create database link create database link MEDIADB connect to dbuser identified by password using 'MEDIADBLINK'

Note: the correctness of the username and password will not be verified here

3. Use linked databases

3.1 querying, deleting, and inserting data is the same as manipulating a local database, except that the table name needs to be written as "table name @ database link name", as shown in

Select * from table_name@MEDIADB

3.2 you can also create a synonym for this table

Create synonym aaa for table_name@MEDIADB

The following statement has the same effect as in 3.1

Select * from aaa

The statement to delete synonyms is

Drop synonym aaa

The following are the supplements of other netizens:

Execute the weighted dbuser account on the Oracle local database side

SQL > grant create database link to dbuser

Configure the tnsnames.ora file for the local database server

$vi $ORACLE_HOME/network/admin/tnsnames.ora

Increase connection configurations that require remote connection to the server, such as:

ORCL_REMOTE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = oradb) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)

Log in to the local database and create a database link

Execute the following query statement, where ORCL_LINK is the database link name (customizable) and ORCL_REMOTE is the connection name previously defined in tnsnames.ora

Dbuser is the user name and password is the password

Create database link ORCL_LINK connect to dbuser identified by password using 'ORCL_REMOTE'

Query how to create a database link in 2:

1) execute the SQL statement.

Select * from user_db_links;-user DB Link

Select * from dba_db_links;-- dba DB Link

Select * from DB Link;-- current blink

2) in PL/SQL, click database links in the left browser to see the database link.

Use linked databases

Querying, deleting, and inserting data is the same as manipulating a local database, except that the table name needs to be written as "table name @ database link name", as shown in

Select * from table_name@ORCL_LINK

Other:

Delete database link (in this case, ORCL_LINK)

SQL > Drop database link ORCL_LINK

The above is all the contents of the article "how to implement cross-database query in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.