Re: Question on UNDO segment transition in an active database
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: lambu999@xxxxxxxxx
- Date: Wed, 20 Apr 2005 12:19:13 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
