Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate disk space usage

Posted on 2008-10-17
4
Medium Priority
?
968 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:grzessio
grzessio earned 1000 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 48

Accepted Solution

by:
schwertner earned 1000 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 1000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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