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

scorpio1348@yahoo.com wrote in news:5a6ef588-c920-42ef-978a-7f459069f392
@y18g2000pre.googlegroups.com:
>I wanted to create 2 separate tablespaces for tables and for indexes.

What is the advantage of having 2 tablespaces on the same disk?


> What is going to be the best options and why?

"Best" based upon which metric(s)?

scorpio1348@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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org