In my previous articles I have given the details about the finding SQL_ID for Oracle SQL queries. In SQL interview questions most of DBA’s will face the question to find oracle database size with easy steps. Most of the times DBA needs to find the actual oracle database size for infra related calculation. Oracle Database size always may vary depending on CPU utilization, Free space hence we need to know the various file which taking size in database. Let’s see the comprises of only data files when no redo and temp are generated. In the second case, you may be asked to find out the overall database size that contains all the data files, temp files and the redo logs. Third, is the size occupied by data in this database or you can say Database usage details which you can get using dba_segments.
Scenarios to find oracle database size :
The key question here is what are multiple scenarios to find the exact database size. There are multiple scenarios which will be used to find oracle database size. The following are 3 scenarios using which we can find oracle database size :
1.Database size without using the No redo and temp files :
The first scenario is to calculate database size of different files. You can use the dba_data_files table to find out the actual database size. The dba_data_files will give the information about the database size in GB for the specified database. The size will come in bytes but you require to convert that in Gigabytes.
SQL Query to calculate actual size of database :
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS DB_Size_IN_GB FROM dba_data_files;
Output :
DB_SIZE_IN_GB
120.34
Scenario 2 : Size of database which is actually used :
You can also find the database size of actual data with using the dba_segments table. The dba_segments table will give you the actual size of occupied data.
Query :
SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;
Scenario 3 : Checking the Size of database by user or schema
You can find out the database size by dba_segement grouping the user or schema as well.
select owner, sum(bytes)/1024/1024 Size_of_db_MB from dba_segments group by owner;
Scenario 4 : Overall Size of database in GB :
Most of the time the dba will ask the questions about the overall size of the database including temp and redo files as well. You require to use multiple system tables and its sum.
Query :
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Total Size”
from
dual;
The above query will give you the total size of the database.
Scenario 5 : How to find the free space and used space in DBA?
Many times you will get question related to the free space in database and used space in database. You can find out that with using following query :
select
“Reserved_Space”,
“Reserved_Space” – “Free_Space”,
“Used_Space”,
“Free_Space”
from(
select
(select sum(bytes/(1014*1024)) from dba_data_files) “Reserved_Space”,
(select sum(bytes/(1024*1024)) from dba_free_space) “Free_Space”
from dual );
Scenario 6 : Find out the PDB and CDB database sizes
Query 1 for PDB DB size: select con_id, name, open_mode, total_size/1024/1024/1024 “PDB_Data_size” from v$pdbs;
Query 2 for CDB DB size :
select sum(bytes)/1024/1024/1024 from cdb_data_files;
These are multiple ways to find out the oracle database size. These ways will be helpful to find out the actual size of the database. I hope you like this article. If you like this article or if you have any issues with the same kindly comment in comments section.