如何优雅的删除Oracle数据库临时表空间？

前言
因生产环境磁盘空间不足，影响了业务，短时间内无法扩容磁盘，经过排查发现，可以释放temp临时表空间来临时释放部分空间。
本文记录了如何释放临时表空间的详细操作步骤。

1、查看旧的临时表空间

首先查看旧的临时表空间的空间占用情况

SQL>select tablespace_name,tablespace_size/1024/1024, allocated_space/1024/1024,free_space/1024/1024 from dba_temp_free_space;
    TABLESPACE_NAME      TABLESPACE_SIZE/1024/1024 ALLOCATED_SPACE/1024/1024 FREE_SPACE/1024/1024
    -------------------- ------------------------- ------------------------- --------------------
    TEMP  

                               23069                     23069                23067

2、新建temp表空间

创建新的临时表空间，用于替换旧的临时表空间

SQL>create temporary tablespace temp1 tempfile ‘+DATA’ size 100m autoextend on;  
Tablespace created.  

3、修改默认临时表空间

将新的临时表空间设置为默认的临时表空间

SQL>alter database default temporary tablespace temp1;  
Database altered. 

4、查看新建的临时表空间使用情况

新的临时表空间被设置为默认临时表空间后，新的临时数据会写到新的临时表空间中，下面查看新建的临时标快的空间占用情况

SQL>select tablespace_name,tablespace_size/1024/1024 total, allocated_space/1024/1024 allocated ,free_space/1024/1024 free from dba_temp_free_space;

tablespace_name       total                       allocated                   free
-------------------- ------------------------- ------------------------- --------------------
TEMP                     23069                     23069                23067
TEMP1                    100                         3                   99

5、查看临时表空间使用情况

5.1、查看谁在使用临时表空间

下面的SQL查询哪些会话在占用旧的临时表空间

SQL>Col PROGRAM for a20
SQL>Col MACHINE for a20
SQL>col username for a15
SQL>SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr order by 7,1;

5.2、然后查看用户的状态是active还是inactive，如果是inactive，直接kill即可

如果旧的临时表空间的会话是active，需要谨慎，不能直接kill，需要等待会话消失或者变成inactive状态。如果是inactive状态，可以直接kill会话。

SQL>select * from v$session where sid=‘1042’;  
SQL>alter system kill session ‘1042,48682’;

5.3、查看临时表空间上正在执行哪些SQL

上面的SQL查看哪些会话运行在旧的临时表空间， 下面的SQL将查询active会话中，正在执行的SQL语句。以此来判断是否是关键的事物。

SQL>Col tablespace name for a20  
SQL>SELECT se.username,  
se.sid,  
se.serial#,  
su.extents,  
su.blocks \* to\_number(rtrim(p.value)) AS Space,  
tablespace,  
segtype,  
sql\_text  
FROM v$sort\_usage su,v$parameter p ,v$session se,v$sql s  
WHERE p.NAME = ‘db\_block\_size’  
AND su.session\_addr = se.saddr  
AND s.hash\_value = su.sqlhash  
AND s.address = su.sqladdr  
ORDER BY se.username ,se.sid;

6、确定旧的临时表空间没有在被使用，直接删除即可

通过上面的两个SQL语句确认旧的临时表空间上没有会话和事物之后，就可以删除旧的临时表空间了。

SQL>drop tablespace temp including contents and datafiles;