Friday, March 14, 2008

Recreate temp tablespace 10g

SQL> create temporary tablespace temp1 tempfile '/DATA/oradata/rdtwh/temp1.dbf' size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> CREATE SMALLFILE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/DATA/oradata/rdtwh/temp01.dbf' SIZE 1000M
3 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M
4 ,'/DATA/oradata/rdtwh/temp02.dbf' SIZE 1000M
5 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M
6 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

SQL>

3 comments:

  1. Heya і'm for the firѕt time heгe. I сame аcross thiѕ boaгd and Ι finԁ It
    reallу helρful & іt helрed me out a lot.
    I аm hoping to provide one thing baсκ and help others like yоu helрed mе.



    Αlso viѕit my ωeblog; UsłUgi Ddd KrakóW

    ReplyDelete
  2. Today i was looking for this method. Quite dificult to find for 10g as all articles are related to 11g. However tonight I will apply this on my database. One more question, What happened to the performance if I delete this temp file while session are alive?

    ReplyDelete
    Replies
    1. There is no performance problem , since you create a new TEMP tablespace for the DB.

      If dropping the old TEMP is hanging follow this post :
      http://agstamy.blogspot.be/2007/12/drop-temporary-tablespace-hang.html

      Delete