CODEWITHSUNDEEP
oracle-databaseoracle10g
Anand Yadav
Anand Yadav

Reputation: 65

how to cleanup the temp tablespace in oracle 10g server , Please provide the steps for linux plateform

I want to clean up the Temp table space which have datafiles temp01.dbf and temp02.dbf, so please suggest me should I drop temp01.dbf file or drop the temp tablespace. Datafiles of Temp tablespaces is given below

33G     temp01.dbf
1.5G    temp02.dbf

Upvotes: 0

Views: 34137

Answers (2)

José Gabriel Zalazar
José Gabriel Zalazar

Reputation: 11

Thanks @anudeepks! Just wanna point something, in step 5 the sentence should be as follows:

 CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/temp/temp.dbf′ SIZE 2000M;

(Note that the filename has changed from temp01.dbf to temp.dbf). Otherwise we get an error because the file already exists, and also such file will be deleted on step 7.

Upvotes: 0

anudeepks
anudeepks

Reputation: 1132

  1. Create Temporary Tablespace Temp

       CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01.dbf′ SIZE 2000M ;

  2. Move Default Database temp tablespace

       ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

  3. Make sure No sessions are using your Old Temp tablespace

       a.  Find Session Number from V$SORT_USAGE: 
  SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; 

   b.  Find Session ID from V$SESSION:

  If the resultset contains any rows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

  SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
  OR
  SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; 

   c.  Kill Session:

   Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

  4. Drop temp tablespace

       DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

  5. Recreate Tablespace Temp

       CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/temp/temp01.dbf′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

       ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

  1. Drop temporary for tablespace temp

      DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Upvotes: 11

Related Questions