Problème d'accès concurrent ? Trigger after logon database

[Moostiq]

Bonjour; voici le code mon trigger en question :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
CREATE OR REPLACE
TRIGGER TRG_LOG_ON 
AFTER LOGON ON DATABASE
DECLARE
	v_new_passwd VARCHAR2(30):='';
	v_username sys.v_$session.USERNAME%TYPE;
	v_date_connexion sys.v_$session.LOGON_TIME%TYPE;
	Err_username EXCEPTION;
  Err_date_cnx EXCEPTION;
  Err_new_passwd EXCEPTION;
	L_Requete varchar2(250);
  job_name varchar2(64);
  v_ddl_cmd varchar2(4000);
BEGIN
  IF REGEXP_LIKE(USER,'^USER[0-9]{3}$') AND sys_context('USERENV', 'BG_JOB_ID') IS NULL THEN
 
    L_Requete:='SELECT distinct USERNAME, logon_time FROM sys.v_$session 
    where logon_time=(
    select min(logon_time) 
    from sys.v_$session 
    where REGEXP_LIKE (USERNAME, ''^USER[0-9]{3}$''))';
    Execute immediate L_Requete INTO v_username ,v_date_connexion;  
    IF v_username IS NULL then
      raise Err_username;
    end IF;
    IF v_date_connexion IS NULL then 
      raise Err_date_cnx;
    end IF;
 
    v_new_passwd:= PKG_OTP.CreateRandomPassword();
    IF v_new_passwd IS NULL then 
      raise Err_new_passwd;
    end IF;
 
    L_Requete:='UPDATE UTILISATEUR_ORA SET UTO_PWD_GPL=:a, UTO_DATE_STATUT=:b, UTO_STA=:c WHERE UTO_USR_GPL=:d';
    Execute immediate L_Requete USING v_new_passwd, v_date_connexion, 'LIBRE', v_username;
    COMMIT; --Liberation d'une place dans le pool 
    dbms_output.put_line('TRG_LOG_ON : Liberation d une place dans le pool (COMMITED)');
 
    v_ddl_cmd:='ALTER USER '||v_username||' IDENTIFIED BY '||v_new_passwd||'';
    job_name:='trg_logon_'||to_char(current_timestamp, 'hh24missff');
  /* Synchrone */  
   sys.DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  job_name,
   job_type           =>  'PLSQL_BLOCK',
   job_action         =>  'BEGIN PKG_OTP.execute_alter_user(''' || v_ddl_cmd || '''); END;',
   start_date         =>  NULL,
   repeat_interval    =>  NULL,
   auto_drop          =>  TRUE,
   enabled            =>  TRUE,
   comments           =>  'trg_log_on job');
   sys.dbms_scheduler.set_attribute(job_name,'job_priority',1);  --priorité maximale = 1
   sys.DBMS_SCHEDULER.RUN_JOB (
   job_name,
   TRUE);-- use_current_session TRUE => synchrone 
 
  END IF;
EXCEPTION
	WHEN Err_username THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La lecture de USERNAME '||v_username||' a echoue',' ','TRG_LOG_ON');
	WHEN Err_date_cnx THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La lecture de logon_time '||v_date_connexion ||' a echoue',' ','TRG_LOG_ON');
  WHEN Err_new_passwd THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La regeneration de New passwd '||v_new_passwd ||' a echoue',' ','TRG_LOG_ON');
  WHEN OTHERS THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(' TRG_LOG_ON OTHERS :'|| Sqlerrm , 1, 200),'Erreur OTHERS  '||v_username||' ??? new_passwd:*'||v_new_passwd||'*',' ','TRG_LOG_ON');
END TRG_LOG_ON;

Son comportement doit etre celui - ci :
1 - un USERXXX se connecte à la database
2 - le trigger va chercher son nom et sa date de cnx dans la vue v_$session
3 - on crée un mot de passe aléatoire unique pour se user
4 - on update ce nouveau mot de passe, user, date_cnx, le statut du user (LIBRE, CONNECTE, RESERVE, etc...) etc... dans la table UTILISATEUR_ORA
5 - on COMMIT
6 - on crée un job (auto_dropper à priori et de priorité maximale =1) afin d'effectuer l'ALTER USER USERXXX IDENTIFIED BY nouveau_motdepasse
7 - FIN

Or lorsque je teste le trigger via un loop infini de connections de USERXXX, et une table UTILISATEUR_ORA de 2 enregistrements, j'arrive (au bout d'un laps de temps) soit :
1 - les 2 tuples de UTILISATEUR_ORA sont tous à 'RESERVE', et donc autoblocage : cela arrive lorsque deux tuples différents sont réservés en même temps par deux users USERXXX différent [ce qui est normal!];
2 - l'un des 2 tuples est bloqué à 'RESERVE', l'autre tuple est donc utilisé alternativement par les deux USERXXX (passe de l'etat 'LIBRE' à 'RESERVE' et inversement) et tout se passe bien .

Donc à un moment de l'UPDATE de la table UTILISATEUR_ORA, il doit y avoir un autre UPDATE en cours pour que l’état reste à 'RESERVE' ? ou je ne sais pas ce qui se passe ?

Le meilleur des comportements serait qu'un tuple d'UTILISATEUR_ORA reste locké même si user vient juste de se connecter. Comment faire faire cela à mon trigger ?

Merci pour votre aide