REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM     c_tabsp_tmp_rebuild.sql
REM
REM <SQLDIR_GRP>TABSP REVERSE</SQLDIR_GRP>
REM
REM SQLDIR Group Descriptions:
REM	TABSP		- 	Tablespace
REM	REVERSE		- 	Reverse Engineering
REM
REM Author:
REM	Vitaliy Mogilevskiy vit100gain@earthlink.net
REM 
REM Purpose:
REM     <SQLDIR_TXT>
REM	This script rebuilds any LOCAL TEMPORARY tablespace
REM     </SQLDIR_TXT>
REM
REM Usage:
REM     c_tabsp_tmp_rebuild.sql
REM 
REM Example:
REM     c_tabsp_tmp_rebuild.sql
REM
REM
REM History:
REM	01-08-2002	Vitaliy Mogilevskiy	Created
REM
REM


set pages 100
set head on
set lines 300
set trims on
col file_name format a45

select tablespace_name, file_name, bytes/1024 Kbytes from  dba_temp_files;



accept ts_name prompt "Enter TEMPORARY tablespace name to rebuild: "

set pages 0
set verify off

prompt --:
prompt --:
prompt --Run the following in SQL*Plus: 
select 'DROP TABLESPACE '||tablespace_name||';'
from dba_tablespaces
where tablespace_name = '&&ts_name'
and   contents = 'TEMPORARY'
and   extent_management = 'LOCAL';

prompt --:
prompt --:
prompt --Run the following on Unix:
select 'rm -i '||file_name
from  dba_temp_files
where tablespace_name = '&&ts_name';


prompt --:
prompt --:
prompt --Run the following from SQL*plus:
SELECT 
   DECODE(ROWNUM,1,'CREATE TEMPORARY TABLESPACE '||tmpts.tablespace_name,'')||
   DECODE(ROWNUM,1,' TEMPFILE '||chr(39)||tmpf.file_name||chr(39)||' SIZE '||tmpf.bytes/1024||'K '||DECODE(tmpf.file_id,max_file.file_id,'',','),
                   chr(39)||tmpf.file_name||chr(39)||' SIZE '||tmpf.bytes/1024||'K '||DECODE(tmpf.file_id,max_file.file_id,'',','))||
   DECODE(tmpf.file_id,max_file.file_id,' EXTENT MANAGEMENT LOCAL UNIFORM SIZE '||tmpts.INITIAL_EXTENT/1024||'K;','')
FROM dba_temp_files tmpf
,    (SELECT max(file_id) file_id
      ,      tablespace_name
      FROM   dba_temp_files
      GROUP BY tablespace_name) max_file
,    dba_tablespaces tmpts
WHERE tmpts.contents = 'TEMPORARY'
AND   tmpts.extent_management = 'LOCAL'
AND   tmpts.tablespace_name = '&&ts_name'
AND   tmpts.tablespace_name = max_file.tablespace_name
AND   tmpts.tablespace_name = tmpf.tablespace_name;