Query to retrieve disk space allocated by DATAFILES but not used

With the article “Query to retrieve disk space allocated by DATAFILES but not used”, we provide a statement that allows you to recover disk space, so in case of emergency, we can try to recover some unused GB from the TABLESPACE.

Before we start with the article “Query to retrieve allocated space by DATAFILES”, we would like to introduce this website to you if you are seeing it for the first time! Scriptdba is the first vertical website on Oracle databases completely in Italian.

In fact, during 2020 we will launch the English version, but Italian will always be there anyway.

Did you get it right? A website in two languages. Why?

Because we like to make things simple, simple for you readers obviously! (a little less for us).

On ScriptDBA.com you will find guides, tutorials, and troubleshooting on the most enterprise database there is!

The space can only be recovered if contiguous, so if it is too fragmented, it will be necessary to carry out reorganization activities to have a linear and non-fragmented write.

Query to retrieve disk space allocated.

Statement to recover space from all data files in a particular file system:

set pages 999
set lines 300
select 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*(select DISTINCT BLOCK_SIZE from dba_tablespaces))/1024/1024 ) || 'm;' cmd
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(+)
and a.file_name like '%/&file_system%'
and ceil(blocks*(select DISTINCT BLOCK_SIZE from dba_tablespaces)/1024/1024)- ceil((nvl(hwm,1)*(select DISTINCT BLOCK_SIZE from dba_tablespaces))/1024/1024 ) > 0;

Statement to recover space from all data files of a TABLESPACE:

set pages 999
set lines 300
select 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*(select DISTINCT BLOCK_SIZE from dba_tablespaces))/1024/1024 ) || 'm;' cmd
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(+)
and a.tablespace_name ='&tbsp'
and ceil(blocks*(select DISTINCT BLOCK_SIZE from dba_tablespaces)/1024/1024)- ceil((nvl(hwm,1)*(select DISTINCT BLOCK_SIZE from dba_tablespaces))/1024/1024 ) > 0;

To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.

Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin

For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.

For any doubt, do not hesitate to comment on the article.