Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: <<<storage clause>>> HELP HELP

Re: <<<storage clause>>> HELP HELP

From: Dick Allie <dallie_at_ionet.net>
Date: 1997/05/20
Message-ID: <338202F9.68E5@ionet.net>#1/1

Tischendorf und Schuch wrote:
>
> N:Y.99.M.NEWS Ingersheim, 15.05.1997
>
> Dear Oracle Specialists,
>
> I thought, that I had understood the basic architecture of oracle
>
> but I have made the experience, that this seems to be not true.
>
> Can somebody give me a short education in using the storage clause
>
> defining tablespaces and tables inside a tablespace.
>
> To be concrete:
>
> I have a project for that I estimated the bytes-volume using a
>
> formula like:
>
> inivolume=(num of rows*(avg(rowbytes +row header+ column header)/
>
> (Dbblocksize-61-initrans*23)*(100-pctfree)/100
>
> But this is not so important for the moment.
>
> I got values as
>
> table_a: 137M
>
> table_b: 6M
>
> table_c: 104M
>
> table_d: 70M
>
> :
 

> :
>
> and so on and I got a total amound of about 1.302M (=1G 302M) of space.
>
> I had the opinion to create a tablespace TS_PROJECT with the
>
> following clause:
>
> create tablespace TS_PROJECT datafile 'filename' Size 1300M
>
> default storage ( initial 1300M next 10M minextents 2, maxextents
>
> 121)
>
> to have enough storage space for further growing ups.
>
> The create table statements start with
>
> create table table_a (...) tablespace TS_PROJECT
>
> storage (initial 137M next 1 M);
>
> create table table_b (...) tablespace TS_PROJECT
>
> storage (initial 6M next 500K);
>
> and so on.
>
> When I start this sql-script, the third table could not be created
>
> because the tablspace is full. Why this?
>
> I expect, that mostly 121 extents can be created, but this is
>
> done, when data are inserted. Why can the tablespace run out of
>
> storage space, when I create tables, which are much smaller in the
>
> addition of initial storage space than the tablespace?
>
> The examples in the oracle manuals donĀ“t help to understand this.
>
> In the admin guide of Oracle 7.1 an example is given:
>
> create tablespace rb_segs
>
> datafile .... size 50M
>
> default storage (
>
> initial 50K
>
> next 50K
>
> minextents 2
>
> maxextents 50
>
> pctincrease 0);
>
> How does this work?
>
> Initial: 50K
>
> Next: 50K
>
> maxextents =
>
> 50-2*50K 2400K
>
> -----
>
> 2500K
>
> 2500K is much less than 50M as was defined in the size of the
>
> datafile. Where is my fault of understanding and how can I
>
> organize the size of the tablespace and the size of each table
>
> best, when I know the volumne they will have?
>
> I am quite confused and I am looking forward for any help or tip.
>
> Please email me when possible, because I get emails automatically
>
> and I find not always the time to read news.
>
> Thnaks a lot to all of you
>
> M. Tischendorf
>
> -----------------------------------------------------------
>
> /\
>
> / a\ Manfred Tischendorf
>
> / s \
>
> / u \ Bruehlstr. 2
>
> / k \ D 74379 Ingersheim
>
> / r \ Deutschland / Germany
>
> \a /
>
> \ / Tel.: +49 7142 9800-27 / +49 171 6821030
>
> \ / Fax: +49 7142 9800-29
>
> \____/ E-Mail: tischendorf_at_arkusa.de
>
> \ / WWW: http://www.arkusa.de/
>
> \/
>
> -----------------------------------------------------------

Hi,
The problem stems from the default storage parameter minextents 2. Everytime you create a table you will get the initial extent (137m for the first table plus the next size (1m) for the 2nd extent. This adds up to more than you allocated for the tablespace.

When creating the table use the minextents storage parameter and set it to 1. This way you will only get 1 extent. Also set pctincrease to 0 in the table create statement so that each next extent is the same size.

Set the default storage parameter inital to a small number say 1m so if a table is created in the tablespace without the storage parameter initial it won't try to use the default that you set to your tablespace size.

The tablespace should be big enough to hold your initial sizes and a multiple of the next sizes for a six month or 1 year growth.

If you need more assistance email me and I'll try to help.

Dick Allie - at dallie_at_ionet.net Received on Tue May 20 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US