Oracle database link (DBLINK) Creation and deletion methods, oracledblink

Oracle database link (DBLINK) Creation and deletion methods, oracledblink

Create and delete a database link in Oracle

Create dblink:

create database link [name]  connect to [username] IDENTIFIED BY [password]  using '(DESCRIPTION =  (ADDRESS_LIST =   (ADDRESS = (PROTOCOL = TCP)(HOST = [ip])(PORT = [port]))  )  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = xxx)  )  )';

For example:

create public database link dblink_name connect to SYSTEM using '192.168.1.73:1521/oracle';

Delete dblink:

Drop database link [name]; -- or drop public database link [name];

An error is reported when you try to delete the file today:
The ORA-02018: database link of same name has an open connection

Disable dblink:

ALTER SESSION CLOSE DATABASE LINK [name];

However, the following error is reported:
ORA-02080: database link is in use.
Go to v $ dblink to view

SELECT * FROM v$dblink;

 
This dblink is indeed not in transaction. The solution was not found, and plsql was shut down. After a while, an error was reported when the alter session was executed:
ORA-02080: database link is not open.
I know that you can delete it, and then DROP it.

Below is a supplement from other netizens for your reference:

You can run the following command to check the Global Database Name:
Copy codeThe Code is as follows:
SELECT * FROM GLOBAL_NAME;

You can use the following statement to modify the parameter value:

Alter system set GLOBAL_NAME = TRUE/FALSE;

<1> when the database parameter global_name is set to false, the Database Link name is not required to be the same as that of the remote database.

Connect and communicate between Oracle databases.
The syntax for creating a database link is as follows:

CREATE [PUBLIC] DATABASE LINK linkCONNECT TO username IDENTIFIED BY passwordUSING ‘connectstring'

Note: The account used to CREATE the database link must have the system permission of create database link or create public database link. The account used to log on to the 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 a private database link to another user. A database link is either public or private.

When creating a database link, you can also use the default logon mode, that is, do not specify the remote database user name and password:

Copy codeThe Code is as follows:
Create public database link zrhs_link
Using 'zrhs ';

If the user name and password are not specified, ORACLE uses the current user name and password to log on to the remote database.

USING is followed by a link string, that is, the network service name of the remote database. This service name is stored in the local TNSNAMES. the ORA file defines the protocol, host name, port, and database name.

The statement for deleting a database link is:
Copy codeThe Code is as follows:
DROP [PUBLIC] database link zrhs_link

Database link reference

Generally, when you reference a database link, you can directly put it behind the called table name or view name, and use a @ in the middle as the delimiter:

SELECT * FROM worker @ zrhs_link;

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

Create synonym worker_syn FOR worker @ zrhs_link;

You can also create a local remote view for ease of use:

Create view worker as select * FROM worker @ zrhs_link where... ;

Now this view can be treated like any other view in the local database, and can be authorized to other users to access this view, but this user must have the permission to access the database link.

In another case, the table to be accessed is not under the remote account specified in the database link, but the account has the permission to access the table, add the table username before the table name:

SELECT * FROM camel. worker @ zrhs_link;

<2> when the database parameter global_name = true, the database connection name must be the same as the global name of the remote database.

You can use the following command to check the global name of the database:

SELECT * FROM GLOBAL_NAME;

Syntax for creating dblink:

SQL> create database link name
Connect to username identified by password
Using 'host string name ';

For example:

SQL> create database link ora9i.us.oracle.com ### here, us.oracle.com is the default domain name of oracle ### connect to scott identified by tigerusing 'sun ';

1) The dblink name must be the same as the global database name (global_name) of the remote database;
2) User name. The password is the remote database user name and password;
3) The host string is the string defined in tnsnames. ora;
4) dblink cannot be created between two databases with the same name;
Then, you can access the remote database through dblink.

For example:

SQL> select * from table name @ ora9i.us.oracle.com;
You can also create snapshots to automatically transmit remote data through dblink.
View All database links, go to the system administrator SQL> operator, and run the following command:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';

The following is an example of the second situation:

The headquarters in Beijing has a centralized database with the sid sidbj, Username: userbj, password: bj123, and Beijing IP Address: 192.168.1.101.
A local branch in Xinjiang also has a database whose SID is SIDXJ, user: userxj, password: xj123, and IP address of Xinjiang is 192.168.1.100.
Access Data in the SIDXJ database in the local Xinjiang region to the SIDBJ database in Beijing.
That is to say, in the sidxj database, the user userxj (192.168.1.100) needs to establish a DBLINK to access the data in sidBJ (192.168.1.101) as userbj.
Test environment: Both databases are built on WINXP, and ORACLE versions are Oracle817.
When creating an environment, you must disable the windows Firewall on the two computers. Otherwise, the ping may fail, but the Oracle connection may fail.
1. Q: How to return the GLOBAL_NAME of the database?
Execute SELECT * FROM GLOBAL_NAME;
The GLOBAL_NAME of the database in Beijing is SIDBJ. US. ORACLE. COM.
The GLOBAL_NAME of the database in Xinjiang is SIDXJ.
2. Q: How can I check whether the Global_name parameter is true or False?
A: run SQL> show parameter global_name;
The execution result is as follows:
NAME TYPE VALUE
-----------------------------------------------------------------------------
Global_names boolean TRUE
This parameter is true.

You can also view the v $ option view. If Advanced replication is set to TRUE, Advanced replication is supported. Otherwise, Advanced replication is not supported.
The execution statement is: select * from v $ option;
3. Q: How can I check whether the Oracle version supports synchronization?
A: run the select * from v $ option where PARAMETER = 'advanced replicase' statement,
If the returned value is True, it is supported. Otherwise, it is not supported. Check whether the two databases support this function.
Procedure:

1. Create an Oracle client locally to connect to tns_xj_to_bj, which is used to connect to the database in Beijing.
2. Create a connection tnsxj to connect to the local sidxj database and log on to PLSQL using tnsxj, userxj, and xj123.
Create a remote data connection DBLink to Beijing.
Create database link SIDBJ. US. ORACLE. COM connect to userbj identified by bj123 using 'tns _ xj_to_bj ';
Among them: SIDBJ. US. ORACLE. COM is the global_name of the remote database, userbj is the username used to connect to SIDBJ, and bj123 is the password of userbj,
Tns_xj_to_bj is the service name of the local database connected to Beijing.
3. test whether the connection is successful:
Select * from dual@SIDBJ.US.ORACLE.COM
If the following result is returned, the connection is successful.
DUMMY
-----
X
4. query the created remote connection name:
SQL> select owner, object_name from dba_objects where object_type = 'database link ';
OWNER OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
System sidbj. US. ORACLE. COM
USERXJ DBLINK_XJ_TO_BJ
USERXJ SIDBJ
5. So far, a dblink is established on the computer in Xinjiang to connect to the database in Beijing,
Create a table in the database in Beijing for testing.
Create table USERBJ. BJ_TEST
(
STU_ID NUMBER,
STU_NAME VARCHAR2 (100)
)
Add a record:
Insert into BJ_TEST (STU_ID, STU_NAME)
Values (1, 'zhong derong ');
Query the table information in the database of Beijing in the database of Xinjiang:
Select * from bj_test@SIDBJ.US.ORACLE.COM
Query results:
STU_ID STU_NAME
------------------------------------------------------------------------------------------
1 o'clock derong
Indicates that the query is normal.
Description: This query is used.
All the above scripts have passed the test.

Articles you may be interested in:

• Two methods for creating Database Link in Oracle