How To Check Database Size In Oracle

In this Oracle tutorial, we will learn how to check database size in Oracle. We are working on the latest version of Oracle, i.e. 21c, but the steps shared will also work on other versions.

How To Check Database Size In Oracle

Let us discuss all the approaches to check the database size of all the versions of Oracle like Oracle 21c, Oracle 11g, Oracle 12c, etc.

Database size in Oracle 21c

Oracle database combines multiple objects, files, tablespaces, users, etc. Each of them plays a vital role in storing and organizing the data so that it can be presented in minimum time as and when required.

The database values are recorded in bytes (the smallest unit of memory after nibble), and these bytes can be converted into kilobytes (kb), Megabytes (MB), Gigabytes (GB), and so on.

The table below shows the memory unit chart as follows: 1024 bytes = 1kb, 1024 kb = 1MB, and so on.

1 KB	1024 Bytes
1 MB	1024 KB
1 GB	1024 MB
1 TB	1024 GB
1 PB	1024 TB

Dividing the value by 1024 once will give database size in kb, twice will provide value in megabytes (MB), and so on.

All of the databases are divided into three parts:

• Data files: It holds the information of all the files, objects, tablespaces, users, etc.
• Temp Files: It records all the temporary files in the Oracle database.
• Log files: Each activity on the Oracle database is recorded in the form of logs, and these logs are stored in the log files.

In the upcoming sections, we will learn how to write queries using these objects to fetch the database size in Oracle 21c.

Also, check: Oracle check database status

DB size in Oracle 11g

Oracle database 11g was once one of the popular databases, but now we have new versions, such as 12c, 19c, and 21c (latest). Most of the companies have upgraded from 11g to other versions.

We assume that since you are reading this section, you are looking for a way to calculate the database size, so we are going to share the steps for calculating database size in Oracle 11g.

To check database size in Oracle, execute the script below.

-- view total file and bytes
SELECT * 
FROM DBA_DATA_FILES;

-- add all the bytes and display size of database 
SELECT SUM(BYTES) AS "Size in Byte"
FROM DBA_DATA_FILES;

-- display size of database in KB
SELECT SUM(BYTES)/1024 AS "Size in KB"
FROM DBA_DATA_FILES;

-- display size of database in MB
SELECT SUM(BYTES)/1024/1024 AS "Size in MB"
FROM DBA_DATA_FILES;

In the above script, dba_data_files holds the information in the bytes, and using the sum aggregate function, we have added all the bytes together. Later, we divided the value by 1024 to get the values in kb and MB.

Read: Connect to oracle database

Full Database Size in Oracle 21c

In Oracle, the full database size can be calculated by checking the size of the database folder inside the oradata folder.

All the databases are present inside the data folder. In our case, the default orca folder is present, which holds the information for data files, temp files, and log files.

The below image shows the total size and path of the orcl database in Oracle 21c:

In our Oracle query, we have to combine the results of data, temp, and log files to get the full database size in Oracle.

There are multiple ways to combine the results of Oracle queries, but the most efficient and preferred way is to use the union all clause in Oracle.

Example Script:

In the script below, we calculated the full database size in Oracle by combining temp files, data files, and log files. The Union All clause allows us to combine the results of multiple queries.

SELECT SUM(BYTES)/1024/1024/1024 AS "Total Size"
FROM (SELECT BYTES FROM V$LOG
     UNION ALL
      SELECT BYTES FROM V$DATAFILE
     UNION ALL
      SELECT BYTES FROM V$TEMPFILE);

Output:

In the below output, the total size is 4 GB and .53 megabytes. This information differs slightly from the above image because the database keeps growing.

Read: Oracle get database name

Database Size in Oracle in GB

Database size is calculated in bytes in the Oracle database, but you convert the value of bytes in gigabytes (GB) by dividing the bytes 3 times by 1024.

Divide the Oracle database size value in bytes by 1024 x 1024 x1024 to get the Oracle database size in Gigabytes (GB).

In our previous section, Full Database Size in Oracle, we demonstrated an example where the database size is calculated in GB.

Database size in oracle 12c

Before the release of Oracle 19c, Oracle version 12c was one of the most popular Oracle database versions, and individuals and organizations widely used it due to its clustering feature.

Most organizations have upgraded to 19c, but some companies still find Oracle version 12c compatible with their requirements.

Even the Docker hub provides 12c, the latest version for docker containers. They have not yet launched the docker file for Oracle 19c and above.

The commands shared in this blog will work for Oracle 12c as well. Use the script below to see the database size in different categories.

-- data files
SELECT SUM(BYTES)/1024/1024 AS "DATA FILE SIZE IN MB" 
FROM V$DATAFILE;

-- temp files
SELECT SUM(BYTES)/1024/1024 AS "TEMPERORY SIZE IN MB"

FROM V$TEMPFILE;

-- log files
SELECT SUM(BYTES)/1024/1024 AS "LOGS IN  MB"
FROM V$LOG;

In the output below, the yellow text is the script, and the red marked area is the output. The data file consumes 3575 MB, the temporary (sorry for the spelling mistake) file consumes 484 MB, and the log file uses 600 MB. Adding all of these will give the total database size in Oracle.

Read our blog – How to Install Oracle on Docker.

Find database size in Oracle SQL developer

SQL Developer is a free tool Oracle provides to run huge SQL queries. It provides both a graphical and a command-line interface to perform SQL operations.

SQL developer tool allows you to connect with multiple accounts and PDBs simultaneously, so make sure you check the size of the correct database in Oracle.

In the image below, we have executed the program in the above section – Full Database Size in Oracle on the SQL developer tool.

Logical database size in Oracle 21c

Oracle Database 21c is a combination of physical and logical storage. Physical storage includes data files and OS blocks, whereas logical storage includes tablespace, segment, extent, and Oracle data blocks.

All the space occupied by tables and indexes in the Oracle database comes under the logical database. And it is stored inside the dba_segments tables.

Use the below script to view all the information stored inside the dba_segments table.

SELECT *
FROM DBA_SEGMENTS;

There is a column named Bytes that holds the information on the size of each row in the dba_segments table. Adding up all the bytes rows will give the logical database size in Oracle 21c.

SELECT SUM(BYTES)/1024/1024 AS "SIZE IN MB"
FROM DBA_SEGMENTS;

In the below output of the above script, the size of the Logical database is 2010.5 MB. You can convert this value into GB by dividing it by 1024.

Read: How to create a database in Oracle 19c

Database table size in Oracle 21c

A database table is present inside the dba_segments and user_segments. You can use either to fetch the size of a database table in Oracle 21c.

Tables are displayed under the column Segment Name in Oracle database21c. Use the below script to display all the table names with their size in Megabytes (MB).

SELECT SEGMENT_NAME, BYTES/1024/1024 AS "MB"
FROM USER_SEGMENTS;

If you wish to view the size of a particular table, use the where clause in Oracle database 21c. In the example, we created a table and then checked its size.

-- create table
CREATE TABLE EMPLOYEE(
    EMP_ID INTEGER,
    EMP_NAME VARCHAR2(10)
);

-- view the size of table
SELECT SEGMENT_NAME, BYTES/1024/1024 AS "MB"
FROM DBA_SEGMENTS
WHERE segment_name='EMPLOYEE';

The below image is the output of the above code; the yellow text is the script, and the red marked area is the result of the script.

You may also like to read the following Oracle tutorials.

• Oracle Change Database Name
• How to Check Oracle Database Version
• How to Get List all Tables in Oracle
• Connect Excel to Oracle Database

In this tutorial, we learned how to check database size in Oracle.