Closure
Closure
Home » Databases » How to free up reserved space from datafiles / tempfiles of an Oracle database

How to free up reserved space from datafiles / tempfiles of an Oracle database

How to free up reserved space from datafiles / tempfiles of an Oracle database
, , , , Leave a comment
How to free up reserved space from datafiles / tempfiles of an Oracle database
Latest posts by Stratos Matzouranis (see all)

In this article we will see a script with which we can easily free up space from datafiles and tempfiles where it is not used in Oracle databases. More specifically, we will reduce the size of the datafiles so that they are within the limit of High Water Mark (HWM) everyone's.

With this code we can free space from a specific tablespace or from the entire database. By executing it, we will create change commands with the parameter resize.

The code

Running the following code from a command window alter commands will be generated who will do it resize, as well as informing us about the total space that will be freed from the filesystem if these commands are executed. We can also define a threshold so that space is released from only a specific tablespace and only from as many datafiles as are freed above a certain space limit (free space threshold).

For the example, it is set to generate alter statements only for datafiles that will be freed up more than 256mb of space and for those that belong to the tablespace. USERS:

set heading on;
set linesize 300;
set echo off;
column cmd format a300 word_wrapped
column value new_val block_size
select value from v$parameter where name = 'db_block_size';



--generate commands

select 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&block_size)/1024/1024 )  || 'm;' cmd
from dba_data_files df
inner join ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) de on df.file_id = de.file_id
where 1=1
and ceil( blocks*&&block_size/1024/1024) - ceil( (nvl(hwm,1)*&&block_size)/1024/1024 ) > 256 --free space threshold
and tablespace_name = 'USERS'  --filter tablespace
;
     
--generate tempfile commands

select 'alter database tempfile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&block_size)/1024/1024 )  || 'm;' cmd
from dba_temp_files df
inner join ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) de on df.file_id = de.file_id
where 1=1
and ceil( blocks*&&block_size/1024/1024) - ceil( (nvl(hwm,1)*&&block_size)/1024/1024 ) > 512 --free space threshold
--and file_name like '%TEMPORARY_A%'  
and tablespace_name = 'TEMPORARY'
;

--estimate reclaimed space

select 
      ('Space it ll be saved (MB): ' || sum(ceil( blocks*&&block_size/1024/1024) - ceil( (nvl(hwm,1)*&&block_size)/1024/1024 ))) space_save
from dba_data_files df
inner join ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) de on df.file_id = de.file_id
where 1=1
and ceil( blocks*&&block_size/1024/1024) - ceil( (nvl(hwm,1)*&&block_size)/1024/1024 ) > 256 --free space threshold
and tablespace_name = 'USERS'  --filter tablespace
;

Running the script will show us the block size, the alter commands and the total space it will free up. If we want to proceed with the resizing, we execute all the alter commands it has shown us:

SQL> 
8192

alter database datafile '/oracle/oradata/users_44.DBF' resize 27158m;
alter database datafile '/oracle/oradata/users_74.DBF' resize 6094m;
alter database datafile '/oracle/oradata/users_58.DBF' resize 31390m;
alter database datafile '/oracle/oradata/users_59.DBF' resize 30227m;
alter database datafile '/oracle/oradata/users_60.DBF' resize 28979m;
alter database datafile '/oracle/oradata/users_75.DBF' resize 3465m;
alter database datafile '/oracle/oradata/users_68.DBF' resize 26756m;
alter database datafile '/oracle/oradata/users_61.DBF' resize 29126m;
alter database datafile '/oracle/oradata/users_63.DBF' resize 27440m;
alter database datafile '/oracle/oradata/users_40.DBF' resize 27479m;
alter database datafile '/oracle/oradata/users_70.DBF' resize 27351m;
alter database datafile '/oracle/oradata/users_62.DBF' resize 27587m;
alter database datafile '/oracle/oradata/users_67.DBF' resize 26682m;

13 rows selected

Space it ll be saved (MB): 71751

Sources:

Share it

Leave a reply