Re: help sizing extents

Agreed, you really do have to be careful when picking the extent size and number of free-list groups, especially with OPS. The variation in what happens is quite
extreme across the versions of Oracle.

Not only do you need to examine the
INITIAL extents in user_tables, though, to find out what is going you also need to examine BLOCKS in user_extents.

However, my experience with 8.1.5 does
not match yours.

In 8.0 (and 7.3 from memory) the value of initial_extents is 'your specification + freelist_groups * block_size' and then the extent is that size rounded up to 5 blocks. Which really wrecks your attempt to align extents with dml_locks.

In 8.1 the initial_extent is as you specify it, but the number of blocks actually allocated is rounded up to a multiple of 5, and the free-list group taken out of that space. Hooray.

But in both cases, the export actually exports at the value stored in initial_extent - unless you choose the option to compress extents on export. In this case, the value of initial_extent will be the 'rounded up to 5 blocks' value.

Based on the above, I assume your block size is NOT 2K.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Martin Haltmayer wrote in message <37E660E4.5E3DA647_at_in.augsburg.net>...
>I am sorry but this is only true if you have only one freelist group.
>
>-- test_freelgr.sql shows that tables are not declared
>-- with the requested initial extent size if you specify
>-- several freelist groups.
>
>Then do an export and an import with indexfile=abc.tab. Then abc.tab
contains
>the following in Oracle 8.1.5 on NT 4.0 SP 4:
>
>REM CREATE TABLE "TEST"."TEST_FREELGR" ("N" NUMBER) PCTFREE 10
>REM PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 1064960
>REM NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
>REM FREELISTS 1 FREELIST GROUPS 2 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
;
>REM ... 1 rows
>
>So the initial extent is no longer 1M after that import. By the way, before
>reimporting that table select initial_extent, next_extent from user_table
where
>table_name = 'TEST_FREELGR' both report correctly one megabyte (1048576).
>
Received on Fri Sep 24 1999 - 06:29:06 CDT