Oracle DB link创建和应用

1 增加alaias:
[oracle@testdb11gstd admin]$ pwd
/u01/app/oracle/product/11.1.0/db_1/network/admin
vi tnsnames.ora


# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CRDB11GS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = crdb11gstd.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb11gstd)
)
)


db169 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostIP1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ocr)
)
)

prodrac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostIP2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sername)
)
)

[oracle@testdb11gstd admin]$ sqlplus demotest/password

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 30 20:13:19 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production

SQL> create public database link prod_link connect to demoname identified by demopassword using 'prodrac';
--说明一下：
--prod_link 是db_link的别名
--demoname 是目标机器的用户名
--demopassword是目标机器的用户密码
--prodrac 是上面建立的别名

Database link created.

SQL> select count(*) from ps_pools@prod_link;

COUNT(*)
----------
65084
SQL> select count(*) from ps_audiofile@prod_link;

COUNT(*)
----------
42633

SQL> select count(*) from ps_programs@prod_link;

COUNT(*)
----------
327


SQL> show user
USER is "DEMOTEST"
SQL> create table ps_pools as select * from ps_pools@prod_link;

Table created.

SQL> create table ps_audiofile as select * from ps_audiofile@prod_link;

create table ps_audiofile as select * from ps_audiofile@prod_link
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-01013: user requested cancel of current operation
ORA-02063: preceding line from PROD_LINK


SQL> create table ps_audiofile as select * from ps_audiofile@prod_link where 1=0;

Table created.
SQL> insert into ps_audiofile select * from ps_audiofile@prod_link ;

42636 rows created.

SQL> commit;

Commit complete.

SQL> create table sp_pools_ori as select * from ps_pools;

Table created.

SQL> select count(*) from sp_pools_ori;

COUNT(*)
----------
65084