Explain how Oracle uses dblink

DBLINK details

1. Create dblink Syntax:

CREATE [PUBLIC] database link link

Connect to username identified by password

USING 'string string'

Note:

1) permission: The account used to CREATE a database link must have the system permission of create database link or create public database link. The account used to log on to a remote DATABASE must have the create session permission. Both permissions are included in the CONNECT role (create public database link permission is included in DBA ). A public database link is available to all users in the database, while a private link is only available to users who create it. It is impossible for a user to authorize another user to have a private database link. 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, any name can be used.

3) connectstring: connection string. The connection string of the remote database is defined in tnsnames. ora.

4) username and password: the username and password of the remote database. If this parameter is not specified, use the current user name and password to log on to the remote database.

2. Statement for deleting a database link:

DROP [PUBLIC] database link zrhs_link

3. view the created dblink

Select owner, object_name from dba_objects where object_type = 'database link ';

4. Reference of dblink:

[User.] table | view @ dblink

For example:

SELECT * FROM worker @ zrhs_link;

SELECT * FROM camel. worker @ zrhs_link;

5. Create a synonym:

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 be treated like any other view in the local database or authorized to other users to access this view, but this user must have the permission to access the database link.

1 2 Next Page