ORACLE TTS,XTTS 的坑 ORA-00942！！！

原创范计杰 2020-06-14

3148

在一次使用XTTS迁移时，最后导入元数据时遇到ORA-39083: Object type INDEX failed to create with error,ORA-00942: table or view does not exist,导致大量索引导入失败。导致最后的停机时间超过了预期！！！可见模拟迁移的重要性
好在索引与表未使用同一表空间，把索引表空间删掉，手工批量重建了索引。
事后分析可能的原因如下
1、用户创建索引后，收回了用户的表空间权限。
2、索引与表不在同一用户下。（经测试这是导致TTS时索引创建失败的原因）
后面再次测试，索引用户加上CREATE ANY INDEX后可以正常导入。
下面为测试过程

测试用户创建索引后，收回了用户的表空间权限。

SQL> create tablespace users2 datafile '/oracle/app/oracle/oradata/orcl11g/users201.dbf' reuse;

Tablespace created.


TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
SYSAUX                                510        484         26    95% YES |################### |
SYSTEM                                740        738          2   100% YES |####################|
TEMP                                   29         29          0   100% YES |####################|
UNDOTBS1                               75         21         54    28% YES |######              |
USERS                                   5          2          3    40% YES |########            |
USERS2                                 20          2         18    10% NO  |##                  |

6 rows selected.

SQL> 
SQL> 
SQL> create user u1 identified by u1;

User created.

SQL> grant unlimited tablespace to u1;

Grant succeeded.

SQL> create user u2 identified by u2;

User created.

SQL> grant unlimited tablespace to u2;

Grant succeeded.


SQL> create table u1.tab(id number,c varchar2(100))  tablespace users2;

Table created.

SQL> insert into u1.tab select rownum,'t'||rownum from dual connect by level<1000;

999 rows created.

--创建两个索引,一个与表在相同用户下，一个在不同用户下。

SQL>  create index u2.idx_tab on u1.tab(id) tablespace users2;

Index created.

SQL> create index u1.idx2_tab on u1.tab(c) tablespace users2;

Index created.


--收回表空间权限,怀疑与没有表空间权限相关
SQL> revoke unlimited tablespace from u1;

Revoke succeeded.

SQL> revoke unlimited tablespace from u2;

Revoke succeeded.

SQL> 


SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS2', TRUE);


PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> 


SQL> create directory ohome  as '/home/oracle';

Directory created.


SQL> alter tablespace users2 read only;

Tablespace altered.




SQL> alter tablespace users2 read only;

Tablespace altered.



expdp dbmt/dbmt directory=ohome dumpfile=tts2.dmp logfile=tts_exp2.log  transport_full_check=no \
transport_tablespaces=users2 exclude=TABLE_STATISTICS,INDEX_STATISTICS


[oracle@db1 ~]$ expdp dbmt/dbmt directory=ohome dumpfile=tts2.dmp logfile=tts_exp2.log   \
> transport_tablespaces=users2 exclude=TABLE_STATISTICS,INDEX_STATISTICS

Export: Release 11.2.0.4.0 - Production on Sun Jun 14 09:43:07 2020

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 "DBMT"."SYS_EXPORT_TRANSPORTABLE_01":  dbmt/******** directory=ohome dumpfile=tts2.dmp logfile=tts_exp2.log transport_tablespaces=users2 exclude=TABLE_STATISTICS,INDEX_STATISTICS 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "DBMT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBMT.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/tts2.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS2:
  /oracle/app/oracle/oradata/orcl11g/users201.dbf
Job "DBMT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jun 14 09:43:44 2020 elapsed 0 00:00:36



SQL> drop tablespace users2 including contents;


Tablespace dropped.


[oracle@db1 ~]$ impdp dbmt/dbmt directory=ohome logfile=tts_imp.log dumpfile=tts2.dmp \
> transport_datafiles='/oracle/app/oracle/oradata/orcl11g/users201.dbf'

Import: Release 11.2.0.4.0 - Production on Sun Jun 14 09:44:34 2020

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 "DBMT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "DBMT"."SYS_IMPORT_TRANSPORTABLE_01":  dbmt/******** directory=ohome logfile=tts_imp.log dumpfile=tts2.dmp transport_datafiles=/oracle/app/oracle/oradata/orcl11g/users201.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/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"."IDX_TAB" ON "U1"."TAB" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 5 SEG_BLOCK 138 OBJNO_REUSE 87618 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 "USERS2" PARALLEL 1 
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "DBMT"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at Sun Jun 14 09:44:39 2020 elapsed 0 00:00:04


--可以看到u1,u2两个用户都没有表空间的权限，与表在同一用户下索引导成功了，在不同用户下的索引导失败了。可能与表空间权限无关

SQL> @ind u1.tab
Display indexes where table or index name matches %u1.tab%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
U1                   TAB                            IDX2_TAB                          1 C


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANAL DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- --------- ------ ---------
U1                   TAB                            IDX2_TAB                       NORMAL     NO   VALID    NO   N     1          0             0          0          0 14-JUN-20 1      VISIBLE
SQL>

测试不收回 unlimited tablespace权限，索引与表不在同一用户下

SQL> grant unlimited tablespace to u2;

Grant succeeded.

SQL> grant unlimited tablespace to u1;

Grant succeeded.

SQL> create index u2.idx_tab on u1.tab(id) tablespace users2;

Index created.

SQL>  create index u1.idx2_tab on u1.tab(c) tablespace users2;
 create index u1.idx2_tab on u1.tab(c) tablespace users2
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> alter tablespace users2 read only;

Tablespace altered.


[oracle@db1 ~]$ rm /home/oracle/tts2.dmp
[oracle@db1 ~]$ expdp dbmt/dbmt directory=ohome dumpfile=tts2.dmp logfile=tts_exp2.log  transport_full_check=no \
> transport_tablespaces=users2 exclude=TABLE_STATISTICS,INDEX_STATISTICS

Export: Release 11.2.0.4.0 - Production on Sun Jun 14 09:49:21 2020

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 "DBMT"."SYS_EXPORT_TRANSPORTABLE_01":  dbmt/******** directory=ohome dumpfile=tts2.dmp logfile=tts_exp2.log transport_full_check=no transport_tablespaces=users2 exclude=TABLE_STATISTICS,INDEX_STATISTICS 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "DBMT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBMT.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/tts2.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS2:
  /oracle/app/oracle/oradata/orcl11g/users201.dbf
Job "DBMT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jun 14 09:49:58 2020 elapsed 0 00:00:36


SQL> drop tablespace users2 including contents;

Tablespace dropped.


[oracle@db1 ~]$ impdp dbmt/dbmt directory=ohome logfile=tts_imp.log dumpfile=tts2.dmp \
> transport_datafiles='/oracle/app/oracle/oradata/orcl11g/users201.dbf'

Import: Release 11.2.0.4.0 - Production on Sun Jun 14 09:50:44 2020

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 "DBMT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "DBMT"."SYS_IMPORT_TRANSPORTABLE_01":  dbmt/******** directory=ohome logfile=tts_imp.log dumpfile=tts2.dmp transport_datafiles=/oracle/app/oracle/oradata/orcl11g/users201.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/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"."IDX_TAB" ON "U1"."TAB" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 5 SEG_BLOCK 138 OBJNO_REUSE 87707 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 "USERS2" PARALLEL 1 
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "DBMT"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at Sun Jun 14 09:50:48 2020 elapsed 0 00:00:03

oracle

最后修改时间：2020-06-15 21:12:22

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ORACLE TTS,XTTS 的坑 ORA-00942！！！

测试用户创建索引后，收回了用户的表空间权限。

测试不收回 unlimited tablespace权限，索引与表不在同一用户下

评论