Solved

# Tablespace size calculation

Posted on 2001-06-04
1,354 Views
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
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

LVL 1

Expert Comment

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

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)+ ...)
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

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

LVL 2

Expert Comment

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

ID: 7035986

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

ID: 7043048
Force accepted

** Mindphaser - Community Support Moderator **
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
###### Suggested Courses
Course of the Month9 days, left to enroll