Over time Oracle databases grow incrementally in size. Depending on the size of the organisation the growth factor can be such that maintenance needs to be done at periodic intervals, to ensure enough space is available on the server and the database continues to perform efficiently. An Oracle database size depends on a number of factors. In addition to data, there are temporary files and essential control files containing the structure of the database. These are all essential for the database to work.
- Skill level:
- Moderately Easy
Include the data file sizes in the query. The total size of the database includes tables, fields, stored procedures and other database objects. Calculate size from the "dba_data_files" view, as follows:
SELECT SUM(bytes)/1024/1024/1024 data_size from dba_data_files;
Calculate the size of temporary files. Temporary files store data during processing, but is not permanent. Calculate the temporary file size as follows:
select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files.
Obtain the size of the redo log. The redo log saves any changes in the database before they are applied to the actual database data. This provides a way to restore the database to its original state prior to a statement designed to amend any data.
The size of the redo log can be obtained with the following statement:
select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log.
Extract the size of the control file used by Oracle, using the V$CONTROLFILE view. The control file view is used to obtain information on database schema and the objects contained within.
The select statement to obtain the size of the control file is:
select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile
Combine the select statements to calculate the database size. The output corresponds to the total size of the database in gigabytes:
select d.data_sizeT.temp_sizeR.redo_sizeC.controlfile_size "Database Size in GB" from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) d,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) t,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) r,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) c;
This will output the total size of the database in the following format:
Database Size in GB
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for