How to use dblink is established in Oracle

Oracle Application Server OS

1. View the global_name of the database

SELECT * FROM GLOBAL_NAME

two。 Check whether the global_name parameter is true or false

Show parameter global_name

If true, the locally established dblink name must be the same as the remote global_name.

3. Create dblink

CREATE DATABASE LINK database link name CONNECT TO username IDENTIFIED BY password USING 'database connection string'

Example 1:

CREATE PUBLIC DATABASE LINK CONN_MY_LINK CONNECT TO MYUSER IDENTIFIED BY MYPASSWORD USING 'MYORAL'

Where MYORAL is the local name of the access server established for the client server

LINK CONN_MY_LINK is a locally established dblink name

Example 2:

CREATE PUBLIC DATABASE LINK LINK_HSMIS CONNECT TO BI IDENTIFIED BY BI USING'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.188.245.201) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = hsmis)'

Directly replace local naming with a local named connection string.

/ / Grant creation permission grant CREATE DATABASE LINK to smis; grant CREATE SYNONYM to smis

4. Query whether the dblink is created successfully

SELECT * FROM DUAL@ database link name or SELECT * FROM table name @ database link name

5. Delete dblink

Drop public database link CONN_MY_LINK

6. Use synonyms to simplify:

CREATE SYNONYM S_MY_TABLE FOR TABLENAME@ database link name

The solution of Lock in 7.db_link query

To release the connection each time you use a db_link query, call the close function in the dbms_session package

Example: dbms_session.close_database_link (CONN_MY_LINK)

Or when using dblink, even the select text should be commit, or rollback, otherwise a long time will hinder other processes.

8. There is a solution that cannot process the server name when querying dblink

To establish the corresponding connection on the server in the corresponding database, for example, A database is on A1 server, B database is on b1 server, if you want to connect B database in A database, then establish the corresponding tnsnames configuration item on server A1 as follows:

(in Oracle under Win environment, configure it in the file Oracle\ Network\ ADMIN\ tnsnames.ora)

A_TO_B = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = IP of A2 server) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = service name of B database)

Then set up the corresponding dblink, and the easy mistakes are:

Establish a connection in your own client's tnsname.ora, but not in all of the A database

A connection is established in the tnsname.ora of server A1.

9. View the table locking process and unlock

(1) method 1:

Select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid

(2) method 2:

Select * from v$session T1, v$locked_object T2 where t1.sid = t2.SESSION_ID

(3) unlock the process

If there is a record, it indicates that there is a lock. Record the SID and serial#, and replace the recorded ID with the SID and serial below, and then the LOCK can be removed.

Alter system kill session 'SID,serial'

The above is a summary of the dblink application in oracle introduced by the editor. I hope it will be helpful to you. If you have any questions, you are welcome to leave a message. The editor will reply you in time. Thank you very much for your support to the website!