常用expdp数据泵语句

常用数据泵语句

常用sql语句
grant read,write on directory data_dir to user;
select * from dba_directories;
create directory ora_dump as '/home/oracle/ora_dump';

expdp导出

1. 以用户导出

expdp tvvas0725/TJtvvas_321 schemas=tvvas0725 dumpfile=tvvas0725_0117.dmp exclude=STATISTICS,INDEX parallel=4 directory=ORA_DUMP logfile=tvvas0725_0117.log;

2. 以表来导出

expdp ibadb/Ds15_bm3256 tables=t_dhas_userattr parallel=4 exclude=STATISTICS,INDEX DIRECTORY=DATA_DIR DUMPFILE=t_dhas_userattr.dmp logfile=t_dhas_userattr.log

3. 预估导出文件大小

expdp “‘sys/oracle as sysdba’” full=y directory=DC_DIR_MIG_DIR EXCLUDE=STATISTICS,INDEX estimate_only=y estimate=blocks FILESIZE=10G;

4. 全库导出

expdp "'sys/oracle as sysdba'" directory= DC_DIR_MIG_DIR dumpfile=tvvas0725_2019_%U.dmp logfile= tvvas0725_2019.log FULL=Y parallel=4 EXCLUDE=TABLE:"IN('LOGMANAGER','MESSAGE_SYNC_0908BAK')",STATISTICS,schemas:INDEX,SCHEMA:"IN ('SYS', 'TJTEST22','SYSTEM','TEST','TEST0821')" FILESIZE=10G

impdp导入

1. 全库导入

impdp “‘sys/oracle as sysdba’” full=y directory=ora_dump dumpfile= TVVAS0725.dmp logfile= TVVAS0725.log;

2. 重新设定表空间和用户

impdp “‘sys/oracle as sysdba’” remap_tablespace=UPM_DATA:RPT_DATA1 remap_schema=upmdb:ibadb tables=upmdb.upm_t_subscriber transform=oid:n directory=DATA_DIR dumpfile=upm_t_subscriber0116.dmp logfile=upm_t_subscriber0116.log table_exists_action=replace

3. 重设表名

impdp “‘sys/oracle as sysdba’” remap_table=LOGMANAGER:LOGMANAGER_OLD,MESSAGE_SYNC_0908BAK:MESSAGE_SYNC_OLDBAK tables=LOGMANAGER,MESSAGE_SYNC_0908BAK directory=ORA_DUMP TRANSFORM=segment_attributes:n dumpfile=upm_t_subscriber0116.dmp logfile=upm_t_subscriber0116.log table_exists_action=replace

表名不要写成 user.tablename的格式

4.重设表，转换数据，防止报错

impdp “‘sys/oracle as sysdba’” remap_table=T_DHAS_CB_INDATABASE_STD:T_lc_CB_INDATABASE_STD remap_schema=ibadb:upmdbm tables=T_DHAS_CB_INDATABASE_STD directory=ORA_DUMP dumpfile=INDATABASE.dmp logfile=INDATABASE.log TRANSFORM=segment_attributes:n table_exists_action=append

table_exists_action详解

1)  skip：默认操作
2)  replace：先drop表，然后创建表，最后插入数据
3)  append：在原来数据的基础上增加数据
4)  truncate：先truncate，然后再插入数据

Oracle expdp impdp中 exclude/include 的使用