1) To
create new temporary tablespace
SQL>create
temporary tablespace temp1 tempfile ‘/u01/app/oradata/test/temp01.dbf’
size 20m;
2) To
resize temporary tablespace
Using
alter tablespace
SQL>alter
tablespace temp1 add tempfile ‘/u01/app/oradata/test/temp02.dbf’
size 20m resuse;
Using
alter database
SQL>alter
database tempfile ‘/u01/app/oradata/test/temp01.dbf’
resize 30m;
3) To
keep the temporary files offline and
online
SQL>alter
database tempfile ‘/u01/app/oradata/test/temp01.dbf’ offline;
SQL>alter
database tempfile ‘/u01/app/oradata/test/temp01.dbf’
online;
4) To
shrink the temporary tablespace
SQL>alter
tablespace temp1 shrink space keep 20m;
5) To
drop temporary files of temporary tablespace
SQL>alter
database tempfile ‘/u01/app/oradata/test/temp01.dbf’
drop including datafiles;
6) To
check the default temporary tablespace
SQL>select
property_name, property_value from database_properties where
property_name=’DEFAULT_TEMP_TABLESPACE’;
7) To
change the default temporary tablespace for the database
SQL>alter
database default temporary tablespace temp1;
8) To
create tablespace group
SQL>create
temporary tablespace temp2 tempfile ‘/u01/app/oradata/test/temp201.dbf’
size 20m tablespace group
ts_group1;
9) To
add temporary tablespace to tablespace group
SQL>alter
tablespace temp1 tablespace group ts_group1;
10) To
remove temporary tablespace from
tablespace group
SQL>alter tablespace temp1 tablespace group ‘’;
11) To assign tablespace group as default temporary
tablespace
SQL>alter
database default temporary tablespace ts_group1;