Creation of a TEMPORARY Tablespace
----------------------------------

SQL> create TEMPORARY tablespace temp_tempfile_local
2 TEMPFILE '/ora/V817/temp_temp.dbf' size 100M
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
----------------------------------
SQL> create tablespace TEMP_DATAFILE_DICT
2 datafile '/tcase/oradata/V901/temp_data.dbf' size 100M
3 TEMPORARY;
Tablespace created.


Tempfiles Removal
---------------------------

a. You cannot remove datafiles from a tablespace until you drop the tablespace.
b. You can remove tempfiles from temporary tablespaces and keep the logical structure empty.
=> in 8i:
SQL> alter tablespace TEMP_TEMPFILE_LOCAL add tempfile '/oradata/V817/temp_temp01.dbf';
SQL> alter database tempfile '/oradata/V817/temp_temp01.dbf' drop;
SQL> !rm /oradata/V817/temp_temp01.dbf

=> in 9i: you can use the new clause INCLUDING DATAFILES to remove OS files
SQL> alter database tempfile '/oradata/V901/temp_temp01.dbf' drop including datafiles;
[@more@] Note:160426.1

[NOTE:132663.1] ORA-03296 Resizing Temporary Locally Managed Tablespace
[NOTE:131769.1] ORA-03212 at Instance Startup
[NOTE:102339.1] Temporary Segments: What Happens When a Sort Occurs