Mehrere Tabellen in einer Transaktion erstellen

Der SQL-Standard unterscheidet Data Description Language (DDL) zur Erstellung und Änderung von Datenbankobjekten und Data Manipulation Language (DML) zur Bearbeitung von Dateninhalten. Eine Oracle Datenbank verarbeitet DML immer transaktional, d.h. die vorgenommen Änderungen werden also erst für alle sichtbar, wenn die Transaktion commited wird. Das Ausführen von DDL Statements hingegen passiert immer in einer eigenen in sich geschlossenen Transaktion. Das bedeutet z.B., dass vor der Ausführung eines DDL Statements implizit ein "Commit" erfolgt. Weiterhin bedeutet es, dass  man mehrere zusammenhängende DDLs nicht gemeinsam zurückrollen kann. Es gibt jedoch eine wenig bekannte Ausnahme, das "CREATE SCHEMA" Statement. 

Mit dem Befehl CREATE SCHEMA können Tabellen, Views und Grants in einem einzigen Statement angelegt werden. Schlägt der Aufruf fehl, wird entsprechend keine Objekte angelegt, egal an welcher Stelle der Aufruf gescheitert ist. Leider lassen sich mit dieser Methode keine Indizes anlegen und auch Änderungen an Objekten sind nicht möglich. Und obwohl das Statement impliziert, dass ein Schema angelegt wird, ist das auch nicht ganz korrekt. In der Oracle-Welt ist ein Schema im Grunde eine Datenbankbenutzer, der Obejkte besitzt. Voraussetzung ist daher, dass der gewünschte Schema-Nutzer bereits existiert und er die erforderlichen Rechte zum Erstellen von Tabellen und Views besitzt.

Schauen wir uns ein einfaches Beispiel an:

SQL> create schema authorization myuser
  2    create table departments (
  3       id         number        not null
  4      ,dep_name   varchar2(100) not null
  5      ,constraint pk_dep primary key (id) using index tablespace MYTABLESPACE
  6    )
  7    create table employees (
  8       id         number         not null
  9      ,first_name varchar2(100)  not null
 10      ,last_name  varchar2(100)  not null
 11      ,dep_id     number
 12      ,constraint pk_emp primary key (id) using index tablespace MYTABLESPACE
 13      ,constraint fk_emp_dep foreign key (dep_id) references departments(id)
 14    )
 15    create view v_emp_dep
 16      as select e.first_name, e.last_name, d.dep_name
 17         from employees e
 18         join departments d on (e.dep_id = d.id)
 19    grant select on v_emp_dep to public
 20* ;

Schema AUTHORIZATION erstellt.

Danach sind folgerichtig alle Objekte vorhanden. Wir prüfen das exemplarisch anhand einer Tabelle und einer View: 

SQL> desc employees
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 FIRST_NAME                                NOT NULL VARCHAR2(100)
 LAST_NAME                                 NOT NULL VARCHAR2(100)
 DEP_ID                                             NUMBER

SQL> desc v_emp_dep
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 FIRST_NAME                                NOT NULL VARCHAR2(100)
 LAST_NAME                                 NOT NULL VARCHAR2(100)
 DEP_NAME                                  NOT NULL VARCHAR2(100)

Was würde aber nun im Falle eines Fehler passieren? Dazu werden die soeben angelegten Objekte gelöscht und das Statement erneut ausgeführt, diesmal jedoch mit einem Fehler.

SQL> create schema authorization MYUSER
  2    create table departments (
  3       id         number        not null
  4      ,dep_name   varchar2(100) not null
  5      ,constraint pk_dep primary key (id) using index tablespace MYTABLESPACE
  6    )
  7    create table employees (
  8       id         number         not null
  9      ,first_name varchar2(100)  not null
 10      ,last_name  varchar2(100)  not null
 11      ,dep_id     number
 12      ,constraint pk_emp primary key (id) using index tablespace MYTABLESPACE
 13      ,constraint fk_emp_dep foreign key (dep_id) references departments(id)
 14    )
 15    create view v_emp_dep
 16      as select e.first_name, e.last_name, d.dep_name
 17         from employees e
 18         join department d on (e.dep_id = d.id)
 19    grant select on v_emp_dep to public
 20* ;

create schema authorization MYUSER
*
FEHLER in Zeile 1:
ORA-02427: Erstellen von View nicht erfolgreich

SQL> desc employees
ERROR:
ORA-04043: Objekt employees ist nicht vorhanden

Man sieht aus diesem Beispiel zwei Dinge. Zum einen sieht man, dass keine der Tabellen angelegt wurde, obwohl der Fehler erst beim Anlegen der View aufgetreten ist. Zum anderen sieht man, dass eine Fehlersuche nicht ganz einfach sein kann, denn angeblich liegt der Fehler in Zeile 1, betrifft aber das Anlegen der View. Stellt man sich nun ein komplexeres Statement vor mit vielen Tabellen und Views, kann es schnell unübersichtlich werden.

Dass mithilfe des CREATE SCHEMA keine Indexe angelegt werden können, erschließt sich aus dem Syntaxdiagramm in der Dokumentation. So ganz korrekt ist das nicht, denn innerhalb eines CREATE TABLE als Detail eines Primary Key Constraints ist das sehr wohl möglich. Und manchmal verschweigt die Dokumentation auch Dinge. Also hilft nur ausprobieren.

SQL> create schema authorization MYUSER
  2    create table departments (
  3       id         number        not null
  4      ,dep_name   varchar2(100) not null
  5      ,constraint pk_dep primary key (id) using index tablespace MYTABLESPACE
  6    )
  7    create table employees (
  8       id         number         not null
  9      ,first_name varchar2(100)  not null
 10      ,last_name  varchar2(100)  not null
 11      ,dep_id     number
 12      ,constraint pk_emp primary key (id) using index tablespace MYTABLESPACE
 13      ,constraint fk_emp_dep foreign key (dep_id) references departments(id)
 14    )
 15    create view v_emp_dep
 16      as select e.first_name, e.last_name, d.dep_name
 17         from employees e
 18         join departments d on (e.dep_id = d.id)
 19    grant select on v_emp_dep to public
 20    create index ix_emp_name on employees(last_name) tablespace MYTABLESPACE
 21  ;
  create index ix_emp_name on employees(last_name) tablespace MYTABLESPACE
         *
FEHLER in Zeile 20:
ORA-02422: fehlendes oder ungültiges Schema-Element

Leider nein, Indizes lassen sich tatsächlich nicht allgemein im Rahmen eines CREATE SCHEMA anlegen. Dafür ist die Fehlermeldung an dieser Stelle aussagekräftiger und verrät die Zeile mit dem Fehler. 

In Summe ist das CREATE SCHEMA trotzdem eine durchaus sinnvolle Möglichkeit um Objekte innerhalb einer Transaktion zu erstellen, nach dem Motto: Alles oder nichts. Alternativ dazu wird es dann in der Version 23c die Möglichkeit geben, Objekte nur anzulegen oder zu ändern, wenn diese existieren oder eben noch nicht existieren. Details dazu finden sich in einem anderen Blogeintrag.