删除临时表空间释放空间

由于操作系统空间不足，客户需要清理删除临时表空间，测试环境模拟场景进行操作。

一、前期环境准备

SQL>  create temporary tablespace temp_b tempfile '/11.2.0.4/app/oracle/oradata/tt11204/temp_b.dbf' size 10m;
Tablespace created.
SQL> alter user yang temporary tablespace temp_b;

SQL>conn yang/yang
执行如下SQL报错，临时表空间不足

SQL> select * from dba_objects,dba_segments order by 1,2,3,4,5,6

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_B

select file_name,file_id,tablespace_name,bytes/1024/1024/1024,blocks,status,AUTOEXTENSIBLE from dba_temp_files

FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024 BLOCKS STATUS AUTOEX
------------------------------------------------------------ ---------- -------------------- -------------------- ---------- -------------- ------
/11.2.0.4/app/oracle/oradata/tt11204/temp01.dbf 1 TEMP .028320313 3712 ONLINE YES
/11.2.0.4/app/oracle/oradata/tt11204/temp_b.dbf 3 TEMP_B .009765625 1280 ONLINE NO

对临时表空间自扩展后

SQL> alter database tempfile '/11.2.0.4/app/oracle/oradata/tt11204/temp_b.dbf' AUTOEXTEND On;
再次执行
SQL> select * from dba_objects,dba_segments order by 1,2,3,4,5,6

修改用户默认表空间，与实际情况进行模拟还原
SQL> alter user yang temporary tablespace temp;

 二、问题现象准备

以下为实际操作步骤

查询临时表空间组 NULL
SQL> select * from dba_tablespace_groups;
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
TEMP 数据库默认临时表空间

SQL> select username,TEMPORARY_TABLESPACE from dba_users where ACCOUNT_STATUS='OPEN' order by 2;
USERNAME             TEMPORARY_TABLESPACE
-------------------- ------------------------------
SYSTEM               TEMP
SYS                  TEMP
TEST                 TEMP
TT                   TEMP
TESTA                TEMP
YANG                 TEMP
ZABBIX               TEMP
SCOTT                TEMP
8 rows selected.

SQL> select file_id,tablespace_name,bytes/1024/1024/1024 from dba_temp_files

FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024
---------- -------------------- --------------------
1 TEMP .028320313
2 TEMP_B .009765625   通过上述查询可以发现，数据库不存在临时表空间组，默认临时表空间是TEMP，而另一个表空间temp_b并无人使用，因此进行删除回收空间。

删除临时表空间一直hang住，也不报错ALERT无任何有效信息

SQL> drop tablespace temp_b including contents and datafiles;

三、问题处理

新开session 查询event
SQL>  select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from

 gv$session where status='ACTIVE' and username is not null;

INST_ID    SID SERIAL# USERNAME             STATUS     MACHINE              SQL_ID               EVENT                                s LAST_CALL_ET
------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------
      1     11   19289 SYS                  ACTIVE     enmo                 2ymxxw3mapxd9        SQL*Net message to client            5            0
      1     72     621 SYS                  ACTIVE     enmo                 9mzfur1h8f49p        enq: TS - contention              1203           77

可以发现等待事件

 enq: TS - contention 

SQL> select sid,username,event,sql_id,BLOCKING_SESSION from gv$session where sid=72;

SID USERNAME EVENT SQL_ID BLOCKING_SESSION
------ -------------------- ------------------------------ -------------------- ----------------
72 SYS enq: TS - contention 9mzfur1h8f49p 66

SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where sid=66;

INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET
------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------
1 66 1 ACTIVE enmo smon timer 828829 828830

Dropping Temporary Tablespace Hangs Up Due To TS Enqueue Contention (文档 ID 2299966.1)

当临时段存在被使用的情况时，临时段将被保护，无法被删除。

SQL>  select USERNAME,SESSION_ADDR,SQL_ID,TABLESPACE,CONTENTS,BLOCKS from v$sort_usage

USERNAME SESSION_ADDR SQL_ID TABLESPACE CONTENTS BLOCKS
-------------------- ---------------- -------------------------- --------
SYS 00000000F57220D0 c61dmq2mac2m9 TEMP TEMPORARY 128
YANG 00000000F57D9550 c5zk3pjstds2b TEMP_B TEMPORARY 17152

找到使用临时段的session kill

问题处理。

SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where saddr='&SESSION_ADDR ';

SQL>alter system kill session 'sid,serial#' immediate;