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.


Buy Viagra Online Ultram Diflucan Buy Tramadol Tramadol Buy Nexium Xanax On Line Diazepam Buy Xanax Phentermine Buy Darvocet Buy Hydrocodone Valium Online Cipro Buy Vicodin Zyrtec Ativan Adderall Buy Hydrocodone Buy Vicodin Online Buy Zithromax Buy Ambien Xanax Buy Zyban Paxil Darvocet Buy Meridia Buy Alprazolam Adderall Bupropion Adderall Carisoprodol Buy Diflucan Buy Soma Buy Acyclovir Generic Viagra Butalbital Buy Phentermine Online Buy Hydrocodone Buy Valium Online Oxycontin Viagra Buy Oxycontin Prozac Buy Levitra Buy Darvocet Buy Zyprexa Buy Adipex Butalbital Buy Xanax On Line Codeine Buy Biaxin Bontril Buy Cialis Buy Adderall Buy Adipex Buy Lexapro Xanax Online Buy Flexeril Buy Hydrocodone Online Lortab Acyclovir Buy Norvasc Buy Fioricet Zyban Buy Xanax Propecia Buy Lexapro Buy Soma Seroquel Buy Ephedrine Buy Effexor Xanax Online Alprazolam Lipitor Buy Phentermine Cheap Phentermine Buy Zyprexa Ambien Bupropion Meridia