oracle相同数据库下跨schema的表迁移—expdp/impdp
需求：将GUIDO用户下的表迁移到SCOTT用户下
select * from dba_role_privs where GRANTEE='SCOTT';--用户角色
SQL> select * from dba_role_privs where GRANTEE='SCOTT';GRANTEE GRANTED_ROLE ADM DEL DEF COM
-------------------- -------------------- --- --- --- ---SCOTT CONNECT NO NO YES NOSCOTT RESOURCE NO NO YES NOselect * from dba_sys_privs where GRANTEE='SCOTT'; --用户系统权限
GRANTEE PRIVILEGE ADM COM
-------------------- ---------------------------------------- --- ---SCOTT CREATE TABLE NO NOSCOTT CREATE VIEW NO NOSCOTT DEBUG CONNECT SESSION NO NOSCOTT DEBUG ANY PROCEDURE NO NOSCOTT CREATE DATABASE LINK NO NOselect * from dba_tab_privs where GRANTEE='SCOTT'; --用户表级权限GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE---------- ---------- ------------------------------ ---------- ---------- --- --- --- ------------------------SCOTT SYS SYS_PLSQL_DA3FBA35_644_1 SYS EXECUTE YES NO NO TYPESQL> select username,default_tablespace from dba_users where account_status='OPEN';USERNAME DEFAULT_TABLESPACE
------------------------------------------------------------------ ZABBIX ZABBIXSYSTEM SYSTEMSYS SYSTEMSCOTT SCOTT_DATA01GUIDO GUIDO_DATA015 rows selected.SQL> col SEGMENT_NAME for a30
SQL> select s.segment_name,s.tablespace_name from dba_segments s,dba_indexes i where s.segment_name=i.index_name and i.owner='SCOTT';SEGMENT_NAME TABLESPACE_NAME------------------------------ ------------------------------FK_ID SCOTT_DATA01IDX_DUDECT_INFO_REQ_DATE SCOTT_DATA01IDX_T_DU_RES SCOTT_DATA01PK_BORROWER_AGENCY SCOTT_IDX01PK_BORROWER_BANKCARD SCOTT_IDX01PK_BORROWER_COMPANY SCOTT_IDX01PK_BORROWER_FINANCE SCOTT_IDX01PK_BORROWER_INFO SCOTT_IDX01PK_CONFIG SCOTT_IDX01PK_DUDECT_AGREEMENT SCOTT_IDX01PK_DUDECT_AGREEMENT_TEMP SCOTT_IDX01PK_DUDECT_BATCH_LOG SCOTT_IDX01PK_DUDECT_INFO SCOTT_IDX01PK_DUDECT_RESULT SCOTT_IDX01PK_LOAN_REQUEST SCOTT_IDX01PK_LOAN_RESULT SCOTT_IDX01PK_MESSAGE SCOTT_IDX01PK_SYS_EXCEPTION SCOTT_IDX01T_AGREEMENT_ELE SCOTT_DATA01T_AGREEMENT_ELE SCOTT_IDX0120 rows selected. SQL> select * from dba_directories;OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ------------------------- -------------------------------------------------- -------------SYS ORACLE_HOME / 0SYS ORACLE_BASE / 0SYS OPATCH_LOG_DIR /data/app/oracle/product/12.1.0/db_1/QOpatch 0SYS OPATCH_SCRIPT_DIR /data/app/oracle/product/12.1.0/db_1/QOpatch 0SYS OPATCH_INST_DIR /data/app/oracle/product/12.1.0/db_1/OPatch 0SYS DATA_PUMP_DIR /data/app/oracle/admin/hbhsdb/dpdump/ 0SYS XSDDIR /data/app/oracle/product/12.1.0/db_1/rdbms/xml/schema 0SYS ORACLE_OCM_CONFIG_DIR /data/app/oracle/product/12.1.0/db_1/ccr/hosts/WG- 0 HOUBANK-DHDB01/stateOWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID---------- ------------------------- -------------------------------------------------- -------------SYS XMLDIR /data/app/oracle/product/12.1.0/db_1/rdbms/xml 0SYS ORACLE_OCM_CONFIG_DIR2 /data/app/oracle/product/12.1.0/db_1/ccr/state 0SYS DIR_AUTO_BACKUP /data/app/oracle/auto_backup 0SYS EXPDP_DIR /data/backup 012 rows selected.
赋予目录权限：SQL>grant read,write on directory EXPDP_DIR to system;导出：expdp system/manager schemas=SCOTT directory=EXPDP_DIR dumpfile =SCOTT_expdp.dmp logfile=SCOTT_expdp.log;导入：相同的schemaimpdp system/manager schemas=SCOTT directory=EXPDP_DIR dumpfile =SCOTT_expdp.dmp logfile=SCOTT_impdp.log;不同的schemaimpdp system/manager schemas=SCOTT remap_schema=SCOTT:GUIDO directory=EXPDP_DIR dumpfile =SCOTT_expdp.dmp logfile=SCOTT_expdp.log;impdp system/manager directory=EXPDP_DIR dumpfile=ELEMENT.dmp logfile=ELEMENT_impdp.log remap_schema=guido:scott remap_tablespace=GUIDO_DATA01:SCOTT_DATA01,GUIDO_IDX01:SCOTT_IDX01,GUIDO_TEMP:SCOTT_TEMP
#需要映射的schema的默认表空间、索引表空间、默认临时表空间
扩展知识点：
一：映射关系REMAP_SCHEMA、REMAP_TABLESPACE、REMAP_DATAFILE 1、REMAP_SCHEMA 该选项用于将源方案的所有对象装载到目标方案中：REMAP_SCHEMA=source_schema:target_schema 2、REMAP_TABLESPACE 将源表空间的所有对象导入到目标表空间中：REMAP_TABLESPACE=source_tablespace:target:tablespace 3、REMAP_DATAFILE 该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项. REMAP_DATAFIEL=source_datafie:target_datafile二、expdp中TABLE_EXISTS_ACTION参数 当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式： a.忽略（SKIP，默认行为）； b.在原有数据基础上继续增加（APPEND）； c.先DROP表，然后创建表，最后完成数据插入（REPLACE）； d.先TRUNCATE，再完成数据插入（TRUNCATE）。例如：impdp system/manager schemas=GUIDO directory=EXPDP_DIR dumpfile=SCOTT_expdp.dmp logfile=SCOTT_impdp.log TABLE_EXISTS_ACTION=SKIP 三、低版本不能导入高版本的dmp文件，需要在高版本的EXPDP导出时指定版本号，低版本IMPDP无需指定版本。 例如：11.2.0.4导入到10.2.0.5expdp system/manager schemas=GUIDO directory=EXPDP_DIR dumpfile=SCOTT_expdp.dmp version=10.2.0.5.0
四、远程数据泵导出数据expdp system/manager@//10.100.7.198:1521/hsdg schemas=SCOTT directory=EXPDP_DIR dumpfile =SCOTT_expdp.dmp logfile=SCOTT_expdp.log;impdp system/manager@//10.100.7.197:1521/hsdg schemas=SCOTT directory=EXPDP_DIR dumpfile =SCOTT_expdp.dmp logfile=SCOTT_impdp.log;hsdg为数据库连接字符串的名称
五：parfile文件的使用-导出多个表
expdp parfile= parfile.txt
vi parfile.txt
userid=system/xxxxxx
schemas=ELONdirectory=EXPDP_DIRdumpfile=del_table_20180424.dmplogfile=del_table_20180424.loginclude=TABLE:"IN('TEST4','TEST3','TEST3'省略若干表.......
)"
六：QUERY查询条件的EXPDP/IMPDP
按查询条件只导出一张表，发现不需要加schema=的条件，只需要在tables=前面加上schema即可
expdp system/xxxxxx directory=DUMPDIR dumpfile=scott.dmp tables=scott.emp query=scott.emp:\" where creation_date\>"'23-OCT-12'"\"按查询条件也可以同时导出两张表，只需要在tables=中逗号隔开两表，query=中逗号隔开两张表的查询条件即可
expdp system/xxxxxx directory=DUMPDIR dumpfile=scott.dmp tables=scott.BH1WZZK0,scott.BH2WZZK0 query=scott.BH1WZZK0:\" where zctjrq0 like ‘2013%’"\",scott.BH2WZZK0:\" where zctjrq0 like ‘2013%’"\"
导入到异机：
impdp SYSTEM/manager dumpfile=201611182.dmp directory=ODPDIR table_exists_action=append
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015dh.dmp directory=DUMPDIRimpdp system/xxxxxx directory=DUMPDIR dumpfile=scott.dmp remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo
oracle expdp query 泵出表部分数据
cat >exp.par<<EOFdirectory=DUMPDIRdumpfile=scott.dmptables=(scott.emp,scott.dept)query=(scott.emp:"where empno<=888", scott.dept:"where empno>=666")EOFexpdp \'/ as sysdba\' parfile=exp.par
七：排除某些表的EXPDP/IMPDP
expdp system/xxxxxx directory=DATA_PUMP_DIR dumpfile=scott.dmp logfile=scott.log schemas=scott EXCLUDE=TABLE:\"IN\'TABLE1\',\'TABLE2\',\'TABLE3\'\"
impdp到schema为scott2，tablespace为scott2的用户，已经排除某些表的2015dh.dmp文件的impdp导入
impdp SYSTEM/xxxxxx remap_schema=scott:scott2 remap_tablespace=scott:scott2 dumpfile=scott.dmp directory=DUMPDIR