There are at least two ways to determine the size of your Oracle database:
- Total size of datafiles that make up tablespaces of the database
- Total size of tables and indexes in the database
This method sums the total size of datafiles in the DB. Details can be found in the views v$datafile and dba_data_files. This will give your the number of MB allocated to your database:
SELECT sum(bytes/1048576) FROM dba_data_files
Remember, a megabyte is not 1000000 bytes but 1024*1024 bytes.
Sums the total of extents used for the database. It will not tell you if the extent is 10% or 90% full. Details are found in the dba_extent view.
SELECT sum(bytes/1048576) FROM dba_extents;This will give you the total number of MB used by your database.
Try both methods and see for yourself.
For More Information
- What do you think about this tip? E-mail the editor at
- firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2002