Re: storage clause in create tablespace and create table command

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 18 Nov 2001 20:49:08 +0100
Message-ID: <tvg3voar4mr5f9@corp.supernews.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?

  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

First of all: please do _NOT_ post in HTML,many newsreaders, including Forte Agent can not read it, which will limit your audience. When using Outlook express, goto Tools Options Send and set the news message format to plain text.

You are comparing the _default_ storage clause in the create tablespace command and the storage clause in the create table command. The storage clause in the create table command has been added in Oracle 6 in 1989 in order to be capable to _override_ the default storage clause. This was done in an era disk was very expensive and people wanted to allocate a table a small as possible.
In the meantime it has been established this was not one of Oracle smartest idea's. In almost every case _granular_ storage clauses (one specific storage clause per table) invariably result in fragmentation of the free space of a tablespace and a big potential market for tools like Platinum Reorg.

Right now, using Oracle 8i, tablespaces should preferably get created locally managed (as opposed to the default dictionary managed) to avoid fragmentation.
In Oracle 9i dictionary managed tablespaces exist for backwards compatibility only.
One of the consequences of locally managed tablespaces is you won't be able anymore to specify storage on object level.

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Sun Nov 18 2001 - 13:49:08 CST