Re: storage clause in create tablespace and create table command

Comments embedded. And please don't post in HTML, since a lot of knowledegable Oracle gurus hate the stuff (me not being of such elevated stature actually don't mind!): they use readers from the arcane world of Unix that can't deal with it.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"rocr" <rolland_cright_at_hotmail.com> wrote in message
news:aiTJ7.5399$rt6.285638_at_news...
Hi,

The question I have is why do you have a storage clause in the create
tablespace command if you specify a storage clause in the create table
command?



----

Because you *shouldn't* specify a storage clause at the table level, and the
default one at the tablespace level then gets used as the table's own
storage clause.

You shouldn't specify a storage clause at segment level because that will
tend to cause each segment to have its own storage clause.  That will mean
you have lots of different extent sizes with the tablespace, and *that* will
mean you will one day suffer from tablespace fragmentation.  By having one
default at the tablespace level itself, you are trying instead to have one
consistent extent size per tablespace, and that will eliminate fragmentation
as a possibility.

Lots of stuff about tablespace fragmentation, and how to avoid it 100% by
using locally managed tablespace, on my web site (amongst many other
places).

Regards
HJR


----



  Here is an example.  I create a tablespace using the following command:

  CREATE TABLESPACE bif030_tbs99lwr
      Datafile  'c:\oracle\oradata\cisdata\dbf\bif030_tbs99lwr.dat' SIZE
100M
      DEFAULT STORAGE (
        INITIAL 10M
        NEXT 5M
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        PCTINCREASE 0) online;

  I then create a table as follows:

  CREATE TABLE BIF030 (
      C_CUSTOMER            CHAR (15)     NOT NULL,
      ... (many rows ommitted)
      N_FACTOR              NUMBER (10,5) NOT NULL)
         PARTITION BY RANGE ( T_DATETIME)
        ( PARTITION bif030_p99lwr VALUES LESS THAN (
to_date('1999-12-31','YYYY-MM-DD' ))
          TABLESPACE bif030_tbs99lwr PCTFREE 5 PCTUSED 10 INITRANS 1
MAXTRANS 255
          STORAGE (INITIAL 50M NEXT 5M MINEXTENTS 1 MAXEXTENTS 20
PCTINCREASE 0),
          ... (many rows ommitted)
         )
NOCACHE;

just wondering...

Cheers,
clueless