Reduce Oracle Datafile Size

---

Disclaimer

Click the images in the article below to toggle between thumbnail and full-size. Enlarge all | Reduce all

---

Introduction

This article shows how to view space utilization statistics for an Oracle tablespace and reduce the size of its datafile if it has a lot of unneeded free space.

Check for Unused Space

Here’s a script you can run in SQL Developer or SQL *Plus. It will show the space utilization for a single tablespace. The tablespace name is on the last line of the script. I’m using “VISION” in the script below. Replace “VISION” with the name of your tablespace and run the script under a System account.

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name 
and df.tablespace_name = 'VISION' ;
                	

On my system, the script produced the output below, showing that the tablespace called “VISION” has been allocated with 2GB but it's only using 27MB.

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
VISION                                 27       2021       2048         99
					

Resize the Datafile

I'm going to resize the datafile to 200MB. This will recover about 1.8GB and still leave plenty of room for growth. This tablespace is used in my Oracle Hyperion EPM System as the data source for a sample Planning Application, so to be on the safe side I stopped all Hyperion services before running the command that reduces the datafile size.

Here's the command to reduce the size of the datafile. The path to the datafile and the datafile name may vary on your system.

ALTER DATABASE DATAFILE 'C:\APP\ORACLE_SA\ORADATA\ORCL\VISION_DATA.dat'
   RESIZE 200M;
				

After running the command, the script we ran earlier now shows that the datafile size has been reduced to 200MB.

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
VISION                                 27        173        200         87
				

Final Remarks

This allows us to make generous initial allocations for our tablespace datafiles and then recover unused space once the tablespace has been populated with tables and other objects.

-Tom Cockerline, February 2017