PLSQL_Database Link basic concepts and usage (concepts), plsql_databaselink

PLSQL_Database Link basic concepts and usage (concepts), plsql_databaselink

2014-06-08 BaoXinjian

I. Summary

1. How to Establish a database connection

2. Query Method

3. Connection Type

4. Common queries

 

Ii. Detailed Analysis

1.How to Establish a database connection

(1). Use tns for Creation

Create database link linkname

Connect to user_name identified by password

USING tns_dbname

(2) directly establish

Create database link linkname

Connect to user_nameIDENTIFIED BY password

USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.142.202.12) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = dbname)

)

)'

 

2.Query Method

(1) directly use select * form tablename @ dblink

(2) create synonyms and search by synonyms

(3) Establish materialized views to improve execution efficiency

Create public synonym syn_name for tablename @ linknam

 

3.Connection Type

(1). Private

(2). Public

When establishing a common database connection, you must have sysadmin and sys accounts.

Create public database link linkname

Connect to user_name identified by password

USING tns_dbname

 

4.Common queries

(1) query all dblink connections of the Current Account

Select * from user_db_links;

(2) query all the dblink connections of the current system

Select * from dba_db_links

(3) query the currently connected dblink connection

Select * from v $ dblink

 

Thanks and Regards