Re: Oracle 9.2.0.1.0 Undo Management?

From: Jackie Chang <no_at_mail.com.tw>
Date: Sun, 19 Jun 2005 19:40:24 +0800
Message-ID: <d93ln6$9b1$1@news.seed.net.tw>

I retry again....

SHOW PARAMETER UNDO

NAME                 TYPE    VALUE
-------------------- ------- ---------
undo_management      string  AUTO
undo_retention       integer 5
undo_suppress_errors boolean FALSE
undo_tablespace      string  UNDOTBS1

==================== CREATE ====================

CONN TESTUNDO/PASS_at_ORA92
CREATE TABLE SOMETABLE
(
  FIELD1 VARCHAR2(10),
  FIELD2 VARCHAR2(10)
);

BEGIN
  FOR I IN 1.. 10000 LOOP
    INSERT INTO SOMETABLE VALUES('1','2');   END LOOP;
END;
/

COMMIT;

• TEST SQL =================== CONN TESTUNDO/PASS_at_ORA92 -- START TIME UPDATE SOMETABLE SET FIELD1='1234567890', FIELD2='123567890'; ROLLBACK; -- END TIME SELECT ( T.BYTES-SUM(F.BYTES) )/1024/1024 "USED(M)" FROM DBA_DATA_FILES T, DBA_FREE_SPACE F WHERE T.TABLESPACE_NAME='UNDOTBS1' AND F.TABLESPACE_NAME='UNDOTBS1' GROUP BY T.BYTES
  /
  
  • UPDATE 10000 ROWS TEST RESULT ===

  START END USED(M)
-------- -------- -------

18:03:09 18:03:11 16.9375
18:03:55 18:03:56 17.1875
18:04:02 18:04:03 17.375
18:04:10 18:04:11 20.25
18:04:18 18:04:20 23.125
18:04:22 18:04:24 26
18:04:27 18:04:29 26
18:04:33 18:04:35 26
18:04:39 18:04:41 26.1875
18:04:48 18:04:50 26.375
18:04:56 18:04:58 26.375
18:05:06 18:05:07 26.375
18:05:14 18:05:16 26.375
18:05:20 18:05:22 26.375
18:05:27 18:05:29 26.375
18:05:31 18:05:33 26.625
18:05:37 18:05:39 27.625
18:05:40 18:05:41 27.625
18:05:45 18:05:47 27.625
18:05:48 18:05:49 27.625
18:05:51 18:05:53 27.5625
18:05:54 18:05:55 27.5625
18:05:56 18:05:58 27.5625
18:05:59 18:06:01 27.5625
18:06:03 18:06:05 27.5625
18:06:09 18:06:11 27.5625
18:14:07 18:14:08 28.5625
18:15:14 18:15:16 27.5625
18:39:36 18:39:38 27.5625
18:47:46 18:47:48 30.4375
18:47:51 18:47:52 30.4375
18:51:21 18:51:22 30.4375
18:57:10 18:57:12 30.625

That is my test. It reuse undo tablespace.

Jackie Chang

>> Oracle 9.2.0.1.0 undo tablespace extend managment use LOCAL. I think the
>> tablespace set to autoextend.
>> I use Oracle Enterprise Manager Console check it! And check UNDOTBS01.DBF
>> file size!
>>
>> Undo_retention to large(10800 9iR2 default), So Oracle does not reuse
>> undo
>> tablespace.
>> Thinks a lot!
>
> When you check it again (31st time), make sure you try it later than (the
> first transaction time + the undo retention duration).  Then tell us what
> has happened.
>
> -- 
> Hans Forbrich

Received on Sun Jun 19 2005 - 06:40:24 CDT