Oracle: Replace/reset Temporary TABLESPACE

Oracle-replace-temporary-tablespace.sql

/**
 * I found after running a particular performance test that Oracle started hanging,
 * and I noticed that the drive space was full. I eventually noticed that the TEMP
 * tablespace had expanded to fill all the avlailable drive space. I found the following
 * set of commands online that allowed me to drop the bloated tablespace and recreate
 * a new one.
 * @author John A. Marsh (with lots of help from the Internet)
 * @since 2014-08-01
 */

CREATE TEMPORARY tablespace temp2 tempfile '/home/oracle/app/oracle/oradata/orcl/temp02.dbf' SIZE 100m autoextend ON NEXT 100m maxsize 2000m;
ALTER DATABASE DEFAULT TEMPORARY tablespace temp2;
ALTER DATABASE tempfile '/home/oracle/app/oracle/oradata/orcl/temp01.dbf' DROP including datafiles;
ALTER tablespace temp ADD tempfile '/home/oracle/app/oracle/oradata/orcl/temp01.dbf' SIZE 1000m autoextend ON NEXT 100m maxsize 2000m;
ALTER DATABASE DEFAULT TEMPORARY tablespace temp;
DROP tablespace temp2 including contents AND datafiles;