Solved

Tablespace size calculation

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

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

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