How to Measure Oracle Database Capacity

The capacity of an Oracle Database can refer to several things: the hard disk space assigned to the datafiles – the files containing the actual data stored in the database – or the overall hard disk capacity of the database. If the latter, to know its capacity as well as the datafile capacity you must also learn the size of the temp files, temp tablespaces and the undo-redo logs, and then add all these figures together.

Instructions

Datafiles

1. Launch Oracle and log in with your administrator account.

2. Paste the following command into Oracle:

   select sum(bytes)/1024/1024 "Meg" from dba_data_files;

3. Run the query. Oracle will return the number of megabytes that have been assigned to all datafile in the database combined.

Temp files

1. Run Oracle and log in as the administrator.

2. Type or paste the following query into oracle:

   select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

3. Run the query to discover the size of the temp files in megabytes.

Temporary Tablespaces

1. Run Oracle and log in with your admin account.

2. Copy and paste this query:

   SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
   FROM V$temp_space_header
   GROUP BY tablespace_name;

3. Run the command. Oracle returns the size of the free and used space allocated to the temporary files – add the two figures together to get the total capacity.

Undo Logs

1. Load up Oracle and sign in with administrator permissions.

2. Copy and paste the query below into Oracle:

   select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

3. Run the query to discover the size of the undo logs.

References

• Oracle Database FAQ: How do I find the overall database size?