oracle 10g 使用expdp network_link导出远程数据库到本地文件
远程:
expdp中使用连接字符串和network_link的区别
expdp属于服务端工具,而exp属于客户端工具,expdp生成的文件默认是存放在服务端的,而exp生成的文件是存放在客户端的
1.expdp username/password@connect_string //对于使用这种格式来说,directory使用源数据库创建的,生成的文件存放在服务端。
如何将生成的文件放在目标数据库而不放在源数据库呢,在expdp中使用network_link
2.
a. 创建到服务端的dblink
conn aa/aacc
create database link link_name connect to username identified by password using 'connect_string' ;//username和password是server端的
b.
conn / as sysdba
create or replace directory dir as 'directory';
grant read,write on directory dir to username;
c.expdp username2/password2 directory=dir network_link=link_name ... //这里的username2用创建dblink的那个用户aa,directory也是目标数据库创建的
比如在本机expdp远程服务器的数据库,先在本机创建到服务端的dblink,然后创建directory及授权,然后expdp useranme2/password2 .....
3.如果想不生成dmp文件而直接导入一个数据库,原理和2类似,直接使用impdp带network_link ,这样可以直接impdp,而绕过了expdp的步骤
下面是network_link常用的三个环境测试
1,服务器端的数据导出到指定的客户端
2,不同数据库间迁移数据。
3,同一个数据库中不同用户之间迁移数据。
一:服务器端的数据导出到指定的客户端
1,修改客户端的TNSNAMES文件
POWER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = power1)
)
)
2,创建dblink
SQL> CREATE PUBLIC DATABASE LINK "POWER1"
2 CONNECT TO scott
3 IDENTIFIED BY "Oracle"
4 USING 'POWER1';
Database link created.
SQL> select * from dual@power1;
D
-
X
3,directory目录
SQL> set lines 170
SQL> col owner for a15
SQL> col directory_name for a60
SQL> col directory_name for a30
SQL> col DIRECTORY_PATH for a70
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ ----------------------------------------------------------------------
SYS DUMP /tmp
SYS TOAD_BDUMP_DIR /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace
SYS XMLDIR /u01/app/oracle/product/11.2/db_1/rdbms/xml
SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl11g/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2/db_1/ccr/state
如果不存在使用create directory创建再用grant授予用户权限
4,导数据
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott
Export: Release 11.2.0.3.0 - Production on Tue Mar 26 18:22:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
grant这个命令要在源端数据库上面执行
SQL> GRANT exp_full_database TO scott;
Grant succeeded.
回到客户端上面
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott
Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:40:03 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/tmp/scott_test.dmp"
ORA-27038: created file already exists
Additional information: 1
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott REUSE_DUMPFILES=Y
Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:42:29 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link=power1 schemas=scott REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.129 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 1.800 GB 19096576 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/tmp/scott_test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:46:1
已经成功导出到客户端指定的位置