前言

因生产环境磁盘空间不足，影响了业务，短时间内无法扩容磁盘，经过排查发现，可以释放temp临时表空间来临时释放部分空间。

本文记录了如何释放临时表空间的详细操作步骤。


1、查看旧的临时表空间

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

1

2

3

4

5

6


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表空间

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

1

2


SQL>create temporary tablespace temp1 tempfile ‘+DATA' size 100m autoextend on; 

Tablespace created. 



3、修改默认临时表空间

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

1

2


SQL>alter database default temporary tablespace temp1; 

Database altered.



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

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

1

2

3

4

5

6


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查询哪些会话在占用旧的临时表空间

1

2

3

4

5

6

7

8

9

10

11

12

13

14


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会话。

1

2


SQL>select * from v$session where sid=‘1042'; 

SQL>alter system kill session ‘1042,48682';



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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15


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语句确认旧的临时表空间上没有会话和事物之后，就可以删除旧的临时表空间了。

1


SQL>drop tablespace temp including contents and datafiles;