Re: Does oracle 8i round extents up for new tables?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Thu, 1 Nov 2001 18:24:45 +1100
Message-ID: <3be0f894$0$9815$afc38c87@news.optusnet.com.au>

Hi Jonathan,
I think I don't actually find that at all!

Here's my test:

SQL> create tablespace test
  2 datafile 'D:\oracle\oradata\hjr817\test01.dbf' size 2m   3 minimum extent 52K;

Tablespace created.

SQL> create table blah(
  2 col1 char(5))
  3 tablespace test;

Table created.

SQL> select * from dba_extents where segment_name='BLAH';

OWNER                          SEGMENT_NAME


------------------------------ ---------------------------------------------

---

---

PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES


------------------------------ ------------------ --------------------------

---

• ---------- ---------- ---------- BLOCKS RELATIVE_FNO
  ---------- ------------
  SYSTEM BLAH TABLE TEST 0 7 2 57344 7 7

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE


------------------------------------ ----------- ---------------------------

---
db_block_size                        integer     8192

Now, my maths (as you know) is hopeless, but 57344/8192 is 7, and last time I checked, 7 wasn't a multiple of 5.

What's more, 52K in real money is 52*1024=53248, so quite where the 4K extra came from (half a block??), I have no idea... but I can't see it doing its 5-block trick. Maybe I'm using duff values though?

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1004561190.23845.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> I think you may find that  even MINIMUM EXTENT
> you choose is rounded up, so that the value stored as
> the tablespace default  is a multiple of 5 blocks.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Screen saver or Life saver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
> Howard J. Rogers wrote in message
> <3be055b5$0$15807$afc38c87_at_news.optusnet.com.au>...
> >I think I remember that article.  And I think it says that 8i (and all
> >Oracle versions) will still perform the "round up to 5 blocks" trick
> >*unless* you specify MINIMUM EXTENT (for Oracle 8) or use locally managed
> >tablespaces (for 8i).
> >
>
>
>

Received on Thu Nov 01 2001 - 01:24:45 CST