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

This article will explain in detail the method of realizing cross-database query by Oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Implementation result: write a stored procedure under a user in one database, use DBLINK to connect to another database in the stored procedure, fetch the number from a user in this database, and then insert it into a table in the current database.

two。 Implementation method steps:

1. Create a stored procedure

two。 Create a database link in a stored procedure first

3. Created successfully

4. Take data from another database and insert it into the current database

5. Mission accomplished

three。 How to create a DBLINK:

Create public database link dblink connect to totalplant identified by totalplant using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = LOCALHOST) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = prd.gdc)'

Grammatical explanation: create public database link DBLINK name (pick it at will)

Connect to username identified by password

Using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = the IP address of the server where the database to be connected is located) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = the local service name of the database to be connected (that is, the SID of the database to be connected))

two。 If you create a DBLINK for private

Create database link dblink connect to totalplant identified by totalplant using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = LOCALHOST) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = prd.gdc)'

four。 The method of fetching numbers from the connected database after a successful connection:

Select * from tbl_ost_notebook@dblink

Description: just add "@ DBLINK name" after the table name.

five。 How to view all DBLINK in the current database:

Select * from dba_db_links

six。 Method to delete a specified DBLINK under the current database:

1. If you are creating a DBLINK for public, you need to use the

Drop public database link dblink

two。 If you are creating a DBLINK for private, you need to use the

Drop database link dblink

Description: drop public database link DBLINK name

seven。 How to view the global database name of the current database:

1. Select * from global_name

eight。 How to see what permissions the current user has for DBLINK:

SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS WHERE PRIVILEGE IN ('CREATE SESSION','CREATE DATABASE LINK',' CREATE PUBLIC DATABASE LINK'); this is the end of this article on "Oracle implementation of cross-database query". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.