How to find the total database size in Oracle

To find the total database size in Oracle, you can use a variety of queries, depending on the level of detail you need (e.g., total size of all tablespaces, individual tablespace sizes, or database overall). Here's a breakdown:

---

1. Using DBA_DATA_FILES (For DBAs)

The DBA_DATA_FILES view contains information about all data files in the database, which can be used to calculate the total size of the database.

Query for the Total Database Size:

SELECT SUM(bytes) / 1024 / 1024 / 1024 AS total_size_gb
FROM dba_data_files;

• This will return the total size of the database in GB by summing the sizes of all the data files (in the DBA_DATA_FILES table).

---

2. Using DBA_TABLESPACES (For DBAs)

You can also get the size of each tablespace, which will give you an overview of the database's total size.

Query for Tablespace Sizes:

SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb
FROM dba_data_files
GROUP BY tablespace_name;

• This will return the size of each tablespace in the database, along with the total size for each (in GB).

---

3. Using USER_SEGMENTS (For Non-DBA Users)

If you don't have DBA privileges, you can use the USER_SEGMENTS view, which will show you the space used by all objects (tables, indexes, etc.) in your schema.

Query for Your Schema Size:

SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_size_gb
FROM user_segments;

• This will give you the total space used by your schema in GB.

---

4. Using V$DATABASE and V$DATAFILE

If you want a high-level overview of the total size of the database, including all data files, you can use the following query:

SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_size_gb
FROM v$datafile;

• This query retrieves the total size of the data files in the database from the V$DATAFILE view, and the result is in GB.

---

5. Detailed Database Size Breakdown

To see a more detailed breakdown of the database size, you can include information about tablespaces, used space, and free space.

Query for Detailed Size Information:

SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS allocated_size_gb,
       ROUND(SUM(free_space) / 1024 / 1024 / 1024, 2) AS free_space_gb,
       ROUND((SUM(bytes) - SUM(free_space)) / 1024 / 1024 / 1024, 2) AS used_space_gb
FROM (
    SELECT tablespace_name, bytes, 0 AS free_space
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_space
    FROM dba_free_space
)
GROUP BY tablespace_name;

This query provides:

• Allocated Size (total size of the tablespace)
• Free Space
• Used Space

---

Key Points:

• DBA_DATA_FILES provides the most accurate overall database size by summing all data file sizes.
• USER_SEGMENTS will give you the size of the objects in your schema if you're not a DBA.
• V$DATAFILE provides a quick way to get the total size of the data files in the database.
• For a detailed breakdown, you can combine allocated and free space in the DBA_FREE_SPACE view.