Solved

Tablespace size calculation

Posted on 2001-06-04
6
1,344 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
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 50 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now