标签归档：impdp ORA-00942

impdp 创建index提示ORA-00942: table or view does not exist

发表于 2025 年 1 月 19 日由惜分飞

在好几次的expdp/impdp迁移数据的过程中都遇到了index创建的过程中提示表不存在的现象提示类似：
impdp-ora-00942

通过观察可以发现index和table不在同一个用户下面,猜测是由于index的用户本身没有访问表的权限,而是通过其他用户比如sys/system或者有dba权限等有访问表和对index所在用户写入数据的用户操作导致,通过试验重现了该现象
创建两个用户,一个有dba权限（用来存放表数据）【xff1】，一个是resource权限用来创建index【xff2】

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 11:36:07 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE USER XFF1 IDENTIFIED BY oracle ;

User created.

SQL> grant dba to xff1;

Grant succeeded.

SQL> CREATE USER XFF2 IDENTIFIED BY oracle ;

User created.

SQL> grant connect,resource to xff2;

Grant succeeded.

SQL> create table xff1.t_object as select * from dba_objects;

Table created.

SQL> create index xff2.i_object on xff1.t_object(object_id);

Index created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

使用expdp导出数据

C:\Users\XFF>expdp "'/ as sysdba'" dumpfile=xff.dmp schemas=xff1,xff2

Export: Release 11.2.0.4.0 - Production on Sun Jan 19 11:37:02 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" dumpfile=xff.dmp schemas=xff1,xff2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11 MB
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
. . exported "XFF1"."T_OBJECT"                           8.774 MB   90627 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
  C:\APP\XFF\ADMIN\ORCL\DPDUMP\XFF.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Sun Jan 19 11:37:05 2025 elapsed 0 00:00:02

使用impdp导入数据（把xff用户映射到u中）

C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff1:u1,xff2:u2

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 11:37:16 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=xff.dmp remap_schema=xff1:u1,xff2:u2
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/TABLE_DATA
. . imported "U1"."T_OBJECT"                             8.774 MB   90627 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE INDEX "U2"."I_OBJECT" ON "U1"."T_OBJECT" ("OBJECT_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 1
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"U2"."I_OBJECT" creation failed
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sun Jan 19 11:37:17 2025 elapsed 0 00:00:00

重现了ORA-39083 ORA-00942错误,根据经验确认可能是由于impdp导入的时候,创建index切换到当前index所属用户进行,而该用户没有访问需要创建index对应的表的权限导致,通过impdp sqlfile解析出阿里.sql文件进行分析,确认是该情况导致.
index