This write-up will guide about checking or verifying the disk space occupied by Oracle Database.
How to Check Disk Space Occupied by Oracle Database?
The size of the Oracle database depends on the physical space consumed by temp files, logs, and data files of the database on the disk:
To check the occupied space by the database on the disk, search, and open “SQL Developer” from the Start menu:
Wait for it to open:
Double-click on the available database connection whose size you want to check. Provide the password and click on the “OK” button:
The “SQL Worksheet” will open after the successful login in the database:
Type this command in the “SQL Worksheet” to check the disk space occupied by the Oracle database:
col "Used Disk Space in GBs" format a30
col "Free Disk Space in GBs" format a30
select round(sum(used_space.bytes) / 1024 / 1024 / 1024 ) "Total Size of Database in GBs"
, round(sum(used_space.bytes) / 1024 / 1024 / 1024 ) - round(free_space.f / 1024 / 1024 / 1024) "Used Disk Space in GBs"
, round(free_space.f / 1024 / 1024 / 1024) "Free Disk Space in GBs"
from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used_space
, (select sum(bytes) as f from dba_free_space) free_space
group by free_space.f;
Let’s understand the above-given code snippet:
- Statements with the “col” keyword are defining the output of the query (30 characters wide) with the headers “Total Size of Database in GBs”, “Used Disk Space in GBs”, and “Free Disk Space in GBs”.
- The “select” statement is selecting three values and formats them as a string in GBs.
- The first line of the “select” statement calculates the size of the database by adding datafiles, tempfiles, and log files.
- The second line of the “select” statement calculates the used space by subtracting the amount of free space from the total size of the database.
- The third line of the “select” statement selects the amount of free space from the “dba_free_space” table.
- The “from” statement defines the tables “used_space” and “free_space” used in the query.
- The “group” statement will group the output of the query by the amount of free space in the database.
Once the query will execute, the output will display disk space occupied by Oracle Database:
We have discussed the procedure to check the disk space occupied by the Oracle Database.
Conclusion
Checking the occupied disk space by the Oracle database is necessary to make decisions regarding capacity planning, resource management, compliance, and performance optimization. The disk space consumed by the database is derived from the sum of space consumed by temp files, logs, and data files of the database. This post demonstrated how to check disk space occupied by Oracle Database.
