Manage

How to estimate database size in Oracle

What are the main considerations for estimating the size of an Oracle database? Are there any rules of thumb? How to estimate the size and number of datafiles, redo log files, control files and archived redo log files?

Published: 14 Nov 2005

What are the main considerations when estimating database size of an Oracle database? Are there any rules of thumb?
How to estimate the size and number of datafiles, redo log files, control files and archived redo log files?

The best way to estimate database size is to load some sample data into your tables and then calculate statistics on those tables. Query DBA_TABLES for the AVG_ROW_LEN to get an idea of the average number of bytes for each row. Next, you'll have to estimate how many rows you will need in the table. Some tables will hold a fairly static number of rows and some will grow over time. Multiply your expected row volume for each table by its AVG_ROW_LEN. That will give you a good estimate for the table sizes.

The number of control files should be three for most Oracle databases. Typically, you will want three to five online redo log groups (with two members each) for your database. However, if you see "checkpoint not complete" messages in your alert log, then you may want to add more groups.

The datafiles depend on your tablespaces and how much data each tablespace needs to hold. If your tablespace will be small, then you will only require one datafile for the tablespace. If the tablespace will be large, then you may require more datafiles.

For more information:
How to find Oracle database instance size

Dig Deeper on Oracle database design and architecture

SearchDataManagement

Join the conversation

1 comment

Send me notifications when other members comment.

Oldest

[-]

orahow - 24 Oct 2019 5:28 AM

You can check Oracle database size from dba_segments.

select sum(bytes)/1024/1024/1024 from dba_segments;

To find overall allocated database size:

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) "Size in GB"

from

dual

Dig Deeper on Oracle database design and architecture

Understanding rollback in Oracle following SHUTDOWN IMMEDIATE

Oracle backup with RMAN and Automatic Storage Manager

Oracle backup and recovery best practices

Why am I getting the ORA-01195 error when issuing a recover command?

Join the conversation

1 comment

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.