O objetivo deste artigo se deve a uma dúvida que eu tinha e que acabou se confirmando em questão do uso de BLOB no Oracle.
A dúvida seria se ao inserir um arquivo qualquer, por exemplo de media (Estou usando para o teste) numa tabela com uma coluna BLOB, qual seria realmente o tamanho deste arquivo após a inserção na tabela.
Então segue o mesmo abaixo.
Vamos criar o diretório no filesystem, tranferir os arquivos e criar o diretório no Oracle apontando para o diretório no filesystem que estão os arquivos.
[oracle@serveroracle ~]$ mkdir files [oracle@serveroracle ~]$ cd files/ [oracle@serveroracle files]$ ls -l total 0 [oracle@serveroracle files]$ ls -ltr total 78116 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv [oracle@serveroracle files]$ sqlplus /nolog SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 28 11:08:03 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn tomiasi/tomiasi
Connected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oraprd02/oradata/dbprod/users01.dbf
/oraprd02/oradata/dbprod/sysaux01.dbf
/oraprd02/oradata/dbprod/undotbs01.dbf
/oraprd02/oradata/dbprod/system01.dbf
/oraprd02/oradata/dbprod/users02.dbf
/oraprd02/oradata/dbprod/users03.dbf
6 rows selected.
Vamos criar as Tablespaces necessárias para armazenar os arquivos de media na tabela com a coluna BLOB.
SQL> create tablespace FILES datafile '/oraprd02/oradata/dbprod/files01.dbf' size 10M autoextend on next 10M;
Tablespace created.
SQL> create tablespace FILES_INDEX datafile '/oraprd02/oradata/dbprod/files_index01.dbf' size 10M autoextend on next 10M; x01.dbf' size 10M autoextend on next 10M;
Tablespace created.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 FILES YES NO YES
7 FILES_INDEX YES NO YES
7 rows selected.
SQL> disc
Disconnected from Oracle Database 10g Release 10.2.0.5.0 - Production
SQL> conn sys/******* as sysdba
Connected.
Com o usuário SYS vamos criar o diretório no Oracle apontando para o diretório no filesystem e garantir privilégio de leitura e escrita para o usuário TOMIASI conforme abaixo.
SQL> create directory DT_FILES as '/home/oracle/files'; Directory created. SQL> grant read,write on directory DT_FILES to tomiasi; Grant succeeded. SQL> disc Disconnected from Oracle Database 10g Release 10.2.0.5.0 - Production SQL> conn tomiasi/tomiasi Connected. SQL> !
Vamos listar o datafile criado da tablespace FILES.
[oracle@serveroracle files]$ ls -l /oraprd02/oradata/dbprod/files01.dbf -rw-r----- 1 oracle oinstall 10493952 Oct 28 11:15 /oraprd02/oradata/dbprod/files01.dbf [oracle@serveroracle files]$ exit exit
Vamos criar a tabela com a coluna BLOB para inserirmos os arquivos de media na mesma.
SQL> create table my_files
(f_id number, f_file blob)
lob (f_file) store as flob_store (
tablespace FILES storage (initial 1M next 1M pctincrease 0)
chunk 4
pctversion 10
INDEX flob_index (
tablespace FILES_INDEX)) tablespace USERS
storage (initial 1M next 1M pctincrease 0);
/
Table created.
SQL>!
[oracle@serveroracle files]$ pwd /home/oracle/files [oracle@serveroracle files]$ exit exit
SQL> desc my_files; Name Null? Type ----------------------------------------- -------- ---------------------------- F_ID NUMBER F_FILE BLOB SQL> set lines 155 SQL> select segment_name, segment_type from user_segments; SEGMENT_NAME SEGMENT_TYPE --------------------------------------------------------------------------------- ------------------ MY_FILES TABLE FLOB_STORE LOBSEGMENT FLOB_INDEX LOBINDEX SQL> create sequence f_id_seq start with 1 increment by 1; Sequence created.
Vamos criar agora o Procedimento para inserirmos o arquivo de media na tabela MY_FILES com a coluna BLOB.
SQL> create or replace procedure insert_files
(p_name_file in varchar,
p_return out varchar)
as
f_blob blob;
f_bfile bfile;
begin
insert into my_files (f_id,f_file) values (f_id_seq.nextval,empty_blob())
returning f_file into f_blob;
f_bfile := bfilename('DT_FILES',p_name_file);
dbms_lob.fileopen(f_bfile);
dbms_lob.loadfromfile(f_blob,f_bfile,dbms_lob.getlength(f_bfile));
dbms_lob.fileclose(f_bfile);
commit;
p_return := 'File inserted with Successuful';
end;
/
Procedure created.
SQL> !
[oracle@serveroracle files]$ ls -ltr total 78116 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv [oracle@serveroracle files]$ exit exit
SQL> set serveroutput on
SQL> variable retorno varchar2(100);
SQL> exec insert_files('arq1.mkv',:retorno);
PL/SQL procedure successfully completed.
SQL> !
[oracle@serveroracle files]$ ls -ltr /oraprd02/oradata/dbprod/files01.dbf -rw-r----- 1 oracle oinstall 94380032 Oct 28 11:46 /oraprd02/oradata/dbprod/files01.dbf [oracle@serveroracle files]$ ls -ltr /oraprd02/oradata/dbprod/files_index01.dbf -rw-r----- 1 oracle oinstall 10493952 Oct 28 11:21 /oraprd02/oradata/dbprod/files_index01.dbf [oracle@serveroracle files]$ exit exit
Vamos visualizar nos segmentos o tamanho em MB da coluna F_FILE com o nome do segmento de FLOB_STORE.
Podemos observar que o tamanho da mesma tem 80 M.
SQL> select segment_name, segment_type, (bytes/1024/1024) MB from user_segments; SEGMENT_NAME SEGMENT_TYPE MB --------------------------------------------------------------------------------- ------------------ ---------- MY_FILES TABLE 1 FLOB_STORE LOBSEGMENT 80 FLOB_INDEX LOBINDEX .125 SQL> !
Vamos agora observar o tamanho do arquivo em bytes no filesystem e na coluna da tabela.
[oracle@serveroracle files]$ ls -l total 78116 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv [oracle@serveroracle files]$ exit exit
SQL> select dbms_lob.getlength(f_file) from my_files;
DBMS_LOB.GETLENGTH(F_FILE)
--------------------------
79903788
SQL> !
Observando acima os valores para ambos são idênticos, porém no segmentos há 1,85 MB a mais por consequência de outras definições de objetos na mesma.
[oracle@serveroracle files]$ ls -l total 78116 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv [oracle@serveroracle files]$ exit exit
SQL> select segment_name, segment_type, bytes from user_segments; SEGMENT_NAME SEGMENT_TYPE BYTES --------------------------------------------------------------------------------- ------------------ ---------- MY_FILES TABLE 1048576 FLOB_STORE LOBSEGMENT 83886080 FLOB_INDEX LOBINDEX 131072 SQL>
Agora vamos ver rodando o backup físico desta tablespace qual o tamanho do backup da mesma.
[oracle@serveroracle fisico]$ rman target sys/****** Recovery Manager: Release 10.2.0.5.0 - Production on Fri Oct 28 13:19:48 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: DBPROD (DBID=862715809)
RMAN> run {
allocate channel c1 device type disk maxpiecesize 1024M;
backup as backupset
format='/orabackup/dbprod/fisico/tbs_lob2_%d_%t_%p_%D_%M_%Y_%t'
tag='TBS_LOB'
(tablespace 'FILES');
backup as backupset
format='/orabackup/dbprod/fisico/tbs_lob2_index_%d_%t_%p_%D_%M_%Y_%t'
tag='TBS_LOB_INDEX'
(tablespace 'FILES_INDEX');
release channel c1;
}
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=146 devtype=DISK
Starting backup at 28-OCT-11
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00007 name=/oraprd02/oradata/dbprod/files01.dbf
channel c1: starting piece 1 at 28-OCT-11
channel c1: finished piece 1 at 28-OCT-11
piece handle=/orabackup/dbprod/fisico/tbs_lob_DBPROD_765726276_1_28_10_2011_765726276 tag=TBS_LOB comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-OCT-11
Starting backup at 28-OCT-11
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00008 name=/oraprd02/oradata/dbprod/files_index01.dbf
channel c1: starting piece 1 at 28-OCT-11
channel c1: finished piece 1 at 28-OCT-11
piece handle=/orabackup/dbprod/fisico/tbs_lob_index_DBPROD_765726279_1_28_10_2011_765726279 tag=TBS_LOB_INDEX comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-OCT-11
released channel: c1
RMAN> [oracle@serveroracle fisico]$ ls -ltr tbs* -rw-r----- 1 oracle oinstall 81846272 Oct 28 13:44 tbs_lob_DBPROD_765726276_1_28_10_2011_765726276 -rw-r----- 1 oracle oinstall 98304 Oct 28 13:44 tbs_lob_index_DBPROD_765726279_1_28_10_2011_765726279 [oracle@serveroracle fisico]$ exit exit
Observando acima a peça de backup da tablespace ficou bem próxima do tamanho do arquivo com 1,85 MB a mais.
SQL> !
[oracle@serveroracle files]$ ls -l total 78116 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv [oracle@serveroracle files]$ mv arq1.mkv arq1.mkv.bkp [oracle@serveroracle files]$ ls -ltr total 156232 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv.bkp [oracle@serveroracle files]$ exit exit
SQL> select dbms_lob.getlength(f_file) from my_files;
DBMS_LOB.GETLENGTH(F_FILE)
--------------------------
79903788
Vamos agora criar um procedimento para extrair da tabela com a coluna BLOB para o filesystem.
SQL> create or replace procedure extract_file_blob
(p_id in number,
p_filename in varchar,
p_return out varchar)
as
f_blob blob;
v_start number := 1;
bytelen number := 32767;
vlr raw(32767);
c number;
f_bfile utl_file.file_type;
begin
-- Definir Diretório de Saída
f_bfile := utl_file.fopen('DT_FILES',p_filename,'w', 32767);
-- Obter Tamanho do blob
select dbms_lob.getlength(f_file) into c
from my_files
where f_id = p_id;
-- Selecionar blob dentro da variável
select f_file into f_blob
from my_files
where f_id = p_id;
-- Ler chunks do BLOB e escreve eles para o arquivo até completar.
while v_start < c
loop
dbms_lob.read(f_blob, bytelen, v_start, vlr);
utl_file.put_raw(f_bfile, vlr, true);
v_start := v_start + bytelen;
end loop;
utl_file.fclose(f_bfile);
p_return := 'File in the directory';
end;
/
Procedure created.
Vamos executar o procedimento acima.
SQL> exec extract_file_blob(1,'arq1.mkv',:retorno); PL/SQL procedure successfully completed. SQL> print retorno; RETORNO -------------------------------------------------------------------------------------------------------------------------------- File in the directory SQL> !
[oracle@serveroracle files]$ ls -ltr total 156232 -rw-r--r-- 1 oracle oinstall 79903788 Oct 26 18:30 arq1.mkv.bkp -rw-r--r-- 1 oracle oinstall 79903789 Oct 28 14:37 arq1.mkv [oracle@serveroracle files]$ rm arq1.mkv.bkp
Observando acima ao extrair o arquivo da tabela para o filesystem, o mesmo foi executado com Sucesso com uma diferença de apenas 1 byte.
Executei o arquivo que é um video e o mesmo executou com sucesso até o seu final.
Vamos agora transferir outro arquivo de media e executar novamente com os teste realizados acima.
[oracle@serveroracle files]$ ls -ltr total 156244 -rw-r--r-- 1 oracle oinstall 79916721 Oct 27 20:54 arq2.mkv -rw-r--r-- 1 oracle oinstall 79903789 Oct 28 14:37 arq1.mkv [oracle@serveroracle files]$ exit exit
SQL> set lines 155
SQL> set serveroutput on
SQL> variable retorno varchar2(100);
SQL> exec insert_files('arq2.mkv',:retorno);
PL/SQL procedure successfully completed.
SQL> print retorno;
RETORNO
--------------------------------------------------------------------------------------------------------------------------------
File inserted with Successuful
SQL> select dbms_lob.getlength(f_file) from my_files;
DBMS_LOB.GETLENGTH(F_FILE)
--------------------------
79903788
79916721
SQL> !
Vamos executar novamente o RMAN.
[oracle@serveroracle files]$ rman target sys/******* Recovery Manager: Release 10.2.0.5.0 - Production on Fri Oct 28 15:20:32 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: DBPROD (DBID=862715809)
RMAN> run {
allocate channel c1 device type disk maxpiecesize 1024M;
backup as backupset
format='/orabackup/dbprod/fisico/tbs_lob2_%d_%t_%p_%D_%M_%Y_%t'
tag='TBS_LOB'
(tablespace 'FILES');
backup as backupset
format='/orabackup/dbprod/fisico/tbs_lob2_index_%d_%t_%p_%D_%M_%Y_%t'
tag='TBS_LOB_INDEX'
(tablespace 'FILES_INDEX');
release channel c1;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=141 devtype=DISK
Starting backup at 28-OCT-11
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00007 name=/oraprd02/oradata/dbprod/files01.dbf
channel c1: starting piece 1 at 28-OCT-11
channel c1: finished piece 1 at 28-OCT-11
piece handle=/orabackup/dbprod/fisico/tbs_lob2_DBPROD_765732079_1_28_10_2011_765732079 tag=TBS_LOB comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-OCT-11
Starting backup at 28-OCT-11
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00008 name=/oraprd02/oradata/dbprod/files_index01.dbf
channel c1: starting piece 1 at 28-OCT-11
channel c1: finished piece 1 at 28-OCT-11
piece handle=/orabackup/dbprod/fisico/tbs_lob2_index_DBPROD_765732087_1_28_10_2011_765732087 tag=TBS_LOB_INDEX comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-OCT-11
released channel: c1
RMAN> exit
Recovery Manager complete.
[oracle@serveroracle files]$ ls -l /orabackup/dbprod/fisico/*lob2* -rw-r----- 1 oracle oinstall 162750464 Oct 28 15:21 /orabackup/dbprod/fisico/tbs_lob2_DBPROD_765732079_1_28_10_2011_765732079 -rw-r----- 1 oracle oinstall 98304 Oct 28 15:21 /orabackup/dbprod/fisico/tbs_lob2_index_DBPROD_765732087_1_28_10_2011_765732087 [oracle@serveroracle files]$ ls -ltr total 156244 -rw-r--r-- 1 oracle oinstall 79916721 Oct 27 20:54 arq2.mkv -rw-r--r-- 1 oracle oinstall 79903789 Oct 28 14:37 arq1.mkv [oracle@serveroracle files]$ du -hs 153M . [oracle@serveroracle files]$ mv arq2.mkv arq2.mkv.bkp [oracle@serveroracle files]$ ls -ltr total 156244 -rw-r--r-- 1 oracle oinstall 79916721 Oct 27 20:54 arq2.mkv.bkp -rw-r--r-- 1 oracle oinstall 79903789 Oct 28 14:37 arq1.mkv [oracle@serveroracle files]$ exit exit
SQL> exec extract_file_blob(2,'arq2.mkv',:retorno); PL/SQL procedure successfully completed. SQL> print retorno; RETORNO -------------------------------------------------------------------------------------------------------------------------------- File in the directory SQL> !
[oracle@serveroracle files]$ ls -ltr total 234372 -rw-r--r-- 1 oracle oinstall 79916721 Oct 27 20:54 arq2.mkv.bkp -rw-r--r-- 1 oracle oinstall 79903789 Oct 28 14:37 arq1.mkv -rw-r--r-- 1 oracle oinstall 79916722 Oct 28 15:25 arq2.mkv [oracle@serveroracle files]$
Concluímos que ao inserir um arquivo, por exemplo de 100 MB o mesmo será inserido neste tamanho.
Pós e Contras de inserir um arquivo de media e outros dentro do banco de dados.
Pós
Segurança de obter a informação em um único lugar e somente usuários autorizados poderão visualizar tais arquivos e pessoas que possuem acesso ao Sistema Operacional mas não tem acesso ao banco de dados podem modificá-las;
Os arquivos no SO são passiveis de infecção caso o servidor não tenha um antivirus;
Mudança de acesso ao sistema de arquivo pode inviabilizar ou deixar a aplicação instável;
Se a aplicação tiver acesso de escrita e leitura no sistema de arquivos ela poderá ser um forma de invasão ou causar danos ao Sistema Operacional hospedeiro.
Fazer pesquisa por fragmentos de textos em campos BLOB é fácil em diversos arquivos, do Sistema Operacional pode ser muito lento.
Contras
Dependendo dos tamanhos dos arquivos que serão inseridos, deve-se obter uma boa estratégia de backup e de utilização de como o arquivo será inserido no banco de dados, pois o backup pode-se ficar muito grande e mais lento para executar do que usar os arquivos em disco e criar na tabela apenas um ponteiro para o diretório de arquivos.
Se aplicarmos um nível de segurança no servidor e no diretório que estes arquivos serão armazenados e com o nível de segurança de backup destes arquivos, podemos manter estes arquivos no servidor sem problemas.
Portanto devemos sempre ter uma estratégia bem definida de uso desses procedimentos.
OBS: Se caso o seu Banco de Dados seja 11g e Enterprise Edition existe uma Option para compactação de dados “Oracle Advanced Compression” que ajuda a gerenciar melhor os dados compactando-os, reduzindo tráfico de rede e backup dos mesmos.
Sobre Oracle Advanced Compression entre em Oracle Advanced Compression
Sobre DBMS_BLOB entre em DBMS_BLOB Documentação Oracle
Sobre criar uma tabela com colunas BLOB entre em CREATE TABLE Documentação Oracle
Autor: Maycon Tomiasi
Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.
