?
Solved

Tablespace size calculation

Posted on 2001-06-04
6
Medium Priority
?
1,356 Views
Last Modified: 2008-02-07
I have 80 million datas. & approx. 8-10 tables. What can be the tablespace size reqd for it? or how can i go for calculating the tablespace size?
0
Comment
Question by:cchatterjee
[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
6 Comments
 
LVL 1

Expert Comment

by:dbrower
ID: 6153360
You need to know the average size of a record in each table. Then figure (avg_row_size * #of_rec) + overhead.

The over head is usually small (maybe a few meg)

good luck.
0
 
LVL 1

Accepted Solution

by:
misho2000 earned 200 total points
ID: 6153854
Your firat estiamation is based on vsize function which can give you total length of record.
the sysntax is like this
select (vsize(colunm1)+vsize(colunm2)+vsize(colunm3)+ ...)
from your table;
You can have this inforamtion using column definition from ER diagram or database server model but you have to perform calculation manually.
After that you predict how many rows you will have in every table and multiply record size * number rows you receive initial estimation. Add 10% for free space and aproximately 100 bytes for every record overhead. Add 50% for first several inserts in order to have view how tables increase. It is very important to know how your data  grows on daily or weekly or monthly basis. Do correction if necessary.
0
 
LVL 4

Expert Comment

by:fva
ID: 6155222
The production size of the tablespaces will also depend on the PCTUSED and PCTFREE parameters of your tables. Oracle leaves some free space in each block for future updates. When doing a large number of deletes/inserts, it also refrains from using the space freed by deletes until PCTUSED is reached. This can cause significant skewing of estimated size versus production environment size and you'll probably want to take account for that.

F.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Expert Comment

by:RMZ
ID: 6156184
hi
first connect sys or system
in morning run the query and in night run it again
from it u can get how many mega used per/day so u can take decition
SQL> SELECT TABLESPACE_NAME,SUM(BYTES) FROM DBA_FREE_SPACE
  2  GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
INDX                            104855552
RBS                              99889152
SYSTEM                          278366208
TEMPORARY                       102909952
USR                             216778752
----
i hope it help u
----rmz---
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7035986
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7043048
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.
Suggested Courses

777 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