Oracle tablespaces can be set to auto-extend, but not to auto-contract. As a result, one can end up with a 10GB datafile that only has 10MB of stuff in it. Enterprise Manager Console and various other tools make shrinking a datafile a point-and-click operation, but remember what I said about tools? Here is how to look at your datafiles in SQL*Plus to see which ones are shrinkable:

SET LINESIZE 120
COLUMN file_name FORMAT a45
COLUMN percent_free FORMAT 99.00
CREATE VIEW free_space AS (
     SELECT SUM(bytes) free_bytes, tablespace_name
     FROM dba_free_space
     GROUP BY tablespace_name
);
SELECT d.tablespace_name,
           d.bytes overall_size,
           f.free_bytes/d.bytes*100 percent_free,
           d.file_name
     FROM free_space f, dba_data_files d
     WHERE f.tablespace_name = d.tablespace_name;
DROP VIEW free_space;

I’m creating a view and dropping it here for the simple reason that I’m too lazy to bother with subqueries right now and performing some quick and dirty DDL isn’t a big deal. Anyway, this will show you each tablespace, how big it is, and how much of it is being used. High % free + high byte count = tablespace that can be shrunk. I’m pretty sure this query will break if a tablespace uses more than one datafile, but as they say this is good enough for government work.

Once you have the path to the datafile you want to shrink, it’s just an ALTER DATABASE command to resize it:

ALTER DATABASE
     DATAFILE 'd:\oracle\oradata\dev1\mydatafile.ora' RESIZE 500M;

Oracle throws a nice error if you try to make it smaller than its contents.