Oracle DATABASE LINK (dblink) Creation and deletion method _oracle

Oracle Create, delete database LINK

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 

Today, when trying to delete the error:
Ora-02018:database link of same name has an open connection

Close Dblink:

ALTER session Close DATABASE LINK [name];

But the error:
Ora-02080:database link is in use
Go to V$dblink View

SELECT * from V$dblink;

The dblink did not in transaction, very puzzled. Search not find a solution, and then the Plsql off, after a while to open, execute ALTER session, the error:
Ora-02080:database Link is not open
I know this time should be able to delete, and then drop, OK.

The following are other users of the supplement you can refer to:

The database global name can be identified by the following command:

Copy Code code as follows:

SELECT * from Global_name;

Modify the parameter values by using the following statement:

ALTER SYSTEM SET Global_name=true/false;

<1>, when the database parameter is global_name=false, the database link name is not required to be the same as the remote database name.

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

CREATE [public] the DATABASE link link
CONNECT to username identified by password

USING ' connectstring '

Note: The account that creates the database link must have system permissions for Create or create public database link, and the account used to log on to the remote databases must have the Create session permission. Both of these permissions are included in the Connect role (CREATE public DATABASE link permissions are in the DBA).

A public database link is available to all users in the database, and a private link is available only to the user who created it. It is not possible for a user to authorize a private database link to another user, a database link is either public or private.

When you create a database link, you can also use the default logon method, which does not specify the user name and password for the remote database:

Copy Code code as follows:

Create Public database link Zrhs_link
Using ' ZRHS ';

Without specifying a username and password, Oracle logs on to the remote database using the current username and password.

The using following specifies the link string, which is the network service name of the remote database, which is saved in a local Tnsnames.ora file that defines the protocol, host name, port, and database name.

The statement that deletes a database link is:

Copy Code code as follows:

DROP [public] DATABASE LINK Zrhs_link

References to database links

Typically referencing a database link, you can place it directly after the called table name or view name, using a @ as a separator in the middle:

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 easy use:

CREATE VIEW worker as SELECT * from Worker@zrhs_link where ...;

This view can now be treated like any other view in the local database, or you can authorize other users to access this view, but the user must have permission to access the database link.

In the other case, the table you want to access is not under the remote account specified in the database link, but the account has permission to access the table, then we add the user name of the table before the table name:

SELECT * from Camel.worker@zrhs_link;

<2>, when database parameters are global_name=true, it requires the database link name to be the same as the global name of the remote database

The database global name can be identified with the following command

SELECT * from Global_name;

To create Dblink syntax:

Sql>create DB link Database link name
Connect to User name identified by password
Using ' host string name ';

Such as:

Sql>create database link ora9i.us.oracle.com ### here us.oracle.com for the Oracle Default domain name ###
Connect to Scott identified by Ti Ger
using ' sun ';

1) The Dblink name must be the same as the global database name (global_name) of the remote database;
2 user name, password for remote database username, password;
3 The host string is a string defined in the native Tnsnames.ora;
4. No dblink shall be established between two databases of the same name;
Then, you can access the remote database via Dblink.

Such as:

Sql>select * from table name @ora9i.us.oracle.com;
You can also create snapshots (snapshot) to automate remote data transfer via Dblink.
To view all the database links, go to the system administrator sql> operator, and run the command:

Sql>select owner,object_name from dba_objects where object_type= ' DATABASE LINK ';

The second case is illustrated below:

Beijing's headquarters has a centralized database, its SID is SIDBJ, user name: USERBJ, Password: bj123, Beijing's IP address is: 192.168.1.101.
in the local (Xinjiang) branch also has a database, its SID is Sidxj, User: USERXJ, Password: xj123, Xinjiang's IP address is: 192.168.1.100.
to access data from the SIDXJ database in local Xinjiang to the SIDBJ database in Beijing.
that is, in a SIDXJ database, the user userxj (192.168.1.100) needs to establish dblink to access the data in USERBJ (SIDBJ) as a user of 192.168.1.101.
Test environment: All two databases are built on WinXP, Oracle versions are Oracle817
when setting up the environment, be careful to shut down the firewall of Windows on two computers, otherwise, there will be ping, but the Oracle connection does not pass.
1, Q: How do I return the global_name of the database?
executes a select * from Global_name;
Global_name of the database in Beijing is SIDBJ.US.ORACLE.COM
the global_name for the database in Xinjiang is SIDXJ
2, Q: How to view Global_ is the name parameter true or False?
Answer: Perform:sql> show parameter global_name; The results of the
are as follows:
NAME TYPE VALUE
-----------------------------------------------------------------------------
Global_names Boolean True
to indicate that the argument is true.

You can also support advanced replication functionality by viewing the V$option view, if the advanced replication is true, otherwise it is not supported.
Execute the statement as: SELECT * from V$option;
3. Q: How do I check Oracle's version to support the sync function?
A: Execute select * from v$option where parameter= ' Advanced replication ' statement,
How to return a value of true is supported, otherwise it is not supported. In all two databases, checking for support is the only line.
Establish steps:

1, establish a local Oracle client connection TNS_XJ_TO_BJ, used to connect the Beijing database.
2, a new connection TNSXJ, used to connect the local SIDXJ database, tnsxj/userxj/xj123 login to the plsql.
The following is the creation of a remote data connection Dblink connecting to Beijing.
Create DATABASE link SIDBJ. US. Oracle.com connect to USERBJ identified by bj123 using ' TNS_XJ_TO_BJ ';
Where: SIDBJ.US.ORACLE.COM is a remote database GLOBAL_NAME,USERBJ is connected to the SIDBJ username, bj123 is the USERBJ password,
TNS_XJ_TO_BJ is a locally established service name for a database connected to Beijing.
3, test the success of the connection:
SELECT * FROM Dual@SIDBJ.US.ORACLE.COM
If the return result is as follows, the connection succeeds.
DUMMY
-----
X
4, the query has established the 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. At this point, a dblink was established on the computer in Xinjiang to connect to the database of Beijing,
In the Beijing database, set up a table for testing.
CREATE TABLE USERBJ. Bj_test
(
STU_ID number,
Stu_name VARCHAR2 (100)
)
Add a record here:
Insert into Bj_test (stu_id, Stu_name)
VALUES (1, ' Zhongderong ');
Query the database in Xinjiang for information on tables in the Beijing database:
SELECT * FROM Bj_test@SIDBJ.US.ORACLE.COM
Query results:
stu_id Stu_name
---------- --------------------------------------------------------------------------------
1 Zhongderong
Indicates that the query is normal.
Description: The query is
All of the above scripts pass the test.