Oracle Cross-Library query Dblink usage examples _oracle

This example describes the use of Oracle Cross Library query Dblink. Share to everyone for your reference, specific as follows:

1. Before you create a job

Before you create a dblink, first check to see if the user has appropriate permissions. For a specific user, after logging in using Sqlplus user/pwd, execute the following statement:

Copy Code code as follows:

SELECT * from User_sys_privs t where T.privilege like Upper ('%link% ');

Under the SYS user, the result is:

SYS CREATE DATABASE LINK NO
SYS DROP public DATABASE LINK NO
SYS CREATE public DATABASE LINK NO

You can see that there are three kinds of permissions in the database Dblink:

Create DATABASE link--created Dblink can only be used by the creator, and other users cannot use
Create public DATABASE link--public means that all users created by Dblink can use the
Drop public DATABASE link--deletes the specified dblink

If you want to change the permissions of a user, you need to modify it under the SYS user:

Copy Code code as follows:

Grant CREATE Public Database Link,drop public database LINK to Scott;

There are two ways to view the Dblink, respectively, as follows:

①.

Copy Code code as follows:

Select Owner,object_name from dba_objects where object_type= ' DATABASE LINK ';

Ii.

Copy Code code as follows:

SELECT * from Dba_db_links;

2. Create Dblink

Create public database link Link_name
connect to Usrname identified by "PASSWORD"
using " 
(DESCRIPTION = ( Address = (PROTOCOL = TCP) (HOST = XXX.XXX.XXX.XXX) (PORT = 1521))
(Connect_data = (SERVER = dedicated) (Service_Name = XX X))
 ) ';

Note: The use followed by a string, which must not appear unnecessary space, otherwise it will error ORA-12514, in the above code in order to facilitate reading of the line, there may be spaces and lead to errors, so when using the space to remove the OK.

Here Link_nam is the custom name; username and password are the username and password in the specified Oracle database, service_name If you are unsure, you can obtain the following statement:

Copy Code code as follows:

Show parameter service_names;

Or

Copy Code code as follows:

Select Name,value from V$parameter where name= ' Service_names '

Use of 3.dblink

The use of Dblink is relatively simple, the general access to the table in the form of the following format can be: [user.] Table@link_name.

Copy Code code as follows:

Select StudentID from Abc.studeng@abc_ten;

4. Delete Dblink

After you determine the name of the dblink you want to delete, you can delete it directly by using the drop command:

Copy Code code as follows:

Drop public database link abc_ten;

I hope this article will help you with your Oracle database program design.