Some years ago I had a situation with a tablespace that was growing at a rapid rate. I contacted the developer who found that an auditing section of the application had been erroneously enabled. He turned off the auditing, deleted the unwanted and unneeded audit records, and declared success. Unfortunately, his ‘fix’ did not help a bit at the OS level—where the filesystem was 95% full. It didn’t even help at the tablespace level. In fact, deleting rows from a table does not free up actual, reportable space at any level. That was true then, and it’s true today.
Levels of Space Management
There are three distinct levels of space management to be aware of in your database: the segment (tables, indexes, etc.) level, the file (or tablespace) level, and the OS (or disk) level. Once rows are deleted from a table, how can we get that reflected all the way back to the OS level?
We first need to remove the “swiss cheese” holes from the table by performing some sort of reorganization. If there is sufficient space to create another tablespace, then “alter table…move…” is in many ways the best option (and my personal favorite). Another good option is to export the table, truncate the table, and then reimport.
There are things to consider with each of the above options, however. With “alter table…move,” you will need to rebuild any indexes that may be on the table as they will be in an “UNUSABLE” state after the table is moved. It’s a good idea to rebuild indexes in any case when a large number of rows have been deleted from a table. It not only shrinks the index segment, but it can have a huge bearing on performance. If doing an export/import for your reorg, you have to consider any foreign keys that may be on the table. Also, the table will be completely unavailable for the duration of the reorg—which may mean downtime for the application.
For this illustration, let’s assume that there is only one table, and its indexes, occupying the tablespace that we’re trying to shrink. Our steps for reorging using the ‘alter table…move’ option are:
1. Create a tablespace to hold the objects temporarily.
SQL> create tablespace REORG datafile '<file name> ' ;
2. Move the table to the REORG tablespace:
SQL> alter table MY_TABLE move tablespace REORG ;
3. Rebuild any indexes on the table in the REORG tablespace as well:
SQL> alter index MY_TABLE_IX rebuild tablespace REORG ;
If there ARE additional segments, you would want to repeat the above steps to move them out of our ballooned up tablespace. Once the tablespace is empty, resize the associated datafiles. If there are a lot of files, using the old “SQL from SQL” trick is best. This will return a series of ‘alter tablespace’ commands to shrink each file:
SQL> select 'alter database datafile '''||file_name||''' resize 128m ;'
2 from dba_data_files
3 where tablespace_name = 'MY_BALLOON' ;
If the files are not already autoextensible, do the following to generate the commands to alter each file and ensure that you don’t blow out the tablespace when moving your data back to it. You want the tablespace to be able to grow just enough, but not too much
SQL> select 'alter database datafile '''||file_name||''' '
2 ||'autoextend on next 128m maxsize unlimited ;'
3 from dba_data_files
4 where tablespace_name = 'MY_BALLOON' ;
Finally, our files have been shrunk and our filesystem utilization is back to normal. Move the table and indexes back using the same steps you used to move them out—just change the destination tablespace name. A snap! Right?
There’s Got to Be an Easier Way!
Yes and no. The following can be used:
SQL> alter table MY_TABLE enable row movement ;
SQL> alter table MY_TABLE shrink space cascade ;
The result of the above is that the table is reorged in place—no moving around or export/importing. This method is really best for addressing performance issues due to table fragmentation. Here’s the problem with using it to recover space—and it’s the same problem that you will have with the export/truncate/import option:
Say your tablespace looks like this, with each rectangle below representing an extent in a table:
If we truncate table ‘A’, we will have this:
We now have some freespace in the tablespace, but we can only shrink the associated file a small amount. Even if we truncate (or shrink) every table in the tablespace, we will have this:
We still cannot shrink the file more than one rectangle (a technical term I just coined):
Only by moving everything out of the tablespace, and then back in, can we recover any significant space:
Now shrink the file:
Three Levels of He--, I Mean, Space Management
If you are simply cleaning up data, then deleting from your table is all that needs to be done. Oracle will fill the holes with new inserts or updates. If you need to recover space in a tablespace, then using “alter table…shrink” will do that job along with the added bonus that your table access (i.e. performance) will be improved. Often you will see significant improvement after a shrink, particularly if the table is large or has seen a high level of “churn.” If your filesystem is full, however, a full blown reorg is often the only way to get from point A to point B. By using the ‘alter table…move’ method, you will not need to worry about table dependencies nearly as much as you will if you have to go the route of exporting the tables, dropping them, and reimporting.
See all articles by Mary Mikell Spence