Re: Question on UNDO segment transition in an active database

The old transactions carry on with the old tablespace until they are finshed (however long that is)
New transactions use the new tablespace. Eg

Session 1

SQL> CREATE TABLE UNDO_TEST(C1 NUMBER); Table created.

SQL> INSERT INTO UNDO_TEST SELECT OBJECT_ID FROM ALL_OBJECTS; 51545 rows created.

SQL> SELECT TABLESPACE_NAME,STATUS,COUNT(*)  2 FROM DBA_UNDO_EXTENTS
 3 GROUP BY TABLESPACE_NAME,STATUS; TABLESPACE_NAME STATUS COUNT(*)
------------------------------ --------- ----------
UNDOTBS1 ACTIVE 1
UNDOTBS1 EXPIRED 593 2 rows selected.

Now in a second session

SQL> CREATE UNDO TABLESPACE UNDOTBS02
 2 DATAFILE 'E:\ORACLE\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 10M; Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02; System altered.

SQL> CREATE TABLE UNDO_TEST2(C1 NUMBER); Table created.

SQL> INSERT INTO UNDO_TEST2 SELECT OBJECT_ID FROM ALL_OBJECTS; 51546 rows created.

and back to the first session

SQL> / TABLESPACE_NAME STATUS COUNT(*)
------------------------------ --------- ----------
UNDOTBS1 ACTIVE 1
UNDOTBS1 EXPIRED 591
UNDOTBS1 UNEXPIRED 2
UNDOTBS02 ACTIVE 3
UNDOTBS02 EXPIRED 21 5 rows selected.

SQL> I don't have an explanation for the two UNEXPIRED segments that appeared in UNTOTBS01.
--

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 20 2005 - 07:23:25 CDT