Solved

Calculate disk space usage

Posted on 2008-10-17
4
961 Views
Last Modified: 2012-05-05

I would like to calculate how much actual disk space will my database use (I know how many rows I will have, I need maximum size on disk). Is it enough to take the column size attribute and calculate from that, or do I have to consider other stuff?

The question is related to any database.

0
Comment
Question by:Dejan Pažin
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:grzessio
grzessio earned 250 total points
ID: 22739259
do not forget about:

0) default database size
1) indexes
2) backups

it also needs storage.
0
 
LVL 17

Author Comment

by:Dejan Pažin
ID: 22739293

Any short way to calcuate that? It would be good enough if I could simply check the size of database as it is at the moment, then enter say 1000 rows, and measure the size of the database again.

All I need is a way to measure the database size (Oracle or MsSql).
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 22739540
Getting used tablespace per user per segment (table, index ....) in mega bytes

--enter username for which you want to get the tablespace information
 
SELECT   owner, segment_type, tablespace_name,
         SUM (BYTES / 1024 / 1024) size_mb
    FROM dba_segments
   WHERE owner = NVL (UPPER ('&owner'), owner)
GROUP BY segment_type, tablespace_name, owner
ORDER BY owner, segment_type, tablespace_name, size_mb
/
 
-- with segments for all users
 
SELECT   owner, segment_type, tablespace_name,
         SUM (BYTES / 1024 / 1024) size_mb
    FROM dba_segments
GROUP BY segment_type, tablespace_name, owner
ORDER BY owner, segment_type, tablespace_name, size_mb
/
 
-->***** used (Mega Bytes) tablespace per user
 
SELECT   owner, tablespace_name,
         SUM (BYTES / 1024 / 1024) size_mb
    FROM dba_segments
GROUP BY tablespace_name, owner
ORDER BY owner, tablespace_name
/
0
 
LVL 7

Assisted Solution

by:grzessio
grzessio earned 250 total points
ID: 22739592
if you have all the indexes in place it could be quite a good way to estimate usage but 1000 rows is a bit too small value.
if your default sizes are big enough it is possible that inserting 1000 rows will not change database size.

you could:
sum(for every table <take no of max rows in the future in the table> * size of columns)
multiply it by 2 (for indexes)
add default database size multiplied by 2
multiply it by n (for n - 2 last database backups)
and then multiply it again by 3 or 4 (just in case).

you can try to insert more rows just to test if above expression will work.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now