People ask this question a lot on the internet and most of the answers just seem to focus on a query of dba_data_files, similar to that shown below:
select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;
That’s fine to start with, but you should also include tempfiles, which are used when an operation such as a large sort is too big to fit into the relevant memory allocated to the session.
select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;
Your redo logs can also use up a large amount of disk space - especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).
select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;
The database obviously needs controlfiles to record information such as which datafiles belong to the database. If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.
select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;
From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.
Flash Recovery Area
select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
These views will show sizing details and free space available.
Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:
Block change tracking file
select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;
Files referenced by database directories or the utl_file_dir parameter
Your application may read from, or write to external files via database directories or the utl_file_dir parameter.
Other examples of using external directories are for
(a) External tables -
select a.owner||’.'||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
dba_external_locations a, dba_directories b
(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).
select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
from dba_file_group_tablespaces a, dba_file_group_files b
There are a large number of files which you could also include in your sizing if you wanted to. Though most of these are really external to the database.
(a) The spfile/pfile and any ifile referenced files.
(b) Any external scheduler jobs (i.e. program_type=’EXECUTABLE’ and program_action which points to a shell script).
(c) Configuration files such as Oracle wallet files and database gateway/hs services files.
(d) Oracle networking files. (e.g. tnsnames.ora, sqlnet.ora, listener.ora)
(f) Any application code that needs to be deployed to the database server.
(g) Any database management or monitoring scripts that need to be on the server.
(h) Files referenced by the audit_file_dest parameter, if audit_trail is set to use the “OS” or “XML” options.
(i) Archived redo logs and standby redo logs. Be aware of space usage related to the workload of your database and whether or not you have multiple destinations defined.
(j) Any software that needs to be deployed to the server. (e.g. The oracle software itself takes up several gigabytes of space).
If you wanted to be very precise, you could remove unused space from the calculations above, but I haven’t done that here, in order to keep things more straightforward. In real-life you would probably be better leaving the extra space available to allow for future growth of the database - which you should also plan into your size calculations.
As you can see, calculating size requirements for an Oracle database is not always as simple as you would think. I’ve tried to include all elements in this post, though in reality a lot of them won’t apply to most databases. Please feel free to share any other items not on the list, that you feel should be included.