Undo tablespace is a kind of permanent tablespace used by oracle database engine to manage undo data if you are running your database in automatic undo management mode. This undo data or undo records are generally used to:
- Roll back transactions when a ROLLBACK statement is issued
- Recover the database
- Provide read consistency
- Analyze data from an earlier point in time by using Oracle Flashback Query
- Recover from logical corruptions using Oracle Flashback features
How to Create Small file Undo Tablespace
Similar to other two tablespaces i.e. Permanent and Temporary tablespace which we have discussed in the previous two tutorials, undo tablespace can also be created as a small file as well as a big file tablespace.
Since we are creating an undo tablespace thus we will use CREATE UNDO TABLESPACE clause.
CREATE SMALLFILE UNDO TABLESPACE “tbsp_undo”
 DATAFILE ‘C:\APP\TBSP_DEMO\tbsp_undo.dbf’ SIZE 100M
 AUTOEXTEND ON NEXT 500M
 MAXSIZE UNLIMITED
 RETENTION NOGUARANTEE;
The create undo statement is slightly different than the others which we have done so far. So let’s delve deeper into this statement and see what we have done here.
The first line is self-explanatory, here we are creating a small file undo tablespace by the name of tbsp_undo.
In the second line we have our data file clause using which I have added a data file tbsp_unod.dbf to our undo tablespace which has initial size 100MB. As it’s a small file tablespace therefore we can add multiple data files into it which is not true in case of a big file tablespace which can have maximum of 1 data file. For the simplicity of the demonstration here I have added only one data file.
In the third line we have auto extend clause. Using this clause we are ensuring that as soon as our data file tbsp_undo.dbf which is of 100MB size gets filled with undo segments, oracle engine will extend it automatically and increase it by 500 Megabytes.
In the fourth line we have clause MAXSIZE. Using this clause we can set the maximum size to which the data file of undo tablespace can extend or say grow. Here I have set the maximum size of our data file tbsp_undo.dbf to UNLIMITED. You can also limit the max size of your data file by simply writing the size. For example say you want to set the max size of your data file to 50MB in that case simply write 50M in the place of UNLIMITED.
In the 5th and last line we have a Retention clause which is right now set on No Guarantee. You can set Retention clause either on Guarantee or No Guarantee.
- RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.
- RETENTION NOGUARANTEE returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is also the default setting.
If you do not specify this clause then oracle engine will by default set the retention on No Guarantee.
How to Drop Undo Tablespace
To drop a tablespace from the database, we use the DROP TABLESPACE statement. The optional clause INCLUDING CONTENTS recursively removes any segments (tables, indexes, and so on) in the tablespace, like this:
DROP TABLESPACE dba_sandbox INCLUDING CONTENTS;
Dropping a tablespace does not automatically remove the datafiles from the file system. You need to use the additional clause INCLUDING CONTENTS AND DATAFILES to remove the underlying datafiles as well as the stored objects, like this:
DROP TABLESPACE hr_data INCLUDING CONTENTS AND DATAFILES;
That’s it on How to create undo tablespace. Hope you were able to understand the concept of creating Undo Tablespace that I discussed here. Kindly do share this on your social network and help me reach out to more people. Thanks for reading & have a great day!
