MANAGING TEMPORARY TABLESPACE IN ORACLE 10G
Checking which Temporary tablespace currently in use
select PROPERTY_NAME,PROPERTY_VALUE from database_properties
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
Adding a tempfile to an Temporary tablespace
ALTER TABLESPACE TEMP1
ADD TEMPFILE 'D:/oracle/oradata/temp1a.dbf' SIZE 10M AUTOEXTEND ON;
Dropping an Temporary tablespace
DROP TABLESPACE TEMP1;
We cannot drop the temporary tablespace that currently in use.
Create new temporary tablespace, and alter system to new temporary tablespace
Creating new temporary tablespace
CREATE temporary TABLESPACE temp2
TEMPFILE 'D:/oracle/oradata/temp2.dbf' SIZE 10M AUTOEXTEND ON;
Changing the default temporary tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
