Creating index in non existing schema results in ORA-1129 and not ORA-1918

xifenfei

Bug 17058847  Creating index in non existing schema results in ORA-1129 and not ORA-1918 This note gives a brief overview of bug 17058847.
The content was last updated on: 22-OCT-2013
Click here for details of each of the sections below.
Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.2
Versions confirmed as being affected	11.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

The fix for 17058847 is first included in

12.2 (Future Release)

Interim patches may be available for earlier versions - click here to check.

Symptoms:	Related To:
Error May Occur ORA-1129 / ORA-1918	(None Specified)

Description

Creating an index in a non-existing schema results inORA-1129 instead of ORA-1918. The existence of user is not checked before executing create index.  Workaround  None other than issue a valid SQL as it will error with or without this fix.

xifenfei

6:48:38 SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for HPUX: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

16:48:26 SQL> create index abc.ind_xifenfei on SERVICE.t_user(create_date);
create index abc.ind_xifenfei on SERVICE.t_user(create_date)
                                         *
ERROR at line 1:
ORA-01129: user's default or temporary tablespace does not exist


Elapsed: 00:00:00.02

16:49:05 SQL> select count(*) from dba_users where username='ABC';

  COUNT(*)
----------
         0

Elapsed: 00:00:00.03

parknkjun

SYS@test>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SYS@test>create index abc.indx_t2 on service.t2(id);
create index abc.indx_t2 on service.t2(id)
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SYS@test>select count(*) from dba_users where username='ABC';

  COUNT(*)
----------
         0

11.2.0.3没有这个bug？

xifenfei

parknkjun 发表于 2014-9-26 10:00
SYS@test>select * from v$version;
BANNER
----------------------------------------------------------- ...

service.t2(id)  你这个要真实存在的
abc.indx_t2 是不存在的