Oracle Database : A Bigfile and Shrink Wishlist

 

A few days ago I mentioned a couple of new features related to bigfile tablespaces in Oracle database 23ai.

Having played with these new features, a couple of things were added to my wish list.

Convert Smallfile to Bigfile Tablespaces

If would be awesome if we could convert smallfile tablespaces to bigfile tablespaces. At the moment we have to create a new bigfile tablespace and move everything to it. It would be nice if we had some type of simple convert option instead.

I don’t know if this would be possible with some internal jiggery-pokery, or if it would just hide the manual process of moving everything from us. Either way it would be cool.

Being able to convert a smallfile tablespace to bigfile tablespace gives us the option to use the new shrink tablespace functionality, assuming the process of converting didn’t already achieve this for us. 🙂

As much as I like the new bigfile defaults, we don’t create a lot of new databases these days. We are mostly supporting existing databases, so being able to do an easy convert would be really handy.

Smallfile Shrink Tablespace

As much as I love the new shrink tablespace functionality for bigfile tablespaces, I have loads of existing smallfile tablespaces that could benefit from a shrink operation. I know how to manually reclaim free space, as described here, but it is a pain. It would be much better if the shrink tablespace could support smallfile tablespaces as well.

Of course, I’m happy to ignore this if the conversion from smallfile to bigfile tablespaces were possible, as that would solve my issue via a different route. 🙂

Why do I care?

I’m not a fan of randomly shrinking things, but we do get incidents that leave us in a position where a big clean up is necessary. I recently wrote a post called When Auditing Attacks, where I mentioned how we accidentally generated loads of auditing records. Having a no-brainer way to clean this stuff up would be so useful!

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.