oracle 提示存在lob,案例:Oracle数据库临时文件特别大 commit后lob字段使用临时表空...

测试结果$ ./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

Oracle研究中心解决方案

通过上面的试验证明我们可以通过设置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

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址