commit后lob字段使用临时表空间未释放

临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百M甚至几个G的临时表空间不释放,随着时间的积累,会话占用的临时表空间还在继续增加,最终的现象是100G的数据文件,160G的临时表空间还在继续报临时表空间不足.查询v$sort_usage发现其SEGTYPE全部为LOB_DATA而CONTENTS为TEMPORARY,而且BLOCKS都很大,通过上面的信息大概分析,怀疑是因为数据库查询或者操作LOB类型时候使用了TEMPORARY,但是没有释放导致

相关版本信息

OS:AIX 6.1(64)
DB:10.2.0.5

测试案例证明

--执行查询脚本
$ more check.sql
connect / as sysdba
select * from v $tempseg_usage where username not in ( 'HDDS_CLPS_DTA' , 'FOGLIGHT' );
 
--测试脚本1
$ more test1.sh
sqlplus /nolog <<EOF
connect / as sysdba
drop user xifenfei cascade;
create user xifenfei identified by tc
default tablespace users temporary tablespace temp quota unlimited on users ;
grant connect,resource,alter session to xifenfei;
revoke unlimited tablespace from xifenfei;
 
 
connect xifenfei /tc
select to_nclob( 'a' ) from dual;
 
!sqlplus /nolog @check
 
commit;
 
!sqlplus /nolog @check
 
EOF
 
--测试脚本2
$ more test2.sh
sqlplus /nolog << EOF2
connect xifenfei /tc
alter session set events '60025 trace name context forever' ;
select to_nclob( 'a' ) from dual;
 
!sqlplus /nolog @check
 
commit;
 
!sqlplus /nolog @check
 
EOF2

测试结果

$ ./test1.sh
 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
SQL> Connected.
SQL> drop user xifenfei cascade
           *
ERROR at line 1:
ORA-01918: user 'XIFENFEI' does not exist
 
Grant succeeded.
 
SQL>
Revoke succeeded.
 
SQL> SQL> SQL> Connected.
SQL>
TO_NCLOB( 'A' )
--------------------------------------------------------------------------------
a
 
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected.
 
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
    EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
       10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
          1        128          1
 
 
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SQL>
Commit complete.
 
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected.
 
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
    EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
       10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
          1        128          1
 
 
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
--测试脚本2
$ ./test2.sh
 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
SQL> Connected.
SQL>
Session altered.
 
SQL>
TO_NCLOB( 'A' )
--------------------------------------------------------------------------------
a
 
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected.
 
no rows selected
 
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SQL>
Commit complete.
 
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected.
 
no rows selected
 
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.Without event 60025 set (before and after commit):都出现v$tempseg_usage中存在对应记录,而且提交后不能释放Temp LOB space
2.With event 60025 set (before and after commit):都未现v$tempseg_usage中存在对应记录,证明提交后释放Temp LOB space

解决方案
通过上面的试验证明我们可以通过设置event 60025来解决该版本的会话提交后Temp LOB space不能被回收的问题.
我们可以通过在session级别使用”alter session set events ’60025 trace name context forever’;”来实现。如果想实现全库级别的,但是因为event 60025不能通过system设置生效,所以我们可以通过logon触发器来实现该功能

create or replace trigger sys.login_db after logon on database
begin
execute immediate 'alter session set events ' '60025 trace name context forever' '' ;
end ;
/

注意这个是ORCLE bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8]),从10.2.0.4开始虽然已经修复了该bug,但是默认情况下:为了更加高效的利用temp,在session未断开前,不自动释放temp 空间,可以通过设置event 60025来强制会话在commit之后就立即释放temp space


文章来自:http://www.xifenfei.com/3915.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值