Syntax to Find / Check / Change / Set Default Temporary Tablespace for Oracle 10g, 11g Database

advertisements

_____________________________________________________________________________________________________________________

Default Temporary tablespace Syntax

SQL> create temporary tablespace temp_tbs tempfile '/u01/app/oracle/oradata/qa01/temp_tbs_01' size 100m;

Tablespace created.

SQL> alter database default temporary tablespace temp_tbs;

Database altered.

SQL> select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';

DEFAULT_TEMP_TABLESPACE

TEMP_TBS

Name of default temporary tablespace

You cannot drop the default temporary tablespace of a database. If you do like that you will get the following error.

SQL> drop tablespace temp_tbs;

drop tablespace temp_tbs

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

_____________________________________________________________________________________________________________________

Archive