INTRODUCCION:
Las pruebas que se realizan en esta entrada son para mover campos LOBs, de una tabla especifica, esto se realiza con 2 metodos y 2 opciones por metodo.
1.- Metodo ALTER TABLE MOVE BLOB
1.1 Mover el campo aplicando securefile
1.2 Mover el campo aplicando basicfile
2.- Metodo DBMS_REDEFINITION
2.1 Mover el campo aplicando securefile
2.2 Mover el campo aplicando basicfile
LOBS se categorizan en CLOB (almacenan texto que contiene grandes cantidades de bytes),
--
NCLOB :(es similar al CLOB solo que almacena texto cuyo juego de caracteres está definido)
--
BLOB :(almacena información multimedia dentro de la base de datos)
--
BFILE :(similar al BLOB solo que la información multimedia está almacenada en el sistema operativo).
Todo tipo de dato LOB tiene dos partes
a) LOB Value (es el contenido) el cual constituye el valor a almacenar por ejemplo: un texto o contenido multimedia.
b) LOB Locator, es un puntero a la ubicación del valor LOB (LOB Value) que es almacenado en la base de datos.
Si el texto o información multimedia se guarda dentro de la base de datos,
el contenido se almacena en un segmento separado de la tabla. Este segmento es de tipo LOB y almacena solo el LOB Value mientras
la tabla que se definió con él campo LOB solo lleva el LOB Locator como puntero al segmento LOB
Los valores LOB son almacenados out-of-line:
· Cuando se especifica explícitamente con la clausula DISABLE STORAGE IN ROW en la creación de la tabla.
· Cuando el tamaño del LOB es mayor que 3964 bytes (independientemente de las propiedades de la columna)
· Si se actualiza un LOB que está almacenado out-of-line y el LOB resultante es más pequeño de 3964 bytes,
aún así sigue almacenado como out-of-line.
Los valores LOB son almacenados in-line cuando:
· cuando se crea la tabla no se especifica ningún parámetro en la clausula de almacenamiento del LOB.
· Se especifica explícitamente la clausula ENABLE STORAGE IN ROW cuando se crea la tabla, y el tamaño del LOB almacenado en la fila dada es pequeño,
4K o menos.
· El valor del LOB es NULL (independientemente de las propiedades de almacenamiento de la columna)
Para este ejemplo moveremos segmentos LOBs de un tablespace a otro, de dos formas (ALTER TABLE MOVE y DBMS_REDEFINITION) y compararemos los beneficios y contra de cada estrategia.
1.- Creamos Una tabla
CREATE TABLE test_blob (
id NUMBER(15)
, username VARCHAR2(30)
, file_name VARCHAR2(1000)
, image BLOB
, timestamp DATE
)
/
Table created.
2.- Creamos un directoria para dejar un PDF.
CREATE OR REPLACE DIRECTORY DIR_TMP AS '/home/oracle/blob' /
Directory created.
ktm.pdf
3.- Configuramos el archivo de control de Sqlloader:
oad_test_blob_data.ctl
load data
infile *
replace
into table test_blob
FIELDS TERMINATED BY ','
(
id INTEGER external
, username CHAR
, file_name CHAR
, image LOBFILE(file_name)
TERMINATED BY EOF
NULLIF file_name='NONE'
, timestamp SYSDATE
)
begindata
1,JHUNTER,ktm.pdf
4.-Creamos un Procedimiento almacenado para introducir imagenes guardadas en un directorio y introducidas a un campo de tipo LOB, mediante
Sqlloader
write_blob_to_file.sql
CREATE OR REPLACE PROCEDURE "WRITE_BLOB_TO_FILE" AS
-- +----------------------------------------------------+
-- | PROGRAM VARIABLES |
-- +----------------------------------------------------+
v_out_outdir VARCHAR2(2000) := 'EXAMPLE_LOB_DIR';
v_out_filename VARCHAR2(500);
v_out_filename_ext VARCHAR2(4) := '.out';
v_out_filename_full VARCHAR2(500);
v_file_count NUMBER := 0;
v_file_handle UTL_FILE.FILE_TYPE;
-- +----------------------------------------------------+
-- | DYNAMIC SQL VARIABLES |
-- +----------------------------------------------------+
TYPE v_lob_cur_typ IS REF CURSOR;
v_lob_cur v_lob_cur_typ;
v_sql_string VARCHAR2(4000);
-- +----------------------------------------------------+
-- | BLOB WRITE VARIABLES |
-- +----------------------------------------------------+
v_blob_loc BLOB;
v_buffer RAW(32767);
v_buffer_size CONSTANT BINARY_INTEGER := 32767;
v_amount BINARY_INTEGER;
v_offset NUMBER(38);
-- +----------------------------------------------------+
-- | EXCEPTIONS |
-- +----------------------------------------------------+
invalid_directory_path EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_directory_path, -29280);
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);
invalid_identifier EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_identifier, -00904);
SQL_cmd_not_prop_ended EXCEPTION;
PRAGMA EXCEPTION_INIT(SQL_cmd_not_prop_ended, -00933);
BEGIN
-- +----------------------------------------------------+
-- | ENABLE SERVER-SIDE OUTPUT |
-- +----------------------------------------------------+
DBMS_OUTPUT.ENABLE(1000000);
v_sql_string := 'SELECT file_name, image FROM test.test_blob';
OPEN v_lob_cur FOR v_sql_string;
LOOP
FETCH v_lob_cur INTO v_out_filename, v_blob_loc;
EXIT WHEN v_lob_cur%NOTFOUND;
v_file_count := v_file_count + 1;
v_out_filename_full := v_out_filename || v_out_filename_ext;
v_file_handle := UTL_FILE.FOPEN(v_out_outdir, v_out_filename_full, 'wb', 32767);
v_amount := v_buffer_size;
v_offset := 1;
DECLARE
invalid_LOB_locator EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_LOB_locator, -06502);
BEGIN
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => v_blob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT_RAW(
file => v_file_handle,
buffer => v_buffer,
autoflush => true);
UTL_FILE.FFLUSH(file => v_file_handle);
END LOOP;
EXCEPTION
WHEN invalid_LOB_locator THEN
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '| *** ERROR *** |');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'Invalid LOB Locator Exception for :');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '===================================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> ' || v_out_filename);
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'SQL Text:');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '===================================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> ' || v_sql_string);
UTL_FILE.FFLUSH(file => v_file_handle);
WHEN others THEN
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '| *** ERROR *** |');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'WHEN OTHERS ERROR');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '=================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> SQL CODE : ' || SQLCODE);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> SQL ERROR MESSAGE : ' || SQLERRM);
UTL_FILE.FFLUSH(file => v_file_handle);
END;
UTL_FILE.FCLOSE(v_file_handle);
END LOOP;
CLOSE v_lob_cur;
DBMS_OUTPUT.PUT_LINE('Wrote out ' || v_file_count || ' file(s) to ' || v_out_outdir || '.');
EXCEPTION
WHEN invalid_directory_path THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Invalid Directory Path: ' || v_out_outdir);
WHEN table_does_not_exist THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Table Not Found.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
WHEN invalid_identifier THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Invalid Identifier.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
WHEN SQL_cmd_not_prop_ended THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : SQL command not properly ended.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
END;
/
SQL>
@write_blob_to_file.sql
oracle@lpocrvmax01 [cisco1]$ sqlldr userid=test/test control=load_test_blob_data.ctl log=load_test_blob_data.log
Para sacar el tamaño del segmento LOB:
select segment_name, bytes, bytes/1024/1024 EspacioMB from user_segments where segment_name in ('TEST_BLOB');
Para obtener el scripts de la tabla:
SET LONG 10000
SELECT dbms_metadata.get_ddl('TABLE', 'TEST_BLOB', 'TEST') FROM dual;
Para obtener el tamaño y extensiones asociadas al tablespace:
column segment_name format a30
column tablespace_name format a13
set pagesiz 1000
select segment_name, sum(bytes) BYTES, TABLESPACE_NAME, count(*) EXTENTS
from user_extents
group by segment_name, TABLESPACE_NAME
order by 1;
SEGMENT_NAME BYTES TABLESPACE EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000091075C00004$$ 4194304 TEST_AUTO 19
SYS_LOB0000091075C00004$$ 4227858432 TEST_AUTO 246
TEST_BLOB 65536 TEST_AUTO 1
SELECT SUM(bytes)/1024/1024 MB FROM dba_segments
WHERE (owner = 'TEST' and segment_name = 'SYS_LOB0000091045C00004$$')
OR (owner, segment_name) IN (SELECT owner, segment_name FROM dba_lobs WHERE owner = 'TEST' AND table_name = 'TEST_BLOB');
4032 MB
Para obtener contenido del campo LOB:
select DBMS_LOB.SUBSTR(image, 5, 12) from TEST_BLOB;
Para mover el segmento (LOB Segment y Index Segment) debemos ejecutar el siguiente sql:
Aqui movemos los segmentos LOBs, a un nuevo tablespace aplicando la nuevas caracteristicas
de Oracle11g, SECUREFILE (Compression, deduplicacion y encriptacion)
SQL> ALTER TABLE TEST_BLOB MOVE LOB(IMAGE) STORE AS SECUREFILE (TABLESPACE TEST_AUTO_TMP COMPRESS HIGH DEDUPLICATE);
Table altered.
Elapsed: 00:10:17.72
RESULTADO:
SEGMENT_NAME BYTES TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000091075C00004$$ 65536 TEST_AUTO_TMP 1
SYS_LOB0000091075C00004$$ 87162880 TEST_AUTO_TMP 14 <<==== (84 EspacioMB)
TEST_BLOB 65536 TEST_AUTO 1
SELECT SUM(bytes) BYTES FROM dba_segments
WHERE (owner = 'TEST' and segment_name = 'SYS_LOB0000091045C00004$$')
OR (owner, segment_name) IN (SELECT owner, segment_name FROM dba_lobs WHERE owner = 'TEST' AND table_name = 'TEST_BLOB');
83 MB
#######################################################################
Aqui movemos los segmentos LOBs, a un nuevo tablespace , dejando la propiedad basica, BASICFILE.
ALTER TABLE TEST_BLOB MOVE LOB(IMAGE) STORE AS BASICFILE
(TABLESPACE TEST_AUTO);
Nota Metalink: SecureFiles Migration and Accessing securefile metadata information
[ID 1170351.1]
#######################################################################
ALTER TABLESPACE TEST_AUTO_TMP ADD DATAFILE '+UCSRAC_DATA/cisco/datafile/test_auto_tmp2' size 4096m;
#######################################################################
AHORA REALIZAMOS LAS PRUEBAS CON DBMS_REDEFINITION
#######################################################################
1.- Sacamos el scrip de la creacion de la tabla:
select dbms_metadata.get_ddl('TABLE', 'TEST_BLOB', 'TEST')
CREATE TABLE "TEST"."TEST_BLOB_ITERIM"
( "ID" NUMBER(15,0),
"USERNAME" VARCHAR2(30),
"FILE_NAME" VARCHAR2(1000),
"IMAGE" BLOB,
"TIMESTAMP" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_AUTO"
LOB ("IMAGE") STORE AS SECUREFILE (COMPRESS HIGH DEDUPLICATE
TABLESPACE "TEST_AUTO_TMP" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
Uso de Redefinition:
How to Re-Organize a Table Online (Doc ID 177407.1)
select bytes/1024/1024 "mb" from dba_segments where segment_name like 'TEST_BLOB' and owner='TEST'
select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024 MB
from dba_lobs a, dba_segments b
where a.segment_name = b.segment_name
and a.owner = b.owner
and a.owner= 'TEST'
and a.table_name='TEST_BLOB'
order by b.bytes/1024/1024;
4032 MB
##############################################################################
GRANT que debe tener el usuario de redefinir:
GRANT CREATE ANY TABLE TO TEST;
GRANT ALTER ANY TABLE TO TEST;
GRANT DROP ANY TABLE TO TEST;
GRANT LOCK ANY TABLE TO TEST;
GRANT SELECT ANY TABLE TO TEST;
GRANT CREATE ANY INDEX TO TEST;
GRANT CREATE ANY TRIGGER TO TEST;
Ver si la tabla puede ser redefinida:
begin
dbms_redefinition.can_redef_table
(uname=>'TEST',
tname=>'TEST_BLOB',
options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
1.- START Redefinition
SQL> set timing on
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
(uname=>'TEST',
orig_table=>'TEST_BLOB',
int_table=>'TEST_BLOB_ITERIM',
options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/SQL>
PL/SQL procedure successfully completed.
Elapsed: 00:10:24.10
2.- Obtener errores del proceso de redefinition:
set timing off
vari num_errors number
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
(uname=>'TEST',
orig_table=>'TEST_BLOB',
int_table=>'TEST_BLOB_ITERIM',
num_errors=>:num_errors);
END;
/
PL/SQL procedure successfully completed.
SQL> print num_errors
NUM_ERRORS
----------
0
3.- finish_redef_table
begin
dbms_redefinition.finish_redef_table
(uname=>'TEST',
orig_table=>'TEST_BLOB',
int_table=>'TEST_BLOB_ITERIM');
end;
/
4. Drop table TEST_BLOB_ITERIM
5. Reduccion del tamaño de los segmentos LOBs
SELECT SUM(bytes)/1024/1024 MB FROM dba_segments
WHERE (owner = 'TEST' and segment_name = 'SYS_LOB0000091045C00004$$')
OR (owner, segment_name) IN (SELECT owner, segment_name FROM dba_lobs
WHERE owner = 'TEST' AND table_name = 'TEST_BLOB');
97 MB
SEGMENT_NAME BYTES TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000091084C00004$$ 65536 TEST_AUTO 1
SYS_LOB0000091084C00004$$ 101842944 TEST_AUTO 70
TEST_BLOB 65536 TEST_AUTO 1
#################################################################################
Procedimiento sin Advance Compress DBMS_REDEFINITION
#################################################################################
CREATE TABLE "TEST"."TEST_BLOB_ITERIM"
(
"ID" NUMBER(15,0),
"USERNAME" VARCHAR2(30),
"FILE_NAME" VARCHAR2(1000),
"IMAGE" BLOB,
"TIMESTAMP" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_AUTO"
LOB ("IMAGE") STORE AS BASICFILE (
TABLESPACE "TEST_AUTO_TMP" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
SQL> set timing on
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
(uname=>'TEST',
orig_table=>'TEST_BLOB',
int_table=>'TEST_BLOB_ITERIM',
options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;SQL>
PL/SQL procedure successfully completed.
Elapsed: 00:04:34.45
begin
dbms_redefinition.finish_redef_table
(uname=>'TEST',
orig_table=>'TEST_BLOB',
int_table=>'TEST_BLOB_ITERIM');
end;
/
drop table TEST_BLOB_ITERIM
SEGMENT_NAME
BYTES TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000091232C00004$$
4194304 TEST_AUTO_TMP
19
SYS_LOB0000091232C00004$$ 4228907008 TEST_AUTO_TMP 247
TEST_BLOB
65536 TEST_AUTO
1
###############################################################################
SELECT SUM(bytes)/1024/1024 MB FROM dba_segments
WHERE (owner = 'TEST' and segment_name = 'SYS_LOB0000091045C00004$$')
OR (owner, segment_name) IN (SELECT owner, segment_name FROM dba_lobs WHERE owner = 'TEST' AND table_name = 'TEST_BLOB');
4033 MB
#############################################################################
ALTER TABLE MOVE BLOB BASICFILE
#############################################################################
set timing on
ALTER TABLE TEST_BLOB MOVE LOB(IMAGE) STORE AS BASICFILE (TABLESPACE TEST_AUTO_TMP);
CONCLUSION
Las pruebas realizadas sobre el campo LOB, de la tabla TEST_BLOB campo LOB (IMAGE) al cual
cargamos un archivo PDF de 80 MB, 50 veces, generando 4022 MB de segmentos LOB (SYS_LOB0000091045C00004$$), 271 EXTENTS.
En ambos procedimientos de migracion del campo LOB, con alter tables move y dbms_redefinition (con y sin Advance Compress), los tiempos son similares tomando en cuenta el origen del LOB, mismos storage, Luns, velocidad de los discos, etc.
Las ventajas:
Con ALTER no genera redolog
Procedimiento mas simple
Con Redefinition la tabla que Online
se puede cambiar la estructura de la tabla y aplicando nuevas caracterisiticas.
Desventajas:
Con ALTER se deja indisponible la tabla por el tiempo que lleve mover los segmentos LOBs.
Con Redefinition genera redolog
Procedimiento mas complejo.
Se necesita espacio para tabla de paso.
Resultados en tiempo y Espacio:
1) Alter table move LOB SECUREFILE
Elapsed: 00:10:17.72 Con indisponibilidad de la tabla durante este lapso de tiempo.
Space: conserva el mismo tamaño de los segmentos.
2) dbms_redefinition Con SECUREFILE HIGH y DEDUPLICATE
Elapsed: 00:10:24.10 Con downtime 0
Space: Reduccion de EXTENTS de 256 a 70 , con Espacio en MB 100
3) dbms_redefinition Con BASICFILE
Elapsed: 00:04:34.45 Con downtime 0
Space: conserva el mismo tamaño de los segmentos.
4) Alter table move LOB BASICFILE
Elapsed: 00:04:28.92 Con indisponibilidad de la tabla durante este lapso de tiempo.
Space: conserva el mismo tamaño de los segmentos.
