Query size datafiles of a tablespace

Published by Alessandro Caredda on 8 March 20238 March 2023

With the article Query size datafiles of a tablespace, we will explain how to identify the sizes of each individual data file in a tablespace.

With this query, it is possible to understand if a data file has reached its maximum size or if it still has extents available.

Let’s see how it works.

The columns selected by the query are:

TABLESPACE_NAME – Name of the tablespace
FILE_NAME – Name of the data files that make up a tablespace
BYTES – Size of the data file converted to MB
MAXBYTES – Maximum size that the data file can reach
AUTOEXTENSIBLE – Indicator if the data file is auto-extendable
STATUS – The status of the data file, either ONLINE or OFFLINE
The information is retrieved from the DBA_DATA_FILES and the DBA_TABLESPACE tables and related by the JOIN a.tablespace_name = b.tablespace_name.

Query size datafiles of a tablespace:

col file_name for a80
col tablespace_name for a22 
set lines 200 
set pages 999 
col status for a7
select a.tablespace_name, 
--a.file_id, 
a.file_name, 
a.bytes/(1024*1024) MB_ATT, 
a.MAXBYTES/(1024*1024) MB_MAX,substr(a.AUTOEXTENSIBLE,1,1) AUTOEXT, b.status
from dba_data_files a, dba_tablespaces b
where a.TABLESPACE_NAME LIKE '%&tbsp%'
and a.tablespace_name = b.tablespace_name
--and a.AUTOEXTENSIBLE='YES' 
order by a.file_name;

Once the output is retrieved, we can understand if we can extend an existing datafile, since the size is still far from the max size, or if we need to create a new datafile.

On scriptdba.com you will find guides, tutorials, and troubleshooting for the most enterprise database out there!

To view my guides on CRS commands click here! At this LINK, however, you will find SQL queries

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin

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