Jun 14, 2022 8:00:00 AM by Nisarg Upadhyay
This article explains the different ways to determine Oracle database size. We are going to learn about the following topics:
The Oracle database size depends on utilization and free space; therefore, we must know the size of the data file, redo log files and temp file. Also, you can calculate the size of the database by getting the size occupied by the user data. To get the size occupied by the user data, you can query the dba_segments catalog view.
First, let’s understand the various system catalog views used to get the size of the database.
Following is the list of the catalog views that can be used to populate the Oracle database size.
Catalog View Name
This catalog view provides the information of the oracle database file.
This catalog view provides the information of the temporary in oracle database.
This catalog view provides the information of the redo log file in the oracle database. The information is displayed from the control file.
This catalog view provides the information of the control file of the oracle database.
First, let us find the size of the database files.
Script 1: Populate oracle database file size.
select file_name "Datafile location",tablespace_name "Tablespace Name", SUM(BYTES)/1024/1024 "Datafile size in MB" from dba_data_files group by file_name,tablespace_name;
Script 2: Populate redo log file size of oracle database.
SELECT a.group# "Log file group", b.member "Log file name and location", a.members "Members of the logfile group", a.bytes/1024/1024 "Log file size in MB", a.status "Log file status" FROM v$log a, v$logfile b WHERE a.group# = b.group#;
Script 3: Populate temp file size.
select file_name "Name and Location of file",tablespace_name "Tablespace name", bytes/1024/1024 "Size" from dba_temp_files;
Script 1: Populate the actual Oracle database size consumed on disk.
SELECT SUM (bytes) / 1024 / 1024 "Physical Size of Database in MB" FROM dba_data_files;
Script 2: Populate the oracle database size with used space and free space.
select round(sum(usedsize.bytes) / 1024 / 1024 ) "Overall Database Size"
, round(sum(usedsize.bytes) / 1024 / 1024 ) - round(freespace.p / 1024 / 1024 ) "Used space"
, round(freespace.p / 1024 / 1024 ) "Free space"
from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes
from v$log) usedsize , (select sum(bytes) as p from dba_free_space) freespace group by freespace.p
Script 3: Populate the database size of all pluggable databases.
select con_id "Container ID", name "Pluggable Database Name", open_mode "Pluggable Database State", total_size/1024/1024 "Total Size of database in MB" from v$pdbs;
Script 4: Populate the database size of CDB.
SELECT SUM(BYTES)/1024/1024 "CDB size in MB" FROM cdb_data_files ORDER BY CON_ID;
There isn’t a field or tool exactly that indicates the size of a database, but the majority of the size comes from the datafiles of tablespaces. In Toad, there is a graphical representation of the space usage in Tablespaces that can be accessed in two ways:
To find the size (in megabytes) of your redo logs:
The two above queries will show you how large your database is (essentially).
Also, if the database is in archivelog mode, files are generated in the archivelog destination. Issue the following query to see where the archived redo logs get placed: select * from v$parameter where name = 'log_archive_dest';
Try Toad free for 30 days.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
In this article, we learned a few different methods to populate the database size, and got familiar with the SQL Scripts and Queries that are used to populate the following information.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.
Tags: Toad for Oracle
Written by Nisarg Upadhyay
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 11 years of experience with SQL Server administration and 2 years in managing oracle database. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on firstname.lastname@example.org.