Re: Tablespaces sizing!

scorpio1348_at_yahoo.com wrote:
> Hello all.
> I have a question regarding the tablespace size. Assume that I create
> the new database and now I wanted to create the Application for some
> company that has a feature like this:
>
> Company: WCA Wireless Net
> Numbers of orders per year: 44 million
> Average number of equipment sales per phone: 2.5
> Number of phone plans: 13
> Number of phones: 100
>
> And I must create tables like:
>
> Data Model:
> phone table: phone_id (PK), phone_name, model_no, phone_price
> plan table: plan_id (PK), plan_name, plan_price
> plan_state_table: plan_id (FK), state_id (FK)
> state table: state_id (PK), state_description
> equipment table: equip_id (PK), equip_name, phone_id (FK),
> equip_price
> order table: order_id (PK), plan_id (FK), phone_id (FK), equip_total,
> order_total
> order equip table: order_id, equip_id
>
> and also I can say that:
>
> Data sample for phone table and example for analyzing row size/extent
> size:
> Insert into phone values (phone_id_seq.nextval, 'Motorola
> LX','M4569BL',99.89);
> analyze table phone compute statistics;
> select table_name, avg_row_len from user_tables where table_name =
> 'PHONE';
> avg_row_len = 29 bytes
> so I can understand that This application expects to store approx. 100
> different phones at a time: 29 bytes * 100: ~3K worth of data for the
> phone table. And we can say that extent sizes could be 64K (very
> small) to store the data in this table.
>
> I wanted to create 2 separate tablespaces for tables and for indexes.
>
> The question is what is the best option for the sizes of these
> tablespaces? And why?
> How can understand that if I wanted to create the different
> application or managing the size of the tablespaces for the different
> accept.
>
> For example for the application above I create the tablespaces with
> these options:
>
> SQL> create tablespace tables
> 2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\tables01.dbf'
> 3 size 100m autoextend on maxsize 200m
> 4 extent management local uniform size 100k;
>
> Tablespace created.
>
> SQL> create tablespace indexes
> 2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\indexes01.dbf'
> 3 size 100m autoextend on maxsize 200m
> 4 extent management local uniform size 100k;
>
> Tablespace created.
>
> I know this is the just practice; I created as a part of my personal
> server at home but how about the real word? What is going to be the
> best options and why?
>
> Thank you all

Look at the demos in Morgan's Library (www.psoug.org) under DBMS_SPACE. Table and Index costing procedures can be used to determine the size by segment.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org