RE: Locally Managed Tablespaces

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 20 Jan 2005 15:34:06 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87C09@irvmbxw02>

>-----Original Message-----
>Mladen Gogala
>Tablespaces with uniform allocation cannot be fragmented, while system
>allocation allows fragmentation.
>A case like that was constructed by Jonathan or Gopal, I believe.
Example: run the following script to see fragmentation.

create tablespace x
  extent management local autoallocate
  datafile 'd:\oradata\sdjrk\dbf\x01.dbf' size 20544 K ; declare

   n pls_integer := 1 ;
begin

   for i in 1..16
   loop

      execute immediate
         'create table t' || to_char (n, 'FM00') ||
         ' (d date) tablespace x' ;
      execute immediate
         'create table t' || to_char (n + 1, 'FM00') ||
         ' (d date) tablespace x' ;
      execute immediate
         'create table t' || to_char (n + 2, 'FM00') ||
         ' (d date) tablespace x' ;
      execute immediate
         'create table t' || to_char (n + 3, 'FM00') ||
         ' (d date) tablespace x' ;
      execute immediate
         'create table t' || to_char (n + 4, 'FM00') ||
         ' (d date) tablespace x storage (initial 1024K)' ;
      n := n + 5 ;

   end loop ;
   for i in 1..16
   loop

      execute immediate 'drop table t' || to_char (i * 5 - 1, 'FM00') ;
      execute immediate 'drop table t' || to_char (i * 5 - 3, 'FM00') ;

   end loop ;
end ;
/
select sum (bytes) / 1024 as free_kbytes  from dba_free_space
 where tablespace_name = 'X' ;
create table t99 (d date) tablespace x storage (initial 2048K) ;      

Proof:  

SQL> create tablespace x
  2 extent management local autoallocate   3 datafile 'd:\oradata\sdjrk\dbf\x01.dbf' size 20544 K ; Tablespace créé.  

SQL> declare
  2 n pls_integer := 1 ;
  3 begin

  4     for i in 1..16
  5     loop
  6        execute immediate
  7           'create table t' || to_char (n, 'FM00') ||
  8           ' (d date) tablespace x' ;
  9        execute immediate
 10           'create table t' || to_char (n + 1, 'FM00') ||
 11           ' (d date) tablespace x' ;
 12        execute immediate
 13           'create table t' || to_char (n + 2, 'FM00') ||
 14           ' (d date) tablespace x' ;
 15        execute immediate
 16           'create table t' || to_char (n + 3, 'FM00') ||
 17           ' (d date) tablespace x' ;
 18        execute immediate
 19           'create table t' || to_char (n + 4, 'FM00') ||
 20           ' (d date) tablespace x storage (initial 1024K)' ;
 21        n := n + 5 ;
 22     end loop ;
 23     for i in 1..16
 24     loop
 25        execute immediate 'drop table t' || to_char (i * 5 - 1, 'FM00') ;
 26        execute immediate 'drop table t' || to_char (i * 5 - 3, 'FM00') ;
 27     end loop ;

 28 end ;
 29 /
Procédure PL/SQL terminée avec succès.  

SQL> select sum (bytes) / 1024 as free_kbytes   2 from dba_free_space
  3 where tablespace_name = 'X' ;
FREE_KBYTES

---

       2048  

SQL> create table t99 (d date) tablespace x storage (initial 2048K) ; create table t99 (d date) tablespace x storage (initial 2048K) *
ERREUR à la ligne 1 :
ORA-01658: impossible de créer l'ensemble de blocs contigus INITIAL pour le segment dans le tablespa  

SQL>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 20 2005 - 18:39:27 CST