RE: Locally Managed Tablespaces

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Jan 2005 15:34:06 -0800

>-----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>

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

Other related posts: