Solved

Tablespace size calculation

Posted on 2001-06-04
6
1,352 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

840 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