Sql-server – Why can’t I get the available free space after shrinking

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.