Three different ways to determine Oracle database size

This article explains the different ways to determine Oracle database size. We are going to learn about the following topics:

1. Populate the size using catalog views
2. Populate the size of all pluggable databases
3. 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.

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;

Output

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#;

Output

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;

Output

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;

Output

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

Output

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;

Output

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;

Output

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:

1. 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.
2. If the DB Admin module is not present, go to Database | Administer | Tablespaces to get the same information.
3. Optional: Query the numbers and add them up; to find out how many megabytes are allocated by ALL tablespaces, use the following query:
   1. select sum(bytes)/1024/1024 from dba_data_files;

To find the size (in megabytes) of your redo logs:

1. 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’;

Summary

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.

1. The size of data file, redo log file and temp files.
2. The oracle database size with used space and free space in the database.
3. The size of the pluggable database and CDBs.
4. How to use Toad for Oracle to find database size.

Related Links

Blog: DB performance: 120-point database health check across multiple databases

Blog: Toad code review – useful to the programmer

Blog: How to load data from Excel into Oracle using Toad®

Blog: What is a code review and why PL/SQL code quality is important

Blog: Code analysis: Why PL/SQL code quality matters

Blog: Advanced code review using Code Analysis

Blog: Analyzing Code with the Toad for Oracle Code Analysis Tool

Blog: How to identify the size of a database in Toad for Oracle (67031)

Blog: Simplify SQL Trace with Quest® Toad® for Oracle DBA edition

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.