oracle 更换临时表空间

1. 查看临时表空间位置
   select tablespace_name,file_name,bytes/1024/1024/1024 Gb from dba_temp_files where tablespace_name='TEMP';
   
2. 创建新的临时表空间
create temporary tablespace tempa tempfile '/oradata/data/tempa1.dbf' size 10M  autoextend on next 500M maxsize 30G;
alter tablespace tempa add tempfile '/oradata/data/tempa2.dbf' size 10M  autoextend on next 500M maxsize 30G;


3. 切换新的临时表空间
 alter database default temporary tablespace tempa;


4. 重启数据库;
sql> shutdown immediate;
sql> startup


5. 检查新的临时表空间
 select tablespace_name,file_name,bytes/1024/1024/1024 Gb from dba_temp_files where tablespace_name='TEMPA';
 
6. 查看临时表空间
select c.tablespace_name,
       to_char(e.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb,
       to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') curr_Gb,
       to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb,
       to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb,
       to_char(d.bytes_used * 100 / c.bytes, '99,999.999') || '%' use
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         GROUP by tablespace_name) c,   
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         GROUP by tablespace_name) d,   
       (select tablespace_name, sum(maxbytes) bytes
          from dba_temp_files
         GROUP by tablespace_name) e       
 where c.tablespace_name = d.tablespace_name
   and c.tablespace_name = e.tablespace_name;

#这时看到临时表名称变成TEMPA，说明更新成功


7. 删除旧临时表空间所在的数据文件
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

如果没有删除物理文件，那么需要手动删除物理文件
rm -rf   /oradata/data/temp02.dbf
rm -rf   /u01/app/oracle/oradata/bdspoc/temp01.dbf