source site 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.
go here 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.
https://boxfanexpo.com/i3wu6r6rs5 [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.
https://everitte.org/e22fsv3eyys 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.
follow site Vamos criar as Tablespaces necessárias para armazenar os arquivos de media na tabela com a coluna BLOB.
see 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.
https://traffordhistory.org/lookingback/h4iw8d3z5pt 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.
https://technocretetrading.com/9e21iro13rr 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> !
go to link Vamos listar o datafile criado da tablespace FILES.
click here [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
go to link Vamos criar a tabela com a coluna BLOB para inserirmos os arquivos de media na mesma.
https://ragadamed.com.br/2024/09/18/eyydrx7x 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>!
https://www.modulocapital.com.br/6p5e9umg [oracle@serveroracle files]$ pwd /home/oracle/files [oracle@serveroracle files]$ exit exit
https://www.thephysicaltherapyadvisor.com/2024/09/18/m7u8e6jhuir 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.
Cheap Valium India Vamos criar agora o Procedimento para inserirmos o arquivo de media na tabela MY_FILES com a coluna BLOB.
https://ragadamed.com.br/2024/09/18/4ffmwsbk 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> !
https://www.drcarolineedwards.com/2024/09/18/j3xcqao [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
source link SQL> set serveroutput on SQL> variable retorno varchar2(100); SQL> exec insert_files('arq1.mkv',:retorno); PL/SQL procedure successfully completed. SQL> !
https://technocretetrading.com/1u2rzc3x2 [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
https://semnul.com/creative-mathematics/?p=vgcy2qmif 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.
follow url 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> !
go here Vamos agora observar o tamanho do arquivo em bytes no filesystem e na coluna da tabela.
https://www.thoughtleaderlife.com/c0qbqmk [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
https://www.thephysicaltherapyadvisor.com/2024/09/18/irgo34br SQL> select dbms_lob.getlength(f_file) from my_files; DBMS_LOB.GETLENGTH(F_FILE) -------------------------- 79903788 SQL> !
go to site 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.
https://livingpraying.com/h3c50j3 [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
Buy Diazepam 10Mg Teva 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>
follow url Agora vamos ver rodando o backup físico desta tablespace qual o tamanho do backup da mesma.
enter [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)
Buy Diazepam Safely 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
https://boxfanexpo.com/b85ff5wm 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
source 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.
