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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
