-- CONNECT SYS
?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/TABLE
?CREATE TABLE "AD"."ADTAB1"
? ? (? ? "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
? ? ? ? "USER_ID" NUMBER NOT NULL ENABLE,
? ? ? ? "CREATED" DATE NOT NULL ENABLE
? ? ) SEGMENT CREATION IMMEDIATE
? PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
? NOCOMPRESS LOGGING
? STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
? PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
? BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
? TABLESPACE "TS_PUB" ;
?-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
?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 := 'IND_ADTAB1_UID';
? i_o := 'MNG';
? EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,45,1,45,1,1,1,0,45,NV,NV,TO_DATE('2015-01-24 08:16:45',df),NV;
? DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
? DELETE FROM "SYS"."IMPDP_STATS";
?END;
?/
?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 := 'IND_ADTAB1_CRT';
? i_o := 'MNG';
? EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,45,1,39,1,1,1,0,45,NV,NV,TO_DATE('2015-01-24 08:16:45',df),NV;
? DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
? DELETE FROM "SYS"."IMPDP_STATS";
?END;
?/
现在方向就很明确了：expdp阶段如何把mng用户下的索引也带上
解决方案如下：
######把表和索引的名称填上，Schemas必须带上ad,mng这两个用户，如果Schema不指定那么默认在SYS下去找，include的功能还是很强大的
expdp \"/ as sysdba\" schemas=ad,mng include=TABLE:\"=\'ADTAB1\'\",INDEX:\"IN \(\'IND_ADTAB1_UID\',\'IND_ADTAB1_CRT\'\)\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
---导出过程正常
Starting "SYS"."SYS_EXPORT_SCHEMA_01":? "/******** AS SYSDBA" schemas=ad,mng include=TABLE:"='ADTAB1'",INDEX:"IN ('IND_ADTAB1_UID','IND_ADTAB1_CRT')" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
?Estimate in progress using BLOCKS method...
?Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
?Total estimation using BLOCKS method: 8 MB
?Processing object type SCHEMA_EXPORT/TABLE/TABLE
?Processing object type SCHEMA_EXPORT/TABLE/IN