Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

  data file resizing shrinking tips

Oracle Tips by Burleson Consulting

April 4, 2014

Question:  How do I reduce the size of my Oracle data file sizes?  I want to shrink the size of my data files to reclaim un-used space.  How do I reduce the file sizes?

Answer:  (by John Weeg) You can start reducing the size of your Oracle database files by setting Free Your Space

Conventional database wisdom dictates that we should treat disk space as though it were free, unfettered space waiting to be filled with data. It's a liberating idea, but not entirely practical. Not surprisingly, it's also rife with potential problems. Get too comfortable with the idea of space being "free" and you may suddenly find yourself scrounging around for it.
 
When setting up a database, for example, a database administrator usually takes educated guesses at the company's space needs, large or small. It's important to point out, however, that those guesses tend to be conservative. The reason? By overestimating the amount of space needed, the administrator is less likely to wind up stuck when he or she first loads all the data. Once the instance runs for a while, it's possible to see just how far off the original estimate of space requirements was. Moreover, the administrator can give back some of that space.
 
Over-allocation of space at the file level affects the backup/recovery window, file checking times and, most painfully, limits the potential allocation of space to a tablespace that needs the extra room. A simpler solution would be to review the evolution of the script, which lets the administrator know which files can and cannot be resized to create more space.
Alter Database
It's possible to release space from data files but only down to the first block of data. This is done with the 'alter database' command. Rather than go through the tedious process of manually figuring out the command every time it's used, it makes more sense to write a script that will generate this command as needed.
 
The basic syntax for this command is:

Alter database name datafile 'file_name' resize size;

 

Where name is the name of the database, file_name is the name of the file and size is the new size to make this file. We can see this size change in the dba_data_files table as well as from the server.

 

First, pull in the database name:

 

Select 'alter database '||a.name
From v$database a;


Once that has been done, it's time to add in data files:

 

select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b;

 

While this is closer to the ultimate solution, it's not quite there yet. The question remains: Which data files do you want to alter? At this point, you can use a generally accepted standard, which allows tablespaces to be 70 percent to 90 percent full. If a tablespace is below the 70 percent mark, one way to bring the number up is to de-allocate some of the space.

 

So how do you achieve percent full? While there are a number of different ways, simple is usually ideal. Here's how it works.

 

Amount of data file space used:

 

Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name;

 

Total available data file space:

 

Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name;

 

So if we add this with our original statement, we can select on pct_used (less than 70 percent):

 

select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
;

 

According to the command, a selection has been made based on tablespace. What if you want to resize based on file? It's crucial to remember that multiple files can exist in any tablespace. Plus, only space that is after the last data block can be de-allocated. So the next step should be to find the last data block:

 

select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id;

 

Now that the command to find the last data block has been inserted, it is time to find the free space in each file above that last data block:

 

Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id;

 

So far, so good. How is it possible, then, to combine commands to ensure the correct amount will be resized? In fact, it's fairly easy.

 

select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||(bytes_total-bytes_free)
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name

Double Checking the Data file shrink

Now, the thing to do is ensure that the right amount of space - not too much, not too little - has been de-allocated. The rule of thumb to follow: Do not go above 70 percent of the tablespace being used. If you have already pulled out how much is used from dba_extents, you can simply add a check to your statement:

 

select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id
;

 

One last thing to do: Add a statement to indicate what is being changed.

 

select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||
'% full now '||
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))||'%'
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id

;

 

At last, here's a script that will create the script. Even so, it's important to pay careful attention when applying the created script. Why? Because Rollback, System and Temporary tablespaces are vastly different creatures, and each should not necessarily be held to the 70 percent rule. By the same token, there might be a very good reason for a tablespace to be over allocated -- like the giant load that will triple the volume tonight.

 

A word of caution, too: Be sure that extents can still be allocated in each tablespace. There may be enough free space, but it may be too fragmented to be useful. That problem will be the focus of another article.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster