An ORA-01129 error is reported when creating index due to user error

Database version SQLselect * fromv $ version; BANNER ------------------------------------------------------------------------------ OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0

Database version SQL select * from v $ version; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionPL/SQL Release 11.2.0.4.0-Production

Database Version

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for HPUX: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

Error Reporting ORA-01129

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

Query table space information

SQL> select TABLESPACE_NAME,CONTENTS,STATUS from dba_tablespaces where TABLESPACE_NAME in ('SERVICE','TEMP');TABLESPACE_NAME                CONTENTS  STATUS------------------------------ --------- ---------SERVICE                        PERMANENT ONLINETEMP                           TEMPORARY ONLINE

Through analysis, it is proved that all related tablespaces exist. Further Check the SQL statement and find that the SERVICE is incorrectly written as SERIVCE AND THE SERIVCE user is checked.

SQL> select count(*) from dba_users where username='SERIVCE';  COUNT(*)----------         0

Through analysis, we can know that the index corresponding to the user does not exist, and thus the ORA-01129 error, according to the principle should report ORA-01918, rather than ORA-01129. querying MOS found Bug 17058847 Creating index in non existing schema results in ORA-1129 and not ORA-1918

• DBA_HIST_TBSPC_SPACE_USAGE: An error occurred while querying the undo tablespace.
• Why Temporary segments cannot be expanded in permanent tablespace
• Enterprise Manager Database Express 12c
• Inactive transaction branch wait event
• ORACLE 12C supports creating multiple indexes in the same column
• Record AUTO_SPACE_ADVISOR_JOB, causing load exception
• MOS again unreliable-ORA-27163: out of memory
• Datafile and tablespace of oracle

Original article address: the user error caused by index creation ORA-01129 error, thanks to the original author to share.