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.
The Solution
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.
Scripts
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:
| select a.tablespace_name, a.file_name, a.bytes/1024/1024 file_size_MB, | |
| (b.block_id+b.blocks-1)*d.db_block_size/1024/1024 highwater | |
| from dba_data_files a , | |
| (select file_id,block_id,blocks | |
| from | |
| (select file_id,block_id,blocks,row_number() over (partition by file_id order by block_id desc) num | |
| from dba_extents) | |
| where num=1) b, | |
| (select value db_block_size | |
| from v$parameter | |
| where name='db_block_size') d | |
| where a.file_id = b.file_id | |
| order by a.tablespace_name,a.file_name; |
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):
| select e.file_id,e.owner,e.segment_type,e.segment_name, | |
| (e.block_id+e.blocks-1)*d.db_block_size/1024/1024 highwater | |
| from dba_extents e, | |
| (select value db_block_size | |
| from v$parameter | |
| where name='db_block_size') d | |
| where (e.file_id,e.block_id) in (select file_id,max(block_id) from dba_extents group by file_id); |
And the last one is for mapping an entire tablespace, so you can know exactly where each segment is:
| select * from | |
| (select block_id first_block, block_id+blocks-1 last_block, bytes/1024 size_kb, 'USED' type, owner, segment_name, segment_type | |
| from | |
| dba_extents | |
| where tablespace_name=upper('&&tbs_name') | |
| union all | |
| select block_id first_block, block_id+blocks-1 last_block, bytes/1024 size_kb, 'FREE' type, null, null, null | |
| from | |
| dba_free_space | |
| where tablespace_name=upper('&&tbs_name') | |
| ) | |
| order by first_block; |
So the highwater mark is the smallest you can shrink the datafile without getting the ORA-03297?
Exactly. If you know you have much more empty space, you will have to move segments from the end of the file and then try again.
Thank you Liron, that is very helpful.
In some situations it might be easier and simpler to create a new tablespace and move everything to the new one, rather than identifying and moving the specific segments. From 11g there are options for online redefinition which can help this be done whilst the database is active.
Don’t forget that moving a table alters all the rowids in the table, so indexes and materialized view logs may well need to be rebuilt also.
Thanks Martin, I agree with everything you wrote.
In some cases, however, you have a lot of free space and only one segment at the end, so you can simply move it and free a lot of space.
The comment about the change of rowids is very important.