測(cè)試結(jié)果$ ./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

--測(cè)試腳本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):都出現(xiàn)v$tempseg_usage中存在對(duì)應(yīng)記錄,而且提交后不能釋放Temp LOB space

2.With event 60025 set (before and after commit):都未現(xiàn)v$tempseg_usage中存在對(duì)應(yīng)記錄,證明提交后釋放Temp LOB space

Oracle研究中心解決方案

通過(guò)上面的試驗(yàn)證明我們可以通過(guò)設(shè)置event 60025來(lái)解決該版本的會(huì)話提交后Temp LOB space不能被回收的問(wèn)題.

我們可以通過(guò)在session級(jí)別使用”alter session set events ’60025 trace name context forever’;”來(lái)實(shí)現(xiàn)。如果想實(shí)現(xiàn)全庫(kù)級(jí)別的，但是因?yàn)閑vent 60025不能通過(guò)system設(shè)置生效,所以我們可以通過(guò)logon觸發(fā)器來(lái)實(shí)現(xiàn)該功能

create or replace trigger sys.login_db after logon on database

begin

execute immediate 'alter session set events ''60025 trace name context forever''';

end;

/

注意這個(gè)是ORCLE bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8]),從10.2.0.4開(kāi)始雖然已經(jīng)修復(fù)了該bug,但是默認(rèn)情況下:為了更加高效的利用temp,在session未斷開(kāi)前,不自動(dòng)釋放temp 空間,可以通過(guò)設(shè)置event 60025來(lái)強(qiáng)制會(huì)話在commit之后就立即釋放temp space

-----------------溫馨提示--------------------

操作有風(fēng)險(xiǎn)，動(dòng)手需謹(jǐn)慎

Oracle研究中心

http://www.oracleplus.net

本文由大師惜分飛原創(chuàng)分享，轉(zhuǎn)載請(qǐng)盡量保留本站網(wǎng)址

總結(jié)

以上是生活随笔為你收集整理的oracle 提示存在lob,案例:Oracle数据库临时文件特别大 commit后lob字段使用临时表空...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。