oracle 臨時表間重建的方法

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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-715281/，如需轉載，請註明出處，否則將追究法律責任。