You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.
First try and "shrink space" for each affected table:
alter table fragmentedtable enable row movement;
alter table fragmentedtable shrink space;
Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.
The following (taken from Ask Tom & assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
For example:
FILE_NAME SHRINK_TO CURRENT_SIZE SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf 696 700 4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf 579 610 31
/u01/app/oracle/oradata/PHIL112/users01.dbf 85 93 8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf 65 120 55
SQL>
To resize, use:
alter database datafile '/path/to/data/file.dbf' resize 100m;
The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.
To summarize the answers here and to inject my own advice:
1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.
2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.
3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.
4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.
If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.
Best Answer
In shrink action, you need to choose:
And choose desidered size.
Actually you don't have released space because you have still used pages ad the end of your file.
Edit:
You can do it via query, with instructions like this: