How Oracle implements cross-Library queries

Implementation results: Write a stored procedure under a user in a database, use Dblink to connect to another database in a stored procedure, take a number from a user in the database, and then insert a table in the current database.

Two. Implementation method steps:

1. Create a stored procedure

2. Create the database link in the stored procedure first

3. Creation success

4. Extracting data from another database into the current database

5. Task completion

Three. How to create Dblink:

1. 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)

)

)‘;

Syntax Explanation: Create public database link Dblink name (pick yourself up)

Connect to User name identified by password

Using ' (DESCRIPTION =

(Address_list =

(address = (PROTOCOL = TCP) (host = IP address of the server on which the database is to be connected) (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))

)

)‘;

2. If you create a private dblink

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 the number from the connected database after successful connection:

1. Select * from [email protected];

Note: Simply add "@DBLINK name" after the table name.

Five. View all the Dblink methods under the current database:

1. Select * from Dba_db_links;

Six. Delete a specified Dblink method under the current database:

1. If you are creating a public dblink, you need to delete the

Drop public database link dblink;

2. If you are creating a private dblink, you need to delete the

drop database link dblink;

Description: Drop public database link Dblink name;

Seven. View the current database's global database name method:

1. Select * from Global_name;

Eight. See how the current user has permissions for Dblink:

1. Select DISTINCT PRIVILEGE as "Database Link privileges"

From Role_sys_privs

WHERE PRIVILEGE in (' Create SESSION ', ' Create DATABASE LINK ',

' CREATE public DATABASE LINK ');

Eight. References:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_admin.htm#i1008271

How Oracle implements cross-Library queries