13 Replies Latest reply on May 31, 2013 5:01 AM by 1011607

    Help!!! Temporary tablespace issue

    bsac14
      Hi,

      10g

      I had 2 tablespaces temp1 and temp2

      temp1 was created as a normal tablespace as temporary.

      temp2 was created as a temporary tablespace.

      I queried and found out that the temporary tablespace for all the users are temp1(including system)

      and hence I saw the error ORA-25153: Temporary Tablespace is Empty

      So what I did is I drop the tablespace temp1 and tried assigning the users to temp2.

      I am getting error
      Now I am not able to create anything the database.

      Please see below example

      SQL> drop user test;

      User dropped.

      SQL> create user test identified by test;
      create user test identified by test
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00959: tablespace 'TEMP1' does not exist


      I did a restart of database but still the users are assigned to temp1 which is not there

      Please help !!!!!!!!!!!!!!!!!!