13 Replies Latest reply: May 31, 2013 12:01 AM by 1011607 RSS

    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 !!!!!!!!!!!!!!!!!!
        • 1. Re: Help!!! Temporary tablespace issue
          bsac14
          Cannot create anything in the database due to ORA-00959: tablespace 'TEMP1' does not exist
          • 2. Re: Help!!! Temporary tablespace issue
            Iordan Iotzov
            Did you change the database default TEMP tablespace?
            ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2 ;
            Iordan Iotzov
            • 3. Re: Help!!! Temporary tablespace issue
              bsac14
              SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2
              2 /
              ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2
              *
              ERROR at line 1:
              ORA-12907: tablespace TEMP2 is already the default temporary tablespace
              • 4. Re: Help!!! Temporary tablespace issue
                rarain
                Hi,

                Please post the output of following query:-


                SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

                select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by file_name;

                select distinct(temporary_tablespace) from dba_users;

                Thanks
                • 5. Re: Help!!! Temporary tablespace issue
                  bsac14
                  PROPERTY_NAME
                  ------------------------------
                  PROPERTY_VALUE
                  --------------------------------------------------------------------------------
                  DESCRIPTION
                  --------------------------------------------------------------------------------
                  DEFAULT_TEMP_TABLESPACE
                  TEMP2
                  Name of default temporary tablespace


                  SQL> SQL>
                  PROPERTY_NAME
                  ------------------------------
                  PROPERTY_VALUE
                  --------------------------------------------------------------------------------
                  DESCRIPTION
                  --------------------------------------------------------------------------------
                  DEFAULT_TEMP_TABLESPACE
                  TEMP2
                  Name of default temporary tablespace

                  ====================================================
                  SQL> SQL>
                  TABLESPACE_NAME
                  ------------------------------
                  FILE_NAME
                  --------------------------------------------------------------------------------
                  BYTES/1024/1024 MAXBYTES/1024/1024 AUT
                  --------------- ------------------ ---
                  TEMP2
                  /db08/oracle/bricry1/TEMP02.DBF
                  500 0 NO

                  TEMP2
                  /db08/oracle/bricry1/TEMP02_2.DBF
                  500 0 NO

                  TABLESPACE_NAME
                  ------------------------------
                  FILE_NAME
                  --------------------------------------------------------------------------------
                  BYTES/1024/1024 MAXBYTES/1024/1024 AUT
                  --------------- ------------------ ---

                  TIVOLIORTEMPTS
                  /db08/oracle/bricry1/tivoliortempts_01.dbf
                  25 0 NO

                  =========================================
                  SQL> SQL>
                  TEMPORARY_TABLESPACE
                  ------------------------------
                  TIVOLIORTEMPTS
                  TEMP2
                  TEMP1

                  SQL> SQL>
                  SQL>
                  • 6. Re: Help!!! Temporary tablespace issue
                    rarain
                    Hi,

                    Only those users facing error to whom you have assigned TEMP1 temporary tablespace as there is no such temporary tablespace exist in your database. Do one thing change temporary tablespace for those users from TEMP1 to TEMP2 by following the below steps.

                    Step 1> Login as sys users and generate the below alter user script to change temporary tablespace:-

                    SQL> spool alter_user

                    SQL> select 'alter user ' || username || ' temporary tablespace temp2;' from dba_users where temporary_tablespace='TEMP1';

                    SQL> @alter_user.lst

                    Thanks
                    • 7. Re: Help!!! Temporary tablespace issue
                      bsac14
                      I am not able to create or alter anything in the database
                      as sys and system have default tablespace as temp1
                      • 8. Re: Help!!! Temporary tablespace issue
                        onedbguru
                        default TEMP tablespace or default tablespace?? two different things..

                        what error message do you get when you try to change it?
                        • 9. Re: Help!!! Temporary tablespace issue
                          bsac14
                          Hi,

                          It is default temporary tablespace
                          • 10. Re: Help!!! Temporary tablespace issue
                            Hemant K Chitale
                            Have you tried
                            ALTER USER SYSTEM TEMPORARY TABLESPACE TEMP2;
                            ?

                            Hemant K Chitale
                            • 11. Re: Help!!! Temporary tablespace issue
                              onedbguru
                              You did not answer my other question. What errors do you get???

                              Are you SYS or SYSTEM?
                              • 12. Re: Help!!! Temporary tablespace issue
                                bsac14
                                SQL> SQL> SQL> show user
                                USER is "SYS"
                                SQL> ALTER USER SYSTEM TEMPORARY TABLESPACE TEMP2;
                                ALTER USER SYSTEM TEMPORARY TABLESPACE TEMP2
                                *
                                ERROR at line 1:
                                ORA-00604: error occurred at recursive SQL level 1
                                ORA-00959: tablespace 'TEMP1' does not exist



                                Any command I give results in this error

                                Is my database corrupted ?
                                • 13. Re: Help!!! Temporary tablespace issue
                                  1011607
                                  If tempfile is missing, on subsequent reboot of db, oracle create the missing tempfile. Or u can do one thing set default temporary tablespace to temp2.
                                  U can identify the default temporary tablespace with database_properties view.