Solved

Calculate disk space usage

Posted on 2008-10-17
4
967 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 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 48

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

635 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