Pós e Contras do uso de Inserção de Arquivo do Filesystem no Banco de Dados.

go here 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.

Buy Xanax In Usa 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.

here [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://annmorrislighting.com/nypa3187k 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.

https://templedavid.org/symons/af8ql11l Vamos criar as Tablespaces necessárias para armazenar os arquivos de media na tabela com a coluna BLOB.

https://discovershareinspire.com/2024/05/qhc2ffe1q 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.

see url 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.

go here 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> !

https://photovisions.ca/iymivwza Vamos listar o datafile criado da tablespace FILES.

follow [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

Cheap Valium Canada Vamos criar a tabela com a coluna BLOB para inserirmos os arquivos de media na mesma.

https://domainebregeon.com/w1zkw4uy 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>!

Buy Xanax 5Mg Uk [oracle@serveroracle files]$ pwd /home/oracle/files [oracle@serveroracle files]$ exit exit

https://www.vertaglia.com/te447bp 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 Xanax For Sale Online Vamos criar agora o Procedimento para inserirmos o arquivo de media na tabela MY_FILES com a coluna BLOB.

see 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.jacobysaustin.com/2024/05/fpc2gtz [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

see SQL> set serveroutput on SQL> variable retorno varchar2(100); SQL> exec insert_files('arq1.mkv',:retorno); PL/SQL procedure successfully completed. SQL> !

https://someawesomeminecraft.com/2024/05/13/olsyemoz [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

Buy Alprazolam Canada 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.

Buy Xanax Bar 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> !

https://emduk.org/ln0zts4g Vamos agora observar o tamanho do arquivo em bytes no filesystem e na coluna da tabela.

Buy Xanax From Canada [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://annmorrislighting.com/9434xa349 SQL> select dbms_lob.getlength(f_file) from my_files; DBMS_LOB.GETLENGTH(F_FILE) -------------------------- 79903788 SQL> !

source link 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.

go here [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 Generic Valium Online 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>

source Agora vamos ver rodando o backup físico desta tablespace qual o tamanho do backup da mesma.

https://discovershareinspire.com/2024/05/xmasgu97e [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)

https://grannysglasses.com/?p=y3jeivtwn 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

Cheap Xanax Prescription 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

enter 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

%name Pós e Contras do uso de Inserção de Arquivo do Filesystem no Banco de Dados.

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.