How big is my oracle database?

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:

Datafiles
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.
Tempfiles
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).
 

Redologs
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.

Controlfiles
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”
from
dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;
(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,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;
 

Miscellaneous files
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.
Examples include:

(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)
(e) Passwordfile.
(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.

Leave a Reply

You must be logged in to post a comment.


Ativan Norco Valium Ultracet Buy Zocor Ultracet Darvocet Buy Glucophage Buy Ultram Buy Biaxin Norco Buy Lexapro Buy Viagra Online Buy Norco Buy Lipitor Buy Line Xanax Biaxin Buy Tramadol Online Prozac Lortab Buy Zithromax Buy Viagra Online Buy Cialis Buy Propecia Vicodin Flexeril Prozac Buy Oxycontin Buy Celexa Hydrocodone Propecia Buy Diflucan Buy Glucophage Vicodin Lipitor Zyprexa Buy Alprazolam Buy Xanax Buy Percocet Buy Cialis Online Buy Cialis Percocet Buy Ativan Cialis Buy Bupropion Buy Hydrocodone Online Norco Buy Diflucan Xanax Online Buy Alprazolam Ambien Buy Generic Viagra Zyrtec Buy Prozac Buy Cheap Phentermine Buy Norco Buy Lortab Hydrocodone Soma Buy Hydrocodone Online Diazepam Butalbital Cheap Phentermine Buy Xanax On Line Buy Norvasc Buy Tenuate Buy Zyban Viagra Online Buy Zyprexa Nexium Valium Buy Lortab Online Xanax Buy Flexeril Buy Xanax Diazepam Seroquel Acyclovir Clonazepam Buy Phentermine Online Fioricet