数据泵导出导入数据标准文档

1, 确认导出用户数据分布
select distinct owner||'+'||tablespace_name from dba_segments
where owner in (select username from dba_users where account_status='OPEN')
and owner like '%UAM%' order by 1;

OWNER||'+'||TABLESPACE_NAME
-------------------------------------------------------------
UAMQAS05+UAMQAS
UAMQAS07+UAMQAS[@more@]2, 确认导出用户拥有权限
select username,granted_role from user_role_privs;
USERNAME GRANTED_ROLE
------------------------------ ------------------------------
UAMQAS05 CONNECT
UAMQAS05 RESOURCE

select username,privilege from user_sys_privs;
USERNAME PRIVILEGE
------------------------------ ----------------------------------------
UAMQAS05 CREATE VIEW
UAMQAS05 UNLIMITED TABLESPACE

select owner,table_name,grantee,privilege from user_tab_privs;
OWNER TABLE_NAME GRANTEE PRIVILEGE
------------------------------ ------------------------------ ------------------------------ -------------
SYS UAMQASDMPDIR UAMQAS05 WRITE
SYS UAMQASDMPDIR UAMQAS05 READ

确认用户拥有:connect,resource,create view,unlimited tablespace权限

3, 确认导出用户目录权限
select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------
SYS UAMQASDMPDIR /home/oracle/sql/uamqas02/exp

select owner,table_name,grantee,privilege from user_tab_privs;
OWNER TABLE_NAME GRANTEE PRIVILEGE
------------------------------ ------------------------------ ------------------------------ -------------
SYS UAMQASDMPDIR UAMQAS05 WRITE
SYS UAMQASDMPDIR UAMQAS05 READ

确认用户对目录对象:UAMQASDMPDIR,实际位置/home/oracle/sql/uamqas02/exp拥有读写权限

4, 新建导入用户，参考导出用户权限，配置导入用户权限和目录对象
create tablespace uamqas_ts datafile '/oradata/qasdb01/uamqas_ts01.dbf' size 1000m
autoextend on next 500m maxsize 20000m extent management local default compress basic;

create user uamqas11 identified by uamqas11 default tablespace uamqas_ts;

grant connect,resource,create view to uamqas11;

create directory exp_dir as '/home/oracle/temp';
grant read,write on directory sys.exp_dir to uamqas11;

5, 导出数据
源数据库和目标数据库版本不一致的情况，在高版本数据库发起导出动作时，加上参数version=10.2(低版本数据库)，以保持兼容性
expdp uamqas05/uamqas05 directory=UAMQASDMPDIR dumpfile=expdp.uamqas05.20110224.dmp content=all
logfile=expdp.uamqas05.20110224.log exclude=TABLE:"IN ('UAM_INTERACTIVE_LOG')" schemas=uamqas05

如果存在exclude或者include，特殊字符注意使用斜杠转义

expdp uamqas05/uamqas05 directory=UAMQASDMPDIR dumpfile=expdp.uamqas05.2011022402.dmp content=METADATA_ONLY
logfile=expdp.uamqas05.2011022402.log include=TABLE:"IN ('UAM_INTERACTIVE_LOG')" schemas=uamqas05

6, 导入数据
cd /home/oracle/temp
impdp uamqas11/uamqas11 directory=exp_dir dumpfile=expdp.uamqas05.20110224.dmp content=all
logfile=impdp.uamqas05.20110224.log exclude=TABLE:"IN ('UAM_INTERACTIVE_LOG')"
remap_schema=uamqas05:uamqas11 REMAP_TABLESPACE=uamqas:uamqas_ts

impdp uamqas11/uamqas11 directory=exp_dir dumpfile=expdp.uamqas05.2011022402.dmp content=METADATA_ONLY
logfile=impdp.uamqas05.2011022402.log include=TABLE:"IN ('UAM_INTERACTIVE_LOG')"
remap_schema=uamqas05:uamqas11 REMAP_TABLESPACE=uamqas:uamqas_ts

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15877397/viewspace-1046814/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15877397/viewspace-1046814/