This article explains the different ways to determine Oracle database size. We are going to learn about the following topics:
- Populate the size using catalog views
- Populate the size of all pluggable databases
- Using Toad for Oracle
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.
List of catalog views that are used to populate the Oracle database size
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.
Populate Data file, redo log, and temp file size.
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;
Populate Database size.
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;
Identify database size in Toad for Oracle
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:
- If the DB Admin module is an add-on on the license key, go to Database | Monitor | Database Browser. In the new window, click on the database in question and then click on the Space Usage tab.
- If the DB Admin module is not present, go to Database | Administer | Tablespaces to get the same information.
- Optional: Query the numbers and add them up; to find out how many megabytes are allocated by ALL tablespaces, use the following query:
- select sum(bytes)/1024/1024 from dba_data_files;
To find the size (in megabytes) of your redo logs:
- select sum(bytes)/1024/1024 from v$log;
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.
- The size of data file, redo log file and temp files.
- The oracle database size with used space and free space in the database.
- The size of the pluggable database and CDBs.
- How to use Toad for Oracle to find database size.
Have questions, comments?
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
Help your colleagues
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.