Undo Tablespace growing even with retention = 0

From: Jagjeet Singh <jagjeet.malhi_at_gmail.com>
Date: 7 Oct 2006 03:16:49 -0700
Message-ID: <1160216209.893655.11770@i3g2000cwc.googlegroups.com>

Hi All,

Can you please help me to understand this. I have set created new undo tablespace of 4 mb on my test database.

I set undo_retention = 0 and was updateding a table with 1 records and commiting each time.
But still my undo tablespace is growing.

here is the test case

SQL>
SQL> r
  1 select file_name,round(bytes/1048576) size_mb , autoextensible   2* from dba_Data_files where tablespace_name = 'UNDO'

FILE_NAME                                                   SIZE_MB AUT

-------------------------------------------------------- ---------- ---

/TEST/datafile/o1_mf_undo_2lgqkhgv_.dbf                           4 YES

SQL>
SQL> create table t as select rownum a from dual;

Table created.

SQL> get und_test
  1 declare

  2  v_time     int;
  3  v_undo     int;

  4 begin
  5 select sum(bytes)/1048576 into v_undo from dba_segments where segment_type like '%UNDO%';
  6 ---
  7 dbms_output.put_line(' -- Before Starting Undo Size : '||v_undo );   8 execute immediate ' alter system set undo_retention=0 ';   9 ----
 10 v_time := dbms_utility.get_time ;
 11 while ( ((dbms_utility.get_time - v_time)/100) < 300 )  12 loop
 13 update t set a=a;
 14 commit;
 15 end loop;
 16 ----
 17 select sum(bytes)/1048576 into v_undo from dba_segments where segment_type like '%UNDO%';
 18 dbms_output.put_line(' -- Undo Size with undo_retention=0 : '||v_undo );
 19 ----
 20 ----- second time with undo_retention = 9000  21 execute immediate ' alter system set undo_retention=9000 ';  22 v_time := dbms_utility.get_time ;
 23 while ( ((dbms_utility.get_time - v_time)/100) < 300 )  24 loop
 25 update t set a=a;
 26 commit;
 27 end loop;
 28 ----
 29 select sum(bytes)/1048576 into v_undo from dba_segments where segment_type like '%UNDO%';
 30 dbms_output.put_line(' -- Undo Size with undo_retention=9000 : '||v_undo );
 31 ----
 32* end;
 33 /
-- Before Starting Undo Size : 1

• Undo Size with undo_retention=0 : 251
• Undo Size with undo_retention=9000 : 476

PL/SQL procedure successfully completed.

Using 10g on linux 4.0
Undo is LMT manual segment space management. No. of Transaction : I fixed the loop for 300 seconds.

This tablespace's size increased to 251 mb with undo_retention=0 and with
unto_retention=9000 it reach to 476 mb.

Regards,
Jagjeet Singh Received on Sat Oct 07 2006 - 05:16:49 CDT