Reclaiming Unused Space in an Oracle Database

One of the core features of Everpure's Flash Array is Thin provisioning. Thin provisioning is a method of dynamically allocating storage capacity on-demand across multiple applications in a flexible and efficient manner, thus reducing storage utilization rates. What that means is the space consumed on the Flash Array will be a function of the actual capacity written by the database, and not the space allocated to data files or diskgroups.

The Problem

During a database's normal lifecycle, several common operations should, in theory, free up space:

Dropping or re-sizing large data files
Truncating tables
Deleting archived redo logs

Whereas these operations may release space back to the database (or the ASM diskgroup/file system), they typically do NOT release space back to the Flash Array. This means that released space cannot easily then be consumed by databases or applications on different hosts that use different ASM diskgroups or file systems.

Here is an example that illustrates the problem:

Oracle considers ASM disk group FRA to be using 99 GB of storage, with 401 GB free:

However, the storage array sees over 200 GB in use:

The Solution

An Oracle database can be created in one of two ways:

On an Oracle proprietary database file system and volume manager known as Automatic Storage Management (ASM)
Oracle Automatic Storage Management (ASM) is essentially an Oracle-managed file system which manages all database data files, redo logs, control files, backup sets – essentially everything except for the Oracle software and configuration files.
It is widely used in RAC environments, but it is also common for single-instance databases.
ASM files are not visible at the OS level, although the underlying devices are visible.
Oracle provides a CLI ( asmcmd) that provides limited visibility and functionality to the ASM files.
On an operating system file system (e.g. EXT4, XFS)

Both these storage options can suffer from this problem and the solution for the different scenarios is presented below.

Oracle Database on ASM

ASM requires permissions to persist across Linux reboots. RAC environments typically requires device names to remain consistent across the nodes of the cluster.

UDEV rules are a popular mechanism to provide consistent permissions and device names, but DBAs may also use ASM Filter Drivers or ASMLib.

ASM Filter Drivers was deprecated in 2025, but ASMLib 3.x has replaced it. Like ASM Filter Drivers, ASMLib 3.x supports thinly provisioned storage and storage reclamation.

Method 1 - ASMFD THIN_PROVISIONED Attribute

The THIN_PROVISIONED attribute enables or disables the functionality to discard unused storage space after a disk group rebalance is completed. The attribute may be set on a diskgroup by diskgroup basis.

The attribute value can be set to true to enable, or set to false to disable the functionality. The default value is false.


SQL > alter diskgroup DATA set attribute 'thin_provisioned'='TRUE';

When the COMPACT phase of a rebalance operation has completed, Oracle ASM informs the Flash Array which space is no longer used and can be re-purposed.

ASM Disk Group rebalance can be triggered using the following command.


SQL > alter diskgroup DATA rebalance with balance compact wait;

Steps to use ASMFD Thin_Provisioned

The entire process is demonstrated below by way of an example. Here are the high-level steps that will be performed.

Install and configured the ASMLib 3.x device management package on each node of the RAC cluster. Ensure all devices are stamped as ASMLib disks.
Create a new ASM Disk Group
Set the thin_provisioning attribute to TRUE
Create a tablespace in this diskgroup and populate it with some data
Check the space allocated at the database and array level.
Drop the tablespace and run ASM rebalance.
Check the space again. It should get returned back to storage.
Note: It may take some time before the Purity interface shows the space returned.

Steps in Detail

We start with a new database that has a 2T Disk Group ASMTP.

$ asmcmd lsdg
State Type Rebal Total_MB Free_MB Usable_file_MB Voting_files Name
MOUNTED EXTERN N 2097152 2097004 2097004 N ASMTP/
MOUNTED EXTERN N 307200 147720 147720 N DATA/
MOUNTED NORMAL N 81920 80804 32210 Y GRID/
Note: some columns of output have been removed to aid readability.
> purevol list --space gct-oradb-prd-rac-asmtp-00
Name Size Virtual Thin Provisioning Data Reduction Total Reduction Unique
Snapshots Total
gct-oradb-prd-rac-asmtp-00 2T 144.63M 100% 6.3 to 1 >100 to 1 19.14M 0.00
19.14M

Set the thin_provisioned attribute to TRUE

SQL > alter diskgroup ORA01PRD_DATA set attribute 'thin_provisioned'='TRUE';

Create a 1T tablespace called THIN_PROV_TS.

SQL> create bigfile tablespace THIN_PROV_TS datafile '+ASMTP' size 1024g

Create a bunch of tables and populate it with random data. In this example, we have built a TPCC schema with 5000 warehouses.

Check the space again on both ASM and the Flash Array. ASM reports we have consumed about 1TB of capacity, but Purity reports a much lower number - 168GB, due to the Flash Array's ability to compress and dedupe efficiently.

$ asmcmd lsdg
State Type Rebal Total_MB Free_MB Usable_file_MB Voting_files Name
MOUNTED EXTERN N 2097152 1048420 1048420 N ASMTP/
MOUNTED EXTERN N 307200 147556 147556 N DATA/
MOUNTED NORMAL N 81920 80804 32210 Y GRID/
purevol list --space gct-oradb-prd-rac-asmtp-00
Name Size Virtual Thin Provisioning Data Reduction Total Reduction Unique
Snapshots Total
gct-oradb-prd-rac-asmtp-00 2T 1.00T 50% 5.6 to 1 11.3 to 1 168.08G 3.77K
168.08G

Drop the THIN_PROV_TS tablespace.

SQL> drop tablespace THIN_PROV_TS including contents;
Tablespace dropped

Space is released from the database, but not (yet) from the Flash Array.

$ asmcmd lsdg
State Type Rebal Total_MB Free_MB Usable_file_MB Voting_files Name
MOUNTED EXTERN N 2097152 2097004 2097004 N ASMTP/
purevol list --space gct-oradb-prd-rac-asmtp-00
Name Size Virtual Thin Provisioning Data Reduction Total Reduction Unique
Snapshots Total
gct-oradb-prd-rac-asmtp-00 2T 1.00T 50% 5.6 to 1 11.3 to 1 168.13G 3.77K
168.13G

Log into the ASM instance and run rebalance on the Disk Group.

SQL> alter diskgroup ASMTP rebalance with balance compact wait;
Diskgroup altered.

Within a few minutes, we should be able to see that space previously consumed by tablespace THIN_PROV_TS is now reclaimed by the Flash Array.

purevol list --space gct-oradb-prd-rac-asmtp-00
Name Size Virtual Thin Provisioning Data Reduction Total Reduction Unique
Snapshots Total
gct-oradb-prd-rac-asmtp-00 2T 146.30M 100% 11.8 to 1 >100 to 1 7.65M 167.98G
167.99G

Note: Oracle recommends that all the disks in a disk group be of the same size. Starting with version 12.2, it enforces this condition for disk groups created with Normal or High redundancy. This document discusses different scenarios of reclaiming space that is no longer being used by an Oracle database.

Method 2 - ASM Disk Group Reorganization

As discussed above, using the ASM Filter Driver with the THIN PROVISIONED attribute set to true can reclaim space when the tablespace is dropped. However, ASM Filter Driver is relatively new and there are many Oracle database installations that either have not or can not make the switch to using ASM Filter Driver.

Moreover, there could be cases where the database has a large amount of free space due to dropped or truncated objects but it is not practical to drop the tablespace. For such scenarios, the method described below can be used.

Add a new disk and drop the old one

Identify the disks that have a large amount of free space that can be reclaimed, and the disk group they are part of.
Execute the following command to replace the existing bloated disk in the disk group with a new disk.

This SQL also initiates a ASM rebalance of the blocks from the old disk to the new disk.

As the wait clause is used, control will be returned only after the operation is complete.
```
SQL > alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA2' name DATA2
drop disk DATA1
rebalance power 16 w
```
When the command completes, the old disk will no longer be part of the disk group and can be safely deleted on the Flash Array.

Oracle Database on Operating System File System

EXT4 and XFS are the most popular file systems for creating Oracle databases. Both of them provide the mount option discard. A file system on a thin-provisioned volume, when mounted with the discard flag, will recognize when space becomes unused after a file is deleted. It will then issue trim commands to UNMAP the unused space

/etc/fstab entry for ext4 file system

/dev/mapper/oracle-oraprod-data /u01 ext4 discard,noatime 0 0

/etc/fstab entry for xfs file system

/dev/mapper/oracle-oraprod-data /u01 xfs discard,noatime 0 0