Undo Tablespace

Undo Tablespace:

• Undo tablespace also contains old image (or) previous image (or) uncommitted transaction information.
• Every undo tablespace contain 10 undo segments.These undo segments dynamically switching one segment to another segment whenever first undo segment is full. This feature is available oracle 9i onwards
• These undo tablespace supports LMTS (Locally Managed Tablespace). It supports-undo retention time with guarantee (or) without guarantee.

With Guarantee :

            If undo tablespace is set with guarantee option even committed transactions are available in undo tbalespace till the undo retention time period specified.

With No Guarantee :

            By default undo tablespace is available With No Guarantee option.If the undo tablespace set with no guarantee option then committed transactions may or maynot available even though undo retention time period specified.

            It is based on the availability of undo segments. Undo tablespce provides more security and more performance compare to rollback tablespace.

Note :- if undo tablespace is offline then users cannot perform any DDL & DML operations.

How to create undo tablespace :

SQL> create undo tablespace undo_orcl datafile '/u01/oradata/orcl/undoorcl.dbf' size 10m;

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                             string      AUTO

undo_retention                                     integer     900

undo_tablespace                                string      UNDOTBS1

How to change the default undo tablespace :

SQL> alter system set undo_tablespace = undo_orcl;

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                            string      AUTO

undo_retention                                     integer     900

undo_tablespace                                string      UNDO_ORCL

SQL> select tablespace_name,status from dba_tablespaces;

Default undo tablespace segments always must be online. And other undo tablespace segments are available in offline.

SQL>select tablespace_name,segment_name,status from dba_rollback_segs;

How to set undo retention time :

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                             string      AUTO

undo_retention                                    integer     900

undo_tablespace                                 string      UNDO_ORCL

 SQL> alter system set undo_retention=400;

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                             string      AUTO

undo_retention                                      integer     400

undo_tablespace                                string      UNDO_ORCL

How to identify the undo tablespace with guarantee / without guarantee option:-

SQL>select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION

------------------------------ -----------

SYSTEM                                NOT APPLY

SYSAUX                                NOT APPLY

UNDOTBS1                        NOGUARANTEE

TEMPX                                   NOT APPLY

USERDATA                        NOT APPLY

TS1                                          NOT APPLY

TEMP_PROD                      NOT APPLY

TEMP_PROD2                    NOT APPLY

RBS_ORCL                         NOT APPLY

UNDO_ORCL                      NOGUARANTEE

10 rows selected.

How to set the undo tablespace with guarantee option :

SQL> alter tablespace undo_orcl retention guarantee;

SQL>  select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION

------------------------------ -----------

SYSTEM                                NOT APPLY

SYSAUX                                NOT APPLY

UNDOTBS1                       NOGUARANTEE

TEMPX                                  NOT APPLY

USERDATA                       NOT APPLY

TS1                                         NOT APPLY

TEMP_PROD                     NOT APPLY

TEMP_PROD2                   NOT APPLY

RBS_ORCL                        NOT APPLY

UNDO_ORCL                     GUARANTEE

10 rows selected.

SQL>alter tablespace undo_orcl retention noguarantee;

How to identify pending offline undo segments :

SQL>select tablespace_name,segment_name,status from dba_rollback_segs;

SQL> select usn,xacts,status from v$rollstat;

       USN      XACTS STATUS

---------- ---------- ---------------

         0          0 ONLINE

        11          0 ONLINE

        12          0 ONLINE

        13          0 ONLINE

        14          0 ONLINE

        15          0 ONLINE

        16          0 ONLINE

        17          0 ONLINE

        18          0 ONLINE

        19          0 ONLINE

        20          0 ONLINE

11 rows selected.

open other window

$sqlplus kishore/kishore

kishore@PROD>create table demo(sno number);

kishore@PROD>insert into demo values(3333);

goto previous window

SQL>select usn,xacts,status from v$rollstat;

SQL>alter system set undo_tablespace=undo_orcl;

SQL>select usn,xacts,status from v$rollstat;

                        then we can find 12^th undo segment becomes pending offline

goto kishore user

kishore@orcl>commit;

goto sys user

SQL>select usn,sacts,status from v$rollstat;

After some time pending offline undo segment will disappear.

How to identify deffered rollback segment :

            This segment is available dynamically in the system tablespace. It is useful to hold user transactions information temporarily when user tablespace become offline.

SQL>select tablespace_name,segment_type from dba_segments where segment_type like ‘%DEF%’;

goto other window

kishore@orcl>sqlplus kishore/kishore

kishore@orcl>insert into  demo values(444);

Goto sys user

SQL>alter tablespace user_orcl offline;

SQL>col segment_name for a20

SQL>select tablespace_name,segment_type where segment_type like ‘%DEF’;

SQL>alter tablespace user_orcl online;