Toad World Blog

Three different ways to determine Oracle database size

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:

  1. Populate the size using catalog views
  2. Populate the size of all pluggable databases
  3. Using Toad for Oracle

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-Jan-18-2022-05-54-52-11-PM

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

 

Details
dba_data_files

This catalog view provides the information of the oracle database file.

  • File Name: The database filename.
  • FILE ID: The database file identifier.
  • TABLESPACE NAME: The tablespace name to which the database file belongs.
  • BYTES: Size of the database file.
  • BLOCKS: Size of oracle database block.
  • STATUS: The database file status. The database file status can be any of the following:
    • AVAILABLE
    • INVALID
  • RELATIVE FILE NO: The tablespace-relative file number.
  • AUTOEXTENSIBLE: If the database file is extendable or not.
  • MAX BYTES: Maximum size of the database file.
  • MAX BLOCKS: Maximum size of the oracle database file.
  • INCREMENT BY: Number of the tablespace blocks used to auto extension increment.
  • USER BYTES: The size of database files that can be used to store the user data.
  • ONLINE STATUS: Online status of the database file. The database file status can be any of the following
    • SYSOFF
    • SYSTEM
    • OFFLINE
    • ONLINE
    • RECOVER
dba_temp_files

This catalog view provides the information of the temporary in oracle database.

  • File Name: The file name and physical location of the database temporary file.
  • FILE ID: File ID.
  • TABLESPACE NAME: The temporary tablespace name to which the database temp file belongs.
  • BYTES: Size of database temporary file.
  • BLOCKS: Size of oracle database block.
  • STATUS: The database temp file status. The database temp file status can be any of the following:
    • AVAILABLE
  • RELATIVE FILE NO: The relative file number of the tablespace.
  • AUTOEXTENSIBLE: If the database file is extendable or not. The valid options are Yes or
  • MAX BYTES: Maximum size of the database temp file.
  • INCREMENT BY: The default increment size for auto extension of database temp file.
  • USER BYTES: The size of a good portion of the database file. The unit is in oracle block.
V_$log

This catalog view provides the information of the redo log file in the oracle database. The information is displayed from the control file.

  • Group#: The redo log group number.
  • THREAD#: The thread number of the redo log.
  • SEQUENCE#: The log sequence number of the redo log.
  • BYTES: The size of the redo log file. The unit is in Bytes.
  • MEMBERS: The total number of members of the redo log filegroup.
  • ARCHIVED: Archive status of the redo log. The valid values are.
    • YES
    • NO
  • Status: Status of the redo log file. The status can be any of the following:
    • UNUSED
    • CURRENT
    • ACTIVE
    • CLEARING
    • CLEARING CURRENT
    • INACTIVE
  • First_Change#: The Lowest system change number in the redo log.
  • FIRST_TIME: The first SCN (System change number) in the redo log.
V$controlfile

This catalog view provides the information of the control file of the oracle database.

  • Name: The name and location of the control file.
  • IS_RECOVERY_FILE_DEST: This indicates whether the control files have been created in the fast recovery area. The valid values are following:
    • YES
    • NO
  • BLOCK_SIZE: The control file block size. The unit is bytes.
  • FULL_SIZE_BLOCK: The control file size. The unit is an oracle block.

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

oracle database size 1

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

oracle database size 2

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

oracle database size 3

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

oracle database size 4

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

oracle database size 5

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

oracle database size 6

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

oracle database size 7

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

Try Toad free for 30 days. 

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

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.

 

Tags: Toad for Oracle

Nisarg Upadhyay

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 nisargupadhyay87@outlook.com.