A recent question on the OTN database forum raised the topic of returning free space in a tablespace to the operating system by rebuilding objects to fill the gaps near the start of files and leave the empty space at the ends of files so that the files could be resized downwards.
This isn’t a process that you’re likely to need frequently, but I have written a couple of notes about it, including a sample query to produce a map of the free and used space in a tablespace. While reading the thread, though, it crossed my mind that recent versions of Oracle introduced a feature that can reduce the amount of work needed to get the job done, so I thought I’d demonstrate the point here.
When you move a table its indexes become unusable and have to be rebuilt; but when an index becomes unusable, the more recent versions of Oracle will drop the segment. Here’s a key point – if the index becomes unusable because the table has been moved the segment is dropped only AFTER the move has completed. Pause a minute for thought and you realise that the smart thing to do before you move a table is to make its indexes unusable so that they release their space BEFORE you move the table. (This strategy is only relevant if you’ve mixed tables and indexes in the same tablespace and if you’re planning to do all your rebuilds into the same tablespace rather than moving everything into a new tablespace.)
Here are some outputs demonstrating the effect in a 18.104.22.168 database. I have created (and loaded) two tables in a tablespace of 1MB uniform extents, 8KB block size; then I’ve created indexes on the two tables. Running my ts_hwm.sql script I get the following results for that tablespace:
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE ------- ----------- ----------- ---------- --------------- ------------------ 5 128 255 TEST_USER T1 TABLE 256 383 TEST_USER T2 TABLE 384 511 TEST_USER T1_I1 INDEX 512 639 TEST_USER T2_I1 INDEX 640 65,535 free free
Notice that it was a nice new tablespace, so I can see the two tables followed by the two indexes at the start of the tablespaces. If I now move table t1 and re-run the script this is what happens:
alter table t1 move; FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE ------- ----------- ----------- ---------- --------------- ------------------ 5 128 255 free free 256 383 TEST_USER T2 TABLE 384 511 free free 512 639 TEST_USER T2_I1 INDEX 640 767 TEST_USER T1 TABLE 768 65,535 free free
Table t1 is now situated past the previous tablespace highwater mark and I have two gaps in the tablespace where t1 and the index t1_i1 used to be.
Repeat the experiment from scratch (drop the tables, purge, etc. to empty the tablespace) but this time mark the index unusable before moving the table and this is what happens:
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE ------- ----------- ----------- ---------- --------------- ------------------ 5 128 255 free free 256 383 TEST_USER T2 TABLE 384 511 TEST_USER T1 TABLE 512 639 TEST_USER T2_I1 INDEX 640 65,535 free free
Table t1 has moved into the space vacated by index t1_i1, so the tablespace highwater mark has not moved up.
If you do feel the need to reclaim space from a tablespace by rebuilding objects, you can find that it’s quite hard to decide the order in which the objects should be moved/rebuilt to minimise the work you (or rather, Oracle) has to do; if you remember that any table you move will release its index space anyway and insert a step to mark those indexes unusable before you move the table you may find it’s much easier to work out a good order for moving the tables.
Footnote: I appreciate that some readers may already take advantage of the necessity of rebuilding indexes by dropping indexes before moving tables – but I think it’s a nice feature that we can now make them unusable and get the same benefit without introducing a risk of error when using a script to recreate an index we’ve dropped.