Useful shortcuts for vi editor

Sunday, 19 October 2014

How to define and drop temporary tablespace?

Please execute the commands as "sys";

SQL> CREATE TEMPORARY TABLESPACE ALPER_TS_TEMP TEMPFILE 
 'C:\tablespaces\data_01.dbf' 
 SIZE 8000M 
 AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

To alter the tablespace:
SQL> ALTER TABLESPACE ALPER_TS_TEMP ADD TEMPFILE 'C:\tablespaces\data_temp_new_02.dbf' size 128m;

To drop the tablespace:
SQL> DROP TABLESPACE ALPER_TS_TEMP INCLUDING CONTENTS AND DATAFILES;

Used Oracle Version and OS: Oracle XE and Win7

Ref: http://onlineappsdba.com/index.php/2008/02/08/tablespace-and-datafiles-in-oracle-database-back-to-basics/