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

This article will explain in detail how to carry out oracle create database link_ database link testing, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

-create db link syntax

CREATE [SHARED] [PUBLIC] DATABASE LINK dblink

[CONNECT TO

{CURRENT_USER

| | user IDENTIFIED BY password [dblink_authentication] |

}

| | dblink_authentication |

]...

[USING connect_string]

-the following is the semantics of the db link clause

-the following clause must be used with the shared clause

Dblink_authentication

You can specify this clause only if you are creating a shared database link-that is

You have specified the SHARED clause. Specify the username and password on the target instance.

This clause authenticates the user to the remote server and is required for security.

The specified username and password must be a valid username and password on the remote instance.

The username and password are used only for authentication. No other operations are performed on behalf of this user.

-the following clause

SHARED

If this clause creates a db link based on a single network connection (from the source library to the target library), so many sessions can share this db link, which is a bit like shared server mode

Specify SHARED to create a database link that can be shared by multiple sessions using a single

Network connection from the source database to the target database. In a shared server configuration

-this mode keeps a certain amount of data connected to the target database to prevent excessive connections. However, shared private database connections are very useful if multiple clients access the same local mode object

-so use the same private database connection

Shared database links can keep the number of connections into the remote database from becoming too large.

Shared links are typically also public database links. However, a shared private database link can be useful

When many clients access the same local schema, and therefore use the same private database link.

In this mode, multiple sessions of the source database are shared to the target database with the same connection, that is, one source database is connected to the target database, and the other is disconnected.

In a shared database link, multiple sessions in the source database share the same connection to the target database.

Once a session is established on the target database, that session is disassociated from the connection, to make the

Connection available to another session on the source database. To prevent an unauthorized session from attempting to

-to prevent unauthorized connections to the target library, you must specify that dblink_authentication only allows authorized users to access the target library

Connect through the database link, when you specify SHARED you must also specify the dblink_authentication clause for

The users authorized to use the database link.

-create a database link

SQL > create database link dlink1 connect to scott identified by system using 'orcl'

Database link created

-- query database link information

SQL > desc user_db_links

Name Type Nullable Default Comments

DB_LINK VARCHAR2 (128) Name of the database link

USERNAME VARCHAR2 (30) Y Name of user to log on as

PASSWORD VARCHAR2 (30) Y Deprecated-Password for logon

HOST VARCHAR2 (2000) Y SQL*Net string for connect

CREATED DATE Creation time of the database link

SQL > select * from user_db_links

DB_LINK USERNAME PASSWORD HOST CREATED

-

DLINK1 SCOTT orcl 2013-1-25 1

SQL > select * from tab where rownum select * from base_bill@dlink1 where rownum create or replace procedure proc_database_link

2 as

3 v_link varchar2 (1000)

4 begin

5 select bill_name into v_link from base_bill@dlink1 where rownum=1

6 dbms_output.put_line (v_link)

PL/SQL procedure successfully completed

SQL > create table t_tb (a varchar2 (1000))

-connect to another system user

SQL > conn system/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as system@orcl

SQL > show user

User is "system"

-database link created by scott users is not available to system users, that is, database links created by create database link are only available to create users

SQL > select bill_name from base_bill@dlink1 where rownum=1

Select bill_name from base_bill@dlink1 where rownum=1

ORA-02019: connection description for remote database not found

-reconnect scott users

SQL > conn scott/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott@orcl

-delete dlink1 database link

SQL > drop database link dlink1

Database link dropped

SQL >

SQL >

-create a public database link, that is, all users of the library can use this database link

SQL > create public database link dlink1 connect to scott identified by system using 'orcl'

Database link created

SQL > show user

User is "scott"

-currently available to create users

SQL > select bill_name from base_bill@dlink1 where rownum=1

BILL_NAME

one

-connect to the system user again

SQL > conn system/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as system@orcl

-system users are also available

SQL > select bill_name from base_bill@dlink1 where rownum=1

BILL_NAME

one

SQL >

-the difference between private and public database link

-Public (Note: public owner is public)

SQL > select * from dba_db_links

OWNER

-

PUBLIC

-Private (Note: private owner is the user who created the database link)

SQL > select * from dba_db_links

OWNER

-

SCOTT

-db link created by current_user

SQL > create database link dlink1 connect to current_user

Database link created

SQL > desc Tosca

Name Type Nullable Default Comments

-

A VARCHAR2 (100) Y

-- the following error is reported when running

SQL > select * from t_a@dlink1 where rownum=1

Select * from t_a@dlink1 where rownum=1

ORA-02019: connection description for remote database not found

-the following two parameters control the maximum number of db link that a parameter or session can open at the same time

SQL > show parameter open_link

NAME TYPE VALUE

-

Open_links integer 4

Open_links_per_instance integer 4

-create 5 db link in succession

SQL > create public database link dlink1 connect to scott identified by system using 'orcl'

Database link created

SQL >

SQL > create public database link dlink2 connect to scott identified by system using 'orcl'

Database link created

SQL > create public database link dlink3 connect to scott identified by system using 'orcl'

Database link created

SQL > create public database link dlink4 connect to scott identified by system using 'orcl'

Database link created

SQL > create public database link dlink5 connect to scott identified by system using 'orcl'

Database link created

SQL > desc tweea

Name Type Nullable Default Comments

-

A VARCHAR2 (100) Y

SQL > update t_a@dlink1 set axi10 where rownum=1

1 row updated

SQL > update t_a@dlink2 set axi10 where rownum=1

1 row updated

SQL > update t_a@dlink3 set axi10 where rownum=1

1 row updated

SQL > update t_a@dlink4 set axi10 where rownum=1

1 row updated

-when opening the fifth db link, an error is reported.

SQL > update t_a@dlink5 set axi10 where rownum=1

Update t_a@dlink5 set axiom 10 where rownum=1

ORA-02020: too many database links in use

-if connect to identified by is not specified, then the database link uses the user name and password of each user who is connected to the database.

-This is called a connected user database link. That is, the user name and password of each user connected to the remote library attempts to connect.

SQL > create public database link dlink6 using 'orcl'

Database link created

SQL > rollback

Rollback complete

SQL > select * from t_a@dlink6 where rownum=1

On how to carry out oracle create database link_ database link testing to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.