Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


terça-feira, 1 de setembro de 2015

Oracle Data Pump Export: Utilizando o arquivo de parâmetro (parfile)

Por Eduardo Legatti

Olá,

Tanto no utilitário Oracle Datapump Export (expdp) quanto no utilitário Oracle Datapump Import (impdp), é possível utilizar um arquivo de parâmetro "parfile" de forma a facilitar e até automatizar o processo de geração ou importação de um dump. Fazendo uso do arquivo de parâmetro, também é possível colocar o usuário e a senha que fará o processo de exportação ou importação. Segue abaixo o exemplo de um arquivo de parâmetro que utilizarei para realizar uma exportação de alguns schemas do banco de dados BD01.
 
[oracle]$ cat BD01.par
USERID=system/manager
SCHEMAS=SCOTT,ADAM,BLAKE
EXCLUDE=STATISTICS
DIRECTORY=DATA_PUMP_DIR
FLASHBACK_TIME=systimestamp
REUSE_DUMPFILES=y
COMPRESSION=ALL
QUERY=ADAM.T1:"WHERE rownum=0"
QUERY=BLAKE.T1:"WHERE rownum=0"
QUERY=SCOTT.T2:"WHERE rownum=0"
QUERY=ADAM.T2:"WHERE rownum=0"
QUERY=BLAKE.T2:"WHERE rownum=0"
REMAP_DATA=ADAM.T3.COL_BLOB:SYS.PKG_NULL_BLOB.SF_NULL_BLOB
REMAP_DATA=BLAKE.T3.COL_BLOB:SYS.PKG_NULL_BLOB.SF_NULL_BLOB

Acima, o arquivo de parâmetro BD01.par contém as seguintes configurações:

  • Utilizará o usuário SYSTEM para fazer a exportação.
  • Exportará objetos dos schemas SCOTT, ADAM e BLAKE.
  • Não exportará estatísticas de objetos (tabelas, índices).
  • Utilizará o objeto directory DATA_PUMP_DIR que será o destino do arquivo dump.
  • Fará a exportação de forma consistente (point in time).
  • Irá sobrescrever o aquivo dump no destino caso o mesmo já exista.
  • Irá comprimir o arquivo dump de exportação.
  • Irá exportar a tabela T1 dos schemas ADAM e BLAKE sem os registros.
  • Irá exportar a tabela T2 dos schemas ADAM, SCOTT e BLAKE sem os registros.
  • Irá realizar a exportação da tabela T3 dos schemas ADAM e BLAKE ignorando o conteúdo da coluna COL_BLOB.

Agora irei realizar a exportação fazendo uso do arquivo de parâmetro BD01.par conforme exemplo abaixo.

[oracle]$ expdp parfile=BD01.par

Export: Release 11.2.0.3.0 - Production on Tue Sep 1 10:00:04 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** parfile=BD01.par
Estimativa em andamento com o mtodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o mtodo de BLOCKS: 90.08 GB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLESPACE_QUOTA
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/SYNONYM/SYNONYM
Processando o tipo de objeto SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processando o tipo de objeto SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/FUNCTION
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processando o tipo de objeto SCHEMA_EXPORT/TYPE/TYPE_BODY
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
. . exportou "ADAM"."T1"               0 KB        0 linhas
. . exportou "ADAM"."T2"           5.132 KB        0 linhas
. . exportou "ADAM"."T3"          891.86 KB    10090 linhas
. . exportou "ADAM"."T4"         623.087 MB   934567 linhas
. . exportou "ADAM"."T5"            1.065 MB  264791 linhas
. . exportou "BLAKE"."T1"               0 KB       0 linhas
. . exportou "BLAKE"."T2"           2.132 KB       0 linhas
. . exportou "BLAKE"."T3"           91.86 KB   10090 linhas
. . exportou "BLAKE"."T4"         755.023 MB  654567 linhas
. . exportou "BLAKE"."T5"           2.065 MB  367791 linhas
. . exportou "SCOTT"."T1"           322.5 KB   44615 linhas
. . exportou "SCOTT"."T2"           1.235 KB       0 linhas
. . exportou "SCOTT"."T3"           91.86 KB   10090 linhas
. . exportou "SCOTT"."T4"         455.023 MB  834567 linhas
. . exportou "SCOTT"."T5"           4.065 MB  594798 linhas

Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 :
  /tmp/BD01.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concludo com sucesso em 10:02:40

quarta-feira, 5 de agosto de 2015

Oracle Data Pump Export: Ignorando o conteúdo de colunas BLOB ao gerar o dump de exportação com a cláusula REMAP_DATA

Por Eduardo Legatti

Olá,

No artigo de Junho/2015 eu abordei uma técnica de como realizar um dump de exportação pelo Datapump Export (expdp) ignorando linhas de uma tabela através da cláusula QUERY sem a necessidade de ignorar a tabela através da cláusula EXCLUDE. Recapitulando, no utilitário expdp, podemos utilizar a cláusula EXCLUDE para ignorar alguns objetos que não queremos que sejam exportados. A desvantagem em utilizar a opção EXCLUDE para ignorar tabelas durante a exportação é que, ao importar novamente o dump, as tabelas que foram excluídas poderão fazer falta no novo ambiente. Agora imagine uma tabela que possui uma coluna do tipo BLOB que contém poucos registros, mas pelo fato de armazenar documentos grandes na coluna BLOB, acaba por ter um tamanho de 100 GB. A questão agora é: como fazer para exportar os registros desta tabela ignorando o conteúdo da coluna BLOB? Se o conteúdo do LOB não for necessário no dump de exportação, maximizaremos o tempo da exportação, além de ter um dump muito menor. Utilizando a técnica da cláusula QUERY poderemos exportar a tabela sem nenhum registro, mas não é isso que queremos.

No mais, neste artigo irei abordar como ignorar o conteúdo de uma coluna BLOB durante uma exportação através do utilitário expdp, fazendo uso da cláusula REMAP_DATA que tem como objetivo a especificação de uma função de conversão de dados através de uma package de banco de dados. Segue a demonstração abaixo.
 
[oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Qua Ago 5 10:13:43 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc SCOTT.T1
 Nome                Nulo?    Tipo
 ------------------- -------- ------------------
 COD                          NUMBER
 IMAGEM                       BLOB

SQL> select owner,segment_name,segment_type,bytes/1024/1024 size_mb
  2    from dba_segments
  3   where owner='SCOTT';

OWNER            SEGMENT_NAME                 SEGMENT_TYPE        SIZE_MB
---------------- ---------------------------- ---------------- ----------
SCOTT            T1                           TABLE                 ,0625
SCOTT            SYS_IL0000182291C00002$$     LOBINDEX                  3
SCOTT            SYS_LOB0000182291C00002$$    LOBSEGMENT             1669

3 linhas selecionadas.   

No exemplo acima, temos a tabela T1 de propriedade do usuário SCOTT que possui 1669 MB em segmentos de LOB referente à coluna IMAGEM. A tabela possui apenas 256 linhas na qual queremos exportar. No comando abaixo irei exportar o schema SCOTT de forma usual.
 
[oracle ~]$ expdp system/#5ydl3db# dumpfile=SCOTT.dmp schemas=SCOTT

Export: Release 11.2.0.3.0 - Production on Wed Aug 5 10:19:31 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=SCOTT.dmp
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 1.629 GB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
. . exportou "SCOTT"."T1"                                1.590 GB     256 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/SCOTT.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:20:31
 
Na saída acima, podemos verificar que 1.590 GB referente à tabela T1 foram exportados. Agora irei fazer a mesma exportação só que agora fazendo uso da cláusula REMAP_DATA. Para fazer uso da mesma, o pré-requisito é que uma função incorporada dentro de uma package seja utilizada. Segue abaixo package que irei criar que terá como objetivo retornar um valor empty_blob(), ou seja, um tipo de valor NULL para coluna do tipo BLOB.

SQL> create or replace package pkg_null_blob
  2  as
  3     function sf_null_blob (col_blob in blob)
  4        return blob;
  5  end;
  6  /

Pacote criado.

SQL> create or replace package body pkg_null_blob
  2  as
  3     function sf_null_blob (col_blob in blob)
  4        return blob
  5     is
  6        blob_null   blob := empty_blob();
  7     begin
  8        return blob_null;
  9     end;
 10  end;
 11  /

Corpo de Pacote criado.

SQL> grant execute on pkg_null_blob to public;

Concessão bem-sucedida.

Após a criação da package e concedidas as permissões necessárias, irei realizar novamente a exportação, só que agora fazendo uso da cláusula REMAP_DATA conforme demonstração abaixo.
 
[oracle ~]$ expdp system/#5ydl3db# 
            dumpfile=SCOTT.dmp 
            schemas=SCOTT 
            remap_data=SCOTT.T1.IMAGEM:sys.pkg_null_blob.sf_null_blob

Export: Release 11.2.0.3.0 - Production on Wed Aug 5 10:25:55 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=SCOTT.dmp
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 1.629 GB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
. . exportou "SCOTT"."T1"                                6.906 KB     256 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/SCOTT.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:26:34

Pronto. Podemos perceber pela saída acima que somente 6.906 KB referente à tabela T1 foram exportados, ou seja, o conteúdo da coluna IMAGEM do tipo de dado BLOB, foi mapeado para NULL para cada linha exportada da tabela T1.

quinta-feira, 2 de julho de 2015

Gerando hash MD5 do conteúdo de colunas do tipo LOB no Oracle com a package DBMS_CRYPTO

Por Eduardo Legatti

Olá,

Nos artigos de Novembro/2007 e Dezembro/2009 e eu demonstrei de forma prática como gerar arquivos externos ao banco de dados através das packages UTL_FILE e DBMS_LOB, a utilização do objeto DIRECTORY, bem como a apresentação de uma função para geração de hash MD5 através da package DBMS_OBFUSCATION_TOOLKIT para geração de senhas e pequenos textos. Neste artigo, irei abordar novamente a utilização dos objetos DIRECTORY e da package DBMS_LOB quanto a geração de hash MD5. A diferença é que agora irei demonstrar como gerar um hash MD5 para dados do tipo LOB (BLOB, CLOB) através da package DBMS_CRYPTO disponível à partir do Oracle 10g.

O objetivo do artigo será demonstrar uma função de banco de dados que chamarei de md5_blob que receberá como parâmetro a coluna de uma tabela do tipo de dado BLOB. Em alguns casos ela poderá ser muito útil, como foi pra mim, quando precisei verificar em uma tabela a quantidade de documentos binários duplicados (*.doc, *.xls, *.pdf, *.jpg, etc.) que estavam armazenados em uma coluna do tipo BLOB.

Segue abaixo os comandos executados para criar um ambiente de forma a simular uma tabela contendo documentos binários. No caso em questão, irei armazenar um arquivo chamado imagem.jpg.
 
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:04:59 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create or replace directory arquivo_dir as '/tmp';

Diretório criado.

-- Criando a tabela de teste
SQL> create table tab_imagem (
  2    id number primary key,
  3    nome varchar2(20),
  4    imagem blob
  5  );

Tabela criada.

SQL> grant write,read on directory arquivo_dir to scott;

Concessão bem-sucedida.

SQL> grant execute on dbms_crypto to scott;

Concessão bem-sucedida.

-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rwxr--r-- 1 oracle oinstall 820 Jul 2 10:09 imagem.jpg

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:14:32 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Criando a stored procedure carrega_imagem
SQL> create or replace procedure carrega_imagem (p_nome_arquivo in tab_imagem.nome%type) as
  2   v_bfile bfile;
  3   v_blob blob;
  4  begin
  5    insert into tab_imagem (id,nome,imagem)
  6    values (1,p_nome_arquivo,empty_blob())
  7    return imagem into v_blob;
  8    v_bfile := bfilename('ARQUIVO_DIR',p_nome_arquivo);
  9    dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile));
 11    dbms_lob.fileclose(v_bfile);
 12    commit;
 13  end;
 14  /

Procedimento criado.

-- Carregando o arquivo para a tabela tab_imagem
SQL> execute carrega_imagem('imagem.jpg');

Procedimento PL/SQL concluído com sucesso.
  
Pronto. O arquivo imagem.jpg foi carregado para a tabela conforme demonstrado abaixo.
 
SQL> select * from tab_imagem;

        ID NOME             IMAGEM
---------- ---------------- ------------------------------------------------------------
         1 imagem.jpg       47494638396109000600F70000000000B1B1B1AFAFAFA3A3A39999997777
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            27CCCCCC191919171717111111C0C0C00D0D0D090909070707AAAAAAA4A4
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            58FFFFFF4848484444444040403838383232322E2E2EDDDDDD2828282626
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            B30000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            002C000000000900060000080E0049081C48B0A0C1830813260C08003B00

Segue abaixo a função de banco de dados que será utilizada para gerar o hash MD5 da coluna do tipo BLOB.
 
SQL> create or replace function md5_blob (p_blob in blob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => p_blob,
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.
  
Após a criação da função, poderemos ver abaixo através da instrução SELECT que o hash MD5 do conteúdo da coluna IMAGEM da tabela TAB_IMAGEM foi gerado com sucesso.

SQL> select id,nome,md5_blob(imagem) from tab_imagem;

        ID NOME            MD5_BLOB(IMAGEM)
---------- --------------- --------------------------------
         1 imagem.jpg      4B812BDC6240D0770A12DA89E4630BF2

1 linha selecionada.

Apenas para comprovar que o hash MD5 que foi gerado é válido, utilizarei o comando md5sum no Linux para verificar que o valor do hash é o mesmo que foi gerado pelo Oracle.
 
$ md5sum imagem.jpg
4b812bdc6240d0770a12da89e4630bf2  imagem.jpg

Em relação à colunas do tipo CLOB, a função é praticamente idêntica, o que muda um  pouco é a utilização da procedure utl_raw.cast_to_raw como demonstrado abaixo.

SQL> create or replace function md5_clob (p_clob in clob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => utl_raw.cast_to_raw (p_clob),
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.
  

segunda-feira, 1 de junho de 2015

Oracle Data Pump Export: Ignorando múltiplas tabelas ao gerar o dump de exportação sem fazer uso da cláusula EXCLUDE

Por Eduardo Legatti

Olá,

Em alguns momentos, nós DBAs, somos solicitados a realizar um dump de exportação de um schema de banco de dados. Na maioria das vezes esse dump será importado para um outro ambiente, como por exemplo, no ambiente de DEV ou HOM. O que acontece às vezes é que somos solicitados a fazer o dump de forma que algumas tabelas não precisem estar no dump de exportação. Muitas vezes são tabelas grandes (de muitos gigabytes) que são usadas para armazenar logs, históricos, imagens através de colunas LOB. Enfim, dependendo da situação, são tabelas que não serão necessárias em um novo ambiente. No utilitário expdp (Datapump Export), podemos utilizar a cláusula EXCLUDE para ignorar alguns objetos que não queremos que sejam exportados. A desvantagem em utilizar a opção EXCLUDE para ignorar tabelas durante a exportação é que, ao importar novamente o dump, em alguns casos, seremos acionados a criar no novo ambiente as tabelas que não foram exportadas, inclusive as constraints Foreign Keys caso existam nas tabelas que foram ignoradas durante a exportação.

No mais, o objetivo desse artigo será demonstrar o uso da cláusula EXCLUDE para ignorar tabelas durante uma exportação através do Data Pump Export (expdp), bem como demonstrar uma outra forma, através da cláusula QUERY de ignorar tabelas durante uma exportação sem a necessidade de excluí-las do dump. Segue a demonstração abaixo.
 
$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Seg Jun 1 10:13:21 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1 (cod number constraint pk_t1 primary key);

Tabela criada.

SQL> create table t2 (cod number constraint fk_t2_t1 references t1);

Tabela criada.

SQL> create table t3 (cod number);

Tabela criada.

SQL> insert into t1 values (1);

1 linha criada.

SQL> insert into t2 values (1);

1 linha criada.

SQL> insert into t3 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

 
Acima, eu criei 3 tabelas no schema SCOTT para poder realizar a demonstração. Poderia ter criado dezenas de tabelas, mas não há necessidade. Segue abaixo o exemplo de como poderíamos exportar todas aos objetos do schema SCOTT menos as tabelas T1 e T3 utilizando a cláusula EXCLUDE.
 
$ expdp system/manager schemas=SCOTT
  dumpfile=scott.dmp 
  EXCLUDE=TABLE:\"IN \(\'T1\',\'T3\'\)\"
  nologfile=y

Export: Release 11.2.0.3.0 - Production on Seg Jun 1 10:23:21 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=SCOTT
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 64 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exportou "SCOTT"."T2"                                    5 KB       1 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/scott.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:23:26

Pronto. Podemos ver acima que apenas a tabela T2 foi exportada, ou seja, ao importar o dump gerado com o utilitário impdp (Datapump Import), apenas a tabela T2 será criada no destino. Como a tabela T2 tem uma foreign key para a tabela T1, a mesma será importada sem essa restrição de integridade já que ao final da importação o erro "ORA-00942: a tabela ou view não existe" será emitido pelo fato a tabela T1 não existir. Segue abaixo um outra forma de fazer a mesma coisa, só que com a vantagem de que apenas as linhas das tabelas ignoradas não serão exportadas, ou seja, a estrutura da tabela será exportada para o dump, mas as linhas da tabela não. Para isso, será usado a cláusula QUERY onde será passado para cada tabela um predicado que não retorne nenhuma linha, como por exemplo "WHERE rownum=0". Segue exemplo abaixo.
 
$ expdp system/manager schemas=SCOTT
  dumpfile=scott.dmp
  QUERY=SCOTT.T1:\"WHERE rownum=0\"
  QUERY=SCOTT.T3:\"WHERE rownum=0\"
  nologfile=y

Export: Release 11.2.0.3.0 - Production on Seg Jun 1 10:25:56 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=SCOTT
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 128 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exportou "SCOTT"."T1"                                    0 KB       0 linhas
. . exportou "SCOTT"."T2"                                    5 KB       1 linhas
. . exportou "SCOTT"."T3"                                    0 KB       0 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/scott.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:26:05

Pronto. Podemos acima ver que todas as 3 tabelas foram exportadas. A diferença é que as tabelas T1 e T3 estão vazias, ou seja, nenhuma linha das tabelas T1 e T3 foram exportadas. Ao importar o dump gerado, todas as 3 tabelas serão importadas mas, da mesma forma que no primeiro exemplo, como a tabela T2 tem uma Foreign Key para a tabela T1, a mesma será importada sem essa restrição de integridade. Vale a pena salientar que durante a importação, não será mais emitido o erro "ORA-00942: a tabela ou view não existe", mas o erro "ORA-02298: não é possível validar (SCOTT.FK_T2_T1) - chaves mães não localizadas".

segunda-feira, 4 de maio de 2015

Detectando sessões bloqueadoras e sessões bloqueadas no Oracle: DBA_WAITERS, V$LOCKED_OBJECT, V$LOCK, V$SESSION

Por Eduardo Legatti

Olá,

Uma das tarefas de um DBA é monitorar o banco de dados de forma que seja identificado possíveis problemas que possam vir a comprometer a performance ou a disponibilidade do mesmo. Dentre alguns problemas que podem ser identificados antecipadamente, estão aos bloqueios (locks), em especial, aqueles onde existam sessões bloqueadoras e sessões bloqueadas. Dependendo da situação, descobrir de forma rápida a sessão (SID) no banco de dados que está bloqueando por muito tempo um recurso, é fundamental para se evitar maiores problemas. Dependendo do recurso bloqueado, (tabela, linha, etc.) várias sessões ficarão bloqueadas até a liberação do bloqueio do recurso pela sessão bloqueadora. O problema em si não é o bloqueio, mas por quanto tempo a sessão fica bloqueada. Em determinados casos, por falhas na aplicação, entre outros, o bloqueio pode ficar eternamente e, em casos extremos, somente matando a sessão bloqueadora (kill session ...) irá liberar o recursos e resolver o problema.

Enfim, o objetivo deste artigo será demonstrar algumas formas de identificar as sessões que estão bloqueando outras sessões. Serão apresentadas algumas views e consultas SQL que poderão ser úteis para este fim. Irei simular abaixo um ambiente na qual uma sessão no Oracle irá bloquear outras sessões. Segue demonstração abaixo:

SESSÃO 1

 
C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mai 4 10:17:20 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select distinct sid from v$mystat;

       SID
----------
       278

SQL> create table t1 (id number);

Tabela criada.

SQL> insert into t1 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> update t1 set id=10;

1 linha atualizada.


Acima, a sessão de SID 278 realizou um UPDATE na tabela T1 e não efetuou COMMIT.


SESSÃO 2


SQL> select distinct sid from v$mystat;

       SID
----------
       165

SQL> create table t2 (id number);

Tabela criada.

SQL> insert into t2 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> update t2 set id=100;

1 linha atualizada.

SQL> update t1 set id=20;
-- Aguardando


Na sessão de SID 165 acima, tanto na tabela T2 quanto na tabela T1 foram realizados UPDATEs. No entanto, ao realizar o UPDATE na tabela T1, a sessão ficou bloqueada, ou seja, aguardando que a sessão de SID 278 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T1.


SESSÃO 3


SQL> select distinct sid from v$mystat;

       SID
----------
        41

SQL> update t2 set id=30;
-- Aguardando

 
A sessão de SID 41 efetuou um UPDATE na tabela T2 e ficou bloqueada, ou seja, aguardando que a sessão de SID 165 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T2. Enfim, diante da situação das sessões de banco de dados acima, poderemos ver abaixo, consultando a view dinâmica de desempenho V$SESSION, quais sessões estão bloqueadas. Caso deseje ver por quanto tempo a sessão está bloqueada, basta adicionar a coluna SECONDS_IN_WAIT na instrução SELECT a seguir. 

SQL> select sid,
  2 serial#,
  3 status,
  4 username,
  5 osuser,
  6 program,
  7 blocking_session blocking,
  8 event
  9  from v$session
 10  where blocking_session is not null;

   SID  SERIAL# STATUS   USERNAME   PROGRAM      BLOCKING EVENT
------ -------- -------- ---------- ------------ -------- -----------------------------
    41    17090 ACTIVE   SYS        sqlplus.exe       165 enq: TX - row lock contention
   165     6770 ACTIVE   SYS        sqlplus.exe       278 enq: TX - row lock contention


Pelo resultado acima, é possível verificar que a sessão de SID 41 está sendo bloqueada pela sessão de SID 165, que está sendo bloqueada pela sessão de SID 278. Uma outra forma de ver essa situação é consultar as views DBA_WAITERS e DBA_BLOCKERS. Caso estas views não estejam criadas no banco de dados, as mesmas poderão ser criadas através do script $ORACLE_HOME/rdbms/admin/catblock.sql. Segue o resultado da view DBA_WAITERS conforme demonstrado abaixo.
 
SQL> select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION
--------------- ---------------
             41             165
            165             278

Uma outra forma que temos para visualizar situações com esta é executar o script $ORACLE_HOME/rdbms/admin/utllockt.sql. Este script é muito útil quando existem várias sessões bloqueadoras e bloqueadas e precisamos saber qual sessão iniciou todo o processo de bloqueio. A visualização do resultado é em forma de uma Tree View, que demonstra em uma hierarquia, as sessões bloqueadoras e as sessões bloqueadas.
 
SQL> @utllockt.sql

Tabela criada.

Tabela criada.

2 linhas criadas.

Commit concluído.

Tabela eliminada.

1 linha criada.

Commit concluído.

WAITING_SESSION   LOCK_TYPE      MODE_REQUESTED MODE_HELD    LOCK_ID1   LOCK_ID2
----------------- -------------- -------------- ------------ ---------- ---------
278               None
   165            Transaction    Exclusive      Exclusive    393227     454
      41          Transaction    Exclusive      Exclusive    524312     454

Tabela eliminada.

Acima, é possível determinar que a sessão de SID 278 iniciou o processo de bloqueio que fez com que a sessão de SID 165 ficasse bloqueada. Como consequência, a sessão de SID 165 também bloqueou a sessão de SID 41. Em relação ao objeto (tabela) que está sofrendo bloqueio, é possível determiná-lo consultando as views DBA_OBJECT, V$LOCKED_OBJECT, V$LOCK. O código PL/SQL abaixo retorna um resultado na qual é possível ver o SID da sessão bloqueadora, o SID da sessão bloqueada e o recurso (objeto) que detém o bloqueio (lock).
  
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.enable (1000000);
  3
  4     FOR do_loop IN (SELECT session_id,
  5                            a.object_id,
  6                            xidsqn,
  7                            oracle_username,
  8                            b.owner owner,
  9                            b.object_name object_name,
 10                            b.object_type object_type
 11                       FROM v$locked_object a, dba_objects b
 12                      WHERE xidsqn != 0 AND b.object_id = a.object_id)
 13     LOOP
 14        DBMS_OUTPUT.put_line ('.');
 15        DBMS_OUTPUT.put_line ('Blocking Session   : ' || do_loop.session_id);
 16        DBMS_OUTPUT.put_line (
 17              'Object (Owner/Name): '
 18           || do_loop.owner
 19           || '.'
 20           || do_loop.object_name);
 21        DBMS_OUTPUT.put_line ('Object Type        : ' || do_loop.object_type);
 22
 23        FOR next_loop
 24           IN (SELECT sid
 25                 FROM v$lock
 26                WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
 27        LOOP
 28           DBMS_OUTPUT.put_line (
 29              'Sessions being blocked   :  ' || next_loop.sid);
 30        END LOOP;
 31     END LOOP;
 32  END;
 33  /
.
Blocking Session   : 278
Object (Owner/Name): SYS.T1
Object Type        : TABLE
Sessions being blocked   :  165
.
Blocking Session   : 165
Object (Owner/Name): SYS.T1
Object Type        : TABLE
Sessions being blocked   :  41
.
Blocking Session   : 165
Object (Owner/Name): SYS.T2
Object Type        : TABLE
Sessions being blocked   :  41

Procedimento PL/SQL concluído com sucesso.
 

quarta-feira, 1 de abril de 2015

Abordando o COMMIT Assíncrono disponível a partir do Oracle 10g R2 (Asynchronous Commit)

Por Eduardo Legatti

Olá,

Este artigo foi escrito por mim e pelo meu colega Carlos H. Y. Furushima (ACE A), e tem como objetivo fazer uma abordagem sobre o commit assíncrono do Oracle (Asynchronous Commit), de forma a tratar suas principais características e diferenças em relação ao commit síncrono. Esta funcionalidade (commit assíncrono) surgiu no Oracle 10g Release 2 com o intuito de alterar o comportamento de uma transação com o propósito de oferecer ganhos de performance.


O que é transação de banco de dados?

Uma transação é uma sequência (1 ou mais) de operações de leitura (read) e escrita (write) executadas por um programa (unidade lógica de trabalho) sobre um sistema gerenciador de banco de dados (SGDB), ou seja, trata-se de uma visão abstrata que o sistema gerenciador de banco de dados (SGDB) tem de um programa. Esta transação em um SGBD de modelo transacional clássico (banco de dados relacional) possui quatro importantes propriedades cujo objetivo é manter os dados protegidos de acesso concorrente e de falhas de sistema. Uma transação possui quatro diferentes propriedades chamadas de ACID, um acrônimo derivado da primeira letra das seguintes propriedades: Atomicidade, Consistência, Isolamento e Durabilidade.

A transação em seu ciclo de vida faz uso de três principais recursos da arquitetura computacional, tais como: área de armazenamento volátil (memoria), CPU e área de armazenamento permanente (Disco - Storage). O fato de uma transação ser interrompida no meio de seu ciclo de vida pode deixar o banco de dados em um estado vulnerável e inconsistente. O intuito das propriedades ACID é impor ao SGDB o tratamento dos efeitos de transações parciais (transações interrompidas) do banco de dados.


Atomicidade e Durabilidade de uma transação

Uma transação é completada somente após sua confirmação ou cancelamento, conforme as propriedades de atomicidade (A) e durabilidade (D). Para manter este mecanismo, um SGBD de modelo transacional clássico (banco de dados relacional) mantém um registro de log para operações de escritas no banco de dados, nomeado em contexto Oracle, como "redo logs". Essa estrutura é responsável por garantir as propriedades de atomicidade (A) e durabilidade (D) de modo que se o banco de dados sofrer uma eventual queda antes que os dados alterados sejam escritos de forma persistente em disco, o log será utilizado para restaurar essas informações quando o sistema for normalizado. Deste modo, o SGDB garante a atomicidade, desfazendo as ações de transações que não realizaram a confirmação (operação de COMMIT) e a durabilidade, garantindo que todas as ações de transações que realizaram a confirmação (operação de COMMIT) fiquem persistentes em disco e se tornem tolerantes às falhas do sistema (banco de dados). 

  • Atomicidade (A): A execução de toda transação deve ser considerada atômica (indivisível), ou seja, ou todas as ações são executadas ou nenhuma delas é. Em caso de não confirmação ou interrupção abrupta o banco de dados deve voltar ao mesmo estado em que estava antes do início da transação.
  • Durabilidade (D): Se uma transação é concluída com sucesso (através de uma operação commit bem sucedida), então seus efeitos são persistentes (duráveis), mesmo que o sistema sofra uma queda antes que esses resultados (blocos ou dados modificados) sejam persistidos no disco.

Entendendo e modificando o comportamento de um COMMIT no Oracle

A execução da operação de COMMIT encadeia um exercício de gravação sequencial das entradas de redo (redo entries) para uma área permanente (disco - redo log files). No Oracle, o processo background LGWR (Log Writer) é o executor desta ação que visa proteger o banco de dados em caso de queda da instância, uma vez que os dados (blocos sujos no database buffer cache) em área volátil (SGA - Memória) possivelmente não foram gravados nos datafiles. Assim, o intuito é recuperar o "trabalho não salvo" (dados que não foram para os datafiles devido à queda da instância), utilizando os arquivos de redo logs gerados. Ao iniciar uma instância que sofreu uma falha (crash recovery), o processo background SMON será o responsável por refazer todas as ações do "trabalho não salvo nos datafiles".
No Oracle, o registro de redo online, também conhecido como grupo de redo, é um conjunto de no mínimo dois ou mais arquivos que tem como função primária registrar todas as alterações feitas no banco de dados, incluindo as alterações com e sem commit. As entradas de redo são armazenadas temporariamente nos buffers de registro de redo (Redo Log Buffer) da SGA (System Global Area) onde o processo de segundo plano log writer (LGWR) grava essas entradas sequencialmente em um arquivo de registro de redo online.

Estas gravações do buffer de redo para os arquivos ocorrem nas seguintes situações: (1) a cada 3 segundos, (2) quando 1/3 do buffer estiver cheio, (3) quando o comando commit for emitido, (4) quando as entradas de redo no buffer atingir 1 MB, (5) antes do processo de segundo plano DBWn gravar as alterações do cache de banco de dados nos datafiles. Os arquivos de redo log online são utilizados de forma cíclica, por exemplo, se dois arquivos constituem o registro de redo online, o primeiro arquivo é preenchido, o segundo arquivo é preenchido, o primeiro arquivo é reutilizado e preenchido, o segundo arquivo é reutilizado e preenchido e assim por diante. Cada vez que um arquivo é preenchido, ele recebe um número de sequência de registro para identificar o conjunto de entradas de redo.  Como já mencionado anteriormente, comitar uma transação significa apenas a garantia de sua recuperação em caso de falha, e não a gravação dos dados diretamente nos datafiles como muitos imaginam.

Partindo do princípio que uma operação de COMMIT escreve as entradas de redo (redo entries) para os redo log files a fim de oferecer uma recuperabilidade para o banco de dados, é importante salientar que a existência desta sinergia não garante a durabilidade (D) da transação. Seu mecanismo (modo de funcionamento) é quem irá garantir a propriedade "D". O comportamento default (padrão) da operação de COMMIT consiste na execução de escritas síncronas das entradas de redo (redo entries) para os redo log files. Neste modo de funcionamento o Oracle garante a durabilidade (D) da transação, porém é possível modificar este comportamento a partir da versão Oracle 10g Release 2 com uma funcionalidade chamada de "Asynchronous Commit" (Commit Assíncrono), possibilitando a execução de escritas assíncronas das entradas de redo (redo entries) para os redo log files. Utilizando o "Asynchronous Commit" (Commit Assíncrono), a propriedade durabilidade (D) não é mais garantida.



Como demonstrado na figura acima, o comportamento do processo background LGWR pode ser configurado/alterado de forma a otimizar e diminuir o tempo de gravação das informações de uma transação nos Redo Log Files. Vale a pena salientar que o próprio comando COMMIT também oferece este recurso através das cláusulas demonstradas pela figura abaixo.


 



Como alterar o comportamento da operação de COMMIT no Oracle Database?

A alteração do mecanismo (modo de funcionamento) da operação de COMMIT é feita modificando dois comportamentos, ou seja, "quando o LGWR escreve as entradas de redo" e "como o LGWR escreve as entradas de redo". Estes dois comportamentos são configurados por parâmetros de instância (pfile e spfile), são eles COMMIT_WRITE (Oracle 10g), COMMIT_LOGGING e COMMIT_WAIT (Ambos Oracle 11g). É importante ressaltar que a partir da versão 11g R1 o parâmetro COMMIT_WRITE se tornou obsoleto, porém ainda funcional para a manutenção da compatibilidade com a versão 10g R2 sendo desmembrado em dois parâmetros: COMMIT_LOGGING e COMMIT_WAIT.


Oracle 10g R2

COMMIT_WRITE = "Quando e como o LGWR escreve as entradas de redo"

No Oracle 10g R2, o uso da funcionalidade Asynchronous Commit é feita pelo parâmetro COMMIT_WRITE. Seu uso é feito tanto no nível de sessão (ALTER SESSION) quanto no nível de sistema (ALTER SYSTEM). O parâmetro COMMIT_WRITE pode receber até dois argumentos, onde é determinado "quando" (de forma imediata IMMEDIATE ou agrupada BATCH) e "como" (de forma síncrona WAIT ou assíncrona NOWAIT).

Importante:
  • Se o parâmetro não receber qualquer argumento, o Oracle assumirá seu comportamento default (IMMEDIATE e WAIT).
  • Se o parâmetro receber somente o argumento IMMEDIATE ou BATCH (quando), o Oracle assumirá o argumento default referente ao comportamento "como" (WAIT).
  • Se o parâmetro receber somente o argumento WAIT ou NOWAIT (como), o Oracle assumirá o argumento default referente ao comportamento "quando" (IMMEDIATE).

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_WRITE = '{ [IMMEDIATE] | [BATCH] },{ [NOWAIT] | [WAIT] }';

Oracle 11g R1 ou Superior

COMMIT_LOGGING = "Quando o LGWR escreve as entradas de redo".

O Oracle Database por meio do processo background LGWR escreve as entradas de redo sequencialmente em um redo log file. Existem duas opções de "quando" as entradas de redo são escritas para o redo log file: de forma imediata (IMMEDIATE) após a execução da operação de COMMIT ou de forma agrupada (BATCH), onde várias operações de COMMIT são escritas em uma única requisição de I/O, ou seja, o Oracle troca intensa quantidade de I/O com baixa volumetria de blocos por uma tênue quantidade de I/O com grande volumetria de blocos. O resultado disso é a diminuição de requisições de I/O oriundas de operações de COMMIT.

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_LOGGING = '{ [IMMEDIATE] | [BATCH] }';

COMMIT_WAIT = "Como o LGWR escreve as entradas de redo".

O Oracle Database por meio do processo background LGWR escreve as entradas de redo sequencialmente em um redo log file. Existem duas opções de "como" as entradas de redo são escritas para o redo log file: de forma síncrona (WAIT) após a execução da operação de COMMIT na qual o processo background LGWR espera pela confirmação de que as entradas de redo sejam escritas nos redo log files e a forma assíncrona (NOWAIT), após a execução da operação de COMMIT. Com isso, o processo background LGWR não espera pela confirmação de que as entradas de redo sejam escritas nos redo log files.

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_LOGGING = '{ [IMMEDIATE] | [BATCH] }';
 

Teste de desempenho (COMMIT)

Para medir a performance de escrita em um banco de dados Oracle no que se refere ao fechamento de uma transação com o comando COMMIT, foram realizados alguns testes de inserção (INSERTS) em uma tabela no banco de dados. O teste consistiu em medir o tempo gasto para inserção de 10, 100, 1.000 e 10.000 linhas respectivamente, de acordo com a configuração dos parâmetros COMMIT_LOGGING e COMMIT_WAIT na sessão do usuário.  Para simular as inserções, foram utilizados algumas stored procedures e functions de banco de dados, bem como a chamada de um bloco PL/SQL através do SQL*Plus. Após a execução dos testes, os resultados abaixo foram obtidos.

Obs: a vertente de tempo do gráfico foi transformada em um intervalo de referência entre 0 e 10 de forma a simplificar a sua visualização, ou seja, quanto menor o valor, melhor a performance.
  • Simulação de inserção de 10 linhas 


  • Simulação de inserção de 100 linhas




  • Simulação de inserção de 1.000 linhas


   
  • Simulação de inserção de 10.000 linhas  

     

    segunda-feira, 2 de março de 2015

    Abordando uma "New Feature" do particionamento por referência (Reference Partitioning) do Oracle 12c

    Por Eduardo Legatti

    Olá,

    No artigo de Fevereiro/2011 fiz uma breve introdução sobre o conceito de tabelas particionadas de forma a demonstrar as operações de manutenção envolvidas nas mesmas e no artigo de Abril/2011 demonstrei como particionar uma tabela de forma on-line. Já nos artigos de Junho/2011 e Maio/2012 eu demonstrei como utilizar o Interval Partitioning e o Reference Partitioning respectivamente, ambos introduzidos no Oracle 11g.

    Neste artigo, irei abordar uma inovação feita no Oracle 12c que permite utilizar o método de particionamento Reference Partitioning em uma tabela filha, tendo como pai, uma tabela particionada pelo método Interval Partitioning. No Oracle 11g isso não é possível.

    Apenas para recapitular, o interessante sobre o particionamento por referência (Reference Partitioning), é que o mesmo permite que uma restrição de integridade (Foreign Key) definida na tabela filha, seja utilizada como chave de partição. Isso permite que a tabela filha herde a chave de particionamento da tabela pai sem a necessidade de duplicar a coluna chave de partição da tabela pai na tabela filha. Em outras palavras, a tabela filha é particionada da mesma maneira que a tabela pai. Consultas SQL executadas nas tabelas pai e filha através de "Joins" terão seu desempenho melhorado, pois dependendo do predicado utilizado na consulta, o otimizador do Oracle irá determinar a partição envolvida na tabela pai (partition pruning) e identificar partição da tabela filha referente. No caso do particionamento por intervalo (Interval Partitioning), o mesmo automatiza a criação de novas partições de uma tabela por uma determinada faixa ou intervalo (RANGE), de forma que o DBA não precise se preocupar em criar novas partições manualmente.

    Abaixo, irei criar a tabela PEDIDO com 3 partições iniciais, tendo como coluna chave de partição, o campo DATA_PEDIDO. O tipo de particionamento utilizado será por faixa de valores (RANGE) utilizando o Interval Partitioning de forma que cada partição armazene os registros de um mês específico. Depois irei criar a tabela ITEM_PEDIDO utilizando o tipo de particionamento por referência (Reference Partitioning).

    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:37:34 2015
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Conectado a:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> create table pedido (
      2     cod_pedido  number primary key,
      3     data_pedido date not null,
      4     cod_cliente number not null)
      5  partition by range (data_pedido)
      6  interval(numtoyminterval(1,'month'))
      7  store in (users)
      8  (
      9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
     10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
     11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
     12  );
    
    Tabela criada.
    
    SQL> create table item_pedido (
      2     cod_pedido  number not null,
      3     cod_produto number not null,
      4     quantidade  number,
      5     constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
      6  partition by reference (fk_itempedido_pedido);
    create table item_pedido (
    *
    ERRO na linha 1:
    ORA-14659: O método de particionamento da tabela mãe não é suportado
    

    Com o erro demonstrado acima, é possível perceber que com o Oracle 11g não é possível criar uma tabela particionada por referência na qual a tabela pai (referenciada), esteja particionada por intervalo (Interval Partitioning). Iremos verificar abaixo que à partir do Oracle 12c isto já será possível.

    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:48:20 2015
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Conectado a:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> create table pedido (
      2     cod_pedido  number primary key,
      3     data_pedido date not null,
      4     cod_cliente number not null)
      5  partition by range (data_pedido)
      6  interval(numtoyminterval(1,'month'))
      7  store in (users)
      8  (
      9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
     10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
     11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
     12  );
    
    Tabela criada.
    
    SQL> create table item_pedido (
      2     cod_pedido  number not null,
      3     cod_produto number not null,
      4     quantidade  number,
      5     constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
      6  partition by reference (fk_itempedido_pedido);
    
    Tabela criada.  
    

    Pronto. Como demonstrado acima, no Oracle 12c é possível criar uma tabela particionada por referência tendo como tabela referenciada, uma tabela particionada por RANGE através do Interval Partitioning.
    SQL>  select table_name,
      2          partitioning_type,
      3          partition_count,
      4          interval
      5     from user_part_tables;
    
    TABLE_NAME           PARTITION PARTITION_COUNT INTERVAL
    -------------------- --------- --------------- --------------------------
    PEDIDO               RANGE             1048575 NUMTOYMINTERVAL(1,'MONTH')
    ITEM_PEDIDO          REFERENCE         1048575 YES 

    Agora irei inserir dados nas duas tabelas de forma a demonstrar como o Oracle será capaz de criar automaticamente uma partição na tabela PEDIDO para acomodar registros com datas de um determinado mês, bem como demonstrar que a partição criada automaticamente será herdada pela tabela ITEM_PEDIDO.
     
    SQL> insert into pedido
      2  select level,
      3         to_date('31/12/2014')+level,
      4         trunc(dbms_random.value(1,1000))
      5    from dual
      6  connect by level <= 120;
    
    120 linhas criadas.
    
    SQL> insert into item_pedido
      2  select level,
      3         trunc(dbms_random.value(1,1000)),
      4         trunc(dbms_random.value(1,100))
      5    from dual
      6  connect by level <= 120;
    
    120 linhas criadas.
    
    SQL> exec dbms_stats.gather_schema_stats('SCOTT');
    
    Procedimento PL/SQL concluído com sucesso.
    
    SQL> select table_name,
      2         partition_name,
      3         high_value,
      4         num_rows
      5    from user_tab_partitions;
    
    TABLE_NAME           PARTITION_NAME  HIGH_VALUE                       NUM_ROWS
    -------------------- --------------- ------------------------------ ----------
    PEDIDO               P_2015_01       TO_DATE(' 2015-02-01 00:00:00'         31
    PEDIDO               P_2015_02       TO_DATE(' 2015-03-01 00:00:00'         28
    PEDIDO               P_2015_03       TO_DATE(' 2015-04-01 00:00:00'         31
    PEDIDO               SYS_P501        TO_DATE(' 2015-05-01 00:00:00'         30
    ITEM_PEDIDO          P_2015_01                                              31
    ITEM_PEDIDO          P_2015_02                                              28
    ITEM_PEDIDO          P_2015_03                                              31
    ITEM_PEDIDO          SYS_P501                                               30
    
    8 linhas selecionadas.
      
    Podemos ver pelo resultado acima que a partição SYS_P501 foi criada automaticamente na tabela PEDIDO para acomodar os registros referentes ao mês de Abril, e que essa mesma partição também foi herdada pela tabela ITEM_PEDIDO.

    Postagens populares