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

This article mainly introduces how to use DBLINK, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

DBLINK detailed explanation

1. Create a dblink syntax:

CREATE [PUBLIC] DATABASE LINK link

CONNECT TO username IDENTIFIED BY password

USING 'connectstring'

Description:

1) permissions: the account that creates the database link must have the system permission of CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK, and the account used to log in to the remote database must have CREATE SESSION permission. Both permissions are included in the CONNECT role (CREATE PUBLIC DATABASE LINK permissions are in DBA). A public database link is available to all users in the database, while a private link is available only to the user who created it. It is impossible for one user to authorize a private database link to another user. A database link is either public or private.

2) link: when GLOBAL_NAME=TRUE, the link name must be the same as the global database name global_name of the remote database; otherwise, you can name it anything you want.

3) connectstring: connection string, which defines the connection string of the remote database in tnsnames.ora.

4) username, password: user name and password of the remote database. If not specified, log in to the remote database using the current user name and password.

two。 Delete the database link statement:

DROP [PUBLIC] DATABASE LINK zrhs_link

3. View dblink that has been created

Select owner,object_name from dba_objects where object_type='DATABASE LINK'

Reference to 4.dblink:

[user.] table | view@dblink

Such as:

SELECT * FROM worker@zrhs_link

SELECT * FROM camel.worker@zrhs_link

5. Create synonyms:

For frequently used database links, you can create a local synonym for ease of use:

CREATE SYNONYM worker_syn FOR worker@zrhs_link

6. Create a remote view:

CREATE VIEW worker AS SELECT * FROM worker@zrhs_link where...

This view can now be treated like any other view in the local database, or other users can be granted access to this view, provided that the user has access to the database link.

Other:

How to modify GLOBAL_NAME:

1. Set global_names to false in the init.ora file of the remote database.

Or

two。 Execute the following statement with the sys user: ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE

Restart the database settings after modification to take effect.

The global name of the database can be found with the following command: SELECT * FROM GLOBAL_NAME

= dblink actual combat =

1.dblink is divided into two categories: public and private.

The public dblink uses the public modifier keyword. The public keyword is required for both create and drop.

A public dblink is open to everyone, and synonyms created on top of that dblink are also open to everyone. (test and confirm, but the test is conducted among multiple users of an instance)

Private dblink can only be accessed by the creator, and synonyms on it cannot be accessed by other users. You need to create a view for the user and authorize the view to the desired user before the user can access the view.

In addition, synonyms with dblink cannot be directly authorized to the user. Otherwise, an error report is equivalent to:

Grant select on table1@user1 to user2 *

ERROR at line 1:

ORA-02021: DDL operations are not allowed on a remote database

two。 When creating a dblink, you can use a connection string (as in tnsname.ora), which is more efficient.

Thank you for reading this article carefully. I hope the article "how to use DBLINK" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.