Fazendo uso de Wallets no Oracle para omitir informações de usuários e senhas durante uma autenticação no banco de dados

Olá,

Quando precisamos conectar no banco de dados Oracle, geralmente precisamos fornecer três informações básicas: o usuário, a senha e a string de conexão (Oracle Net Service Name) contendo as informações do banco de dados de destino na qual queremos nos conectar. Em relação a string de conexão, podemos utilizar um alias referente a um serviço de banco de dados localizado no arquivo TNSNAMES.ORA. Por exemplo, utilizando o SQL*Plus, em vez de utilizarmos um alias TNS contendo a string de conexão para o banco de dados (ex: sqlplus user/pass@TNS), à partir do  SQL*Plus do Oracle 10g poderemos também utilizar a sintaxe easy connect (ex: sqlplus user/pass@servername:port/DB). Enfim, independente da forma de conexão utilizada, para que uma aplicação possa se conectar no banco de dados, essas três informações são necessárias. No entanto, a seguinte questão poderá surgir: Como fazer para proteger as informações de usuário e senha de forma que as mesmas não fiquem expostas no código da aplicação, arquivos de configuração, scripts SQL e arquivos batch? Bom, é aí que entra o conceito de Wallet, ou seja, um container usado para armazenar credenciais de autenticação de forma segura e criptografada.

Após o Wallet ser configurado, não será mais necessário fornecer as informações de usuário e senha, pois as mesmas estarão armazenadas no Wallet. Para tanto, será necessário apenas informar a string de conexão para o banco de dados de destino (ex: sqlplus /@TNS). Veremos a seguir um exemplo prático de como criar um Wallet.

Após instalação do Oracle Client, o primeiro passo será editar o arquivo SQLNET.ORA localizado em ORACLE_HOME/network/admin de forma a configurarmos o parâmetro WALLET_LOCATION conforme demonstrado abaixo:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = C:\WALLET)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE

Acima, foi definido que o caminho do Wallet será C:\WALLET. O parâmetro SQLNET.WALLET_OVERRIDE foi setado para TRUE apenas para garantir que o método de autenticação seja o de autenticação de banco de dados, ou seja, qualquer método de autenticação, como por exemplo, (OS authentication) será ignorado. Bom, após realizado a configuração acima, irei criar a seguir o Wallet. Vale a pena salientar que será necessário definir uma senha de no mínimo 8 caracteres (letras e números) para a criação do Wallet. Esta senha será necessária para abrir o Wallet conforme demonstrações mais a frente.

C:\>mkstore -wrl "C:\WALLET" -create
Oracle Secret Store Tool : Versão 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle e/ou suas empresas afiliadas. Todos os direitos reservados.

Informe a senha: ********

Informe a senha novamente: ********

Após a criação do Wallet, podemos ver abaixo que dois arquivos foram criados no diretório C:\WALLET.

C:\>dir C:\WALLET
 O volume na unidade C não tem nome.
 O número de série do volume é 74DF-3878

 Pasta de C:\WALLET

02/06/2013  13:02             3.589 cwallet.sso
02/06/2013  13:02             3.512 ewallet.p12
               2 arquivo(s)          7.101 bytes
               2 pasta(s) 14.245.322.752 bytes disponíveis

O próximo passo será o de fornecer as credenciais de autenticação como usuário, senha, e a string de conexão. Abaixo, irei informar o serviço BD01 que deverá estar devidamente configurado no arquivo TNSNAMES.ORA, o usuário scott e a senha tiger.
 

C:\>mkstore -wrl "C:\WALLET" -createCredential BD01 scott tiger
Oracle Secret Store Tool : Versão 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle e/ou suas empresas afiliadas. Todos os direitos reservados.

Informe a senha da wallet: ********

PKI-02002: Não foi possível abrir a wallet. Verifique a senha.

Apenas para fins de demonstração, caso eu informe a senha diferente da que foi definida na criação do Wallet, o erro PKI-02002 será emitido.

C:\>mkstore -wrl "C:\WALLET" -createCredential BD01 scott tiger
Oracle Secret Store Tool : Versão 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle e/ou suas empresas afiliadas. Todos os direitos reservados.

Informe a senha da wallet: ********

Create credential oracle.security.client.connect_string1

Após a digitação correta da senha, podemos ver acima que a credencial foi inserida na Wallet com sucesso. Para nos certificarmos disso podemos utiliza o comando abaixo para verificar as informações.

C:\>mkstore -wrl "C:\WALLET" -listCredential
Oracle Secret Store Tool : Versão 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle e/ou suas empresas afiliadas. Todos os direitos reservados.

Informe a senha da wallet: ********

List credential (index: connect_string username)
1: BD01 scott

Agora poderemos realizar o teste de conexão. Para tanto irei utilizar o sqlplus para simular o cliente remoto. Para que o teste seja realizado com sucesso, o serviço BD01 deverá estar devidamente configurado no arquivo TNSNAMES.ORA conforme demonstrado abaixo:

BD01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BD01)
    )
  )

Vamos então ao teste. Abaixo irei realizar a conexão com o banco de dados, omitinto o usuário e senha. A única informação passada será a do serviço de banco de dados.

C:\>sqlplus /@BD01

SQL*Plus: Release 11.2.0.1.0 Production on Dom Jun 2 13:13:03 2013

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

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

SQL> show user
USER é "SCOTT"

Perfeito. A conexão foi realizada com sucesso. Agora vamos simular a alteração da senha do usuário SCOTT.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Dom Jun 2 13:15:03 2013

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

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

SQL> alter user scott identified by dog;

Usuário alterado.

Se tentarmos realizar a conexão novamente, veremos abaixo que a mesma irá falhar, pois a credencial no Wallet possui informação da senha antiga (tiger).

C:\>sqlplus /@BD01

SQL*Plus: Release 11.2.0.1.0 Production on Dom Jun 2 13:19:03 2013

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

ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado

Para tanto, precisaremos atualizar o Wallet informando a nova senha do usuário SCOTT conforme demonstrado no comando abaixo:

C:\>mkstore -wrl "C:\WALLET" -modifyCredential BD01 scott dog
Oracle Secret Store Tool : Versão 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle e/ou suas empresas afiliadas. Todos os direitos reservados.

Informe a senha da wallet: ********

Modify credential
Modify 1

Após a atualização da senha, poderemos realizar um novo teste conforme a seguir.

C:\>sqlplus /@BD01

SQL*Plus: Release 11.2.0.1.0 Production on Dom Jun 2 13:21:03 2013

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

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

SQL> show user
USER é "SCOTT"

Se por acaso desejarmos excluir essa credencial do Wallet, bastará utilizarmos o comando abaixo.

C:\>mkstore -wrl "C:\WALLET" -deleteCredential BD01
Oracle Secret Store Tool : Versão 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle e/ou suas empresas afiliadas. Todos os direitos reservados.

Informe a senha da wallet:

Delete credential
Delete 1

Enfim, se quisermos inserir várias credenciais no Wallet de forma que mais de um usuário faça uso do mesmo, basta definirmos diferentes nomes de serviços TNS. No mais, no Oracle 11g foi criado o utilitário orapki que oferece muito mais opções para gerenciamento de wallets, entre outros recursos.

Desvendando o erro ORA-06553: PLS-382: a expressão é do tipo incorreto

Olá,

Sabemos que na linguagem SQL não existe o tipo de dado Boolean. Bom, pelo menos no que se refere ao Oracle, o tipo de dado Boolean existe apenas dentro do PL/SQL. O motivo para eu estar escrevendo este artigo vem de um cenário na qual um desenvolvedor pediu um suporte para a equipe de analistas de banco de dados na qual atuo de forma a ajudá-lo a desvendar o erro "ORA-06553: PLS-382: a expressão é do tipo incorreto" que insistia em acontecer a todo momento em que ele chamava uma função no banco de dados através de uma consulta SQL. É aí que o tipo de dado Boolean entra na estória. Para simular a situação ocorrida, tenha como exemplo a função FC_IS_WEEKEND criada abaixo:

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

SQL> create or replace function fc_is_weekend (p_date date)
  2     return boolean
  3  as
  4     aux varchar2(3);
  5  begin
  6     select to_char (p_date,'DY','nls_date_language=AMERICAN') into aux from dual;
  7     if aux in ('SAT','SUN')
  8     then
  9        return TRUE;
 10     else
 11        return FALSE;
 12     end if;
 13  end;
 14  /

Função criada.

Vejamos o que acontece quando chamamos a função através de uma consulta SQL conforme demonstrado abaixo:

SQL> select fc_is_weekend(sysdate) from dual;
select fc_is_weekend(sysdate) from dual
       *
ERRO na linha 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: a expressão é do tipo incorreto

Perceberam o problema? A função foi criada tendo como propósito retornar um valor Booleano (TRUE/FALSE), ou seja, se uma data passada como parâmetro cair em um final de semana, então a função deverá retornar TRUE, senão a mesma deverá retornar FALSE. O erro "ORA-06553: PLS-382" ocorre exatamente pelo fato do SQL não entender o tipo de dado Booleano que está sendo retornado pela função. Neste caso, a função somente poderá ser usada apenas dentro de um bloco PL/SQL como demonstrado abaixo:

SQL> set serveroutput on
SQL> declare
  2    aux boolean;
  3  begin
  4    aux := fc_is_weekend (sysdate);
  5    dbms_output.put_line(sys.diutil.bool_to_int(aux));
  6  end;
  7  /
0

Procedimento PL/SQL concluído com sucesso. 

Acima, eu chamei a função FC_IS_WEEKEND de dentro de um bloco PL/SQL e, para fins de demonstração, transformei o resultado para o tipo de dado numérico usando a função SYS.DIUTIL.BOOL_TO_INT de forma que o resultado pudesse ter sido mostrado. Neste caso, 0 (zero) significa FALSE. Enfim, uma forma de resolver este problema seria alterar a função existente, ou criar uma nova função conforme demonstrado a seguir:

SQL> create or replace function fc_is_weekend2 (p_date date)
  2     return number
  3  as
  4     aux boolean;
  5  begin
  6     aux := fc_is_weekend(p_date);
  7     if aux = TRUE
  8     then
  9        return 1;
 10     else
 11        return 0;
 12     end if;
 13  end;
 14  /

Função criada.

SQL> select fc_is_weekend2(sysdate) from dual;

FC_IS_WEEKEND2(SYSDATE)
-----------------------
                      0

No mais, o ideal seria que o Oracle além de entender um overload da função dentro de uma package, na qual os parâmetros de entrada das funções seriam do mesmo tipo, mas o tipo de dados de saída seriam diferentes, fosse também capaz de perceber a origem da chamada de uma função (SQL ou PL/SQL) de forma a fazer a chamada para a função correta como demonstrado abaixo. A questão é que eu não saberia dizer realmente se existiria alguma vantagem nisso.

SQL> create or replace package pkg_teste
  2  as
  3     function fc_is_weekend (p_date date) return boolean;
  4     function fc_is_weekend (p_date date) return number;
  5  end;
  6  /

Pacote criado.

SQL> create or replace package body pkg_teste
  2     as
  3        function fc_is_weekend (p_date date) return boolean as
  4         aux varchar2(3);
  5        begin
  6         select to_char (p_date,'DY','nls_date_language=AMERICAN') into aux from dual;
  7         if aux in ('SAT','SUN')
  8         then
  9            return TRUE;
 10         else
 11            return FALSE;
 12         end if;
 13        end;
 14
 15        function fc_is_weekend (p_date date) return number as
 16         aux varchar2(3);
 17        begin
 18         select to_char (p_date,'DY','nls_date_language=AMERICAN') into aux from dual;
 19         if aux in ('SAT','SUN')
 20         then
 21            return 1;
 22         else
 23            return 0;
 24         end if;
 25        end;
 26  end;
 27  /

Corpo de Pacote criado.

SQL> select pkg_teste.fc_is_weekend(sysdate) from dual;
select pkg_teste.fc_is_weekend(sysdate) from dual
       *
ERRO na linha 1:
ORA-06553: PLS-307: muitas declarações de 'FC_IS_WEEKEND' são compatíveis com esta chamada 

Ignorando caracteres acentuados, maiúsculos e minúsculos no Oracle (Case Insensitive/Accent Insensitive)

Olá,

Dê uma boa olhada no resultado do SQL abaixo. Em que ordem os caracteres vieram ordenados?

SQL> select nome from t1 order by 1;

NOME
----
A
a
á
Z
z
1
10
2
9

9 linhas selecionadas.

De acordo com o CHARACTERSET em uso no banco de dados, seja ele WE8ISO8859P1 ou WE8MSWIN1252 (ambos muito utilizados aqui no Brasil), quando ordenamos caracteres alfanuméricos fazendo uso da claúsula ORDER BY em um SELECT, vemos caracteres alfabéticos antes dos caracteres numéricos. No cenário do meu banco de dados, isso ocorre porque o parâmetro NLS_SORT está definido com o valor WEST_EUROPEAN.

SQL> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ -------------------------------
NLS_LANGUAGE                   BRAZILIAN PORTUGUESE
NLS_TERRITORY                  BRAZIL
NLS_CURRENCY                   R$
NLS_ISO_CURRENCY               BRAZIL
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/YYYY HH24:MI:SS
NLS_DATE_LANGUAGE              BRAZILIAN PORTUGUESE
NLS_SORT                       WEST_EUROPEAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Cr$
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 linhas selecionadas.

O mesmo efeito será obtido alterando a sessão no banco de dados com o comando "ALTER SESSION" abaixo:

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
A
a
á
Z
z
1
10
2
9

9 linhas selecionadas.

Se quisermos que os caracteres numéricos apareçam antes dos caracteres alfabéticos, basta alteramos a sessão para que o parâmetro NLS_SORT seja definido como BINARY. O valor BINARY significa que a ordenação dos caracteres se seguirão de acordo com a ordem em que os caracteres aparecem no Character Set em uso. Fazendo uma analogia, seria como seguir a ordem da numeração dos caracteres na tabela ASCII. Veja o exemplo abaixo:

SQL> ALTER SESSION SET NLS_SORT='BINARY';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
1
10
2
9
A
Z
a
z
á

9 linhas selecionadas.

Podemos verificar acima que, além do caracteres numéricos virem antes dos caracteres alfabéticos, vemos que o caractere "a" minúsculo veio após o caractere "Z" maiúsculo. Para que a ordenação seja Case Insensitive, basta alterarmos NLS_SORT para BINARY_CI conforme demonstração abaixo:

SQL> ALTER SESSION SET NLS_SORT='BINARY_CI';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
1
10
2
9
a
A
Z
z
á

9 linhas selecionadas.

Já em relação a acentuação, podemos verificar que o caractere "á" ("a" minúsculo acentuado) veio após o caractere "z" minúsculo. Para que a ordenação seja não só Case Insensitive, mas também Accent Insensitive, basta alterarmos NLS_SORT para BINARY_AI, conforme demonstração abaixo:

SQL> ALTER SESSION SET NLS_SORT='BINARY_AI';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
1
10
2
9
a
á
A
Z
z

9 linhas selecionadas.

Uma sintaxe que podemos utilizar de forma que não tenhamos que ficar alterando a sessão no banco de dados a todo momento, é utilizar a função NLSSORT, conforme exemplo a seguir:

SQL> select * from t1 order by NLSSORT(nome,'NLS_SORT=BINARY_CI');

NOME
----
1
10
2
9
a
A
Z
z
á

9 linhas selecionadas.

Bom, a figura abaixo nos mostra um resumo da ordem em que aparecerão os caracteres alfanuméricos dependendo do valor que estiver setado em NLS_SORT.

Já em relação à ignorar caracteres acentuados, maiúsculos e minúsculos em uma pesquisa, tenha como base os dados da tabela abaixo:

SQL> select * from t2;

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Uma maneira prática e manual de pesquisarmos todos os "João(s)" acima ignorando os caracteres acentuados, maiúsculos e minúsculos, seria utilizando a função TRANSLATE conforme exemplo a seguir:

SQL> select nome
  2  from t2
  3  where
  4  upper
  5  (translate(nome,
  6  'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',
  7  'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'))
  8  LIKE
  9  upper((translate('%joao%',
 10  'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',
 11  'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')));

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Bom, além de utilizar a solução acima, teria alguma outra de forma a se obter o mesmo resultado? Sim. Além de setarmos NLS_SORT, basta setarmos também o parâmetro NLS_COMP para ANSI (até o 10g R1) ou LINGUISTIC (à partir do 10g R2). Por padrão o seu valor é BINARY. Como exemplo, realizando o "alter session" abaixo, iremos fazer com que o Oracle ordene e compare linguisticamente ignorando caracteres maiúsculos e minúsculos. (CI = somente Case Insensitive).

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_CI';

Sessão alterada.

SQL> ALTER SESSION SET NLS_COMP='ANSI';

Sessão alterada.

SQL> select * from t2 where nome = 'joao';

NOME
--------------------
Joao
joao
JOAO

Para ignorar caracteres acentuados, poderemos setar NLS_SORT para WEST_EUROPEAN_AI (AI = Accent Insensitive e Case Insensitive):

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_AI';

Sessão alterada.

SQL> select * from t2 where nome = 'joao';

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Uma limitação do NLS_COMP=ANSI se mostra evidente quando fazemos uso do operador LIKE, conforme demonstração a seguir:

SQL> select * from t2 where nome LIKE ('%joao%');

NOME
--------------------
joao

Como podemos ver no resultado acima, apenas um registro foi retornado, ou seja, a pesquisa não ignorou caracteres acentuados, maiúsculos e minúsculos. É por esse motivo que à partir do Oracle 10g R2, o valor "ANSI" está obsoleto conforme a documentação: "A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC". Portanto, alterando o valor de NLS_COMP para LINGUISTIC, poderemos verificar abaixo que a pesquisa utilizando o operador LIKE irá ignorar os caracteres acentuados, maiúsculos e minúsculos.

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_AI';

Sessão alterada.

SQL> ALTER SESSION SET NLS_COMP='LINGUISTIC';

Sessão alterada.

SQL> select * from t2 where nome LIKE ('%joao%');

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Um pouco do SQL*Loader

Olá,

Neste artigo irei descrever um pouco sobre o SQL*Loader, também conhecido como Oracle Loader, e demonstrar um exemplo prático de como poderemos efetuar uma carga de dados proveniente de um arquivo texto no formato CSV para uma tabela no banco de dados. Basicamente, o SQL*Loader lê arquivos de texto e insere os dados no banco de dados. A vantagem de utilizá-lo é que o mesmo permite realizar uma carga de dados de forma bastante rápida. Ele é capaz de inserir milhões de linhas em questão de segundos. Ele realiza essa carga de dados com base nas instruções de um arquivo de controle (control file) previamente configurado. Atenção! Não confunda o control file de banco de dados com o control file do SQL*Loader. Este arquivo de controle diz ao Loader não só onde inserir os dados, mas também de que forma estes dados serão lidos do arquivo texto (data file). Para ilustrar, a arquitetura do SQL*Loader é demonstrada na figura abaixo. Em relação ao arquivo de descarte (discard file), por ser opcional, não irei mencioná-lo nesse artigo.

Para executar o SQL*Loader, é necessário executar o comando sqlldr que está localizado em ORACLE_HOME/bin seja na instalação do Oracle Server ou do Oracle Client. Se não for incluído nenhum parâmetro na chamada do SQL*Loader, será exibido uma ajuda online como a que está apresentada na listagem a seguir:

C:\>sqlldr

SQL*Loader: Release 11.2.0.1.0 - Production on Dom Mar 3 10:54:13 2013

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

Uso: SQLLDR palavra-chave=valor [,palavra-chave=valor,...]

Palavras-Chave Válidas:

    userid -- ORACLE nome do usuário/senha
   control -- nome do arquivo de controle
       log -- nome do arquivo de log
       bad -- nome do arquivo de incorreções
      data -- nome do arquivo de dados
   discard -- descartar nome de arquivo
discardmax -- número de descartes a serem permitidos          (Usar todos como default)
      skip -- número de registros lógicos a serem ignorados   (Usar 0 como default)
      load -- número de registros lógicos a serem carregados  (Usar todos como default)
    errors -- número de erros a serem permitidos              (Usar 50 como default)
      rows -- número de linhas do array de bind do caminho convencional ou entre o salvamento de dados de caminho direto
               (Default: Caminho convencional 64, Caminho direto para todos)
  bindsize -- tamanho do array de bind do caminho convencional em bytes  (Usar 256000 como default)
    silent -- suprime mensagens durante execução (cabeçalho,feedback,erros,descartes,partições)
    direct -- usar caminho direto                      (Usar FALSE como default)
   parfile -- arquivo de parâmetros: nome do arquivo que contém especificações de parâmetros
  parallel -- fazer carga paralela                     (Usar FALSE como default)
      file -- arquivo para alocar extensões
skip_unusable_indexes -- desativar/ativar índices não utilizáveis em partições de índice  (Usar FALSE como default)
skip_index_maintenance -- não manter índices, marcar índices afetados como não utilizáveis  (Usar FALSE como default)
commit_discontinued -- commit das linhas carregadas quando a carga é descontinuada  (Usar FALSE como default)
readsize -- tamanho do buffer de leitura  (Usar 1048576 como default)
external_table -- usar tabela externa para carga; NOT_USED, GENERATE_ONLY, EXECUTE  (Usar NOT_USED como default)
columnarrayrows -- número de linhas para array de coluna de caminho direto  (Usar 5000 como default)
streamsize -- tamanho do buffer de fluxo de caminho direto em bytes  (Usar 256000 como default)
multithreading -- utilizar multithread no caminho direto
resumable -- ativar ou desativar retomável para a sessão atual  (Usar FALSE como default)
resumable_name -- string de texto para ajudar a identificar a instrução retomável
resumable_timeout -- tempo de espera (em segundos) para RESUMABLE  (Usar 7200 como default)
date_cache -- tamanho (em entradas) do cache de conversão de data  (Usar 1000 como default)
no_index_errors -- abortar carga em qualquer erro de índice  (Usar FALSE como default)

OBSERVE: Os parâmetros da linha de comandos podem ser especificados pela
posição ou por palavras-chave.  Um exemplo do primeiro caso é 'sqlload
scott/tiger foo'; um exemplo do segundo caso é 'sqlldr control=foo
userid=scott/tiger'.  Um pode determinar parâmetros pela posição antes,
mas não após os parâmetros especificados por palavras-chave.  Por exemplo,
'sqlldr scott/tiger control=foo logfile=log' é permitido, mas
'sqlldr scott/tiger control=foo log' não é, mesmo que a
posição do 'log' de parâmetro esteja correta.

Como podemos ver acima, existe uma longa lista de parâmetros, entretanto na maior parte das vezes, executaremos comandos semelhantes a sqlldr {user}/{password} CONTROL={arquivo.ctl} DATA={arquivo.csv}. Para exemplificar, irei carregar abaixo um arquivo no formato CSV contendo dados fictícios de pessoas para uma tabela no banco de dados. Segue abaixo a estrutura da tabela:

SQL> desc pessoa
 Nome               Nulo?    Tipo
 ------------------ -------- ------------------
 CODIGO             NOT NULL NUMBER
 NOME               NOT NULL VARCHAR2(100)
 CIDADE                      VARCHAR2(100)
 UF                          VARCHAR2(2)
 DATA_CADASTRO      NOT NULL DATE

Em relação aos dados de origem, segue um trecho do arquivo CSV contendo os dados de pessoas que deverão ser carregadas na tabela PESSOA de propriedade do usuário SCOTT. O arquivo dados.csv contém 1000 linhas.

C:\>type dados.csv
1;Jose Rodrigues;Belo Horizonte;MG;06/01/2001 08:25:23
2;Reinaldo Duarte;Belo Horizonte;MG;04/09/2005 10:22:08
3;Ronaldo Junior;Belo Horizonte;MG;20/07/2006 13:45:34
4;Leonardo Vieira;São Paulo;SP;14/06/2010 14:06:56
5;Joana Santos;Salvador;BA;11/03/2011 17:56:21
6;Laura Fernandes;Rio de janeiro;RJ;25/10/2008 15:12:39
...

Já em relação ao arquivo de controle, segue abaixo a configuração que utilizarei: 

C:\>type pessoa.ctl
OPTIONS (ERRORS=999999999, ROWS=100)
LOAD DATA
INFILE 'dados.csv'
APPEND
INTO TABLE PESSOA
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 CODIGO,
 NOME,
 CIDADE,
 UF,
 DATA_CADASTRO "to_date(Trim(:DATA_CADASTRO),'DD/MM/YYYY HH24:MI:SS')"
)

Na primeira linha do arquivo de controle eu estou definindo que não quero parar o processo de carga dos dados de forma nenhuma. Independente do número de erros que acontecerem, eu quero continuar com o processo de carga, por isso o parâmetro ERRORS está com um valor alto. O parâmetro ROWS configura a frequência de commits de acordo com os valores de BINDSIZE e READSIZE. Para ambos estou utilizando os valores Default. No mais, segue abaixo a execução da carga de dados.

C:\>sqlldr scott/tiger CONTROL=pessoa.ctl DATA=dados.csv

SQL*Loader: Release 11.2.0.1.0 - Production on Dom Mar 3 10:54:14 2013

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

Atingido o ponto de commit - contagem de registros lógicos 100
Atingido o ponto de commit - contagem de registros lógicos 200
Atingido o ponto de commit - contagem de registros lógicos 300
Atingido o ponto de commit - contagem de registros lógicos 400
Atingido o ponto de commit - contagem de registros lógicos 500
Atingido o ponto de commit - contagem de registros lógicos 600
Atingido o ponto de commit - contagem de registros lógicos 700
Atingido o ponto de commit - contagem de registros lógicos 800
Atingido o ponto de commit - contagem de registros lógicos 900
Atingido o ponto de commit - contagem de registros lógicos 999
Atingido o ponto de commit - contagem de registros lógicos 1000

Após a execução do processo de carga dos dados, poderemos verificar um arquivo de log contendo algumas informações sobre a execução. Neste caso, foi gerado o arquivo pessoa.log. Dentre as informação mais importantes do arquivo de log destaco alguns abaixo:

Arquivo de Controle:    pessoa.ctl
Arquivo de Dados:       dados.csv
Arquivo de Incorreções: dados.bad
Arquivo de Descarte:  nenhum(a) foi especificado(a)
Número a ser carregado: ALL
Número a ser ignorado: 0
Erros permitidos: 999999999
Array de ligação: 100 linhas, máximo de 256000 bytes
Caminho usado: Convencional
Opção de inserção em vigor para esta tabela: APPEND
Opção TRAILING NULLCOLS em efeito

Tabela PESSOA:
  1000 Linhas carregado com êxito.
  0 Linhas não carregado devido a erros de dados.
  0 Linhas não carregado porque todas as cláusulas WHEN falharam.
  0 Linhas não carregado porque todos os campos eram nulos.

Total de registros lógicos ignorados:       0
Total de registros lógicos lidos:        1000
Total de registros lógicos rejeitados:      0
Total de registros lógicos descartados:     0

A execução começou em Dom Mar 03 10:54:14 2013
A execução foi finalizada em Dom Mar 03 10:54:14 2013

O tempo decorrido foi:   00:00:00.23
O tempo de CPU foi:      00:00:00.11

Vale a pena salientar que, se algum registro não for carregado para a tabela, será necessário verificar o motivo do mesmo analisando o arquivo de log e também um arquivo com a extensão .bad. Este arquivo irá conter todos os registros que foram rejeitados pelo processo de carga devido alguma inconformidade ou erro no banco de dados. Para exemplificar, irei alterar o formato de data em uma linha do arquivo de dados (dados.csv) de forma que o mesmo será diferente do formato de data esperado pelo arquivo de controle (pessoa.ctl).

C:\>sqlldr scott/tiger CONTROL=pessoa.ctl DATA=dados.csv

SQL*Loader: Release 11.2.0.1.0 - Production on Dom Mar 3 11:09:42 2013

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

Atingido o ponto de commit - contagem de registros lógicos 100
Atingido o ponto de commit - contagem de registros lógicos 200
Atingido o ponto de commit - contagem de registros lógicos 300
Atingido o ponto de commit - contagem de registros lógicos 400
Atingido o ponto de commit - contagem de registros lógicos 500
Atingido o ponto de commit - contagem de registros lógicos 600
Atingido o ponto de commit - contagem de registros lógicos 700
Atingido o ponto de commit - contagem de registros lógicos 800
Atingido o ponto de commit - contagem de registros lógicos 900
Atingido o ponto de commit - contagem de registros lógicos 999
Atingido o ponto de commit - contagem de registros lógicos 1000

Após a execução do processo de carga, poderemos verificar que, além do arquivo pessoa.log, também foi gerado o arquivo dados.bad. No arquivo de log destaco as informações abaixo:

Registro 1000: Rejeitado - Erro na tabela PESSOA, coluna DATA_CADASTRO.
ORA-01861: o literal não corresponde à string de formato

Tabela PESSOA:
  999 Linhas carregado com êxito.
  1 Linha não carregado devido a erros de dados.
  0 Linhas não carregado porque todas as cláusulas WHEN falharam.
  0 Linhas não carregado porque todos os campos eram nulos.

Total de registros lógicos ignorados:       0
Total de registros lógicos lidos:        1000
Total de registros lógicos rejeitados:      1
Total de registros lógicos descartados:     0

Em relação ao arquivo dados.bad, podemos verificar abaixo qual o registro que não foi carregado.

C:\>type dados.bad
4;Leonardo Vieira;São Paulo;SP;2010/04/10 14:06:56

Por fim, o SQL*Loader é uma ferramenta bem versátil que permite a realização de carga massiva de dados para o banco de dados. Com a configuração adequada, o mesmo consegue ler um ou mais arquivos de texto ao mesmo tempo e carregar dados para uma ou mais tabelas. Vale a pena salientar que ele também realiza carga de dados utilizando o caminho direto (direct path), mas com algumas restrições.

Gerando o modelo e o dicionário de dados com o SchemaSpy

Olá,

Imagine um cenário onde você como DBA tenha a tarefa de realizar a engenharia reversa de um schema de banco de dados (um DER com mais de 500 tabelas e seus relacionamentos) e gerar uma documentação para enviar para algum parceiro. Quais opções você teria para gerar e enviar esta documentação (DER e dicionário e dados)? Bom, após escolher uma ferramenta case que fizesse a engenharia reversa do banco de dados, uma opção seria enviar o arquivo proprietário gerado pela ferramenta de modelagem utilizada e pedir para o parceiro instalar o mesmo software de forma a poder abrir o arquivo. Uma outra opção seria enviar uma documentação gerada pela ferramenta case em um dos formatos (.doc, .html, .pdf). O problema de enviar uma documentação no formato (.doc, .pdf) é a legibilidade do modelo de dados. Seria muito difícil ter uma visão do modelo de dados em várias páginas de um documento. Na minha humilde opinião, a melhor opção seria enviar a documentação no formato HTML. O motivo disso é porque o modelo de dados e o dicionário de dados (tabelas, colunas, relacionamentos) poderia ser visualizado com links através de abas proporcionadas pelo browser. Dentre algumas das ferramentas cases de modelagem de dados mais conhecidas hoje no mercado, podemos citar o Enterprise Architect, PowerDesigner, ERwin, Toad Data Modeler, SQL Developer Data Modeler entre outras, a maioria delas gera uma documentação no formato HTML, no entanto, pelo que vi em algumas ferramentas, o modelo de dados (DER) gerado é uma imagem. Neste caso, a visualização e a navegabilidade pelo DER se tornaria difícil e penosa.

Para resolver esse problema, recomendo a utilização do SchemaSpy que é uma ferramenta gratuita baseada em Java (requer Java 5 ou superior) que analisa os metadados de um esquema em um banco de dados e gera uma representação visual em um formato legível pelo browser. Ele utiliza um programa chamado Graphviz para gerar as representações gráficas dos relacionamentos das tabelas. Vale a pena salientar que ele permite que você clique através da hierarquia das tabelas através dos relacionamentos existentes entre as mesmas. O SchemaSpy possui suporte para vários bancos e utiliza o JDBC para extrair as informações do banco de dados.

Bom, neste artigo irei mostrar como utilizar o SchemaSpy (linha de comando). Como exemplo, utilizarei o schema HR (Human Resources) disponível no banco de dados Oracle XE. Será necessário realizar o download do SchemaSpy e também baixar e instalar o Graphviz.

Bom, após a realização das tarefas acima, irei criar o diretório schemaSpy e copiar para dentro dele o arquivo schemaSpy.jar e o driver ojdbc14.jar para conexão com o banco de dados Oracle. Criarei também um subdiretório output que será o destino dos arquivos gerados pelo SchemaSpy.

 

Neste momento já poderemos executar o schemaSpy como demonstrado abaixo:

C:\schemaSpy>java -jar schemaSpy.jar
   -dp "C:\schemaSpy\ojdbc14.jar"
   -t orathin -db XE
   -host localhost
   -port 1521
   -u HR
   -p PASSWD123
   -o "E:\schemaSpy\output"
   -norows
   -hq

Using database properties:
  [schemaSpy.jar]/net/sourceforge/schemaspy/dbTypes/orathin.properties
Gathering schema details...........(10sec)
Writing/graphing summary..............(1sec)
Writing/diagramming details........(2sec)
Wrote relationship details of 8 tables/views to directory 'C:\schemaSpy\output' in 13 sec.
View the results by opening C:\schemaSpy\output\index.html

Após a execução acima, podemos verificar no diretório output que os arquivos que compõem a documentação foi gerada com sucesso.

Agora, basta clicar no arquivo index.html para visualizar não só o modelo de dados (DER), como também o dicionário de dados.

Para quem quiser utilizar uma interface gráfica, também existe o SchemaSpyGUI.

Monitorando o percentual de espaço livre na tablespace. Quando devemos nos preocupar?

Olá,

Para qualquer DBA, o monitoramento constante e preventivo do banco de dados é fundamental. Se o mesmo não for monitorado de forma correta, as aplicações e usuários que conectam no banco dados poderão se deparar com problemas inesperados em algum momento. Dentre os mais variados tipos de problemas que podem ocorrer em um banco de dados, um dos que são bem conhecidos é o erro "ORA-01653: não é possível estender a tabela %s.%s em %s na tablespace %s". Apesar de podermos utilizar alguns recursos (RESUMABLE_TIMEOUT) conforme demonstrado no artigo de Agosto/2008 de forma a evitar erros relacionados a falta de espaço livre em uma tablespace, a ocorrência deste tipo de problema demonstra uma falha grave no monitoramento do banco de dados, e em especial, ao monitoramento do espaço livre nas tablespaces do banco de dados. A não ser que algo totalmente repentino e inesperado venha a acontecer de forma que não haja tempo hábil para detectar previamente o problema, este tipo de situação é inadmissível em um ambiente de produção. Neste artigo irei demonstrar como verificar o percentual de espaço livre nas tablespaces de forma pró-ativa. Para fins de didática, leve em consideração os comandos de criação das tablespaces abaixo:

CREATE TABLESPACE TBS_01 DATAFILE 
  'C:\ORACLE\ORADATA\BD01\TBS01.DBF' SIZE 100M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_02 DATAFILE 
  'C:\ORACLE\ORADATA\BD01\TBS02.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 150M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_03 DATAFILE 
  'C:\ORACLE\ORADATA\BD01\TBS03.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Após a criação das tablespaces acima, eu criei algumas tabelas e efetuei vários INSERTS. Após a realização dessas operações, veja na figura abaixo como ficou o percentual de uso nas tablespaces TBS_01, TBS_02 e TBS_03.

 

De acordo com a figura acima, qual das três tablespaces possui um problema crítico de espaço livre? Se sua resposta foi a tablespace TBS_03, infelizmente você errou. Apesar da tablespace TBS_03 estar com 97% do espaço em uso, é a tablespace TBS_01 com apenas 61% do espaço em uso que merece atenção. Por quê? Simplesmente porque a view de dicionário de dados DBA_TABLESPACE_USAGE_METRICS nos mostra isso. Veja o resultado do SQL abaixo:

SQL> select *
  2    from dba_tablespace_usage_metrics
  3   where tablespace_name in ('TBS_01', 'TBS_02', 'TBS_03')
  4  order by 1;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TBS_01                7808           12800           61
TBS_02               10368           19200           54
TBS_03               12416         4194302   ,296020649

Acima, podemos verificar que a coluna USED_PERCENT referente à tablespace TBS_03 possui o valor de cerca de 0,29% de espaço utilizado. Já as tablespaces TBS_02 e TBS_01 possuem os valores 54% e 61% respectivamente. Bom, mas como se chegou a estes valores? Segue a fórmula abaixo:

USED_PERCENT = USED_SPACE / TABLESPACE_SIZE * 100

Vale a pena salientar que os valores das colunas USED_SPACE e TABLESPACE_SIZE são mostrados em unidade de blocos (block size) e não em bytes. Para provar tal afirmação, verifique o quadro abaixo:

TABLESPACE USED_SPACE                     TABLESPACE_SIZE
---------- ------------------------------ -----------------------------------
TBS_01      7808 * 8192 / 1048576 = 61 MB   12800 * 8192 / 1048576 =   100 MB
TBS_02     10368 * 8192 / 1048576 = 81 MB   19200 * 8192 / 1048576 =   150 MB
TBS_03     12416 * 8192 / 1048576 = 97 MB 4194302 * 8192 / 1048576 = 32768 MB

Ficou com dúvidas em relação aos valores da coluna TABLESPACE_SIZE? Bom, já que os três arquivos de dados foram criados com o mesmo tamanho que é de 100 MB, porque somente a tablespace TBS_01 apresentou 100 MB? O interessante a ser notar nos resultados da view DBA_TABLESPACE_USAGE_METRICS é que ela não leva em conta somente a soma do tamanho atual dos arquivos de dados que compõem uma tablespace, mas o tamanho máximo que eles poderão atingir de acordo com a configurações de cada um, ou seja, dependerá também do valor de MAXSIZE caso a opção AUTOEXTEND do arquivo de dados esteja habilitada.

SQL> select file_id,
  2         tablespace_name,
  3         round(bytes/1024/1024) size_mb,
  4         blocks,
  5         autoextensible,
  6         increment_by,
  7         round(maxbytes/1024/1024) max_mb
  8    from dba_data_files
  9   where tablespace_name in ('TBS_01', 'TBS_02', 'TBS_03')
 10  order by 1;

FILE_ID TABLESPACE_NAME  SIZE_MB   BLOCKS AUT INCREMENT_BY    MAX_MB
------- --------------- -------- -------- --- ------------ ---------
      5 TBS_01               100    12800 NO             0         0
      6 TBS_02               100    12800 YES         1280       150
      7 TBS_03               100    12800 YES         1280     32768

Após consultar a view de dicionário de dados DBA_DATA_FILES acima, podemos verificar que os valores da coluna MAX_MB é exatamente o tamanho máximo que o arquivo de dados poderá se estender conforme demonstrado no comando de criação de cada tablespace no início do artigo. De acordo com o resultado acima podemos concluir que:

• TBS_01 - O arquivo de dados 5 pertencente à tablespace TBS_01 não está com AUTOEXTEND habilitado. Portanto, o arquivo não poderá se estender além dos já 100 MB alocados.

• TBS_02 - O arquivo de dados 6 pertencente à tablespace TBS_02 está com AUTOEXTEND habilitado. A coluna INCREMENT_BY nos mostra 1280 blocos de 8 KB, ou seja, quando necessário, o arquivo irá se estender de 10 MB em 10 MB até o máximo de 150 MB.

• TBS_03 - O arquivo de dados 7 pertencente à tablespace TBS_03, está com AUTOEXTEND habilitado. A coluna INCREMENT_BY nos mostra 1280 blocos de 8 KB, ou seja, quando necessário, o arquivo irá se estender de 10 MB em 10 MB até o máximo de 32 GB (32768 MB).

Para fins de informação, o OEM Database Console e OEM Grid/Cloud Control se utilizam da view DBA_TABLESPACE_USAGE_METRICS  para realização de notificações e envio de alertas sobre o percentual de uso das tablespaces, conforme as métricas estabelecidas (85% WARNING e 97% CRITICAL).

Por fim, vale a pena salientar que a view de dicionário de dados DBA_TABLESPACE_USAGE_METRICS não leva em consideração o espaço físico em disco, ou seja, mesmo que o arquivo de dados da tablespace TBS_03 possa se estender até 32 GB, mas o espaço livre em disco seja de 10 GB, o Oracle não terá como saber disso. Portanto, se não for tomada nenhuma atitude, o arquivo de dados continuará a se estender até esgotar todo o espaço em disco, o que geraria um erro catastrófico. Estes testes foram realizados utilizando não só o File System do sistema operacional como também o ASM (Automatic Storage Management).

Migrando para o SecureFile LOBS no Oracle 11g com o DBMS_REDEFINITION

Olá,

No artigo de Novembro/2012 eu demonstrei um pouco sobre o Oracle SecureFile LOBS no Oracle 11g em relação à compressão de dados e deduplicação. Neste artigo irei demonstrar, de forma prática, uma das formas de como poderemos migrar uma coluna LOB, de BASICFILE, para SECUREFILE. Para isso, farei uso de um recurso que já foi  demonstrado no artigo de Abril/2011 que é a redefinição online de uma tabela (Online table redefintion) usando o pacote DBMS_REDEFINITION. Bom, até o momento, não existe uma forma automática ou um comando (ALTER TABLE ...) que altere uma coluna LOB (CLOB/BLOB) de BASICFILE para SECUREFILE. Apesar de podermos utilizar os métodos CTAS, Export/Import, ou até mesmo utilizar um comando UPDATE para atualizar os dados de uma coluna (BASICFILE) para uma nova coluna (SECUREFILE) na mesma tabela, na minha opinião, um ótimo método é fazer uso da redefinição online, pois o mesmo não ocasiona downtime para a aplicação que acessa o banco de dados, além de ser muito prático.

Abaixo, irei demonstrar na prática como poderemos migrar uma tabela que possui uma coluna LOB padrão BASICFILE, para uma coluna LOB SECUREFILE, já com a deduplicação e compressão média ativadas. A tabela original será a mesma utilizada no artigo de Novembro/2012, a diferença que o seu nome será BLOB_TABLE e não mais BLOB_BASIC.

Seguindo o roteiro já demonstrado no artigo de Abril/2011, irei iniciar o procedimento criando uma tabela interina (INTERIM) que será o objeto alvo da redefinição online. Note que a tabela será praticamente idêntica à tabela original, diferenciando-se apenas pela coluna BLOB, que será do tipo SECUREFILE com a deduplicação e compressão média ativadas. Em comparação com o artigo de Abril/2011, naquele artigo eu não tinha definido nenhuma constraint NOT NULL nas colunas da tabela INTERIM, afim de evitar qualquer tipo de problema nos procedimentos de redefinição online da tabela.

Para fins de didática, neste artigo irei definir as colunas como NOT NULL de forma a espelhar a tabela original. A diferença agora é que irei setar o parâmetro "ignore_errors" da procedure COPY_TABLE_DEPENDENTS como TRUE. Esta procedure pertence à package DBMS_REDEFINITION. Vale a pena salientar que o segmento LOB da tabela original possui cerca de 8,63 GB de tamanho e que, ao final da redefinição online, o mesmo terá cerca de 2,81 GB.

C:\ sqlplus system/manager

SQL*Plus: Release 11.2.0.3.0 Production on Seg Dez 3 19:57:53 2012

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

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

SQL> create table scott.blob_table_interim
  2  (
  3    codigo    number(10) not null,
  4    nome      varchar2(255),
  5    documento blob
  6  )
  7  lob (documento) store as securefile
  8         (
  9           tablespace tbs_lob
 10           deduplicate
 11           compress medium
 12         )
 13  tablespace tbs_data;

Tabela criada.

Após a criação da tabela BLOB_TABLE_INTERIM acima, irei iniciar abaixo, o processo de redefinição online.

SQL> begin
  2  dbms_redefinition.start_redef_table
  3  (
  4     uname           => 'SCOTT'
  5   , orig_table      => 'BLOB_TABLE'
  6   , int_table       => 'BLOB_TABLE_INTERIM'
  7   , col_mapping     => null
  8   , options_flag    => dbms_redefinition.cons_use_pk
  9   , orderby_cols    => null
 10   , part_name       => null
 11  );
 12  end;
 13  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:21:09.42

Após a execução do processo acima, irei iniciar abaixo o processo que irá realizar a cópia dos objetos dependentes da tabela original para a tabela interina. Esse objetos incluem, por exemplo, contraints, triggers, valores default, etc...

SQL> var num_errors number
SQL> begin
  2  dbms_redefinition.copy_table_dependents
  3  (
  4      uname            => 'SCOTT'
  5    , orig_table       => 'BLOB_TABLE'
  6    , int_table        => 'BLOB_TABLE_INTERIM'
  7    , copy_indexes     => dbms_redefinition.cons_orig_params
  8    , copy_triggers    => true
  9    , copy_constraints => true
 10    , copy_privileges  => true
 11    , ignore_errors    => true
 12    , num_errors       => :num_errors
 13    , copy_statistics  => true
 14  );
 15  end;
 16  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:23.46

Após a execução do procedimento de cópia dos objetos dependentes, irei verificar o resultado da variável num_errors. Se a mesma tiver um valor diferente de zero, então é prudente verificar na view DBA_REDEFINITION_ERRORS, qual ação não foi realizada com sucesso.

SQL> print num_errors

NUM_ERRORS
----------
         1

SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;

OBJECT_NAME   BASE_TABLE_NAME     DDL_TXT
------------- ------------------- --------------------------------------------------
SYS_C003910   BLOB_TABLE          ALTER TABLE "SCOTT"."BLOB_TABLE_INTERIM" 
                                  MODIFY ("CODIGO" CONSTRAINT "TMP$$_SYS_C0039100"
                                  NOT NULL ENABLE NOVALIDATE)
1 linha selecionada.

Podemos verificar pelo resultado acima que o processo de cópia de objetos dependentes tentou modificar a coluna CODIGO da tabela interina para NOT NULL. Neste caso, poderemos ignorar esse erro. Abaixo está a instrução SQL na qual podemos verificar todos os objetos que foram redefinidos.

SQL> select object_type, object_name, base_table_name, interim_object_name
  2  from dba_redefinition_objects
  3  where base_table_name = 'BLOB_TABLE' order by 1,2,3;

OBJECT_TYPE  OBJECT_NAME         BASE_TABLE_NAME     INTERIM_OBJECT_NAME
------------ ------------------- ------------------- --------------------------
TABLE        BLOB_TABLE          BLOB_TABLE          BLOB_TABLE_INTERIM
CONSTRAINT   PK_BLOB_TABLE       BLOB_TABLE          TMP$$_PK_BLOB_TABLE0
INDEX        PK_BLOB_TABLE       BLOB_TABLE          TMP$$_PK_BLOB_TABLE0
INDEX        IDX_BLOB_TABLE_NOME BLOB_TABLE          TMP$$_IDX_BLOB_TABLE_NOME0

4 linhas selecionadas.

Bom, caso alguma linha da tabela original tenha sofrido algum tipo de alteração DML durante o processo de redefinição online, é prudente sincronizar a tabela interina antes de finalizar o processo de redefinição. Esta fase é importante para não sobrecarregar a fase de finalização "finish_redef_table".

SQL> begin
  2  dbms_redefinition.sync_interim_table
  3  (
  4      uname      => 'SCOTT'
  5    , orig_table => 'BLOB_TABLE'
  6    , int_table  => 'BLOB_TABLE_INTERIM'
  7    , part_name  => null
  8  );
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Pronto. Agora poderemos finalizar o processo de redefinição online.

SQL> begin
  2  dbms_redefinition.finish_redef_table
  3  (
  4      uname      => 'SCOTT'
  5    , orig_table => 'BLOB_TABLE'
  6    , int_table  => 'BLOB_TABLE_INTERIM'
  7    , part_name  => null
  8  );
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Vale a pena salientar que caso ocorra algum tipo de problema durante o processo de redefinição online ou apenas queiramos abortar o processo por algum motivo, poderemos utilizar a procedure "abort_redef_table" como demonstrado abaixo:

Caso queira abortar o processo
==============================

SQL> begin
  2  dbms_redefinition.abort_redef_table
  3  (
  4      uname=>'SCOTT'
  5    , orig_table=>'BLOB_TABLE'
  6    , int_table=>'BLOB_TABLE_INTERIM'
  7  );
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Bom, após a execução de todas as operações acima, irei verificar o tamanho dos segmentos LOB do schema SCOTT utilizando uma consulta que eu criei e documentei conforme publicação do artigo de Maio/2011.

SQL> select table_name,
  2       decode(partitioned,'/','NO',partitioned) partitioned,
  3       num_rows,
  4       data_mb,
  5       indx_mb,
  6       lob_mb,
  7       total_mb
  8        from (select data.table_name,
  9                partitioning_type
 10               || decode (subpartitioning_type,
 11                          'none', null,
 12                          '/' || subpartitioning_type)
 13                      partitioned,
 14               num_rows,
 15               nvl(data_mb,0) data_mb,
 16               nvl(indx_mb,0) indx_mb,
 17               nvl(lob_mb,0) lob_mb,
 18               nvl(data_mb,0) + nvl(indx_mb,0) + nvl(lob_mb,0) total_mb
 19               from (  select table_name,
 20                     nvl(min(num_rows),0) num_rows,
 21                     round(sum(data_mb),2) data_mb
 22                        from (select table_name, num_rows, data_mb
 23                            from (select a.table_name,
 24                                  a.num_rows,
 25                                  b.bytes/1024/1024 as data_mb
 26                                    from user_tables a, user_segments b
 27                                   where a.table_name = b.segment_name))
 28                   group by table_name) data,
 29                   (  select a.table_name,
 30                          round(sum(b.bytes/1024/1024),2) as indx_mb
 31                       from user_indexes a, user_segments b
 32                         where a.index_name = b.segment_name
 33                      group by a.table_name) indx,
 34                   (  select a.table_name,
 35                         round(sum(b.bytes/1024/1024),2) as lob_mb
 36                      from user_lobs a, user_segments b
 37                     where a.segment_name = b.segment_name
 38                      group by a.table_name) lob,
 39                     user_part_tables part
 40               where     data.table_name = indx.table_name(+)
 41                     and data.table_name = lob.table_name(+)
 42                     and data.table_name = part.table_name(+))
 43    order by table_name;

TABLE_NAME          PARTITIONED NUM_ROWS  DATA_MB  INDX_MB   LOB_MB  TOTAL_MB
------------------- ----------- -------- -------- -------- -------- ---------
BLOB_TABLE          NO             15000      4,5      ,06     2880   2884,56
BLOB_TABLE_INTERIM  NO             15000      3,5        7     8841   8851,50

2 linhas selecionadas.

Podemos verificar que após o processo de redefinição online, a tabela original BLOB_TABLE foi renomeada para BLOB_TABLE_INTERIM e vice versa. Portanto, não precisaremos mais da tabela interina e poderemos dropá-la com segurança. Perceba a diferença de tamanho do segmento LOB após a redefinição online.

SQL> drop table blob_table_interim purge;

Tabela eliminada.

Por fim, mostrarei abaixo um teste em relação ao comando DML (INSERT), de forma a demonstrar a diferença de performance entre SECUREFILE e BASICFILE em uma coluna CLOB.

Oracle 10g
==========

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

5 linhas selecionadas.

SQL> create table clob_basic
  2  (
  3    id         number(10),
  4    nome       varchar2(255),
  5    documento  clob
  6  )
  7  lob (documento) store as (tablespace tbs_lob)
  8  tablespace tbs_data;

Tabela criada.

SQL> declare
  2    l_clob clob := rpad('x', 10000, 'x');
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into clob_basic values (i,'teste',l_clob);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:10:46.94

Podemos verificar acima que, no Oracle 10g, o processo de inserção de 100.000 linhas em uma tabela com uma coluna CLOB (BASICFILE) demorou cerca de 10:46 minutos.

Oracle 11g
==========

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 linhas selecionadas.

SQL> create table clob_basic
  2  (
  3    id         number(10),
  4    nome       varchar2(255),
  5    documento  clob
  6  )
  7  lob (documento) store as (tablespace tbs_lob)
  8  tablespace tbs_data;

Tabela criada.

SQL> declare
  2    l_clob clob := rpad('x', 10000, 'x');
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into clob_basic values (i,'teste',l_clob);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:01:19.60

Podemos verificar acima que, no Oracle 11g, o processo de inserção de 100.000 linhas em uma tabela com uma coluna CLOB (BASICFILE) demorou cerca de 01:19 minutos.

SQL> create table clob_securefile
  2  (
  3    id         number(10),
  4    nome       varchar2(255),
  5    documento  clob
  6  )
  7  lob (documento) store as securefile (tablespace tbs_lob)
  8  tablespace tbs_data;

Tabela criada.

SQL> declare
  2    l_clob clob := rpad('x', 10000, 'x');
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into clob_securefile values (i,'teste',l_clob);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:01:56.56

Podemos verificar acima que, no Oracle 11g, o processo de inserção de 100.000 linhas em uma tabela com uma coluna CLOB (SECUREFILE) demorou cerca de 01:56 minutos.

Portanto, de acordo com os resultados demonstrados pelos testes acima, podemos comprovar que realmente houve uma melhora considerável de performance (pelo menos no INSERT), comparando o Oracle 10g (BASICFILE) e o Oracle 11g (SECUREFILE). No entanto, podemos verificar que o BASICFILE no Oracle 11g não foi apenas superior ao BASICFILE do Oracle 10g, como também foi superior ao próprio SECUREFILE. Neste caso, concluo que o SECUREFILE é vantajoso quando utilizamos outras opções, sejam elas compressão, deduplicação ou criptografia.