本地库创建dblink
1,添加tnsname
[oracle@node1 ~]$ cd $ORACLE_HOME/dbs
[oracle@node1 dbs]$ ls
hc_DBUA0.dat init.ora lkTEST peshm_DBUA0_0 snapcf_test.f
hc_test.dat inittest.ora orapwtest peshm_test_0 spfile.ora
[oracle@node1 dbs]$ cd ..
[oracle@node1 db_1]$ cd network/admin
[oracle@node1 admin]$ vi tnsnames.ora
testdb=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=192.168.10.102)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=testdb)
(SERVER=dedicated)
)
)
2,本地库 创建dblink
SYS@ test>CREATE PUBLIC DATABASE LINK "testdb"
2 CONNECT TO scott
3 IDENTIFIED BY "oracle"
4 USING 'testdb';
3,资源库打开监听
[oracle@solaris102:/export/home/oracle]$ lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 24-MAY-2014 15:21:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
[oracle@solaris102:/export/home/oracle]$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 24-MAY-2014 15:21:13
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 24-MAY-2014 15:20:39
Uptime 0 days 0 hr. 0 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/solaris102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.102)(PORT=1521)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
4,查看本地库的目录
SYS@ test>select * from dba_directories;
SYS DUMP_DIR
/home/oracle/dump_dir
5,资源库上创建一张表
scott@TESTDB>select count(*) from test;
COUNT(*)
----------
14257
如果导出不成功需要在资源库上执行
grant 这个命令要在源端数据库上面执行
grant exp_full_database to scott
6,在本地导出资源库的test表
[oracle@node1 dump_dir]$ expdp scott/oracle directory=dump_dir dumpfile=test.dmp network_link=testdb tables=test
Export: Release 11.2.0.1.0 - Production on Sat May 24 15:34:00 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dump_dir dumpfile=test.dmp network_link=testdb tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 1.269 MB 14257 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/dump_dir/test.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:34:04
7,将资源库的test表直接导入到本地库
[oracle@node1 dump_dir]$ impdp scott/oracle directory=dump_dir network_link=testdb tables=test
Import: Release 11.2.0.1.0 - Production on Sat May 24 15:37:36 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dump_dir network_link=testdb tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."TEST" 14257 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 15:37:42
[oracle@node1 dump_dir]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 24 15:38:05 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
@ >conn scott/oracle
Connected.
SCOTT@ test>select count(*) from test;
COUNT(*)
----------
14257
删除dblink
SYS@ test>drop public database link "testdb";
Database link dropped.
重新创建dblink
SYS@ test>create public database link "testdb" connect to system identified by "oracle" using 'testdb';
Database link created.
1,添加tnsname
[oracle@node1 ~]$ cd $ORACLE_HOME/dbs
[oracle@node1 dbs]$ ls
hc_DBUA0.dat init.ora lkTEST peshm_DBUA0_0 snapcf_test.f
hc_test.dat inittest.ora orapwtest peshm_test_0 spfile.ora
[oracle@node1 dbs]$ cd ..
[oracle@node1 db_1]$ cd network/admin
[oracle@node1 admin]$ vi tnsnames.ora
testdb=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=192.168.10.102)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=testdb)
(SERVER=dedicated)
)
)
2,本地库 创建dblink
SYS@ test>CREATE PUBLIC DATABASE LINK "testdb"
2 CONNECT TO scott
3 IDENTIFIED BY "oracle"
4 USING 'testdb';
3,资源库打开监听
[oracle@solaris102:/export/home/oracle]$ lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 24-MAY-2014 15:21:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
[oracle@solaris102:/export/home/oracle]$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 24-MAY-2014 15:21:13
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 24-MAY-2014 15:20:39
Uptime 0 days 0 hr. 0 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/solaris102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.102)(PORT=1521)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
4,查看本地库的目录
SYS@ test>select * from dba_directories;
SYS DUMP_DIR
/home/oracle/dump_dir
5,资源库上创建一张表
scott@TESTDB>select count(*) from test;
COUNT(*)
----------
14257
如果导出不成功需要在资源库上执行
grant 这个命令要在源端数据库上面执行
grant exp_full_database to scott
6,在本地导出资源库的test表
[oracle@node1 dump_dir]$ expdp scott/oracle directory=dump_dir dumpfile=test.dmp network_link=testdb tables=test
Export: Release 11.2.0.1.0 - Production on Sat May 24 15:34:00 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dump_dir dumpfile=test.dmp network_link=testdb tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 1.269 MB 14257 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/dump_dir/test.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:34:04
[oracle@node1 dump_dir]$ impdp scott/oracle directory=dump_dir network_link=testdb tables=test
Import: Release 11.2.0.1.0 - Production on Sat May 24 15:37:36 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dump_dir network_link=testdb tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."TEST" 14257 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 15:37:42
[oracle@node1 dump_dir]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 24 15:38:05 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
@ >conn scott/oracle
Connected.
SCOTT@ test>select count(*) from test;
COUNT(*)
----------
14257
删除dblink
SYS@ test>drop public database link "testdb";
Database link dropped.
SYS@ test>create public database link "testdb" connect to system identified by "oracle" using 'testdb';
Database link created.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29108064/viewspace-1168939/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29108064/viewspace-1168939/