Reclaiming Unused Space in Oracle Database 23ai with 'shrink_tablespace'

Question: Wouldn't it be nice if you could easily shrink your tablespace?

Well, now you can! In Oracle Database 23ai, we introduced a new procedure to make the process as easy as possible. The shrink_tablespace procedure offers a simple way to reclaim unused or free space in your tablespace.

The next 5 minutes will show you how to use the new shrink_tablespace procedure.

Wait, why would you want to shrink a tablespace?​

Before diving into the "how," it's nice to understand why you might want to shrink a tablespace in the first place.

Consider a scenario where you need to remove old data. Maybe you only want to save data for a certain amount of time. Each time the data is removed, it leaves behind a trail of unused space within your tablespaces.

Over time, these unused pockets of space can grow, making your tablespaces less efficient. Think... Swiss cheese.

10/10 procedure , 2/10 drawing

If you try to resize the datafile before Oracle Database 23ai, you'll get "ORA-03297: file contains used data beyond requested RESIZE value", because there are objects(your tables, indexes, partitions, etc.) that are blocking it from shrinking. You'd need to manually reorganize those first before you could resize the tablespace.

Shrinking your tablespace is a way to optimize your database performance and make sure those valuable database resources are being used efficiently.

OK... that sounds nice, how do I use it?

Let's break down the process of using the new shrink_tablespace procedure into some easy to understand steps.

Step 1: Create and populate a tablespace

To begin the shrinking process we first need a tablespace with some data.

For the purpose of this demo, let's create a new tablespace named 'SOE' that's 10 gigs. Here's an example of how to create a tablespace that's 10 gigs:

CREATE BIGFILE TABLESPACE SOE DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/soe.dbf' SIZE 10G AUTOEXTEND ON;

Once created, populate the tablespace however you want. I'll use Swingbench to add some data (if you're interested, you can checkout how to use Swingbench here).

Step 2: Lets delete some data

After we've added our data (in my case I added about 8 gigs worth of tables and indexes), lets delete some of our tables and indexes so we can create 'holes' or areas of unused space in the tablespace.

Using Swingbench we can take a look at our tables and how much space they take up.

We've got about 5 of our ~8 gigs as tables, and about 3 gigs worth of indexes (not pictured).

Lets delete some tables and indexes and then check how much space the remaining objects take up.

So, I purged about 4 gigs worth of tables and 2.5 gigs of indexes. This gives us a new total size of about 1 gig worth of objects in our tablespace. Remember, our tablespace was 10 gigs when we created it.

Step 3: Check the tablespace size

The tablespace doesn't resize as data is removed. We can check that now (it's still 10 gigs). So, this means we have a tablespace with 10 gigs but we're only using about 1 gig of that.

Step 4: Understanding shrink_tablespace

Now that we've got a tablespace with a bunch of 'holes' in it, we can go ahead and use the shrink_tablespace procedure.

The shrink_tablespace PL/SQL API was made for optimizing the storage of bigfile tablespaces by moving segments (objects) that are blocking the datafile tail to the datafile head, and then resizing the datafile.

The API looks like the following:

DBMS_SPACE.SHRINK_TABLESPACE(ts_name, shrink_mode, target_size, shrink_result)

The API Description:

Input Parameters:

• ts_name: This is the name of the tablespace you want to shrink.

• shrink_mode: Shrink mode has a couple options like TS_MODE_SHRINK, TS_MODE_ANALYZE, and TS_MODE_SHRINK_FORCE. By default, it's set to TS_MODE_SHRINK, which moves objects online by default (except for Index-Organized Tables or IOT). TS_MODE_SHRINK_FORCE also moves objects online by default, but if the online move fails, it attempts an offline move.

• target_size: This parameter lets you pick the new tablespace datafile size (in bytes). The default is TS_TARGET_MAX_SHRINK.

Output Parameters (shrink_result):

• For shrink_mode TS_MODE_SHRINK (I'll show you an example of both below):

  • The procedure returns the total number and size of moved objects.

  • It provides the original and new datafile size.

  • You get information on the process time.

• For shrink_mode TS_MODE_ANALYZE:

  • It generates a list of movable objects.

  • You'll see the total number and size of movable objects.

  • It suggests the target size.

  • It also provides the process time.

Step 5: Can we see it in action already?

Yes. I'll show both the analyze mode and the shrink mode options.

We can start by setting the output of the analyze mode to show on the terminal.

set serveroutput on;
alter session set events '10613 trace name context forever, level 1';

Now, we can run the procedure.

execute dbms_space.shrink_tablespace('SOE', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);

The following is a simplified version of the output that you'll get

It took about half a second to analyze the tablespace. It gives us a bunch of useful things. We can see that the total number of objects in the tablespace is 1.14 gig, we've got the total number of moveable objects in the tablespace as 13, we also have the original as 10 (we knew that), its also kind enough to give us a suggested tablespace size of 2.45 gigs.

Let's take the default parameter's (meaning 'TS_TARGET_MAX_SHRINK') and shrink the tablespace:

execute dbms_space.shrink_tablespace('SOE');

Here's the simplified version of the output we get:

Remember, we started by creating a tablespace with 10 gigs and added about 8 gigs of object. After deleting most of our tables and indexes, we had 1.14 gigs worth remaining.

The output shows us all of this, along with the number of objects that were moved, the time it took to resize the tablespace, and the new tablespace size as 1.35 gigs.

With one command we can now find all of the objects that were blocking the tail of the datafile, relocate those objects, and finally resize the tablespace (in this simple small demo, reclaiming about 9 gigs of space).

Pretty cool, huh?

If you want to try this feature out, along with a ton of other new 23ai features, check out this free sandbox environment: https://livelabs.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3950

What did we learn today?

Using the shrink_tablespace procedure is an easy way to reorganize objects, so we can reclaim free space in the tablespace.

• Oracle Database 23ai introduces the shrink_tablespace procedure, which simplifies the process of resizing datafiles and organizing objects.

• shrink_tablespace can be used in three modes:

  • Default - You can specify the tablespace and let the process shrink the file as small as possible (this uses an online move).

  • Analyze - Get information about the shrink prior to doing it.

  • Force - For the instances where you don't care if the move is done online or offline, the procedure tries to move objects online (the default), and if it fails, an offline move is used.