Why doesn't the database shrink after incidents are deleted?
Article ID: 160416
Updated On:
Products
Issue/Introduction
Environment
Oracle Database
Resolution
This is a very frequently asked question.
It's logical to assume that if you delete data from a file, any file, it's size should get smaller. After all, that's the way it works with word processor files. So, it is a fair assumption that total space occupied by the data within a file is the actual file size. This is not true, however, of Oracle's datafiles. Once space is assigned to an Oracle datafile, it is not automatically "deallocated".
Why would Oracle do this? It turns out that it is much easier and, more importantly, faster for Oracle to work with space already assigned to it rather than having the overhead of coordinating with or waiting for the OS to allocate or deallocate disk space on the fly.
The way Oracle stores information play a large role in why datafiles don't shrink:
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 composed 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.
Another important factor:
Each incident may be associated with one and only one message (or violating component). If that component violates multiple policies, it will have multiple incidents associated with it. The object stored in the database that has the potential to take up the most space is the violating component and not the incident itself. If a violating component has one and only one incident associated with it, once that incident is deleted, then the violating component is also deleted. But if there are ten incidents associated with a particular violating component, the violating component will not be deleted until ALL of the associated incidents have been deleted. Therefore, if there 1 million incidents are deleted, but their associated violating components have OTHER incidents, the violating components will continue to take up space; the space returned may be negligible.
Finally, 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".
You may also want to review:
Converting your LOB tables from BasicFiles to SecureFiles format in Symantec DLP
Oracle tablespace (LOB_TABLESPACE, USERS, etc.) for DLP is full, almost full, or critically full
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.
Feedback
