Wednesday, September 26, 2007

Default Temporary Tablespace

When I was teaching about temporary tablespace, I forgot about how to know where is the default temporary tablespace that is used in current Oracle database? After surfed arround the digital world, I have found some articles that give me a new knowledge. Here are the practice :

prompt> sqlplus /nolog
SQL> conn / as sysdba
SQL> col property_value format a20
SQL> select property_name, property_value from database_properties;

From above query, we can get all of database properties include default_temp_tablespace. If you want to change the default temporary tablespace, you can take a little steps below (my assumption is your database use OMF):

SQL> conn / as sysdba
SQL> create temporary tablespace temp1 datafile 20M;
SQL> alter database default temporary tablespace temp1;
SQL> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

If you want to know about the temporary status and space information, you can query with this:
select * from v$tempfile;

No comments: