Oracle Sequenz resetten Teil 2

Samstag, Oktober 27, 2018 by crypti

Im letzten Post habe ich beschrieben, mit welchem einfachen Befehl man Sequenzen auf 1 resetten kann.
Das ist in der Regel nur dann sinnvoll, wenn alle Tabellen eines Schema geleert wurden. Auf Basis der Erkenntnisse des Posts würde ich jetzt ein sinnvolleres Szenario betrachten.

Oftmals legt man nur bestimmte Testdaten ggf. über Sequenzen an und möchte nach Bereinigung der Testdaten dann die Sequenzen auf den Maximalwert der ID(+1) der entsprechenden Tabelle zu setzen.

Eine Art, ID’s und Sequenzen zu koppeln, ist über eine einfache “META”-Tabelle. Über ein kleines PL/SQL-Script kann dann die passende Sequenz aus der Tabelle ermittelt und z.B. über ein Trigger abgerufen werden.

Hier beschreibe ich mal, wie das geht.

Die META-Tabelle ist relativ einfach aufgebaut, besteht eigentlich nur aus dem Tabellen-Namen (TABLE_NAME) und der Sequenz (SEQUENCE_NAME). Sollten mehrere Spalten einer Tabelle gefüllt werden müssen, könnte noch eine Spalte COLUMN_NAME hinzugefügt werden, da in den meisten Fällen die Sequenzen nur für die ID-Generierung genutzt wird, nutze ich erst einmal die einfachere Variante.

Als erstes wird die Tabelle angelegt:

-- Erst mal die Tabelle anlegen
CREATE TABLE META_SEQUENCES(
   TABLE_NAME VARCHAR2(40) NOT NULL,
   SEQUENCE_NAME VARCHAR2(40) NOT NULL
)
/

-- ein unique Index auf die Tabelle - pro Tabelle soll es nur eine Sequenz geben
CREATE UNIQUE INDEX UC_META_TAB_IDX ON META_SEQUENCES(TABLE_NAME)
/

-- auch noch einen Unique-Index auf der Sequence anlegen, Sequenzen sollten auch nicht mehrfach verwendet werden

CREATE UNIQUE INDEX UC_META_SEQ_IDX ON META_SEQUENCES(SEQUENCE_NAME)
/

Nun legen wir eine kleine PL/SQL-Funktion an, die anhand des übergebenen Tabellen-Namens den nächsten Wert der Sequenz ermittelt.

CREATE OR REPLACE FUNCTION GET_SEQUENCE_BY_TABLENAME(V_TABLE_NAME VARCHAR2) RETURN NUMBER AS
  v_sequence_val number;
  v_sequence_name varchar2(40);
BEGIN
  SELECT sequence_name INTO v_sequence_name FROM META_SEQUENCES where TABLE_NAME=V_TABLE_NAME;
  execute immediate 'SELECT '||v_sequence_name||'.nextval FROM DUAL' INTO v_sequence_val;
  return v_sequence_val;
END;
/

Diese Methode kann dann z.B. in Triggern genutzt werden, um die nächste ID zu erzeugen.

Mit Hilfe der Tabelle und unserem Wissen aus dem letzten Post kann man nun noch eine Reset-Prozedur erstellen, der z.B. nach Testdaten-Löschung die Sequenz auf den letzten Wert resettet.

CREATE OR REPLACE PROCEDURE RESET_SEQUENCES AS
  Cursor v_cursor is select table_name, sequence_name from META_SEQUENCES;
  v_max_id number;
BEGIN
  for v_dataset in v_cursor loop
    --Abruf der letzten ID der entsprechenden Tabelle  
    execute immediate 'select nvl(max(id),0)+1 from '||v_dataset.table_name into v_max_id;
    -- Reset der Sequenz
    execute immediate 'ALTER SEQUENCE '||v_dataset.sequence_name||' RESTART START WITH '||v_max_id;
  end loop;
END;
/

Das war es schon. Werden jetzt Testdaten angelegt und später gelöscht, kann nach der Testdaten-Löschung diese Routine ausgeführt werden und bei allen Tabellen, die in der META_SEQUENCES gepflegt sind, werden dann die entsprechenden Sequenzen auf den Maximalwert+1 der letzten ID gesetzt.

Sequenz reset in Oracle

Dienstag, Oktober 23, 2018 by crypti

Speziell in Testsystemen kommt es öfter mal vor, dass Sequenzen irgendwann volllaufen und Oracle mit einem ORA-08004 abbricht.
Wenn es nackte Umgebungen sind, kann man aber relativ einfach die Sequenzen resetten, seit Oracle 11g gibt es folgende Möglichkeit, eine Sequenz zu resetten:

ALTER SEQUENCE sequenzname restart start with 1 

um die Sequenz “sequenzname” wieder von 1 zu starten.
Mit Hilfe einer PL/SQL-Routine kann man dann entsprechend ein Reset aller Sequences durchführen:

declare
    Cursor v_cur is select sequence_name from user_sequences;
begin
    for v_rec in v_cur loop
        execute immediate 'ALTER SEQUENCE '||v_rec.sequence_name||' RESTART START WITH 1';
    end loop;
end;
/

Easy oder?

Benennt man die Sequenzen nach einem bestimmten Muster oder pflegt Meta-Daten zur Beziehung zwischen Sequenzen und Tabellen-ID-Spalten lässt sich dann auch leicht die maximale ID ermitteln und die Sequenz entsprechend auf den max+1-Wert setzen.
Dazu aber in meinem nächsten Post.

USBTin Can-Adapter unter Linux zum Laufen bringen

Mittwoch, September 5, 2018 by crypti

Für die Analyse von CAN-Bus-Nachrichten hatte ich mir vor einiger Zeit einen USBTin-Can-Bus-Adapter geholt. Der war extrem günstig, auch wenn man quasi nur die Platine erhält, tut er doch genau das was er soll.

Da ich zwischen letztem Mal und heute schon alles komplett vergessen habe, wie ich den unter Linux zum Laufen bringe und das beim nächsten Mal nicht anders sein wird, hier ein Link zu einem entsprechenden Artikel, wie das geht:

https://www.fischl.d … /linux_can_socketcan