In the last post about tablespaces (tablespaces free space) I talked about used and free space in permanent and temporary tablespaces. I also included a script to see all the tablespaces with the free space and even fragmentation information. In this post I’d like to explain a little bit about reducing the tablespace size.
Many times we have a single table or index that just go crazy, consuming a huge amount of space in the tablespace and increasing its size to something we didn’t plan (assuming the files are configured as auto extend). In other cases, we decide to move segments to a different tablespace. In both of these cases (and more) we end up with a tablespace a lot larger than the segments in it and we need to reduce the tablespace size and release the extra space back to the filesystem (or ASM). The problem is that often, when we use the “alter database datafile … resize” command we get the error “ORA-03297: file contains used data beyond requested RESIZE value”.
Understanding the Problem
In order to understand what it means and what to do, let’s first understand how the file looks like. A file (in general and Oracle datafiles are no different) is a set of blocks of data and it has a beginning and end. Even though it is physically split to parts that are stored on different locations on the disk, the blocks and the file parts are ordered. Just for simplicity and to make sure I’m clear, let’s take a file of 100MB and assume that the OS is splitting it into 10MB parts. We now have 10 parts of 10MB each and each part is stored on a different location on the disk (a single part is continuous on the disk). When a process accesses the file, from its point of view there is a 100MB continuous file, starting with block 1 and ending with the last block of the last part of the file (in Oracle, if the block is 8KB so the last block will be block number 12800). This is how we see stuff in Oracle data dictionary as well (block_id in dba_extents is exactly that).
So what is the problem? When we want to reduce the size of a datafile, it can only be done from the end of the file. We cannot release space in the middle of the file and “glue” the blocks around it. We can only release blocks at the end of the file and just mark an “earlier” block as the end of the file.
Now that the problem is clear, we can easily understand that the solution is to clear the end of the file and then we will be able to reduce its size.
The concept is to find the last block of the last segment in the file. As we have a lot of space in the tablespace (the space we wish to release), we will use the “alter table… move” or the “alter index … rebuild” to recreate the segment in the same tablespace. This will create a new segment before deleting the old one, so the new segment will have to be created in a different place in the tablespace, freeing the end of the file. Now we can do that again and again, until the end of the file is large enough to run the “alter database datafile … resize” without failures. It can be a hard work and take some time (it might need downtime as well), but it works.
When I first ran into this problem, I realized that there is no information about where is the last block of the file and which segment it belongs to, so I wrote scripts for that.
The first script will simply give you the location of the last block of the last segment in every file, so you can know what is the smallest size you can provide to the “alter database datafile … resize” command:
The next script is similar to the first one, but it will also give you the segment type and name, so you will know which segment to move (note that it may take some time to execute):
And the last one (and I published it in my OTN article in the past), is for mapping an entire tablespace, so you can know exactly where each segment is: