Oracle – Shrinking/Reclaiming Free Tablespace Space

The short answer is No. Unfortunately the way to do this in Oracle does require the "big stack of boring queries". The articles you linked to are some of the best information available on the subject. The datafile does indeed become fragmented, so that even if free space exists below the highest segment, Oracle will not automatically consolidate it when a RESIZE is done.

To "defragment" the tablespace you need to move these segments to the start of the datafile rather than at the end. For tables this is an offline process meaning the table will be unavailable while the move is taking place. Indexes can be moved either offline or with Enterprise Edition they can be moved online. Since you have an outage window I recommend you follow these steps.

A. Shrink datafiles with free space beyond the high water mark. This can be done as follows (the query is similar to Frosty Z's procedure):

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'alter database datafile '''|| file_name || ''' resize ' || 
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a 
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) 
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.

SELECT DISTINCT tablespace_name FROM
(      
    SELECT tablespace_name, block_id + blocks LastBlock,
       lead(block_id) OVER (PARTITION BY File_ID 
          ORDER BY tablespace_name, file_id, block_id) NextBlock
       FROM dba_free_space 
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;

C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)

SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
   SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
   FROM dba_free_space
   WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;

D. Move each table and rebuild the indexes and statistics.

E. Repeat step A.

I just built most of these queries, so you will want to thoroughly test them before use. I suppose you could create a procedure that would use EXECUTE IMMEDIATE to create the actual statements to run dynamically, but because queries will receive ORA-08103: Object no longer exists while the move is in progress, I think it is best to control that process manually even if it does mean a bit more time/effort.

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a great post about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move;

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

1. The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
2. You're not specifying a tablespace when creating the index and it's not using the one you think, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace:

select * from dba_users where username = 'MY_USER'

If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table 
    on my_table ( my_column ) 
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use DBMS_STATS.GATHER_TABLE_STATS() and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.