This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 5, 2009 1:22 PM by OrionNet RSS

ora 01129

672625 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    sorry, I create TEMP2 as TEMPORARY tablespace
  • 4. Re: ora 01129
    492514 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points