Undo Tablespace

By Manish Sharma

Create Undo Tablespace

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 say 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 small file as well as big file tablespace.

Since we are creating an undo tablespace thus we will use CREATE UNDO TABLESPACE clause.

 DATAFILE 'C:\APP\TBSP_DEMO\tbsp_undo.dbf' SIZE 100M

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 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 at 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:

Dropping a tablespace does not automatically remove the datafiles from the file system. Use the additional clause INCLUDING CONTENTS AND DATAFILES to remove the underlying datafiles as well as the stored objects, like this:

That’s it on How to crate 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 networking and help me reach out to more people. Thanks for reading & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7 You will get 5GB extra free cloud storage means total 20GB free cloud storage.

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn