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

Tuesday, May 15, 2007

Criacao de Usuario Customizado (Duvida)


pessoal eu estou no oracle 10g e queria saber se existe a possibilidade de fazer o seguinte. Tenho uma tabela de usuário onde tem login e senha.. e gostaria de como ficaria a trigger após o insert para fazer um create user xxx identify by xxxx .... e dar o grant de create session to xxxx para o usuário do oracle...


E o exemplo completo.

ops$marcio:LX10G> connect / as sysdba
Connected.
sys:LX10G>
sys:LX10G> grant create user to ops$marcio;

Grant succeeded.

sys:LX10G> grant create session to ops$marcio with admin option;

Grant succeeded.

sys:LX10G>
sys:LX10G>
sys:LX10G> connect /
Connected.
ops$marcio:LX10G>
ops$marcio:LX10G> create table usuarios ( username varchar2(30), senha varchar2(30));

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace
2 procedure create_user (
3 p_username in usuarios.username%type,
4 p_senha in usuarios.senha%type
5 ) is
6 l_create long := 'create user '|| p_username ||' identified by '|| p_senha;
7 l_grant long := 'grant create session to '|| p_username;
8 begin
9 execute immediate( l_create );
10 execute immediate( l_grant );
11 end;
12 /

Procedure created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure run_create (
2 p_username in usuarios.username%type,
3 p_senha in usuarios.senha%type
4 ) is
5 pragma autonomous_transaction;
6 l_job number;
7 begin
8 dbms_job.submit( l_job, 'create_user( '''|| p_username ||''','''|| p_senha ||''');');
9 commit;
10 end;
11 /

Procedure created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace trigger tgr_aifer_usuarios
2 after insert on usuarios
3 for each row
4 begin
5 run_create( :new.username, :new.senha );
6 end;
7 /

Trigger created.

ops$marcio:LX10G>
ops$marcio:LX10G> show user
USER is "OPS$MARCIO"
ops$marcio:LX10G>
ops$marcio:LX10G> insert into usuarios values ('novo_usuario', 'senha01');

1 row created.

ops$marcio:LX10G>
ops$marcio:LX10G> exec dbms_lock.sleep(5)

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> select * from usuarios;

USERNAME SENHA
-------------------- ------------------------------
novo_usuario senha01

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> select username, password
2 from dba_users
3 where username = 'NOVO_USUARIO'
4 /

USERNAME PASSWORD
-------------------- ------------------------------
NOVO_USUARIO 25249E3F24F1A585

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> connect novo_usuario/senha01
Connected.
novo_usuario:LX10G>

Labels:


Sunday, May 13, 2007

Subquery 10g.

Há algum tempo, respondi uma dúvida sobre subquery que achei interessante reproduzir na integra. A dúvida a seguinte:
Eu gostaria de entender quais são as vantagens e desvantagens de usar uma subquery na cláusula select. Quando usar e quando não usar.
Interessante a pergunta. É bom quando temos que trabalhar um pouco para responder, assim aprendemos mais. Nesse caso, deu para perceber bem como o otimizador do Oracle está mais "inteligente" a cada nova versão. Explicando melhor, para essa dúvida, era simples a resposta (até a versão 10g) porque uma tabela enorme e outra menor quando juntadas (JOIN) em uma query principal, mesmo com índices (e a maioria dos ambientes não se atenta ao fato de que os parâmetros OPTIMIZER_INDEX_* vêm com um default não apropriado) o otimizador, faria o HASH JOIN, varrendo as duas tabelas. Isso apenas para iniciar a investigação, quando executei o exemplo abaixo no 10g, tive que ajustar a quantidade de linhas, setar o OPTIMIZER_MODE=ALL_ROWS e coletar as estatísticas com o CASCADE => false. É claro que isso varia de ambiente para ambiente, caso voce faça o teste, ele pode não representar o que foi o meu, mas serve de ínicio para investigação.

O setup do teste é o seguinte, uma tabela (t1) com muitos registros ~ (1 milhão no 10g e 700 mil no 9i) contendo object_id, object_name, created e status - bastante simples. A outra parte (t2), uma bem menor, com 3000 registros contendo object_id e created e essa tabela foi construída das 3000 primeiras linhas da primeira (t1). O propósito é o join entre ambas para recuperar o object_name na t2, então há duas queries, uma com join normal (onde eu esperava o HASH JOIN) e outra com subquery na clausula select onde eu esperava o NESTED LOOPING. Mas para minha surpresa, o 10g está muito melhor, basta ele deduzir (dynamic sampling) e a escolha é bem eficiente. Eis o setup

drop table t1;
drop table t2;

create table t1 as
select object_id, object_name, created, status
from dba_objects
/

col mx new_value mx

select max(object_id) mx from t1;

insert into t1
select &mx+rownum, object_name, created, status
from t1, (select null from dual connect by level <=20)
/

select count(*) from t1;
create index objidx on t1 ( object_id );
create table t2 as select object_id, created
from dba_objects where rownum <= 3000
/

begin
dbms_stats.gather_table_stats( user, 'T1', cascade => false );
dbms_stats.gather_table_stats( user, 'T2' );
end;
/

set autotrace traceonly
select t2.object_id, t2.created, t1.object_name
from t1, t2
where t1.object_id = t2.object_id
/

select t2.object_id, t2.created,
( select object_name
from t1
where t1.object_id = t2.object_id
) object_name
from t2
/
set autotrace off

Respondendo a dúvida: A desvantagem de se usar uma subquery é a "tentativa" de reorganizar a busca de forma que o Oracle faça menos esforço para trazer seu resultset. Por que eu disse "desvantagem"? Porque a vantagem mesmo é que, nas versões mais recentes, não é mais necessário lutar com isso, o código fica mais simples e limpo, basta o join e o otimizador decide o caminho.

Quando usar?
Eu diria que, com a versão 10g, raramente, já na 9i e em um ambiente default, sem customização, talvez há certa economia de esforço, portanto, escalabilidade no código. Mas é preciso entender o conceito e uma boa leitura de plano de execução.

Vamos ao resultado do teste.

9i (9.2.0.8)
============
ops$marcio:LX92> select t2.object_id, t2.created, t1.object_name
2 from t1, t2
3 where t1.object_id = t2.object_id
4 /

3000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=729 Card=3000 Bytes=123000)
1 0 HASH JOIN (Cost=729 Card=3000 Bytes=123000)
2 1 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=3000 Bytes=36000)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=419 Card=655473 Bytes=19008717)

ops$marcio:LX92> select t2.object_id, t2.created,
2 ( select object_name
3 from t1
4 where t1.object_id = t2.object_id
5 ) object_name
6 from t2
7 /

3000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3000 Bytes=36000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=29)
2 1 INDEX (RANGE SCAN) OF 'OBJIDX' (NON-UNIQUE) (Cost=1 Card=1)
3 0 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=3000 Bytes=36000)

Note o acesso pelo índice, ou seja, olhe sua seletividade, aqui são 3000/655473, para mim compensou.
E também pode ser notado no tkprof.

select t2.object_id, t2.created, t1.object_name
from t1, t2
where t1.object_id = t2.object_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 31 0.30 0.30 1185 4385 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.30 0.30 1185 4385 0 3000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
3000 HASH JOIN
3000 TABLE ACCESS FULL T2
655473 TABLE ACCESS FULL T1

Ele teve que ler 655 mil linhas juntamente com as 3000 para depois o HASH. Muito esforço.
Já com a subquery.

select t2.object_id, t2.created,
( select object_name
from t1
where t1.object_id = t2.object_id
) object_name
from t2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 31 0.05 0.06 44 9075 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.05 0.06 44 9075 0 3000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
3000 TABLE ACCESS BY INDEX ROWID T1
3000 INDEX RANGE SCAN OBJIDX (object id 33158)
3000 TABLE ACCESS FULL T2

Através do índice, ele evitou praticamente 652 mil linhas (grosso modo falando), mas o esforço foi bem menor. E para abreviar a resposta, que acho enorme, vou colocar os 2 tkprofs do 10g, basicamente é a mesma situação a GRANDE diferença é que eu tive que ralar para sair esse resultado ( até cascade => false) eu usei - e a quantidade de registros da t1 também teve que ser de 1 milhão e pouco.

select t2.object_id, t2.created, t1.object_name
from t1, t2
where t1.object_id = t2.object_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 31 0.59 0.59 0 7539 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.59 0.59 0 7539 0 3000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55

Rows Row Source Operation
------- ---------------------------------------------------
3000 HASH JOIN (cr=7539 pr=0 pw=0 time=94445 us)
3000 TABLE ACCESS FULL T2 (cr=11 pr=0 pw=0 time=21060 us)
1123458 TABLE ACCESS FULL T1 (cr=7528 pr=0 pw=0 time=6740811 us)

===

select t2.object_id, t2.created,
( select object_name
from t1
where t1.object_id = t2.object_id
) object_name
from t2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 31 0.23 0.23 0 9078 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.23 0.23 0 9078 0 3000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55

Rows Row Source Operation
------- ---------------------------------------------------
3000 TABLE ACCESS BY INDEX ROWID T1 (cr=9037 pr=0 pw=0 time=186784 us)
3000 INDEX RANGE SCAN OBJIDX (cr=6037 pr=0 pw=0 time=83942 us)(object id 63397)
3000 TABLE ACCESS FULL T2 (cr=41 pr=0 pw=0 time=21058 us)
Não é uma materia fácil de entendimento a priori, mas se ainda ficou dúvida, coloque comentário e, na medida do possível, esclareço.

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