Jump to Navigation

285 - Resizing Temporary Tablespace

If you try to drop the temp tablespace, you get the error message as the following.

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

 

1. Create a second temp acheter viagra tablespace.

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/d01/oradata/testdb/temp2_01.dbf' SIZE 5M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

2. Default table space and drop the old one.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

3. Create a new temp tablespace.

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/d01/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

4. Change the default table space and drop the second temp.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

 



Main menu 2

Story | by Dr. Radut