資料泵匯出匯入資料標準文件

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