impdp

 

1.schemas

导入用户hxl,同时创建用户hxl,默认表空间是导出前hxl所在的表空间,同时赋予用户connect角色
impdp system/system DIRECTORY=data_pump_dir DUMPFILE=hxl.dmpdp SCHEMAS=hxl

 

2.remap_tablespace

导入用户hxl,使用remap_tablespace将用户的表空间替换为tps_hxl01(之前是tps_hxl)
impdp system/system DIRECTORY=data_pump_dir DUMPFILE=hxl.dmpdp SCHEMAS=hxl remap_tablespace=tps_hxl:tps_hxl01

多个表空间需要转换的这么指定:
remap_tablespace='(EPI_JINAN:TPS_DATA,USERS:TPS_DATA)'

3.dumpfile

针对导出的时候使用了parallel参数生成多个dmp文件,导入的时候也需要指定%u导入

impdp system/system DIRECTORY=data_pump_dir DUMPFILE=file%u.dmp SCHEMAS=hxl

 

4.content

impdp system/system DIRECTORY=data_pump_dir DUMPFILE=hxl.dmpdp SCHEMAS=hxl content=all

这里content的选项
all 导入元数据和数据
data_only 只导入数据,前提是表对象已经存在
metadata_only 只导入元数据

 

5.table_exists_action

impdp scott/hxl directory=data_pump_dir dumpfile=hxl.dmp table_exists_action=replace

表存在的处理方式,有三种处理方式:

有效关键字: (SKIP), APPEND, REPLACE 和 TRUNCATE。

一般采用replace 

该选择不单是针对表，对于函数，存储过程，视图，序列也适用

6.full

全库导入,导入前需要将所有应用用户和表空间事先创建好,否则会导致无法导入.

impdp system/oracle directory=data_pump_dir dumpfile=full_p218.dmp full=y

7.EXCLUDE=INDEX 去掉索引
impdp scott/hxl directory=data_pump_dir dumpfile=hxl.dmp table_exists_action=replace EXCLUDE=INDEX

8. include=INDEX 单独导入索引
impdp scott/hxl directory=data_pump_dir dumpfile=hxl.dmp table_exists_action=replace include=INDEX

9.数据和索引分开导入
并行导入数据,只导入数据
impdp ogg/ogg network_link=LINK_TO_SOURCE01 directory=backdir EXCLUDE=STATISTICS cluster=n tables=scott.tb_trandata_test01 table_exists_action=replace exclude=index parallel=4


生成创建索引的ddl
impdp ogg/ogg network_link=LINK_TO_SOURCE01 directory=backdir cluster=n tables=scott.tb_trandata_test01 SQLFILE=sql_index.ddl include=INDEX

替换并行度
sed -i 's/PARALLEL 1/PARALLEL 4/g' sql_index.ddl

会在backdir对应的目录下生成创建索引的文件:sql_index.ddl
我这里的路径是:/u01/app/backup/sql_index.ddl

[oracle@host02 backup]$ vi sql_index.ddl 
-- CONNECT OGG
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE INDEX "SCOTT"."IDX_TB_TRANDATA_TEST01_N1" ON "SCOTT"."TB_TRANDATA_TEST01" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 4;


  ALTER INDEX "SCOTT"."IDX_TB_TRANDATA_TEST01_N1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- CONNECT OGG
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'IDX_TB_TRANDATA_TEST01_N1';
  i_o := 'SCOTT';
  EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,72,1,72,1,1,1,0,72,NV,NV,TO_DATE('2018-05-31 08:41:44',df),NV;


  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/


然后编写脚本在后台执行,脚本名为run_create_index.sh

#!/bin/sh
sqlplus /nolog 
connect system/oracle
@/home/oracle/dumpdir/sql_index.ddl
exit;
EOF

注意这里需要system登陆执行，后台执行
nohup ./run_create_index.sh > run_create_index.out 2>&1 &