UNDO TABLESPACE AND PENDING OFFLINE STATUS

Undo tablespace concept was introduced in Oracle 9i version which stores before image of modified data in database .Undo data provides features like

1.Rollback of transactions,
2.Read consistency and
3.Flashback features.

Before undo tablespace concept we used to have Rollback segments which can provide rollback and read consistency . These rollback segments could not be used for flashback features. Rollback segments were complex  to use like we had to create them,give them user defined name and size, make them online manually.

In undo tablespace everything can be automatic. When we create undo tablespace then by default Oracle creates 10 undo segments and brings them online.

PENDING OFFLINE UNDO SEGMENTS 
 At a time we can have more than one Undo tablespace in our database but only one will be active (default) for entire database.All Undo segments of active undo tablespace will be online and all undo segments of nonactive undo tablespaces will offline.When we start any transaction then transaction is assigned any of the undo segments which has expired undo extents.Suppose we started any transaction and this transaction is using 7th undo segment in undo tablespace UNDOTBS. When transaction was going on then we switched our undo tablespace to new undo tablespace UNDO1. At this point all the undo segments of UNDOTBS undo tablespace will become offline except that which is holding current transaction,in our case it is 7th undo segment.Now all the undo segment of UNDO1 will become online.

SYS>>create undo tablespace undo1 datafile 'h:\oradata\ibm\undo1.dbf' size 5m;

Tablespace created.

SYS>>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
USERS
UNDO1

6 rows selected.

SYS>>sho parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS

SYS>>select usn,status from v$rollstat;

       USN STATUS
---------- ---------------
         0 ONLINE
         1 ONLINE
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE

11 rows selected.


From some other session

U1>>insert into emp select * from emp;

14 rows created.

From SYS session

SYS>>alter system set undo_tablespace=undo1;

System altered.

SYS>>sho parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900

undo_tablespace                      string      UNDO1

SYS>>select usn,status from v$rollstat;

       USN STATUS
---------- ---------------
         0 ONLINE

         7 PENDING OFFLINE

        11 ONLINE
        12 ONLINE
        13 ONLINE
        14 ONLINE
        15 ONLINE
        16 ONLINE
        17 ONLINE
        18 ONLINE
        19 ONLINE

       USN STATUS
---------- ---------------
        20 ONLINE

12 rows selected.