使用expdb ,impdp 导入导出库,首先在本地必须建立db_link才可导入导出,exp,imp 这两个命令无需建立db_link即可导入导出。
一.远程导出库的步骤:
1.在本地库的配置文件中加如配置:
tnames.ora增加
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 远程主机IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 远程服务名)
)
)
2.创建dblink
SQL> create public database link netlink connect to 远程登录用户名 identified by 密码 using 'orcl';
Database link created.
SQL> select * from test.db@netlink;
ID NAME
---------- --------------------
1 ss
2 dd
3.创建导出临时目录:
SQL> create or replace directory dumpdir as '/oradata/dump';
Directory created.
4.授权用户,一般用system用户。
SQL> grant read,write on directory dumpdir to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select * from test.db@netlink;
ID NAME
---------- --------------------
1 ss
2 dd
1 dddd
SQL> quit
5.在cmd中写expdp 导出语句:
[oracle@ORA11G-DG1 ~]$ expdp 本地登录库的用户名密码(system/orcl) network_link=netlink directory=dumpdir dumpfile=test.dmp logfile=test.log schemas=test
(1):问题1:
Export: Release 11.2.0.4.0 - Production on Wed Dec 10 07:50:25 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas
问题解决办法
SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 10 07:52:01 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant exp_full_database to hr;
Grant succeeded.
SQL> quit
(2):问题2:
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
解决办法
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant exp_full_database to test;
授权成功。
以上是导出步骤。
二.远程导入库:
1.步骤请参照导出库。
2.直接写导入语句:
Impdp utest/utest@orcl directory=DMPDIR schemas=e01 network_link=sourcedatabase remap_schema=e01:e0311 job_name=impjob