Why doesn't the database shrink after incidents are deleted?

Products

Data Loss Prevention Enforce Data Loss Prevention Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

After deleting a significant number of incidents, the size of the datafiles remains unchanged.

Environment

Oracle Database

Resolution

Once space is assigned to an Oracle datafile, it is not automatically "deallocated", but instead made writable again.

It is also important to note that deleting a single incident may not delete the original 'message' that generated the incident as that message may be tied to other incidents and only once every incident with an associated messages is deleted can the 'message' be deleted and reclaim the space as writable.

Additional Information

Oracle Enterprise Edition (and similar versions) provide a DBA functionality to "shrink" or reclaim space. That functionality is not part of the Oracle Standard Edition and its support.

A database is composed of a collection of "schemas" which contain a variety of database "objects" used to contain and facilitate the manipulation of data. All of which take up physical disk space.

Tables are stored in Tablespaces.

Tablespaces are comprised of Datafiles.

And the smallest unit of space in all of Oracle is a block (typically determined by OS; usually 8k in size). All database objects are composed of blocks.

A single block may contain multiple (table) records. As a block fills with records, it passes a upper volume threshold where it won't accept additional data. That block is then removed from a list of blocks that called the "freelist". When records are deleted, the block does not return to the freelist until a lower space threshold is crossed. Given this arrangement, with the ongoing inserts, deletes and updates that happen in an active database, and the notion that records are inserted not in an ordinal manner, but based on the next available space, the free space (not necessarily "available" free space) is scattered across the datafiles in a distributed, swiss-cheese fashion. You can delete a bunch of records, but, not necessarily garner an equal amount of free space. Still, the freed space will likely be used sooner or later.

LOB segments are handled in a different manner than other datatypes. Rather than filling blocks individually and placing them on or off of the freelist based on thresholds, LOB segments cluster blocks into "chunks" using them in an all or nothing manner, with retention after deletion (in place of using UNDO tablespace) set by undo_retention parameter. This is subject to if the data being stored is < or > 4k in size, < having the data stored "in-line" and > "out-of-line".