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/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "AD"."ADTAB1" 6.781 KB 45 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/chh/ad.adtab1.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:22:14
注：上述导出操作如果由普通用户操作，那么必须赋予该用户exp_full_database权限，否则会触发如下错误
ORA-39165: Schema MNG was not found.
ORA-39168: Object path INDEX was not found.
#####在导入目标库之前先将impdp的结果输出到脚本文件
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log sqlfile=ad.adtab1.sql
---ad.adtab1.sql文件内容中已经包含了create index的步骤
。。。其它部分略
-- CONNECT MNG
CREATE INDEX "MNG"."IND_ADTAB1_UID" ON "AD"."ADTAB1" ("USER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
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" PARALLEL 1 ;