Undo Management

SQL> select name from V$tablespace;

SQL> select name from V$datafile;

SQL> create undo tablespace undotbs2

                datafie '/u02/app/oracle/crms/undotbs2.dbf' size 10m;

SQL> desc dba_tablespaces;

SQL> select tablespace_name,contents from dba_tablespaces;

SQL> desc dba_rollback_segs;

                When we create a undo tablespace by default it creates 10 segments and by default 10 will be online.

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SQL> show parameter undo_tablespace

                Only one undo tablespace will be online and the remaining tablespaces will be offline.

SQL> desc V$rollname

SQL> desc V$rollstat

SQL> select a.usn,a.xacts,b.name,a.status from V$rollstat a,V$rollname b where a.usn=b.usn;

SQL> save undo.sql

Connect to scott and insert some data so that we can see the segment which are filled.

SQL> conn scott/tiger

scott> insert into salgrade select * from salgrade;

SQL> @undo.sql

Checking for pending offline status :

We need to change the tablespace after inserting the data.

SQL> alter system set undo_tablespace=undotbs2;

SQL> @undo.sql

                Now one segment shows pending offline status as we changed the undo tablespace.

scott> commit;

SQL> @undo.sql

                The pending offline status will be changed to online if we commits but we need to wait for some time to see the status.