{"id":5945,"date":"2025-09-01T07:00:00","date_gmt":"2025-09-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=5945"},"modified":"2025-09-01T11:23:33","modified_gmt":"2025-09-01T08:23:33","slug":"pws-apeleutheronoume-desmeumeno-xoro-apo-datafiles-tempfiles-se-oracledatabase","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pws-apeleutheronoume-desmeumeno-xoro-apo-datafiles-tempfiles-se-oracledatabase\/","title":{"rendered":"How to free up reserved space from datafiles \/ tempfiles of an Oracle database"},"content":{"rendered":"

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)<\/a> everyone's.<\/p>\n\n\n\n

With this code we can free space from a specific tablespace or from the entire database. By executing it, we will create change commands<\/strong> with the parameter resize<\/strong>.<\/p>\n\n\n\n

The code<\/h2>\n\n\n\n

Running the following code from a command window alter commands will be generated<\/strong> who will do it resize<\/strong>, 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<\/strong> 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).<\/p>\n\n\n\n

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<\/code>:<\/p>\n\n\n\n

set heading on;\nset linesize 300;\nset echo off;\ncolumn cmd format a300 word_wrapped\ncolumn value new_val block_size\nselect value from v$parameter where name = 'db_block_size';\n\n\n\n--generate commands\n\nselect 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&block_size)\/1024\/1024 )  || 'm;' cmd\nfrom dba_data_files df\ninner 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\nwhere 1=1\nand ceil( blocks*&&block_size\/1024\/1024) - ceil( (nvl(hwm,1)*&&block_size)\/1024\/1024 ) > 256 --free space threshold\nand tablespace_name = 'USERS'  --filter tablespace\n;\n     \n--generate tempfile commands\n\nselect 'alter database tempfile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&block_size)\/1024\/1024 )  || 'm;' cmd\nfrom dba_temp_files df\ninner 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\nwhere 1=1\nand ceil( blocks*&&block_size\/1024\/1024) - ceil( (nvl(hwm,1)*&&block_size)\/1024\/1024 ) > 512 --free space threshold\n--and file_name like '%TEMPORARY_A%'  \nand tablespace_name = 'TEMPORARY'\n;\n\n--estimate reclaimed space\n\nselect \n      ('Space it ll be saved (MB): ' || sum(ceil( blocks*&&block_size\/1024\/1024) - ceil( (nvl(hwm,1)*&&block_size)\/1024\/1024 ))) space_save\nfrom dba_data_files df\ninner 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\nwhere 1=1\nand ceil( blocks*&&block_size\/1024\/1024) - ceil( (nvl(hwm,1)*&&block_size)\/1024\/1024 ) > 256 --free space threshold\nand tablespace_name = 'USERS'  --filter tablespace\n;\n<\/pre>\n\n\n\n
<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
SQL> \n8192\n\nalter database datafile '\/oracle\/oradata\/users_44.DBF' resize 27158m;\nalter database datafile '\/oracle\/oradata\/users_74.DBF' resize 6094m;\nalter database datafile '\/oracle\/oradata\/users_58.DBF' resize 31390m;\nalter database datafile '\/oracle\/oradata\/users_59.DBF' resize 30227m;\nalter database datafile '\/oracle\/oradata\/users_60.DBF' resize 28979m;\nalter database datafile '\/oracle\/oradata\/users_75.DBF' resize 3465m;\nalter database datafile '\/oracle\/oradata\/users_68.DBF' resize 26756m;\nalter database datafile '\/oracle\/oradata\/users_61.DBF' resize 29126m;\nalter database datafile '\/oracle\/oradata\/users_63.DBF' resize 27440m;\nalter database datafile '\/oracle\/oradata\/users_40.DBF' resize 27479m;\nalter database datafile '\/oracle\/oradata\/users_70.DBF' resize 27351m;\nalter database datafile '\/oracle\/oradata\/users_62.DBF' resize 27587m;\nalter database datafile '\/oracle\/oradata\/users_67.DBF' resize 26682m;\n\n13 rows selected\n\nSpace it ll be saved (MB): 71751\n<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n