Tablespaces Free Space and Stuff

Over the years I got many questions about free space in tablespaces and other issues related to allocating and de-allocating of space, fragmentation and more. So I decided to write this post that will hopefully summarize this topic. Also, I’ll add scripts that might help you in the future. I’ll try not to write too much about the basics, but there are several issues that I do want to include in this post.
If you haven’t read my OTN article about specific fragmentation problem with tablespace configured to use system allocation extent management and the “alter table shrink” command.

Used and Empty Space

In Oracle, the very small and basic building block for the segments (tables, indexes, etc.) is the block, and its size is 2KB-32KB (the default and most-common value is 8KB). One block contains the data itself and this is the smallest unit Oracle uses (when it reads data from the disk or memory, loads it to the memory, and so on).
When working with the storage, we need to allocate free space from the tablespace for tables and indexes. The space allocation is not done at a block level for several reasons:

• If this was done at block level, Oracle would have needed to allocate blocks all the time (every few inserts can need another block to be allocated) and have been probably spent all of its effort in allocating new space and nothing else.
• Managing the used and free space by knowing which block belongs to which segment is also difficult if we allocate space at a block level. Every block might belong to a different segment.
• When we wish to read an entire table, we use “multiblock IO” which reads a “chunk” of blocks from the disk in one IO operation. This is way more effecient than readin one block after the other. But in order to do that we need to have a “chunk” of blocks belonging to the same table to be located together on the disk (I’m not going to go into too much details here, this is a topic for a different post).

To solve these issues, Oracle allocates space from the tablespace to segments in “chunks”, and the “chunk” is always a set of continuous blocks on the disk. This “chunk” is called extent. So the basic building block for allocating space from the tablespace is an extent.
The important thing to remember here is that when extent is allocated to a table, it doesn’t matter if it has data or not, from the tablespace point of view, this extent is used and we won’t consider this extent as free space anymore.
This is why, when we delete data from the table, we don’t see any difference in the tablespace free space. This extent might be empty and can get new rows, but it is still allocated to the table so it is not free.
The only ways to de-allocate space from a table is either rebuild the table (using “alter table move” for example), shrink it (using “alter table shrink”) or (if you don’t need the data at all anymore) to truncate it.
In order to know how much space is used or free in the tablespace, you can query DBA_FREE_SPACE to see all empty chunks of blocks in the tablespaces (note that tablespace that is completely full won’t appear in this view). You can also query DBA_EXTENTS to see all the extents in the tablespaces (only extents that are allocated to segments).
This is a script I wrote to show information about used and free space in the tablespaces. It shows the following for each tablespace:

• Tablespace name
• Current size on the disk
• Max size (the sum of the maximum size the files can extend automatically)
• Total free size
• Used size
• Free space (percentage from the current size on disk)
• Free space total (percentage from the max size)
• free_chunks (number of free chunks of blocks in the tablespace, relevant to fragmentation)
• largest_chunk (the largest chunk of free space, relevant to fragmentation)

Temporary Tablespace

Temporary tablespaces behave differently, so it is important to understand these differences and how to check for free space in these tablespaces. Temporary tablespaces are used for temporary data (for sorts, temporary tables, etc.) that cannot fit into the memory. The temporary tablespace also uses extents and segments (as this is how Oracle works), however, allocating and de-allocating space is a resource consuming operation. It can be done only in serial (so different users won’t be allocating the same space), and this may lead to large overhead and performance issues. Therefore, Oracle’s architecture allows users to share the same temporary segment, and extents that were allocated to a temporary segment will never be de-allocated.
It is very common that some processes legitimately use temporary space. They will allocate space, and once they are done, the extents and segments will be marked as free but the space itself won’t be de-allocated.
The temporary tablespace free space is not recorded into DBA_FREE_SPACE, so instead, we are using the DBA_TEMP_FREE_SPACE view.
This is the script for temporary tablespaces

Summary

I hope the topic of free-used space in permanent and temporary tablespace is a little bit clearer now. I wanted to talk about decreasing the size of a tablespace as well, as this is also a very common question. But I think this post is long enough, I will write another post to discuss this topic soon.