As you may know, starting from Oracle 23ai, we can shrink permanent tablespaces. However, that was limited to bigfile tablespaces, and shrinking smallfile tablespaces was not possible. Although, by default, tablespaces are created as bigfile tablespace.

Now, in Oracle 23.7, shrinking smallfile tablespaces is possible and we can shrink these tablespaces using the dbms_space.tablespace_shrink procedure. This procedure executes familiar commands such as ‘alter table move online’ and ‘alter index rebuild’ to shrink smallfile tablespaces.

PROCEDURE SHRINK_TABLESPACE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TS_NAME                        VARCHAR2                IN
 SHRINK_MODE                    NUMBER                  IN     DEFAULT
 TARGET_SIZE                    NUMBER                  IN     DEFAULT
 SHRINK_RESULT                  CLOB                    OUT
 ITERATIONS                     NUMBER                  IN     DEFAULT  

Test Scenario

To demonstrate this feature, let’s create a smallfile tablespace and populate it with some tables , drop a few tables and finally shrink the tablespace.

Step 1: Create a Tablespace with 3 Datafiles (Autoextend OFF)

SQL> create smallfile tablespace small_app_tbs datafile 'table1.dbf' size 2g , 'table2.dbf' size 2g, 'table3.dbf' size 2g;
Tablespace created

Step 2: Create Four Tables of Equal Size

SQL> create table vahid.tb1 tablespace small_app_tbs as select * from sys.tbl1;
Table created
SQL> create table vahid.tb2 tablespace small_app_tbs as select * from sys.tbl1;
Table created
SQL> create table vahid.tb3 tablespace small_app_tbs as select * from sys.tbl1;
Table created
SQL> create table vahid.tb4 tablespace small_app_tbs as select * from sys.tbl1;
Table created

Step 3: Check the Table Sizes

SQL> select round(bytes/1024/1024) SEG_SIZE_MB,segment_name,tablespace_name from dba_segments where tablespace_name=upper('SMALL_APP_TBS');
SEG_SIZE_MB SEGMENT_NAME    TABLESPACE_NAME
----------- --------------- ------------------------------
        456 TB1             SMALL_APP_TBS
        456 TB2             SMALL_APP_TBS
        456 TB3             SMALL_APP_TBS
        456 TB4             SMALL_APP_TBS

Step 4: Check the Used Space for Each Datafile

SQL>@datafile_size.sql
FILE_NAME                           TOTALSIZEMB     USEDMB AUTOEXTENSIBLE
----------------------------------  ----------- ---------- --------------
/oracle23.7/base/oradata/table1.dbf            2048        600 NO
/oracle23.7/base/oradata/table2.dbf            2048        596 NO
/oracle23.7/base/oradata/table3.dbf            2048        628 NO

Step 5: Analyze the Tablespace for Shrinking

Before making any changes, we run the DBMS_SPACE.TS_MODE_ANALYZE to determine how much space can be reclaimed:

SQL> set serveroutput on;
SQL> execute dbms_space.shrink_tablespace(TS_NAME=>'SMALL_APP_TBS', shrink_mode =>DBMS_SPACE.TS_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 4
Total Movable Size(GB): .61
Original Datafile Size(GB): 6
Suggested Target Size(GB): 2.01
Process Time: +00 00:00:06.702488
PL/SQL procedure successfully completed

As you can see, shrinking of this tablespace, can reduce tablespace size from 6 GB to 2GB.

Step 6: Perform the Shrink Operation

Run the shrink procedure to reclaim space:

SQL> execute dbms_space.shrink_tablespace(TS_NAME=>'SMALL_APP_TBS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 4
Total Moved Size(GB): .61
Original Datafile Size(GB): 6
New Datafile Size(GB): 2.17
Process Time: +00 00:02:34.071108
PL/SQL procedure successfully completed.

Step 7: Verify the Results

After shrinking, we can see all four tables have moved to two datafiles and one the datafiles was removed.

SQL>@datafile_size.sql
FILE_NAME                           TOTALSIZEMB     USEDMB AUT
----------------------------------- ----------- ---------- ---
/oracle23.7/base/oradata/table1.dbf            2048       1810 NO
/oracle23.7/base/oradata/table2.dbf             174         16 NO

Before running the above command, I enabled trace for that session, and in the trace file, we can see the procedure running the following commands:

PARSING IN CURSOR #139815671265616 len=39 dep=1 uid=135 oct=1 lid=135 tim=4073208427751 hv=997212054 ad='688b2ec8' sqlid='fkx0630xr0gwq'
alter table "VAHID"."TB1" move  online
PARSING IN CURSOR #139815677584744 len=39 dep=1 uid=135 oct=1 lid=135 tim=4073174850219 hv=2522059658 ad='70fd73a0' sqlid='4dgz14yb574wa'
alter table "VAHID"."TB2" move  online
PARSING IN CURSOR #139815674335544 len=39 dep=1 uid=135 oct=1 lid=135 tim=4073139135319 hv=2132665791 ad='6d50bcf0' sqlid='0hvkcg5zjvtdz'
alter table "VAHID"."TB3" move  online 
END OF STMT
PARSING IN CURSOR #139815677576928 len=39 dep=1 uid=135 oct=1 lid=135 tim=4073103914906 hv=3658805126 ad='6bf236c0' sqlid='3u2hhh3d19vw6'
alter table "VAHID"."TB4" move  online 
END OF STMT

Verified the alert log messages and observed the following results:

2025-02-04T15:36:01.979306+03:30
VANAK(3):alter database datafile '/oracle23.7/base/oradata/table1.dbf' resize 2147483648
VANAK(3):Completed: alter database datafile '/oracle23.7/base/oradata/table1.dbf' resize 2147483648
VANAK(3):alter database datafile '/oracle23.7/base/oradata/table2.dbf' resize 182452224
VANAK(3):Resize operation completed for file# 31, fname /oracle23.7/base/oradata/table2.dbf, old size 2097152K, new size 178176K
VANAK(3):Completed: alter database datafile '/oracle23.7/base/oradata/table2.dbf' resize 182452224
VANAK(3):alter tablespace "SMALL_APP_TBS" drop datafile '/oracle23.7/base/oradata/table3.dbf'
2025-02-04T15:36:02.950573+03:30
VANAK(3):Deleted file /oracle23.7/base/oradata/table3.dbf
2025-02-04T15:36:03.012876+03:30
VANAK(3):Completed: alter tablespace "SMALL_APP_TBS" drop datafile '/oracle23.7/base/oradata/table3.dbf'
2025-02-04T15:36:13.954782+03:30
VANAK(3):ORA-01652 addlInfo tsn:9 objn:72805 objd:72834 user:VAHID obj:TB1 subobj: type:2
VANAK(3):ORA-01652: unable to grow table VAHID.TB1 in tablespace SMALL_APP_TBS [VANAK] by 8MB during operation with SQL ID:fkx0630xr0gwq, temp space used by session : 0 (MB)
2025-02-04T15:36:15.792791+03:30
VANAK(3):alter database datafile '/oracle23.7/base/oradata/table1.dbf' resize 2147483648
VANAK(3):Completed: alter database datafile '/oracle23.7/base/oradata/table1.dbf' resize 2147483648
VANAK(3):alter database datafile '/oracle23.7/base/oradata/table2.dbf' resize 182452224
VANAK(3):Completed: alter database datafile '/oracle23.7/base/oradata/table2.dbf' resize 182452224
2025-02-04T15:36:21.573052+03:30
VANAK(3):ORA-01652 addlInfo tsn:9 objn:72805 objd:72838 user:VAHID obj:TB1 subobj: type:2
VANAK(3):ORA-01652: unable to grow table VAHID.TB1 in tablespace SMALL_APP_TBS [VANAK] by 8MB during operation with SQL ID:fkx0630xr0gwq, temp space used by session : 0 (MB)
2025-02-04T15:36:23.317894+03:30
VANAK(3):alter database datafile '/oracle23.7/base/oradata/table1.dbf' resize 2147483648
VANAK(3):Completed: alter database datafile '/oracle23.7/base/oradata/table1.dbf' resize 2147483648
VANAK(3):alter database datafile '/oracle23.7/base/oradata/table2.dbf' resize 182452224
VANAK(3):Completed: alter database datafile '/oracle23.7/base/oradata/table2.dbf' resize 182452224

Conclusion

The ability to shrink smallfile tablespaces in Oracle 23.7 provides more flexibility for managing storage. However, note that dropping segments such as tables and indexes impacts the shrink operation, while simply deleting data from tables does not reclaim space within tablespaces.