Oracle-impdp 按照用户导出,导入后发现存在部分索引不存在，缺失索引是关联其它用户创建的？

 

疑问：Oracle-impdp 按照用户导出,导入后发现存在部分索引不存在，缺失索引是关联其它用户创建的？

？？？

如下测试一下：

11G 单实例测试了一下，数据泵按照用户导出，如果存在用户索引关联其它用户的表，不会被导入；

SQL> show user
USER is “SYS”
SQL> create table system.test1 tablespace users as select * from dba_objects;
SQL> grant select on system.test1 to test_usr;
SQL> create table system.test2 as select * from dba_objects;
SQL> grant select on system.test2 to test_usr;
SQL> select tablespace_name from dba_segments where owner=‘SYSTEM’ and segment_name=‘TEST2’;
TABLESPACE_NAME
SYSTEM

SQL> create index test_usr.ind_object_id on system.test1(object_id);
SQL> create index test_usr.ind_object_id2 on system.test2(object_id);

$ expdp ‘/ as sysdba’ directory=TMP dumpfile=test.dmp owner=TEST_USR CONTENT=METADATA_ONLY
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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
Master table “SYS”.“SYS_EXPORT_SCHEMA_01” successfully loaded/unloaded

$ impdp ‘/ as sysdba’ directory=TMP dumpfile=test.dmp sqlfile=tmp.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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

$ impdp ‘/ as sysdba’ directory=TMP dumpfile=test.dmp sqlfile=tmp1.sql INCLUDE=index

$ cat /tmp/tmp1.sql
– new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
– CONNECT TEST_USR
CREATE INDEX “TEST_USR”.“IND_C” ON “TEST_USR”.“PARTTAB001” (“COL001”)
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 “TEST_TBS” PARALLEL 1 ;

ALTER INDEX “TEST_USR”.“IND_C” NOPARALLEL;
CREATE INDEX “TEST_USR”.“A_ID_1” ON “TEST_USR”.“A” (“ID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE “TEST_TBS” PARALLEL 1 ;
ALTER INDEX “TEST_USR”.“A_ID_1” NOPARALLEL;