释放temp临时表空间

因磁盘空间不足，需要将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>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即可：

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

5.3、查看临时表空间上正在执行哪些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>drop tablespace temp including contents and datafiles;