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)

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);