Message-ID: <F001.005D9AEF.20031212042424@fatcity.com>
Date: Fri, 12 Dec 2003 04:24:24 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Richard Foote" <richard.foote@bigpond.com>
Subject: Re: Re: Little competition
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi Jonathan,

SQL> create tablespace bowie_test
  2  datafile 'c:/bowie/bowie_test01.dbf' size 100m
  3  extent management local uniform size 1m
  4  segment space management auto;

Tablespace created.

SQL> create table bowie_assm (ziggy number)
  2  tablespace bowie_test
  3  storage (initial 1m next 2m pctincrease 100 minextents 3);

Table created.

SQL> select owner, segment_name, blocks from dba_extents
  2  where segment_name = 'BOWIE_ASSM';

OWNER                          SEGMENT_NAME             BLOCKS
------------------------------ -------------------- ----------
BOWIE                          BOWIE_ASSM                  128
BOWIE                          BOWIE_ASSM                  128
BOWIE                          BOWIE_ASSM                  128
BOWIE                          BOWIE_ASSM                  128
BOWIE                          BOWIE_ASSM                  128
BOWIE                          BOWIE_ASSM                  128
BOWIE                          BOWIE_ASSM                  128

7 rows selected.

3 minextents = 1 initial + 2 next + (2 next + (2 * 1.0 pctincrease) = 7 * 1M
extents

It's actually quite a common misconception that NEXT, PCTINCREASE and
MINEXTENTS are ignored for locally managed tablespaces when in fact they're
used to determine the initial size of the object and hence the number of
extents initially allocated.

This was all a bit of fun but I think it did prove my little (mischievous)
point. That it's really quite easy to base ones belief and certainty on a
"fact" that turns out to be totally false because the basis on why you
believe something also turns out to be false. On the surface it appeared to
be quite a reasonable conclusion, that pctfree is not permitted with ASSM
objects because the "evidence" strongly supported such a claim.
Unfortunately the evidence was somewhat erroneous in that it stupidly relied
on incorrect syntax and so an incorrect conclusion resulted. This incorrect
conclusion can then result in inappropriate behaviour and curses from DBAs
as they experience all these "unavoidable" migrated rows. Before you know
it, other Oracle myth is born ...

Of course everyone makes mistakes but to publish them does come with it's
own set of responsibilities. I can't stress enough that one be careful of
"what" you read and be careful of "who" you read.

The truth IS out there ;)

Cheers

Richard

> <quote>
> This could be a serious issue for the Oracle professional unless they
> remember that locally-managed tablespaces with automatic space management
> ignore any specified values for NEXT and FREELISTS.
>
> <end quote>
>
> There is another error here.
> For a bonus 10 points can anyone spot it ?
>
> Hint - try the following in a tablespace
> which is locallally managed, with automatic
> space management, and either system managed
> or uniform sized extents of no more than 1 M.
>
> create table test2(n1 number)
> storage (initial 1M next 2M pctincrease 100 minextents 3);
>
> Regards
>
> Jonathan Lewis


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: richard.foote@bigpond.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).