?
Solved

Calculate disk space usage

Posted on 2008-10-17
4
Medium Priority
?
974 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 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

593 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