Tablespace-Belegungshistorie

Wer wissen möchte, wie sich seine Tablespaces in den letzten Wochen speicherplatzmäßig entwickelt haben, kann dies ganz einfach durch ein einfaches Query herausfinden, ohne dazu teure Zusatztools bemühen zu müssen:

select ts.name „Tablespace“,
vor_4_wochen.full „FULL%_4_WEEKS_AGO“,
letzte_woche.full „FULL%_LAST_WEEK“,
heute.full „FULL%_TODAY“
from (select tsu.tablespace_id,
round(
tsu.tablespace_usedsize/tsu.tablespace_maxsize*100,0) as
full
from wrh$_tablespace_space_usage tsu
where to_date(substr(tsu.rtime,1,13),’MM/DD/YYYY
HH24′)=to_date(to_char(trunc(sysdate),’MM/DD/YYYY
HH24′),’MM/DD/YYYY HH24′)
) heute,
(select tsu.tablespace_id,
round(
tsu.tablespace_usedsize/tsu.tablespace_maxsize*100,0) as
full
from wrh$_tablespace_space_usage tsu
where to_date(substr(tsu.rtime,1,13),’MM/DD/YYYY
HH24′)=to_date(to_char(trunc(sysdate-7),’MM/DD/YYYY
HH24′),’MM/DD/YYYY HH24′)
) letzte_woche,
(select tsu.tablespace_id,
round(
tsu.tablespace_usedsize/tsu.tablespace_maxsize*100,0) as
full
from wrh$_tablespace_space_usage tsu
where to_date(substr(tsu.rtime,1,13),’MM/DD/YYYY
HH24′)=to_date(to_char(trunc(sysdate-28),’MM/DD/YYYY
HH24′),’MM/DD/YYYY HH24′)
) vor_4_wochen,
v$tablespace ts
where ts.ts# = heute.tablespace_id
and ts.ts# = letzte_woche.tablespace_id
and ts.ts# = vor_4_wochen.tablespace_id
order by 4 desc;

Wichtig ist, dass die Datenhaltungszeit der ADDM-Statistiken > 28 Tage eingestellt ist und dass die Statistiken stündlich (alle 60 Minuten) gesammelt werden. Ansonsten liefert das Script eine leere Menge zurück.

E-Mail-Versand in Triggern

Aus dem Sekretariat kam folgende Anforderung: ändert sich die Spalte einer bestimmten Tabelle auf einen bestimmten Wert, soll eine E-Mail mit Informationen dieser Transaktion versendet werden.

Die Herangehensweise ist klar: ein On-Update-Trigger ruft UTL_MAIL (oder UTL_SMTP vor 10g) auf und versendet die E-Mail. Dieser Lösungsweg ist aber nur dann akzeptabel, wenn sichergestellt ist, dass der Benutzer diese Transaktion nicht mehr rückgängig machen kann, z.B. wenn die Applikation kein Rollback zulässt.

Wenn nämlich ein Rollback erfolgt, hat die Spalte wieder ihren ursprünglichen Wert, der Versand der E-Mail ist allerdings bereits erfolgt und kann nicht mehr rückgängig gemacht werden.

Tom „AskTom“ Kyte beschreibt im Oracle Magazine und in seinem Blog eine Vorgehensweise, die dieses Verhalten verhindert:

Die Idee ist, dass man statt des E-Mail-Versandes einen Eintrag in die Queue von DMBS_JOBS vornimmt, die sich um den Versand der Post kümmert. Der Insert in die Job-Queue ist ja auch eine Transaktion, wird also nur vorgenommen, wenn die ursprüngliche Transaktion (die, die den Trigger auslöst) commited wird. Im Detail funktioniert das so:

SQL> create table do_ddl (job number primary key, stmt varchar2(4000));
Table created.

Diese Tabelle beinhaltet die Jobnummern und die Statements, die im jeweiligen Job ausgeführt werden sollen. In unserem Fall z.B. UTL_MAIL.SEND (…).

Anschließend liegen wir eine Stored Procedure an, die die Jobtabelle ausliest und die dort hinterlegten Statements ausführt:

SQL> create or replace procedure do_ddl_safely (p_job in number) is
2   l_rec do_ddl%rowtype;
3   begin
4     select * into l_rec from do_ddl where job = p_job;
5     execute immediate l_rec.stmt;
6   end;
7   /
Procedure created.

Im Trigger schließlich wird der Job in der Queue angelegt:

SQL> declare
2   l_job number;
3   begin
4     dbms_job.submit (l_job, 'do_ddl_safely (JOB);' );
5     insert into do_ddl (job, stmt) values (l_job, 'begin utl_mail.send (...) end;');
6   end;
7   /

Was passiert also?

1. Der Trigger legt einen Job an und schreibt das Statement mit der Jobnummer in die Tabelle DO_DDL.
2. Da für die ursprüngliche Transaktion noch kein Commit vorliegt, wird weder das DBMS_JOB.SUBMIT noch der Insert in die Tabelle wirklich ausgeführt.
3. Beim Rollback wird die ursprüngliche Transaktion (das Ändern des Spaltenwertes) sowie Schritt 1 rückgängig gemacht
   oder
   beim Commit wird die Änderung in der Spalte festgeschrieben, der Job wird erstellt und die Tabelle DO_DDL befüllt.
4. Der Job wird kurzzeitig nach dem Commit ausgeführt und die E-Mail wird versendet.

Diese Vorgehensweise ist natürlich nicht nur auf den Versand von E-Mails beschränkt, sondern eignet sich hervorragend, um ganz allgemein DDL in Triggern auszuführen.

Denn wie schreibt Mr. Kyte so schön:

Whenever you are tempted to do something nontransactional in a trigger, think 500 times more about it and then always decide againt it. It can lead only to really bad things.