Blog do Marcio Portes

Mais e mais nos dias de hoje, o DBA envolve-se com desenvolvimento. Assim, muitas tarefas manuais são enviadas ao nosso grupo - por exemplo: dar permissão para um usuário novo que vai desenvolver, portanto precisa de grant direto, explícito, não se pode usar roles. "Tá", mas qual o problema nisso? O esquema tem 4.540 tabelas. Problema? - Problemão!

Lancei mão do sql dinâmico para ajudar-me nestas tarefas. Dropar o esquema inteiro, dar grants, desabilitar constraints, enfim toda a necessidade que envolva muita intervenção manual o execute immediate entra em ação.

Vou demonstrar um pequeno exemplo de como estou utilizando esse tipo de técnica: desabilitar as triggers de todas as tabelas.

ops$marcio@WIN10GR2> select trigger_name, table_name, status
  2    from user_triggers
  3   where table_name is not null
  4  /

TRIGGER_NAME                   TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
T3_FER_BI                      T3                             ENABLED
T2_FER_BI                      T2                             ENABLED
T1_FER_BI                      T1                             ENABLED

3 rows selected.

ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> begin
  2          for x in ( select table_name
  3                       from user_triggers
  4                      where table_name is not null )
  5          loop
  6                  execute immediate 'alter table '|| x.table_name ||' disable all triggers';
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> select trigger_name, table_name, status
  2    from user_triggers
  3   where table_name is not null
  4  /

TRIGGER_NAME                   TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
T3_FER_BI                      T3                             DISABLED
T2_FER_BI                      T2                             DISABLED
T1_FER_BI                      T1                             DISABLED

3 rows selected.

ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> begin
  2          for x in ( select table_name
  3                       from user_triggers
  4                      where table_name is not null )
  5          loop
  6                  execute immediate 'alter table '|| x.table_name ||' enable all triggers';
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> select trigger_name, table_name, status
  2    from user_triggers
  3   where table_name is not null
  4  /

TRIGGER_NAME                   TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
T3_FER_BI                      T3                             ENABLED
T2_FER_BI                      T2                             ENABLED
T1_FER_BI                      T1                             ENABLED

3 rows selected.

Outro exemplo que uso muito é o script de drop_all. O mais rápido seria "drop user x cascade", mas como muitos de nossos esquemas têm algumas particularidades, optamos por dropar todos os objetos do esquema cada vez que haja necessidade de import.

O script:

declare
  l_stmt   long;
begin
  for x in ( select object_name, object_type
               from user_objects
              where instr(object_type, 'BODY') = 0
                and object_type not in ( 'INDEX', 'TRIGGER') )
  loop
     l_stmt := 'drop '|| x.object_type ||' "'|| x.object_name ||'"'||
               case x.object_type
                 when 'TABLE' then ' cascade constraint'
                 when 'TYPE'  then ' force'
                 else ' ' end ;
     dbms_output.put_line( l_stmt );
     execute immediate l_stmt;
  end loop;
end;
/

Testando...

ops$marcio@WIN10GR2> @dbls %

OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
PACKAGE      DEMO_PKG

PROCEDURE    P
             P1
             PRINT_TABLE

TABLE        EMP                              USERS
             T                                USERS
             T1                               USERS
             T2                               USERS
             T3                               USERS
             VOUCHER                          USERS

TRIGGER      AFTER_LOGON
             T1_FER_BI
             T2_FER_BI
             T3_FER_BI


14 rows selected.

ops$marcio@WIN10GR2> declare
  2    l_stmt   long;
  3  begin
  4    for x in ( select object_name, object_type
  5                 from user_objects
  6                where instr(object_type, 'BODY') = 0
  7                  and object_type not in ( 'INDEX', 'TRIGGER') )
  8    loop
  9       l_stmt := 'drop '|| x.object_type ||' "'|| x.object_name ||'"'||
 10                 case x.object_type
 11                   when 'TABLE' then ' cascade constraint'
 12                   when 'TYPE'  then ' force'
 13                   else ' ' end ;
 14       dbms_output.put_line( l_stmt );
 15       execute immediate l_stmt;
 16    end loop;
 17  end;
 18  /
drop TABLE "T1" cascade constraint
drop TABLE "T2" cascade constraint
drop TABLE "EMP" cascade constraint
drop TABLE "T3" cascade constraint
drop PROCEDURE "PRINT_TABLE"
drop TABLE "T" cascade constraint
drop PACKAGE "DEMO_PKG"
drop PROCEDURE "P1"
drop PROCEDURE "P"
drop TABLE "VOUCHER" cascade constraint

PL/SQL procedure successfully completed.

ops$marcio@WIN10GR2> @dbls %

OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        BIN$DEasi9mBTbmUOwpD1NJYqg==$0
             BIN$DZ5pzE2eTq+eM6VjyJvgEg==$0
             BIN$WeDsXoNZQBizqHrYi/uQRA==$0
             BIN$pSzyvcf+Q0O6LcXRQ1Mu9A==$0
             BIN$rB6EFXcHRXOCeDuESRmrQg==$0
             BIN$zuLLWDEnQSOZL7anbjxfqA==$0

TRIGGER      AFTER_LOGON
             BIN$Rohwa4IBRPSgprfmumcRJA==$0 *
             BIN$i7/bvs1VTMKesphXt4eRBg==$0 *
             BIN$ss3rGWcIQze68EsqaPf+tQ==$0 *


10 rows selected.

ops$marcio@WIN10GR2> purge recyclebin;

Recyclebin purged.

ops$marcio@WIN10GR2> @dbls %

no rows selected

ops$marcio@WIN10GR2>

Como estou usando o 10g, eu preciso fazer uma pequena melhoria, adicionando "purge" ao final de cada drop no script acima.

Labels: how to