Home > Oracle 12c Datenbank > SQL in PLSQL tracen

SQL in PLSQL tracen

SQL Verarbeitung in einer Stored Procedure nachvollziehen

Gerade bei komplexeren und umfangreichen Stored Proceduren ist es manchmal nicht so einfach nachzuvollziehen welche SQL Statements letztendlich ausgeführt werden. Natürlich kann man jetzt mit Debugging Techniken und Code lesen versuchen dies nachzuvollziehen, aber oft geht es nur darum festzustellen welche SQL Verarbeitungen bei einem bestimmten Aufruf ausgeführt werden.

Mit Oracle Native Auditing (aber Oracle 6) hat man hier keine Chance, da nur die Tatsache des Zugriffs auf ein Objekt auditiert wird, aber nicht irgendwelche Abhängigkeiten und Aufrufe.

Aber ab Oracle 12c sieht das anders aus - Unified Auditing mit Audit Policies können dafür genutzt werden!

Unified Auditing einschalten

Zuerst muß man sichergehen, daß Unified Auditing eingeschalten ist. Das kann man mit folgender Abfrage verifizieren:

select * from v$option where PARAMETER = 'Unified Auditing';

PARAMETER        VALUE     CON_ID
---------------- ----- ----------
Unified Auditing FALSE          0

In unserem Beispiel ist Unified Auditing noch nicht verfügbar - hier die Schritte, die Sie in diesem Fall machen müssen:

  • Alle Datenbanken aus diesem Oracle Home stoppen
  • Unified Auditing linken
    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk uniaud_on ioracle
  • Alle Datenbanken aus diesem Oracle Home wieder starten
  • Jetzt sollte die obige Query "TRUE" liefern

Jetzt einen kleinen Test bauen

Bitte beachten - der Test Benutzer darf kein DBA sein!

In unserem Beispiel nutzen wir den Benutzer Scott.

connect scott/tiger

create table DBM_dual (x number);
insert into DBM_dual values (1);
commit;

create or replace function dbm_test (zahl number) return number
is
  myresult number;
begin
  /* zuerst verifizieren ob DML funktionieren */
  delete from DBM_dual;
  insert into DBM_dual values (1);
  /* und dann noch ein SELECT mit einer Bindvariable */
  select zahl * 2 into myresult from DBM_dual;
  return myresult;
end;
/

Als nächstes die Audit Policy anlegen - dazu braucht man entsprechende Berechtigungen.

connect system/oracle

noaudit policy DBM_AUDIT;
drop audit policy DBM_AUDIT;

create audit policy DBM_AUDIT
   ACTIONS select on scott.dbm_dual,
           insert on scott.DBM_dual,
           update on scott.DBM_dual,
           delete on scott.DBM_dual,
           execute on scott.DBM_test;

audit policy DBM_AUDIT;

Jetzt können wir das ganze als Benutzer Scott verifizieren

connect scott/tiger

var myreturn number

begin
  :myreturn := dbm_test(2);
end;
/

print myreturn;

MYRETURN
--------
       4

Jetzt prüfen wir das Ergebnis. Um sicher zu stellen, daß die Audit Information schon auf der Disk steht, zuerst flushen.

connect system/oracle

EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

Jetzt finden wir innerhalb der gleichen SessionID alle Verarbeitungen und deren Reihenfolge, wenn man nach der ENTRY_ID sortiert. Somit haben wir den Stored Procedure Aufruf und die darin ausgeführten Statements.

select SESSIONID, DBUSERNAME, ENTRY_ID, STATEMENT_ID, OBJECT_SCHEMA, OBJECT_NAME, UNIFIED_AUDIT_POLICIES,
       SQL_TEXT, SQL_BINDS
  from unified_audit_trail
where UNIFIED_AUDIT_POLICIES like '%DBM_AUDIT%' /* es koennte ja auch noch eine weitere Policy anschlagen */
order by entry_id;

SESSIONID  DBUSER ENTRY STATEMENT OBJECT   OBJECT UNIFIED_AUDIT
             NAME    ID        ID SCHEMA     NAME      POLICIES SQL_TEXT                         SQL_BINDS
---------- ------ ----- --------- ------ -------- ------------- -------------------------------- ---------
854829528 SCOTT      1        11  SCOTT  DBM_TEST     DBM_AUDIT begin                            #1(0):
                                                                  :myreturn := dbm_test(2);
                                                                  end;
854829528 SCOTT      2        12  SCOTT  DBM_DUAL     DBM_AUDIT DELETE FROM DBM_DUAL
854829528 SCOTT      3        13  SCOTT  DBM_DUAL     DBM_AUDIT INSERT INTO DBM_DUAL VALUES (1)
854829528 SCOTT      4        14  SCOTT  DBM_DUAL     DBM_AUDIT SELECT :B1 * 2 FROM DBM_DUAL     #1(1):2

Alle Statements auf die Tabellen, die nicht zuerst mit dem Stored Aufruf beginnen, muß man jetzt noch ignorieren.

In der Spalte SQL_BINDS findet man den Inhalt der Bind Variablen.

Hinweis

Wenn man Unified Auditing aktiviert, muß man sich auch um das cleanup des Unified Audit Trails kümmern.