Oracle datafile defragmentation

Question

In my system permanent data is separated from some temporary (like log). It's done by storing log tables (user-defined program log, don't mix up with system log) in a different tablespace from the main one.

So there are two tablespaces MAIN and LOG. One datafile is for each tablespace. Both datafiles maxsize is set to 4GB, their initial sizes are 8MB, and they autoextend on next 8MB.

Since I use express edition of oracle, I need them to be not more than 4GB in sum.

And it happens sometimes when I need to reduce the size of LOG datafile in order to free some space for necessary data.

Now I do it like this:

truncate table schema_name.log_table;
alter database datafile '/path/to/the/log/datafile/log1.dbf' resize 128M;

And it works, because truncate gets rid of all the information in the datafile.

But what if I came across a situation when I need to free some size for LOG tablespace from MAIN one. I truncated several tables there (or I know there's much free space in the datafile, it's allocated but not occupated by data, were used before for some operations) and now need to defragment MAIN tablespace's datafile in order to decrease it's size. Without defragmentation I get an exception:

ORA-03297: file contains used data beyond requested RESIZE value

So am I able to perform a defragmentation operation on a datafile somehow?

Answer 1

There are basically three levels of space allocation to think about.

1. The space used by rows in table and index segments. Deleting rows is generally enough to free up space for new data in that segment, but that does not free up space that the segment occupies in the data file.

2. Segments use up space in the data files. Truncating a table shrinks the segments by default, or if a segment has a lot of free space in it you can move the table and/or rebuild the indexes. This will also change the location of the segment in the data file, which can be relevant for ...

3. Space that the data file uses on storage. Shrinking the file is possible but the location of segments in the file is critical. You can only shrink to the end of the last segment. If you have the time and resources you can move/rebuild the segment to a different table space and then shrink the data files and move/rebuild them back again.

This (3) is the issue that you're facing. The complete move and rebuild is not the only option ... You could export everything or try to move/rebuild only segments at the end of the data file, but that's rather more complex. Avoid it if you can.