Undo tablespace management
in this post, we are going to learn about undo and we will create undo tablespace.
What is undo?
Oracle maintains a mechanism to maintain information that is used to roll back or undo, changes to the database.such information consists of records of the actions of transactions before they are committed. These records are called as undo. These records are stored in a separate tablespace called undo tablespace.
Undo records are used to:
- Roll back transactions when a
ROLLBACKstatement is issued - Recover the database
- Provide read consistency
- Analyze data as of an earlier point in time by using Oracle Flashback Query
- Recover from logical corruptions using Oracle Flashback features.
undo segments are also called as rollback segments. once we create a rollback segment, by default it will be in offline state. to make it online use the below command.
alter rollback segment rbs1 online;
to make rollback segments online during instance startup process, set init.ora parameter as
rollback_segments=rbs1
once we create a undo tablespace, by default, Oracle creates 10 undo segments with the naming convention syssmu$n
i.e., syssmu$0 to syssmu$9
undo retention represents how long the transactional data has to be retained in the undo segments. If there is no space for the other transactions oracle overwrites it.
Steps to create an undo tablespace:
Step 1: Find the current undo_tablespace
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS> SYS> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 1000 undo_tablespace string UNDOTBS1 SYS> |
or
SYS> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like ‘%UNDO%’;
Step 2 Create undo_tablespace using the below command
create UNDO tablespace undotbs2 datafile ‘/u01/TEST/undotbs02.dbf’ size 100m;
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SYS> SYS> create UNDO tablespace undotbs2 datafile '/u01/TEST/undotbs02.dbf' size 100m; Tablespace created. SYS> alter system set undo_tablespace='undotbs2' scope=both; System altered. |
Step 3: Check the current undo tablespace again
|
1 2 3 4 5 6 7 8 9 |
SYS> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 1000 undo_tablespace string undotbs2 |
Step 4: Drop the existing undo tablespace
|
1 2 3 |
SYS> drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped. |
Note: Sometimes we get an error ORA-01548 while dropping the existing undo tablespace.The resolution for the error will be discussed in a separate post.
|
1 2 3 4 5 6 |
SYS> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU2_111974964$' found, terminate dropping tablespace |
Some of the important data dictionary views include
|
1 2 3 4 5 6 7 8 9 10 11 |
V$UNDOSTAT V$ROLLSTAT V$TRANSACTION DBA_UNDO_EXTENTS DBA_ROLLBACK_SEGS DBA_TABLESPACES |
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.