A week or so ago Jesse (I think) suggested a test to see whether
auto-allocate LMTs were susceptible to fragmentation, or whether the
fact that under the hood every allocation unit was 64k made this
irrelevant. The test below shows that under 9.2 creating 32 tables,
extending them until each has a next extent of > 64k. Then we drop half
the tables. Can Oracle allocate a new extent for a table. Looks like it
can't and the old fun of fragmentation might remain. I'll be sticking
with ULMTs but flames/corrections welcomed.
SQL> set echo on
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
SQL>
SQL> create tablespace auto_alloc_test
2 datafile 'c:\oracle\oradata\nl9iwk\auto_alloc.dbf' size 32832k
3 extent management local;
Tablespace created.
SQL>
SQL> /*
DOC>create the tables
DOC>*/
SQL>
SQL> begin
2 for i in 1..32 loop
3 execute immediate 'create table table'||i||'(col1 number,col2
number) tablespace auto_alloc_test';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sum(bytes)/1024 free_k from dba_free_space where
tablespace_name='AUTO_ALLOC_TEST';
FREE_K
----------
30720
SQL>
SQL> begin
2 for i in 1..15 loop
3 for j in 1..32 loop
4 execute immediate 'alter table table'||j||'
allocate extent';
5 end loop;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where
tablespace_name='AUTO_ALLOC_TEST';
FREE_M
----------
SQL>
SQL> begin
2 for i in 1..32 loop
3 if i mod 2 = 0 then
4 execute immediate 'drop table table'||i;
5 end if;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where
tablespace_name='AUTO_ALLOC_TEST';
FREE_MB
----------
16
SQL>
SQL> alter table table1 allocate extent;
alter table table1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table NIALL.TABLE1 by 64 in tablespace
AUTO_ALLOC_TEST
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Niall Litchfield
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).
