關于dblink舉例
2024-07-21 02:36:17
供稿：網友
登錄到遠程服務器上的結果：
net97>show parameters global_name
NAME TYPE VALUE
global_names boolean FALSE
net97>
net97>drop database link jinhua;
數據庫鏈接已丟棄。
net97>create database link jinhua
2 connect to username identified by passWord
3 using 'jinhua';
數據庫鏈接已創建。
net97>select * from so@jinhua;
select * from so@jinhua
*
ERROR 位于第 1 行:
ORA-12154: TNS:不能分解服務名稱.
net97>conn username/password@jinhua;
已連接。
net97>
///////////////////////////////////////////////////
登錄到本地的NTserver 上面的Oracle816數據庫：
本地的tnsnames.ora文件里邊有jinhua這個連接串的描述。
//////////////////////////////////////////////////////////////
GLOBAL_NAME=TRUE的時候的情況：
SQL> show parameters global
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
global_names boolean TRUE
SQL>
SVRMGR> create database link jinhua
2> connect to spgroup identified by spgroup
3> using 'jinhua';
語句已處理。
SVRMGR> select count(*) from spgroup.so@jinhua;
select count(*) from spgroup.so@jinhua
*
ORA-02085: ?????JINHUA.world?ORCL.WORLD???
SVRMGR> drop database link jinhua;
語句已處理。
SVRMGR> create database link jinhua.world
2> connect to username identified by password
3> using 'jinhua';
語句已處理。
SVRMGR> select count(*) from username.so@jinhua.world;
select count(*) from username.so@jinhua.world
*
ORA-02085: ?????JINHUA.WORLD?ORCL.WORLD???
SVRMGR> drop database link jinhua.world;
語句已處理。
SQL> conn chaos/chaos
已連接。
SQL> create database link orcl.world
2 connect to spgroup identified by spgroup
3 using 'jinhua';
數據庫鏈接已創建。
SQL> select count(*) from spgroup.so@orcl.world;
COUNT(*)
----------
6800
SQL>
///////////////////////////////////////////////////////////////////
GLOBAL_NAME=FALSE時候的情況：
SQL> shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 16930844 bytes
Fixed Size 75804 bytes
Variable Size 8388608 bytes
Database Buffers 8388608 bytes
Redo Buffers 77824 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> show parameter global_name
NAME TYPE VALUE
global_names boolean FALSE
SQL>
SQL> create database link jinhua
2 connect to spgroup identified by spgroup
3 using 'jinhua';
數據庫鏈接已創建。
SQL> select count(*) from so@jinhua;
COUNT(*)
----------
6800
//////////////////////////////////////////////////////////////////
數據庫連接串的密碼存放在user_db_links里邊的password字段里邊。
To facilitate application requests in a distributed database system, Oracle
uses
database links. A database link defines a one-way communication path from an
Oracle database to another database.
Database links are essentially transparent to the users of an Oracle
distributed database system, because the name of a database link is the same as the global name
of the database to which the link points. For example, the following SQL statement creates a database link in the local database that describes a path to the remote
SALES.US.AMERICAS.ACME_AUTO.COM database.
CREATE DATABASE LINK sales.us.americas.acme_auto.com ... ;
After creating a database link, applications connected to the local database can access data in the remote SALES.US.AMERICAS.ACME_AUTO.COM database. The next section eXPlains how applications can reference remote schema objects in a distributed database and includes examples of how SQL statements use database links.
Additional Information: Oracle supports several different types of database links. For more information, see Oracle8i Distributed
Database Systems.
