Re: locally managed autoallocate (was: Separate Indexes and

On 2003.09.30 22:29, Wolfgang Breitling wrote:
> However, I get a different result:
>
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for Linux: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production
>
> 5 rows selected.
>
> SQL>
> SQL> CREATE TABLESPACE "AUTO16K" LOGGING
> 2 DATAFILE '/u01/ORACLE/ora92/auto16k01.dbf' SIZE 51264K REUSE
> 3 AUTOEXTEND ON NEXT 20480K MAXSIZE 200M BLOCKSIZE 16384
> 4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL
> 5 /
>
> Tablespace created.
>
> SQL>
> SQL> create table a (a number) tablespace auto16k;
>
> Table created.
>
> SQL>
> SQL> select owner,segment_name,extent_id,blocks
> 2 from dba_extents where tablespace_name = 'AUTO16K';
>
> OWNER SEGMENT_NAME EXTENT_ID blks
> -------- ---------------- ---------- ----------
> SCOTT A 0 4
>
> 1 row selected.
>
> SQL>
>
> 4*16K = 64K initial extent.
>
> Only when i replicate your example exactly, i.e. with space management auto,
> do I get the same result:
>
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for Linux: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production
>
> 5 rows selected.
>
> SQL>
> SQL> CREATE TABLESPACE "AUTO16K" LOGGING
> 2 DATAFILE '/u01/ORACLE/ora92/auto16K01.dbf' SIZE 51264K REUSE
> 3 AUTOEXTEND ON NEXT 20480K MAXSIZE 200M BLOCKSIZE 16384
> 4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
> 5 /
>
> Tablespace created.
>
> SQL>
> SQL> create table a (a number) tablespace auto16k;
>
> Table created.
>
> SQL>
> SQL> select owner,segment_name,extent_id,blocks
> 2 from dba_extents where tablespace_name = 'AUTO16K';
>
> OWNER SEGMENT_NAME EXTENT_ID blks
> --------
> ---------------------------------------------------------------------------------
> ---------- ----------
> SCOTT A 0 64
>
> 1 row selected.
>
> So the 1M initial extent allocation is not due to a "5 block minimum
> allocation rule" but due to the fact that automatic space management
> requires 3 blocks plus 1 block for the segment header plus 1 block for
> actual data = 5 blocks, which lifts the request above the 64K threshold for
> a tablespace with 16K extents.

I repeated your test, with the same result. You, of course, are right. Interesting, that means that oracle gave up on that "5 blocks rule". Where did you come accross the fact that automatic space management requires 3 blocks? That is, I suppose, for freeelists & freelist groups? I must confess that I assumed that the old 5 blocks rule still holds true, so I didn't test further. Also, I was testing the problem that I had with autoallocate and automatic segment management, which turned out to be a SCSI controller problem. Basically, when I created the tablespace on EIDE device, it worked as advertised, but when I attempted to do that on a SCSI disk, it failed. To dispell all doubts, SCSI controller died in 2 days, causing, of course a system and the database crash. May it rest in peace, in the place SCSI controllers go when they burn out.

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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_at_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).