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

This article mainly introduces "DBLINK Application Analysis". In daily operation, I believe many people have doubts about DBLINK application analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "DBLINK Application Analysis". Next, please follow the editor to study!

I. Project description

Purpose: cross-database query in oracle

Two database servers db_A (local) and db_B (remote 192.168.1.100). The user user_a under db_A needs to access the data of user_b under db_B.

Solution: query learned to use dblink (that is, database link database chain)

Second, the process of realization

1. Determine whether user user_a has the permission to create dblink.

Log in to the local database as sys:

Select * from user_sys_privs t where t.privilege like upper ('% link%')

SYS CREATE DATABASE LINK NO

SYS DROP PUBLIC DATABASE LINK NO

SYS CREATE PUBLIC DATABASE LINK NO

You can see that dblink has three permissions in the database

CREATE DATABASE LINK (the dblink created can only be used by the creator, but not by other users)

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

DROP PUBLIC DATABASE LINK .

2. If not, use the sys user to log in to the local database to authorize the user user_a

Note: dblink has three permissions:

CREATE DATABASE LINK (the dblink created can only be used by the creator, but not by other users)

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

DROP PUBLIC DATABASE LINK

3. Log in to the local database with the user user_a to create a dblink

Create public database link dblink1 connect to user_b identified by user_bpass using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.100) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)'

Note: when the database parameter global_name=false, the database link name is not required to be the same as the remote database name. The global name of the database can be found with the following command:

SELECT * FROM GLOBAL_NAME

To modify, you can modify the parameter value with the following sentence:

ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE

4. Complete creation and reference

Querying, deleting, and inserting data is the same as manipulating a local database, except that the table name needs to be written as "table name @ dblink server". Example:

Select * from user_bTablename@dblink1

In general, if you reference a database link, you can put it directly after the table or view name of the call, with an @ as the delimiter:

5. Delete DBLink

Drop public database link dblink1

6. Create and delete synonyms

For frequently used database links, you can create a local synonym for easy to use

Create or replace synonym synonym name for table name

Create or replace synonym synonym name for user. Table name

Create or replace synonym synonym name for table name @ database link name

Drop synonym synonym name

7. Create and delete views

Create or replace view view name as (select field from user. Table name @ dblink1)

Drop view View name

Other: creation method 2: requires the mapping of database db_B in tnsnames.ora on database server db_A (omitted)

Sql > create database link database link name connect to username identified by password using 'tnsnames.ora configured host string name'

Creation method 3: PL/SQL graphical configuration interface

Problem: the user has been given the right to dblink, but the error of insufficient permission to create the Times has been solved: I gave the user the right to CREATE PUBLIC DATABASE LINK, so I need create public database link.... to create it. Not create database link..... To be further explored:

Every time you use a dblink query, a connection is created with the remote database. Dblink should not automatically release this connection. If you use a large number of dblink queries, the number of connections to the web project will be insufficient, resulting in the system not running properly and causing the system not to run properly.

At this point, the study of "DBLINK Application Analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.