xifenfei 发表于 2014-9-22 16:45:18

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

Bug 17058847Creating 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 affectedVersions BELOW 12.2
Versions confirmed as being affected
[*]11.2.0.3

Platforms affectedGeneric (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)

DescriptionCreating 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.WorkaroundNone other than issue a valid SQL as it will error with or without this fix.

xifenfei 发表于 2014-9-22 16:47:21

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 发表于 2014-9-26 10:00:12

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 发表于 2014-9-29 16:53:55

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

service.t2(id)你这个要真实存在的
abc.indx_t2 是不存在的
页: [1]
查看完整版本: Creating index in non existing schema results in ORA-1129 and not ORA-1918