Applies To:
Oracle database-enterprise edition-version 8.1.5.0 and later
Information in this document applies to any platform.
GOAL
How to release Temp LOB Segment that have been created explicitly or implicitly by Oracle for intermediate stages of Databa SE processing.
Solution
Prior to 10.2.0.4
=============
Actually we have different kinds of temporary segments. Oracle often requires temporary work space for intermediate stages of database processing. There is different kinds of temporary segments in the database. Some of them is created explicitly by the users. The others is created and accessed for the user by the system.
Use the view V$temporary_lobs in conjunction with dba_segments or v$sort_segment to determine how much space is being used by temporary lobs. We can create an explicit temporary BLOB or CLOB and its corresponding index in the user ' s default tablespace calling DBMS _lob. Createtemporary procedure, and free them by calling Dbms_lob. Freetemporary.
When calling Dbms_lob. Createtemporary, temporary extents is allocated to store lob_data and one temporary extent to store lob_index in 8i. So, a total of three temporary extents is allocated in 8i. However, in 9i (Release 2) and up, only one temporary extent is allocated.
Dbms_lob. Createtemporary can used with limited success prior to 10.2.0.4.
The only true solution prior to 10.2.0.4 and the setting of the event (as discussed below) are to terminate the session tha T created the temporary LOB.
10.2.0.4 and above
===============
Approaches is available:
1-you can use Dbms_lob. Freetemporary where the LOB locator that were freed is marked as invalid.
Dbms_lob. Freetemporary frees space from temp tablespace and it was available to that same session, but the temp segment was not Relea Sed and made available to other sessions. If the session creates another temp lob after freetemporary, the space is reused by the session.
The space is not released until the session exits. That can easily leads to the ORA-1652 error when multiple concurrent sessions is doing a huge LOB operations and not Exitin G, thus the freed space by Dbms_lob. Freetemporary is only available within the calling session and not for the other sessions.
-- ========
--Session1
-- ========
--Sql*plus:release 10.2.0.4.0-production on Tue Apr 7 09:06:31 2009
--Copyright (c) 1982, Oracle. All rights Reserved.
--Connected to:
--Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production
--With the partitioning, OLAP, Data Mining and Real application testing options
Declare
CLB Clob;
CH VARCHAR2 (32767);
K number;
Begin
Dbms_lob.createtemporary (Clb,true,dbms_lob.call);
For I in 1..1500 loop
Ch:=lpad (' O ', 32767, ' Y ');
Dbms_lob.writeappend (Clb,length (CH), CH);
End Loop;
K:=dbms_lob.getlength (CLB);
Dbms_lob.freetemporary (CLB);
Dbms_output.put_line (' The CLOB length: ' | | k);
End
/
--PL/SQL procedure successfully completed.
Select U.tablespace, U.contents, U.segtype, U.extents, U.blocks, Round (((u.blocks*p.value)/1024/1024), 2) MB
From V$session S, v$sort_usage u, SYS. V_$system_parameter P
where s.saddr = U.session_addr
and UPPER (p.name) = ' db_block_size '
Order by MB DESC; Tablespace CONTENTS segtype Extents BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_index 1 128 1
TEMP Temporary Lob_index 1 1declare
CLB Clob;
CH VARCHAR2 (32767);
K number;
Begin
Dbms_lob.createtemporary (Clb,true,dbms_lob.call);
For I in 1..1500 loop
Ch:=lpad (' O ', 32767, ' Y ');
Dbms_lob.writeappend (Clb,length (CH), CH);
End Loop;
K:=dbms_lob.getlength (CLB);
Dbms_lob.freetemporary (CLB);
Dbms_output.put_line (' The CLOB length: ' | | k);
End
/
--PL/SQL procedure successfully completed.
Select U.tablespace, U.contents, U.segtype, U.extents, U.blocks, Round (((u.blocks*p.value)/1024/1024), 2) MB
From V$session S, v$sort_usage u, SYS. V_$system_parameter P
where s.saddr = U.session_addr
and UPPER (p.name) = ' db_block_size '
Order by MB DESC; Tablespace CONTENTS segtype Extents BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_index 1 128 1
TEMP temporary lob_index 1 128 1
<<<<<<<<<<<<< only 4 rows selected >>>>>>>>>> >>>>>>--=========
--Session2
-- =========
Declare
CLB Clob;
CH VARCHAR2 (32767);
K number;
Begin
Dbms_lob.createtemporary (Clb,true,dbms_lob.call);
For I in 1..1500 loop
Ch:=lpad (' O ', 32767, ' Y ');
Dbms_lob.writeappend (Clb,length (CH), CH);
End Loop;
K:=dbms_lob.getlength (CLB);
Dbms_lob.freetemporary (CLB);
Dbms_output.put_line (' The CLOB length: ' | | k);
End
/
--PL/SQL procedure successfully completed.
Select U.tablespace, U.contents, U.segtype, U.extents, U.blocks, Round (((u.blocks*p.value)/1024/1024), 2) MB
From V$session S, v$sort_usage u, SYS. V_$system_parameter P
where s.saddr = U.session_addr
and UPPER (p.name) = ' db_block_size '
Order by MB DESC; Tablespace CONTENTS segtype Extents BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_index 1 128 1
TEMP temporary lob_index 1 128 1
TEMP temporary lob_index 1 128 1
6 Rows selected.--When we disconnect session 2 now and run the same query from session 1 again we'll get 4 rows only
Select U.tablespace, U.contents, U.segtype, U.extents, U.blocks, Round (((u.blocks*p.value)/1024/1024), 2) MB
From V$session S, v$sort_usage u, SYS. V_$system_parameter P
where s.saddr = U.session_addr
and UPPER (p.name) = ' db_block_size '
Order by MB DESC; Tablespace CONTENTS segtype Extents BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_data 48 6144 48
TEMP temporary lob_index 1 128 1
TEMP temporary lob_index 1 128 1
2-exiting the session where is the TEMP segments would be freed completely.
10.2.0.4 and above
===============
In addition to the above approaches for 10.2.0.4 and above a new event introduced (event 60025) where when set if there AR e No active temp lobs in the session (Ie:both cache temp LOB and No-cache temp lobs used is zero) then the TEMP segment itself'll also is freed releasing the space for other sessions to use. Note: This is disabled by default. You can set this using ALTER system at the system level also.
Alter session SET Events ' 60025 Trace name context forever ';
How to Release the Temp LOB Space and Avoid hitting ORA-1652 (document ID 802897.1)
