IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration Oracle Discussion :

logging et triggers


Sujet :

Administration Oracle

  1. #1
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut logging et triggers
    Bonjour tout le monde ,

    actuellement j'ai une base de donness oracle sur laquelle je veux logger les select update et delete(DML). en utilisant la vue v$sql parfois je vois les statements(colonne sql_text) parfois non. j'ai bcp cherché (trcsess et tkprof aussi) mais je me suis la meilleur façon c'est d'ecrire des triggers qui insere le texte de l'instruction sql qui l'a ppelé ds une table. justement le probleme c'est comment je peux obtenir ce texte la à l'interieur du trigger ?? MErci de m'aider.


    merci

  2. #2
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    Tu ne peux pas par des triggers.
    Recherche AUDIT, tu devrais trouver des sujets déjà postés à ce sujet.

  3. #3
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut rebonjour
    j ai essayé avec audit mais sans succes . pouvez vous me donner un lien svp .

    MErci

  4. #4
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Il est possible avec Oracle 9i ou 10g de récupérer le code SQL de l'instruction qui déclenche un trigger grâce aux attributs des événements serveur qui sont aussi utilisables par les triggers INSERT, UPDATE et DELETE sur des tables:


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    dev> drop table tx;
     
    Table dropped.
     
    dev>
    dev> create table tx (x varchar2(100));
     
    Table created.
     
    dev>
    dev> create or replace trigger ttx
      2  after insert on tx
      3  for each row
      4  declare
      5  sql_text ora_name_list_t;
      6  v_stmt varchar2(2000);
      7  n integer;
      8  begin
      9  n := ora_sql_txt(sql_text);
     10  for i in 1..n loop
     11   v_stmt := v_stmt || sql_text(i);
     12  end loop;
     13  dbms_output.put_line('SQL: ' || v_stmt);
     14  end;
     15  /
     
    Trigger created.
     
    dev> show errors
    No errors.
    dev>
    dev> set serveroutput on
    dev>
    dev> insert into tx values ('Ca marche');
    SQL: insert into tx values ('Ca marche')
     
    1 row created.
     
    dev>
    dev>
    dev> exit
    Pour utiliser l'audit, voir le Security Guide.

  5. #5
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut merci
    merci bcp à vous tous.j utiliserai cette solution au cas ou l'audit ne marche pas.en fait, j'avais mis le audit_trail=true et apres j'ai mis : AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE by tata. tata est l'utilisateur avec lequel je me log. le problème c'est que quand je regarde la table sys.aud$ ,colonne sql_text je ne trouve rien du tout,c null en plus quand je fé un select sur une table en utilisateur ,je reviens vers sys.aud$ je ne vois aucune ligne de plus ui a été ajouté. est ce que vous avez une explication svp.

    Cordialement

  6. #6
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Quelle est la version d'Oracle utilisée ?

    audit_trail=true
    ne veut rien dire d'après Oracle® Database Reference 10g Release 2 (10.2)
    Quelle est la véritable valeur de ce paramètre ?

    Pour avoir le code SQL des instructions auditées, il faut avoir:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    audit_trail=db, extended

  7. #7
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut encore merci
    apparemment oracle n'a pa bien fiscelé tout ça :

    1-pour ce qui est de l'audit le db_extended marche à moitié, quand j'entre avec sqlpluus, mon premiere update par exemple est pris je le vois ds aud$, mais le deuxieme non....le reste d'apres est ignoré.

    2-pour ce qui est du trigger, voici l'erreur :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    SQL> CREATE OR REPLACE TRIGGER t_lata
      2    after INSERT or delete or update ON lata
      3      FOR each row
      4      declare
      5      sql_text ora_name_list_t;
      6      v_stmt varchar2(2000);
      7      n binary_integer;
      8      begin
      9       n := ora_sql_txt(sql_text);
     10     FOR i IN 1..n loop
     11      v_stmt := v_stmt || sql_text(i);
     12     end loop;
     13     -- dbms_output.put_line('SQL: ' || v_stmt);
     14     insert into asap.logging(sysevent,table__name  ,login_user ,event_date , statements )  value
    s(ora_sysevent,'lata'  ,ora_login_user ,sysdate , v_stmt )  ;
     15     end;
     16     /
     
    Trigger created.
     
    SQL> update lata
      2  set description= 'mmm mmm' ;
    update lata
           *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.T_LATA", line 7
    ORA-04088: error during execution of trigger 'SYS.T_LATA'
    Cordialement

  8. #8
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Vous n'avez toujours pas donné votre version d'Oracle ...: c'est pourtant une des règles d'utilisation de ce forum. Essayez aussi d'utiliser les balises pour le code.

    ORA-06512: at "SYS.T_LATA", line 7
    ORA-04088: error during execution of trigger 'SYS.T_LATA'
    Ce n'est peut-être pas la raison de l'erreur mais, en général, vous ne devriez pas utiliser le compte SYS pour créer des objets applicatifs (tables et trigger sur tables applicatives).

  9. #9
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut desole.
    c'est vrai. ma version d'oracle est la 10g release 10.2.0.3.0 . j'ai utilisé le compte sys aprés avoir utilisé un compte normal qui donnait la meme erreur, voici l'erreur :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    SQL>  CREATE OR REPLACE TRIGGER t_lata
      2    after INSERT or delete or update ON lata
      3      FOR each row
      4      declare
      5      sql_text ora_name_list_t := ora_name_list_t()  ;
      6      v_stmt varchar2(2000);
      7      n integer := 0;
      8      begin
      9       n := ora_sql_txt(sql_text) ;
     10     FOR i IN 1..n loop
     11      v_stmt := v_stmt || sql_text(i);
     12     end loop;
     13     -- dbms_output.put_line('SQL: ' || v_stmt);
     14     insert into asap.logging(sysevent,table__name  ,login_user ,event_date , statements )  value
    s(ora_sysevent,'lata'  ,ora_login_user ,sysdate , v_stmt )  ;
     15     end;
     16     /
     
    Trigger created.
     
    SQL> update lata 
      2  set description = 'rrrrrrrrrr' ;
    update lata
           *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "ASAP.T_LATA", line 7
    ORA-04088: error during execution of trigger 'ASAP.T_LATA'

  10. #10
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    T'as essayé sans ton insertion dans la table ?
    Moi ça marche bien en DBMS.

    Sinon :

  11. #11
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut tjrs meme erreur
    j ai enlevé le insert et j ai mis le dbms mais la meme erreur :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    SQL>  CREATE OR REPLACE TRIGGER t_lata
      2    after INSERT or delete or update ON lata
      3      FOR each row
      4      declare
      5      sql_text ora_name_list_t := ora_name_list_t()  ;
      6      v_stmt varchar2(2000) := ' ';
      7      n integer := 0;
      8      begin
      9       n := ora_sql_txt(sql_text) ;
     10     FOR i IN 1..n loop
     11      v_stmt := v_stmt || sql_text(i);
     12     end loop;
     13     dbms_output.put_line('SQL: ' || v_stmt);
     14    -- insert into asap.logging(sysevent,table__name  ,login_user ,event_date , statements )  val
    ues(ora_sysevent,'lata'  ,ora_login_user ,sysdate , v_stmt )  ;
     15     end;
     16     /
     
    Trigger created.
     
    SQL> update lata
      2  set description = 'iiiiiiiiiii' ;
    update lata
           *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "ASAP.T_LATA", line 7
    ORA-04088: error during execution of trigger 'ASAP.T_LATA'
    qu'est ce que vous avez comme conf et je n'ai pas..???
    comment je pourrai utilise ??

    et pour l'option audit , pourquoi cela ne marche pas correctement??

    ça m'enerve ...

    merci d'avance

  12. #12
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    Faut apprendre à débugguer un peu.
    Le n° de ligne que l'erreur te donne est celle à partir du DECLARE (il suffit de créer intentionellement une erreur d'affectation pour le vérifier).
    Donc ton pb c'est au niveau du FOR
    Donc un problème de n qui vaut NULL : ora_sql_text qui ne renvoie rien.

    Event Attribute Functions
    When the database fires a trigger, you can retrieve certain attributes about the event that fired the trigger. You can retrieve each attribute with a function call. Table 9-2 describes the system-defined event attributes.

    Note:
    To make these attributes available, you must first run the CATPROC.SQL script.
    The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.
    In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_.

  13. #13
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Il semble que ce soit aussi un bug de la 10.1 et la 10.2 où ora_sql_text retourne NULL d'après Metalink alors que ça marche en 9i

  14. #14
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    Lol, NAB (Not a Bug) :
    BUG N° 4932810
    See: . Bug 4230721 10.1.0.3.0 RDBMS 10.1.0.3.0 TRIGGERS PRODID-5 PORTID-23 4171597
    Abstract: ORA_SQL_TXT FUNCTION RETURNS NULL AFTER 10G UPGRADE . . from bug 4230721
    ---------------- .
    "This issue had surfaced in the dated labels for 9.2.0.7.0 patchset.
    After discussions it was concluded that it was the expected behaviour ie.ORA_SQL_TXT should return null when dml triggers are used.
    ORA_SQL_TXT is a "System defined event attribute" and is supposed to work only with "System triggers".
    This is also what the Documentation says - .
    Donc Désolé Pifor, mais faut plus l'utiliser pour du DML

  15. #15
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut re
    alors les gas . qu'est ce que je peux faire moi pour constituer mes logs ?? y'a t il d'autre fonctions ..??

  16. #16
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    De toute manière par trigger tu ne verras jamais les SELECT.

    Il te reste aussi l'outil Oracle STATSPACK.

  17. #17
    Nouveau membre du Club
    Inscrit en
    Mars 2007
    Messages
    8
    Détails du profil
    Informations forums :
    Inscription : Mars 2007
    Messages : 8
    Par défaut c moi
    si je peu voir les insert, les update et les delete ca sera bien. les select c po grave. est ce qu'il y'a moyen ...??

  18. #18
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    L'audit fonctionne mieux avec la 10g que les attributs d'évenements des triggers pour le DML (y compris pour les SELECT). Pour avoir une trace de chaque requête, il faut utiliser la clause BY ACCESS de la commande AUDIT. Exemple avec la 10.2.0.1:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
     
    SQL> select * from v$version;
     
    BANNER                                                                          
    ----------------------------------------------------------------                
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                
    PL/SQL Release 10.2.0.1.0 - Production                                          
    CORE    10.2.0.1.0      Production                                                      
    TNS for Linux: Version 10.2.0.1.0 - Production                                  
    NLSRTL Version 10.2.0.1.0 - Production                                          
     
    SQL> drop user test cascade;
     
    User dropped.
     
    SQL> --
    SQL> show parameter audit_trail;
     
    NAME                                 TYPE        VALUE                          
    ------------------------------------ ----------- ------------------------------ 
    audit_trail                          string      DB_EXTENDED                    
    SQL> create user test default tablespace users identified by test007;
     
    User created.
     
    SQL> alter user test quota unlimited on users;
     
    User altered.
     
    SQL> grant connect, resource to test;
     
    Grant succeeded.
     
    SQL> audit select table, update table, insert table, delete table by access;
     
    Audit succeeded.
     
    SQL> --
    SQL> connect test/test007;
    Connected.
    SQL> create table t(x number, y varchar2(30));
     
    Table created.
     
    SQL> insert into t(x, y) values (1, 'ok');
     
    1 row created.
     
    SQL> host sleep 1
     
    SQL> update t set y='OK' where x = 1;
     
    1 row updated.
     
    SQL> host sleep 2
     
    SQL> delete t where x=1;
     
    1 row deleted.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> connect /
    Connected.
    SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
     
    Session altered.
     
    SQL> noaudit select table, update table, insert table, delete table;
     
    Noaudit succeeded.
     
    SQL> select timestamp, sql_text from dba_audit_trail where username='TEST' order by timestamp;
     
    TIMESTAMP                                                                       
    --------------------                                                            
    SQL_TEXT                                                                        
    --------------------------------------------------------------------------------
    20-MAR-2007 21:28:31                                                            
    SELECT USER FROM DUAL                                                           
     
    20-MAR-2007 21:28:31                                                            
    SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
    RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
     
     
    20-MAR-2007 21:28:31                                                            
     
    TIMESTAMP                                                                       
    --------------------                                                            
    SQL_TEXT                                                                        
    --------------------------------------------------------------------------------
    SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
    RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
     
     
    20-MAR-2007 21:28:31                                                            
    SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE
    R(PRODUCT)) AND   ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPE
    R(ATTRIBUTE) = 'ROLES')                                                         
     
     
    TIMESTAMP                                                                       
    --------------------                                                            
    SQL_TEXT                                                                        
    --------------------------------------------------------------------------------
    20-MAR-2007 21:28:31                                                            
    SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE
    R(PRODUCT)) AND   ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPE
    R(ATTRIBUTE) = 'ROLES')                                                         
     
    20-MAR-2007 21:28:31                                                            
    insert into t(x, y) values (1, 'ok')                                            
     
    20-MAR-2007 21:28:31                                                            
     
    TIMESTAMP                                                                       
    --------------------                                                            
    SQL_TEXT                                                                        
    --------------------------------------------------------------------------------
    SELECT DECODE('A','A','1','2') FROM DUAL                                        
     
    20-MAR-2007 21:28:32                                                            
    update t set y='OK' where x = 1                                                 
     
    20-MAR-2007 21:28:34                                                            
    delete t where x=1                                                              
     
     
    9 rows selected.

Discussions similaires

  1. Trigger SQL Log Update d'une colonne
    Par lenny32 dans le forum Développement
    Réponses: 4
    Dernier message: 13/08/2012, 13h55
  2. Trigger et log différent
    Par lenoil dans le forum PL/SQL
    Réponses: 1
    Dernier message: 10/03/2011, 21h26
  3. trigger, erreur et log
    Par MuadDib_CH dans le forum Développement
    Réponses: 22
    Dernier message: 18/09/2010, 10h25
  4. Trigger sur table de log pour statistiques
    Par kalagann dans le forum MySQL
    Réponses: 1
    Dernier message: 26/11/2009, 22h09
  5. 10g/datapump/export schema et trigger log on/off
    Par URIOS dans le forum Import/Export
    Réponses: 7
    Dernier message: 03/04/2009, 18h14

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo