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


terça-feira, 1 de julho de 2014

Configurando um Script Master para execução de scripts no SQL*Plus

Por Eduardo Legatti

Olá,

Quando precisamos executar vários scripts SQL em um banco de dados, geralmente automatizamos a sua execução para não termos que ficar executando script por script. Dependendo do ambiente, como no caso de um banco de produção, a boa prática é colher todas as informações da execução realizada afim de se ter uma evidência ou prova do que foi executado. Portanto, um arquivo de log contendo as informações da execuções dos scripts é fundamental. Dentre as informações que eu considero importante que se tenha no arquivo de log, eu posso citar a instrução SQL que foi executada, o nome do script, a hora da execução, o tempo de execução, o nome do servidor e o nome do banco de dados. No artigo de Janeiro/2011 eu mostrei como habilitar o suporte a acentuação no DOS para execução de scripts SQL. Neste artigo irei compartilhar um modelo de script que utilizo com uma certa frequência que tem como objetivo chamar outros scripts e gerar uma evidência da execução realizada. Apesar do script que utilizo fazer mais validações, irei demonstrar apenas o que considero básico para um Script Master. Segue abaixo o conteúdo do mesmo na qual irei realizar a execução de 9 scripts.
 
C:\SQL>type 00-ScriptMaster.sql

WHENEVER OSERROR EXIT 1;
WHENEVER SQLERROR EXIT 1;
SET ECHO ON
SET DEFINE OFF
SET FEEDBACK ON
SET TIME ON
SET TIMING ON
SET APPINFO ON
SET SERVEROUTPUT ON SIZE 1000000
SPOOL 00-ScriptMaster.log
select host_name,instance_name from v$instance;
alter session set nls_territory='BRAZIL';
alter session set current_schema=SCOTT;
@01-script01.sql
@02-script02.sql
@03-script03.sql
@04-script04.sql
@05-script05.sql
@06-script06.sql
@07-script07.sql
@08-script08.sql
@09-script09.sql
spool off
quit;
 
Como demonstrado acima, o script 00-ScriptMaster.sql é o nome do Script Master. Em resumo, ele contém algumas validações como o uso das cláusulas WHENEVER nas duas primeiras linhas. Elas indicam que se algum script não for encontrado ou se ocorrer algum erro na execução de algum script, a execução do Script Master será abortada. Na minha opinião, esse tratamento é fundamental de forma a evitar que os scripts subsequentes sejam afetados por um erro ocasionado na execução de um script anterior. Dessa forma, um script que der algum problema poderá ser tratado pontualmente.

Outro ponto que acho importante se refere ao SET DEFINE OFF que impede que o caractere "&" seja tratado como "host variable" no SQL*Plus. Em relação ao horário de execução e o tempo de execução de cada instrução, SET TIME ON e SET TIMING ON são fundamentais. Na linha 8, o SET APPINFO ON é útil para fins de monitoramento porque o script em execução será visível na coluna MODULE da view dinâmica de desempenho V$SESSION. Portanto, mesmo que o Script Master esteja em execução no servidor de banco de dados, será possível acompanhar de outra máquina qual script está sendo executado no momento consultando a view V$SESSION. O SET SERVEROUTPUT ON é útil para mostrar resultados de códigos PL/SQL que utilizam o comando DBMS_OUTPUT.

Na linha 10, o comando SPOOL irá gerar o arquivo de log na qual darei o nome de 00-ScriptMaster.log. Para evitar qualquer problema com formatos de data, pontos decimais e de milhar, eu costumo forçar o NLS_TERRITORY como demonstrado na linha 12.

Para evitar qualquer problema em relação à falta de priviégios durante a execução dos scripts, eu prefiro executar o Script Master com um usuário DBA, como por exemplo o SYSTEM. Como a conexão é feita pelo SYSTEM, eu forço a sessão para o schema que será alvo da execução dos scripts (SCOTT) como demonstrado na linha 13. Caso o Script Master envolva a execução de scripts em mais de um schema, então vários "alter session set current_schema" poderão ser inseridos ao longo do Script Master. Por fim, as linhas que começam com @ são exatamente os scripts que serão executados conforme demonstrado abaixo:

C:\SQL>dir
 O volume na unidade C não tem nome.
 O Número de Série do Volume é 1C51-B278

 Pasta de C:\SQL

01/07/2014  15:27    [DIR]          .
01/07/2014  15:27    [DIR]          ..
01/07/2014  15:43               517 00-ScriptMaster.sql
01/07/2014  15:37               118 01-script01.sql
01/07/2014  15:13                20 02-script02.sql
01/07/2014  15:14                38 03-script03.sql
01/07/2014  15:48               103 04-script04.sql
01/07/2014  15:53               202 05-script05.sql
01/07/2014  15:15                36 06-script06.sql
01/07/2014  15:17                97 07-script07.sql
01/07/2014  15:17                37 08-script08.sql
01/07/2014  15:35               256 09-script09.sql
              10 arquivo(s)         1.424 bytes
               2 pasta(s)   102.755.786.752 bytes disponíveis

No mais, segue uma execução do Script Master na qual eu simulo um erro de execução do script 04-script04.sql. Neste caso, a execução do Script Master será abortada e a saída gravada no arquivo de log 00-ScriptMaster.log.

C:\SQL>sqlplus system/manager @00-ScriptMaster.sql

SQL*Plus: Release 11.2.0.1.0 Production on Ter Jul 1 15:47:12 2014

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

15:47:21 SQL> select host_name,instance_name from v$instance;

HOST_NAME                         INSTANCE_NAME
--------------------------------- -------------
server01                          BD01

1 linha selecionada.

Decorrido: 00:00:00.01
15:47:21 SQL> alter session set nls_territory='BRAZIL';

Sessão alterada.

Decorrido: 00:00:00.00
15:47:21 SQL> alter session set current_schema=SCOTT;

Sessão alterada.

Decorrido: 00:00:00.00
15:47:21 SQL> @01-script01.sql
15:47:21 SQL> create table t1 (id number);

Tabela criada.

Decorrido: 00:00:00.12
15:47:21 SQL> create table t2 (id number);

Tabela criada.

Decorrido: 00:00:00.03
15:47:21 SQL> create table t3 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:47:21 SQL> create table t4 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:47:21 SQL> @02-script02.sql
15:47:21 SQL> drop table t3 purge;

Tabela eliminada.

Decorrido: 00:00:00.02
15:47:22 SQL> @03-script03.sql
15:47:22 SQL> alter table t1 add nome varchar2(100);

Tabela alterada.

Decorrido: 00:00:00.08
15:47:22 SQL> @04-script04.sql
15:47:22 SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERRO na linha 1:
ORA-00947: não há valores suficientes
 
Enfim, segue abaixo o conteúdo do arquivo de log após uma execução completa do Script Master.

C:\SQL>sqlplus system/manager @00-ScriptMaster.sql
15:54:35 SQL> select host_name,instance_name from v$instance;

HOST_NAME                         INSTANCE_NAME
--------------------------------- ----------------
server01                          BD01

1 linha selecionada.

Decorrido: 00:00:00.00
15:54:35 SQL> alter session set nls_territory='BRAZIL';

Sessão alterada.

Decorrido: 00:00:00.00
15:54:35 SQL> alter session set current_schema=SCOTT;

Sessão alterada.

Decorrido: 00:00:00.00
15:54:35 SQL> @01-script01.sql
15:54:35 SQL> create table t1 (id number);

Tabela criada.

Decorrido: 00:00:00.06
15:54:35 SQL> create table t2 (id number);

Tabela criada.

Decorrido: 00:00:00.01
15:54:35 SQL> create table t3 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:54:35 SQL> create table t4 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:54:35 SQL> @02-script02.sql
15:54:35 SQL> drop table t3 purge;

Tabela eliminada.

Decorrido: 00:00:00.03
15:54:35 SQL> @03-script03.sql
15:54:35 SQL> alter table t1 add nome varchar2(100);

Tabela alterada.

Decorrido: 00:00:00.01
15:54:35 SQL> @04-script04.sql
15:54:35 SQL> insert into t1 values (1,'x');

1 linha criada.

Decorrido: 00:00:00.04
15:54:35 SQL> insert into t1 values (2,'y');

1 linha criada.

Decorrido: 00:00:00.01
15:54:35 SQL> insert into t1 values (3,'z');

1 linha criada.

Decorrido: 00:00:00.01
15:54:35 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.01
15:54:36 SQL> @05-script05.sql
15:54:36 SQL> insert into t2 values (10);

1 linha criada.

Decorrido: 00:00:00.01
15:54:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.01
15:54:36 SQL> 
15:54:36 SQL> declare
15:54:36   2    tdata varchar2(8);
15:54:36   3  begin
15:54:36   4   select to_char(sysdate,'hh24:mi:ss') into tdata  from dual;
15:54:36   5   dbms_output.put_line(tdata);
15:54:36   6   dbms_lock.sleep(300);
15:54:36   7  end;
15:54:36   8  /
15:54:36

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:05:00.07
15:59:36 SQL> @06-script06.sql
15:59:36 SQL> create view v_1 as select * from t1;

View criada.

Decorrido: 00:00:00.07
15:59:36 SQL> @07-script07.sql
15:59:36 SQL> insert into t2 values (200);

1 linha criada.

Decorrido: 00:00:00.03
15:59:36 SQL> insert into t2 values (300);

1 linha criada.

Decorrido: 00:00:00.02
15:59:36 SQL> insert into t2 values (400);

1 linha criada.

Decorrido: 00:00:00.03
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.03
15:59:36 SQL> @08-script08.sql
15:59:36 SQL> delete from t2 where id=200;

1 linha deletada.

Decorrido: 00:00:00.03
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.05
15:59:36 SQL> @09-script09.sql
15:59:36 SQL> update t1 set id=id*10;

3 linhas atualizadas.

Decorrido: 00:00:00.06
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.04
15:59:36 SQL> 
15:59:36 SQL> begin
15:59:36   2   for i in 1..10 loop
15:59:36   3     insert into t4 select level id from dual connect by level <=10;
15:59:36   4     if mod(i, 2) = 0 then
15:59:36   5        commit;
15:59:36   6        dbms_output.put_line('commit');
15:59:36   7     end if;
15:59:36   8   end loop;
15:59:36   9   commit;
15:59:36  10  end;
15:59:36  11  /
commit
commit
commit
commit
commit

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:00.12
15:59:37 SQL> spool off


Durante a execução do Script Master, foi possível identificar o script 05-script05.sql sendo executado consultado a view V$SESSION como demonstrado abaixo:

C:\>sqlplus system/manager

SQL*Plus: Release 11.2.0.1.0 Production on Ter Jul 1 15:55:04 2014

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> SELECT sid,serial#,username,schemaname,
  2  osuser,program,module
  3  FROM V$SESSION
  4  WHERE schemaname='SCOTT';

    SID  SERIAL# USERNAME     SCHEMANAME      OSUSER    PROGRAM      MODULE
------- -------- ------------ --------------- --------- ------------ -------------------
     68    53805 SYSTEM       SCOTT           legatti   sqlplus.exe  02@ 05-script05.sql

1 linha selecionada.



4 comentários:

Marcelo Ribeiro disse...

Muito obrigado pelas explicações: Valiosíssimas para o dia a dia.
Sucesso amigo

Eduardo Legatti disse...

Olá Marcelo,

Obrigado pela visita!!

Abraços e até mais...

Legatti

Anônimo disse...

Boa tarde.
Estou com um problema parecido.
Preciso criar um batch que execute meus scripts.
Eles estarão organizados em pasta.
Table -> COntem todos osc ripts com relação a table.
Entretanto, dentro dessa pasta pode ter subpastas com os scripts.
Voce poderia me ajudar em como faço para automatizar isso?
A ideia é executar todos os scripts automaticamente, varrendo subpastas.

Eduardo Legatti disse...

Olá Anônimo,

Realmente acho que você deveria repensar a estrutura de diretórios dos scripts. Um diretório contendo scripts pra cada tabela realmente vai ser difícil. Não acredito que seja impossível, mas vai dar um pouco de trabalho. Você vai ter que que gerar um script_geral.sql de forma dinâmica que será usado pelo SQL*Plus. O desafio será fazer um shell script (Linux) ou um script Batch (Windows) e varrer todoas as pastas e inserir o caminho e os scripts no arquivo script_geral.sql.

Boa sorte!

Abraços,

Legatti

Postagens populares