Shulou(Shulou.com)06/01 Report--

DBLINK creation

Dblink (Database Link) database link, as the name implies, is the database link. When we want to access the data in another database table across the local database, the dblink of the remote database must be created in the local database. Through the dblink local database, the data in the remote database table can be accessed as if we were accessing the local database.

1. Bdlink classification

The type Owner describes that the user where the Private creates the dblink owns the databaselink that the dblink establishes under a specific schema in the local database. Only the session of the schema that created the databaselink can use this databaselink to access the remote database. And only Owner can delete its own privatedatabaselink. The databaselink of PublicOwner is PUBLIC.Public is database-level, and all users or pl/sql programs with database access rights in the local database can use this databaselink to access the corresponding remote database. GlobalOwner is PUBLIC.Global, database link is network level, When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.

2. Dblink creation

Before creating, you need to know whether the user has the permission to create dblink. There are three related permissions for dblink. Dblink creation

CREATE DATABASE LINK (the DBLINK created can only be used by the creator and cannot be used by other users)

CREATE PUBLIC DATABASE LINK (PUBLIC means that the created DBLINK is available to all users)

DROP PUBLIC DATABASE LINK (delete public DBLINK)

First of all, you need to confirm whether the user has the permission to create the dblink, and if not, give the relevant permission to the user before you can create the dblink.

When creating, you need to have the TNS information of the database you need to access and the available users and passwords. The creation script is as follows:

-- create DBLINK:

Create database link dblink_name

Connect to user_name / * user name * / identified by password / * password * /

Using'(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.92.92.92) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)

'To

After the creation is completed, you can query whether the creation is complete in the DBA_DB_LINKS view. You can also use drop / * public * / database link dblink_name for drop deletion

3. Dblink query and create synonyms

After the creation, you can access the target database, such as querying the table_test table.

Select * from table_test@dblink_name

Generally, for ease of use, you can set synonyms for the tables that need to be accessed:

Create and delete synonyms:

Create or replace synonym table_test for table_test@dblink_name

After setting synonyms, you only need to query synonyms.

Synonyms can also be deleted and recreated using drop

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.