.comment-link {margin-left:.6em;}

Thursday, May 25, 2006

SQL Dinâmico

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:


Sunday, May 21, 2006

Trigger de DDL

Se houver a necessidade de personalizar sua auditoria, as triggers de DDL são uma opção. Imagine que o auditor necessite que uma procedure seja executada a cada novo usuário criado no banco. A solução que apresento é capturar o evento de create e testar se o tipo de objeto é 'user', então executar a procedure (log_user).


SQL> create table
2 audit_usr (
3 quem_criou varchar2(30),
4 quem_foi_criado varchar2(30),
5 quando date default sysdate
6 );

Table created.

SQL>
SQL> create or replace
2 procedure log_user(
3 p_criou in varchar2,
4 p_foi_criado in varchar2
5 )
6 is
7 begin
8 insert into audit_usr ( quem_criou, quem_foi_criado )
9 values ( p_criou, p_foi_criado );
10 end;
11 /

Procedure created.

SQL> show error
No errors.
SQL>
SQL> create or replace
2 trigger trg_ac_audituser
3 after create on database
4 begin
5 if ( ora_dict_obj_type = 'USER' )
6 then
7 log_user( ora_login_user, ora_dict_obj_name );
8 end if;
9 end;
10 /

Trigger created.

SQL> show error
No errors.
SQL> create user a identified by a;

User created.

SQL>
SQL> select * from audit_usr;

QUEM_CRIOU QUEM_FOI_CRIADO QUANDO
------------------------------ ------------------------------ -------------------
OPS$MARCIO A 21/05/2006 22:35:44

1 row selected.

Labels:


Tuesday, May 02, 2006

Sequencia de Fibonacci

Lancei um desafio semana passada em um forum onde participo. A idéia era escrever uma query em SQL puro (sem o uso de PL/SQL) que resultasse na sequência de Fibonacci.

Também havia dado uma dica: que eu consegui em 10g. Portanto usando uma nova feature da versão. A feature usada foi o MODEL. Vale a pena parar e estudar um pouco essa nova cláusula do SELECT a partir da versão 10g.

SQL> select s seq
2 from dual
3 model return all rows
4 dimension by ( 0 d ) measures ( 0 s )
5 rules iterate (12) (
6 s[iteration_number ] = decode(
7 iteration_number, 0, 0, 1, 1, s[iteration_number-2]
8 ) + nvl(s[iteration_number-1],0)
9 )
10 /

SEQ
-------------
0
1
1
2
3
5
8
13
21
34
55
89

12 rows selected.

Labels:


This page is powered by Blogger. Isn't yours?