详解如何删除Oracle数据库临时表空间

目录

• 前言
• 1、查看旧的临时表空间
• 2、新建temp表空间
• 3、修改默认临时表空间
• 4、查看新建的临时表空间使用情况
• 5、查看临时表空间使用情况
• 5.1、查看谁在使用临时表空间
• 5.2、然后查看用户的状态是active还是inactive，如果是inactive，直接kill即可
• 5.3、查看临时表空间上正在执行哪些SQL
• 6、确定旧的临时表空间没有在被使用，直接删除即可

前言

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

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

1、查看旧的临时表空间

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

SQL>select tablespace_name,tablespace_size/1024/1024, allocated_space/1024/1024,free_space/1024/1024 from dba_temp_fpythonree_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  
AphpND s.hash\_value = su.sqlhash  
AND s.address = su.sqladdr  
ORDER BY se.username ,se.sid;

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

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

SQL>drop tablespace temp including contents aandroidnd datafiles;

到此这篇关于详解如何删除oracle数据库临时表空间的文章就介绍到这了,更多相关删除Oracle临时表空间内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！