from的参数dblink的用法？

[haipeng2950]

听说oracle数据库可以用select * from table_name@dblink查询远程数据源的表
不知这个dblink是个什么东西，怎么创建？那位帮助看看

[luckysxn]

create public database link yourdblink using "service_name";


select * from table_name@yourdblink

[black_snail]

CURRENT_USER Example

The following statement defines a current-user database link:

CREATE DATABASE LINK sales.hq.acme.com
CONNECT TO CURRENT_USER
USING 'sales';


Fixed User Example

The following statement defines a fixed-user database link named SALES.HQ.ACME.COM:

CREATE DATABASE LINK sales.hq.acme.com
CONNECT TO scott IDENTIFIED BY tiger
USING 'sales';

Once this database link is created, you can query tables in the schema SCOTT on the remote database in this manner:

SELECT *
FROM [e-mail];

You can also use DML statements to modify data on the remote database:

INSERT INTO [e-mail](acc_no, acc_name, balance)
VALUES (5001, 'BOWER', 2000);

UPDATE [e-mail]
SET balance = balance + 500;

DELETE FROM [e-mail]
WHERE acc_name = 'BOWER';

You can also access tables owned by other users on the same database. This statement assumes SCOTT has access to ADAM's DEPT table:

SELECT *
FROM [e-mail];

The previous statement connects to the user SCOTT on the remote database and then queries ADAM's DEPT table.

A synonym may be created to hide the fact that SCOTT's EMP table is on a remote database. The following statement causes all future references to EMP to access a remote EMP table owned by SCOTT:

CREATE SYNONYM emp
FOR [e-mail];


PUBLIC Example

The following statement defines a shared public fixed user database link named SALES.HQ.ACME.COM that refers to user SCOTT with password TIGER on the database specified by the string service name 'SALES':

CREATE SHARED PUBLIC DATABASE LINK sales.hq.acme.com
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY anupam IDENTIFIED BY bhide
USING 'sales';


Use of the CREATE DATABASE LINK Command

Note: All of the following examples will use DECNET and TCP/IP format.

Let's assume we have two instances, the local instance and an instance in Strassbourg France called TEST2, and we are communicating via TCP/IP. The node address in Strassbourg is 176.999.00.10 and the account we will connect to is LIMITED_USER with a password of NONE.

SQL> CREATE PUBLIC DATABASE LINK FRANCE
CONNECT TO LIMITED_USER IDENTIFIED BY NONE
USING 'T:176.999.00.10:TEST2';

Now that we have our db link setup, we can access tables that the LIMITED_USER has privileges on, for example, the VEHICLES table. To get information from the table we could now issue the command:

SQL> SELECT * FROM VEHICLES@FRANCE;

If we don't want the users to know they are accessing a table in the remote database, we can hide this fact with a synonym:

SQL> CREATE PUBLIC SYNONYM VEHICLES FOR VEHICLES@FRANCE;

Now our select statement simplifies to:

SQL> SELECT * FROM VEHICLES;

You can access views, tables, clusters and sequences via db links and connect strings.


Example VMS DATABASE LINK

Unless network proxies are used under DECNET for VMS, you must specify a username and password for your connect string. The connect string will have the format:

NODE " username password " : : " TASK=ORDNsid "

The TASK variable is actually a command procedure located in either the ORA_NETCONFIG directory or in the user's top level directory. Some sites set up a non-privileged, non-interactive account to connect through, others use net proxies. What your site uses will depend on your site security policy and the policies of your system administrator.

The entire connect string can be loaded into a logical symbol:

$define/nolog FRANCE FRANCE1 " " " sqlnet sqlnet " " " : : " " " TASK=ORDNTEST1 " " "

The triple sets of " allow a single set of quotes to remain in the logical symbol. The command to create the DB LINK now becomes:

SQL> CREATE PUBLIC DATABASE LINK FRANCE
CONNECT TO LIMITED_USER IDENTIFIED BY NONE
USING 'D:FRANCE';

If you are using DATABROWSER some versions will not allow the use of logicals and the entire connect string definition will have to be loaded into the db link.