1 2 Previous Next 16 Replies Latest reply: Mar 5, 2009 3:22 PM by OrionNet RSS

    ora 01129

    672625
      Hola, I have this problem
      The tables can be created correctly, but When I try to create a index, I get the error:

      ORA-01129 user's default or temporary tablespace does not exist


      When using the statement
      SELECT tablespace_name from dba_tablespaces;

      can see the 'USERS' tablespace

      TABLESPACE_NAME
      ---------------------
      SYSTEM
      UNDOTBS1
      SYSAUX
      TEMP
      USERS
      EXAMPLE
      TESTSPACE
      TESTTEMPORAL

      At the first time I used the USERS tablespace, then I created new tablespaces, and the problem is the same. The tablespace was create in the Enterprise manager console.

      And another important thing, it work fine the last week......I did not make changes in the database.

      Thanks
        • 1. Re: ora 01129
          OrionNet
          Hello,

          You are missing temp tablespace , so create temp tablespace and assign it as default temp tablespace.
           CREATE TEMPORARY TABLESPACE TEMP  TEMPFILE '/path_to_temp/temp01.dbf' SIZE 500M REUSE  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
          
           ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
          Regards

          Edited by: OrionNet on Mar 4, 2009 3:05 PM
          • 2. Re: ora 01129
            672625
            Hi, When I run the statement get the error:

            ORA-01543: el tablespace 'TEMP' ya existe

            So, I created TEMP2, as default, and asined it to my user, but the problem ora 01129 persist

            Thanks
            • 3. Re: ora 01129
              672625
              sorry, I create TEMP2 as TEMPORARY tablespace
              • 4. Re: ora 01129
                492514
                and what is the output of
                select default_tablespace,temporary_tablespace from user_users
                Does the result fit to your existing (temp) tablespace?
                • 5. Re: ora 01129
                  OrionNet
                  Hello,

                  You need to run following to set default temp tablespace; you can have temp2 or tep as default temp tablespace.
                  So change accordingly and logout and logack in(just for sanity)
                   
                  
                  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
                  
                  or 
                  
                  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
                  Regards

                  Edited by: OrionNet on Mar 4, 2009 3:46 PM
                  • 6. Re: ora 01129
                    672625
                    Hi, the statement send:

                    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
                    ------------------------------ ----------------------
                    USERS TEMP2

                    Because I used in my last test TEMP2 like default temporary tablespace

                    I put TEMP like default again
                    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
                    ------------------------------ ------------------------
                    USERS TEMP

                    And tried to create the index after change the user tablespace to TEMP.......the problem persist

                    Thanks
                    • 7. Re: ora 01129
                      OrionNet
                      Hello,
                      very interesting, how exactly (statement) you created tablepsace and how did you assign default temp tablespace?

                      1. You can try entire process again from a different session and monitor your alert.log file for any erorrs
                      2. make sure you don't have any storage issues? file system full?

                      Do this as sysdba.
                       sqlplus "/as sysdba"
                       or
                       sqlplus '/as sysdba'
                      
                        a. drop one of temporary tablespace ;
                        b. create a new one with the same name or new name; 
                        c. assign it as default temp tablespace with the command I posted in my previous 
                        d. login from user session and run your query.
                      Regards
                      • 8. Re: ora 01129
                        672625
                        Hi, The problem persist, I followed those steps (in sqlplus). The server don't have error (windows error). where can I see the oracle log??

                        I was create the user and tablespace by Enterprise manager console

                        Now, I make a test in the customer server, the database is empty, it is with the default installation and configuration, and the problem occurre too......


                        I used the installed for OBI SE One, it install the database, OWB and OBI


                        Thanks
                        • 9. Re: ora 01129
                          672625
                          Hi,

                          I createded one index, directly in the enterprise manager console,

                          but I recibe the same error (ora 01129) when try to create in sqlplus:
                          CREATE UNIQUE INDEX XPKStest1.T_Cliente ON test1.T_Cliente
                          (Numero_BP ASC);

                          I need to use sqlplus...

                          Thanks
                          • 10. Re: ora 01129
                            rgoold
                            I think that you have schema and object reversed in the create index statement and that the reference to the non-existant schema XPKStest1 is generating a mis-leading error.

                            Instead of:

                            CREATE UNIQUE INDEX XPKStest1.T_Cliente ON test1.T_Cliente
                            (Numero_BP ASC);

                            I think you mean:

                            CREATE UNIQUE INDEX T_Cliente.XPKStest1 ON T_Cliente.test1
                            (Numero_BP ASC);
                            • 11. Re: ora 01129
                              672625
                              Hi, the schema is TEST1 and the table T_Cliente, if try the statement
                              CREATE UNIQUE INDEX T_Cliente.XPKStest1 ON T_Cliente.test1 (Numero_BP ASC);

                              get the error ora-00942 table or view does not exist

                              Thanks
                              • 12. Re: ora 01129
                                OrionNet
                                Hello,

                                You said
                                the schema is TEST1 and the table T_Cliente, if try the statement

                                I think you got the syntax incorrect, here is the correct syntax
                                 CREATE UNIQUE INDEX t_cliente.xpkstest1
                                   ON test1.t_cliente (numero_bp ASC);
                                   
                                Regards

                                Edited by: OrionNet on Mar 5, 2009 3:58 PM
                                • 13. Re: ora 01129
                                  672625
                                  Hi, the statement
                                  CREATE UNIQUE INDEX t_cliente.xpkstest1
                                  ON test1.t_cliente (numero_bp ASC);


                                  Send the error ora-01129

                                  Is there some file or table to view the configuration of the tablespace
                                  where is the log file?

                                  Thanks
                                  • 14. Re: ora 01129
                                    OrionNet
                                    Hello,

                                    h5. Here is your problem, we have table_name in place of schema name after create unique index and that's why you were getting default temp tablespace error.

                                    Hope this helps
                                    CREATE UNIQUE INDEX test1.xpkstest1
                                     ON test1.t_cliente (numero_bp ASC);
                                       
                                    Regards

                                    Edited by: OrionNet on Mar 5, 2009 4:12 PM
                                    1 2 Previous Next