Oracle Cross-Library query Dblink usage examples _oracle

Source: Internet
Author: User
Tags custom name oracle database

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:


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 ';

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 " 
(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;

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.