It Ninja Turtle database link detailed

-Create Public database link Test_link CONNECT to Scott identified by Tiger using ' (DESCRIPTION = (Address_list = (ADDR ESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))) (Connect_data = (service_name = LEE))) '; --Use select ename from [email protected]_link; --Delete the drop public database link test_link;

One: Dblink created:

    

1. Local Service has been configured

Create Public database

Link tobejing Connect to Scott
Identified by Tiger using ' bejing '

The database connection string ' Bejing ' is the alias name defined in the Tnsnames.ora file in the current client database. You can use NET8 Easy config or directly modify Tnsnames.ora definition.

2. Direct link creation

Create DATABASE link tobejing

Connect to Scott identified by TIGER 
Using ' (D　　 Escription = 
(address_list = 
(ADDRESS = (PROTOCOL = TCP) (HOST = x.x.x.x) (PORT = 1521))  
)  
(Connect_data = 
(service_name = BJORCL)  
)  
) ';

Create DATABASE link Toias
???? Connect to IAS identified by CIS
???? Using ' (DESCRIPTION =
???? (Address_list =
???? (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
???? )
???? (Connect_data =
???? (service_name = cis1.com)
???? )
???? )‘;

host= the IP address of the database, service_name= the SSID of the database.

In fact, the two methods of configuration dblink is similar, I personally feel that the second method is better, this is not affected by local services.

Note: If you create a global dblink, you must use the SYSTM or SYS user to add public before database.

1. Create a public DATABASE LINK without specifying a user and password

[email protected] >conn  system/ Test
Connected.
[email 　　Protected] >create  public DATABASE LINK orcl USING ' bejing ';
Database link created.

2. Create a private database LINK under each user Link name is the same), specify user and password
[email protected] >conn  scott　　/tiger
Connected.
  [ Email protected] >create  database LINK ORCL CONNECT to Scott identified by Tiger;
Database link created.

is actually equivalent to:

CREATE DATABASE LINK orcl USING ' Bejing ' CONNECT to Scott identified by Tiger;

There are 3 types of DB link, and I'll discuss only two of them, connected user and fixed user.
Connected user, simply put, the connecting-side database connects to the remote database with connected users.
Fixed user, in a nutshell, connects to a remote database with a fixed (specified) user.

Look at the creation of the DB link syntax, and you'll be more clear about the two types.

Create the connected user's DB link syntax:
Create Databas link foo Connect to Scott identified by Tiger using ' bejing ';

Create the DB link syntax for the fixed user:
Create Databas link foo using ' Bejing ';

The name and global_names of the DB link, Global_name is the database global name, and Global_name is guaranteed to be unique in the database you are managing. The database name is db_name. Database names generally get relatively short, my habit generally take length 4 characters, the same name probability is high.

So, Oracle imitates a domain name to make a global_name,global_name=db_name+db_domain.
With Global_name, you can implement a globally unique database naming. Example: ORCL. Regress. Rdbms. DEV. RSI Oracle.com

The database global name can be isolated with the following command
SELECT * from Global_name;

If Global_names=true, then the name of the DB link is the same as the global_name of the remote database;
If Global_names=false, then you can name the DB link casually.

Query Global_names is true or false, in PL/SQL, in the command window (not the Windows) execution: Show parameter Global_names

Second, Dblink query:

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 ';

Third, Dblink Delete:

DROP Public DATABASE LINK tobejing

Iv. Use of Dblink:

SELECT ... From table name @ database link name;

Querying, deleting, and inserting data is the same as manipulating the local database, except that the table name needs to be written as a "table name @dblink Server".

Example: Query the EMP table data in Beijing database select * from [email protected] ;

Five, synonym cooperation:

4th from [email protected] you can create synonyms to replace:
CREATE synonym synonym name for table name;

CREATE synonym synonym name for table name @ database link name;

Example: Create synonym Bj_scott_emp for [email protected] ;

then you can use Bj_scott_emp to replace the Distributed link operation with the @ symbol. [email protected]

The DB link is independent of the creation User (user_db_links username), which is not available to other users and cannot be deleted without permission.

It Ninja Turtle database link detailed