Comment embedded.
Regards
HJR
--
Resources for Oracle: http://www.hjrdba.com
===============================
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:tvg3voar4mr5f9_at_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.
>
Not quite true. You can specify a storage clause at the segment lvel till
the cows come home -and the locally managed tablespace will blythely ignore
it in the sense that the actually requested initial and next extent sizes
will not be honoured. But you will still get roughly what you asked for.
So if your uniform extent size is 1m, and you create a table with a storage
clause requesting INITIAL 5m, you will get 5 1Mb extents allocated from the
word go: the segment storage clause is thus respected, but not honoured.
Regards
HJR
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
>
Received on Mon Nov 19 2001 - 01:50:14 CST
