Manejo de Segmentos y Reorganización de espacio en 10g

Para explicar un poco el manejo de espacio de datos en segmentos la idea es mostrar la funcionalidad introducida en 10g para compactar segmentos con espacio inutilizado en forma online (shrink). En versiones 9i se puede realocar espacio realizando un move online, pero para esto es necesario tener como minimo el espacio ocupado por el segmento a realocar. Voy a usar la función show_space.


Voy a crear una tabla T con 10000 registros. La idea es que la columna Y tenga valores entre 0 y 4 y que haya uno de estos valores por bloque. Hice unos calculos previos para hacer que entren justo 5 filas por bloque (mi base usa tamaño de bloque de 8k)

rop@DESA10G> create table t
2  pctfree 30
3  as
4  select rownum x,
5         mod(rownum,5) y,
6         dbms_random.string('a',1000) z
7  from dual
8  connect by rownum <= 10000;  Tabla creada.   rop@DESA10G>  exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           2,000
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.

Se ve que hay 2048 bloques en total y 2000 bloques sin espacio disponible (full).


Vamos a ver si efectivamente se almacenó un valor de Y distinto por bloque:

rop@DESA10G> select y,count(distinct dbms_rowid.rowid_block_number(rowid)) cnt_blk
2  from t
3  group by y;

   Y    CNT_BLK
---------- ----------
   0       2000
   1       2000
   2       2000
   3       2000
   4       2000

rop@DESA10G>

Ahora voy a eliminar los registros cuyo valor de Y sea 0,1 y 2, es decir el 60% de las filas y el 60% espacio ocupado en cada bloque

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................           2,000
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

Observamos que los 2000 bloques que estaban totalmente llenos ahora tienen entre el 50-75 espacio libre, mas precisamente con el 60% disponible.


Ahora, si borramos las filas con Y = 3, solo no quedará un 20% del bloque ocupado, ya que solo quedan las filas con valor Y = 4

rop@DESA10G> delete from t where y = 3;

2000 filas suprimidas.

rop@DESA10G> commit;

Confirmación terminada.

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................           2,000
Full Blocks        .....................               0
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

Podemos notar que ahora los 2000 bloques quedaron con espacio libre entre 75-100, mas precisamente con el 80% libre


En este punto podemos asegurar que tenemos una alocación efectiva del 20%.


Si la tabla T esta alojada en un tablespace con manejo de segmentos automatico (ASSM) se pueden compactar las filas en los bloques. Tenemos que habilitar primero la tabla en cuestión para mover las filas:

rop@DESA10G> alter table t enable row movement;

Tabla modificada.


Luego, ejecutamos el siguiente comando:

rop@DESA10G> alter table t shrink space compact;

Tabla modificada.

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................           1,714
Full Blocks        .....................             285
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.


Vemos que ahora quedaron 285 bloques llenos, es decir, en lugar de dejar 1 fila por cada uno de los 2000 bloques quedaron 285 bloques.

Haciendo la siguiente consulta:

select y,count(distinct dbms_rowid.rowid_block_number(rowid)) cnt_blk
from t
group by y

   Y    CNT_BLK
---------- ----------
   4       287

Se ve que quedaron las filas compactadas en 287 bloques. Sin embargo la tabla sigue alocando el mismo espacio que al principio (16Mb).
Para poder dealocar el espacio usamos el siguiente comando:

rop@DESA10G> alter table t shrink space;

Tabla modificada.

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             285
Total Blocks............................             304
Total Bytes.............................       2,490,368
Total MBytes............................               2
Unused Blocks...........................               4
Unused Bytes............................          32,768
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         111,241
Last Used Block.........................              44

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

Finalmente vemos que ahora quedaron alocados un total de 304 bloques, comparado con los 2048 que estaban alocados inicialmente.

Como mostré arriba ahora se puede dealocar el espacio no utilizado y lo mas importante que se puede hacer online, mas rapido y sin espacio extra.