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