Undo Tablespace in Oracle Database

First of all, check out your undo settings using this following SQL Command.


SQL> show parameter undo

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


You can create one or more undo tablespace but you must have only one active undo tablespace at a time. You can change your active undo tablespace or even retention period.


SQL> create undo tablespace undotbs2;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;
 
System altered.

SQL> alter system set undo_retention=600 scope=both;

System altered.


SQL> show parameter undo

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_management                      string      AUTO                           
undo_retention                       integer     600                            
undo_tablespace                      string      UNDOTBS2                      


However, retention period is not guaranteed by default. If Oracle Instance has long running transactions and undo space is not available, it starts replacing existing committed undo. we can set retention guarantee, in this scenario retention is guaranteed but transaction failure may occur in case of undo space shortages.


SQL> alter tablespace undotbs2 retention guarantee;

Tablespace altered.

SQL> select tablespace_name, retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION                                        
------------------------------ -----------                                      
SYSTEM                         NOT APPLY                                        
UNDOTBS1                       NOGUARANTEE                                      
SYSAUX                         NOT APPLY                                        
TEMP                           NOT APPLY                                        
USERS                          NOT APPLY                                        
EXAMPLE                        NOT APPLY                                        
UNDOTBS2                       GUARANTEE                                        

7 rows selected.


UNDOTBS2 tablespace has retention guarantee (See Above). Now, we will take back our undo tablespace in default state.


SQL> select name from v$tablespace;

NAME                                                                            
------------------------------                                                  
SYSTEM                                                                          
UNDOTBS1                                                                        
SYSAUX                                                                          
USERS                                                                           
TEMP                                                                            
EXAMPLE                                                                         
UNDOTBS2                                                                       

7 rows selected.

SQL> alter system set undo_tablespace=undotbs1 scope=both;

System altered.

SQL> alter system set undo_retention=900 scope=both;

System altered.


Use this following SQL command to drop undotbs2 tablespace.


SQL> drop tablespace undotbs2 including contents;

Tablespace dropped.

SQL> select name from v$tablespace;

NAME                                                                            
------------------------------                                                  
SYSTEM                                                                          
UNDOTBS1                                                                        
SYSAUX                                                                          
USERS                                                                           
TEMP                                                                            
EXAMPLE                                                                         

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: