Locally Managed Tablespaces 2005-01-20 - By Jacques Kilchoer
>-- --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 (See ORA-01658.ora-code.com): impossible de cr¨¦er l'ensemble de blocs contigus INITIAL pour le
segment dans le tablespa
SQL>
--
http://www.freelists.org/webpage/oracle-l
|
|
