Oracle 23c Preview: DDL und Administration

Safe Harbor Statement: alle in diesem Artikel enthalten Informationen stammen aus Oracle Vorträgen von der Oracle OpenWorld 2022 in Las Vegas. Da die Entwicklung der Oracle Datenbank 23c noch nicht abgeschlossen ist, können sich Funktionalitäten ändern oder ganz gestrichen werden.

Hinweis: für einige Funktionalitäten ist die Voraussetzung, dass COMPATIBLE >= 23.0.0 gesetzt ist.

Schema-Level Privilegien

Will man einem Benutzer Leserechte auf alle Tabellen eines Schemas erteilen, gab es bis jetzt nur zwei Möglichkeiten:

Man hat dem Benutzer zu viele Rechte eingeräumt: GRANT SELECT ANY TABLE TO HR;
Oder man hat viele: GRANT SELECT ON SH.xxx TO HR; Befehle absetzen müssen, die man sich am Besten zuvor generiert hat.

Ab Oracle 23c wird das deutlich einfacher.

GRANT SELECT ANY TABLE ON SCHEMA PROD TO HR;

Das soll mit allen „ANY“ System-Privilegien funktionieren, beispielsweise mit EXECUTE ANY PROCEDURE, INSERT/UPDATE/DELETE ANY TABLE, etc.

Package: DBMS_DEVELOPER_ADMIN

Damit Entwickler arbeiten können, brauchen sie oft eine Reihe von System Privilegien sowie Object Privilegien. Ab Oracle 23c kann man ein von Oracle vordefiniertes SET an Privilegien mit einem Befehl granten.

exec DBMS_DEVELOPER_ADMIN.GRANT_PRIVS('Dev_Username');

Der Benutzer Dev_Username enthält:

viele CREATE Privilegien – beispielsweise ADMINISTER SQL TUNING SET, DEBUG CONNECT SESSION, ON COMMIT REFRESH, etc.
einige SELECT GRANTs auf V$Views (V$SQL, V$SQL_PLAN, V$SESSTAT,…) sowie DBA_PENDING_TRANSACTIONS.

Damit sollten die meisten Entwickler das Auslangen finden, auch wenn die eine oder andere zusätzliche V$VIEW aus meiner Sicht noch fehlt – beispielsweise: V$SQLAREA, V$SESSION.

DDL: IF [NOT] EXISTS

Nichts ist mühsamer als Fehlermeldungen, nur weil ein Objekt vorhanden oder nicht vorhanden ist. Das macht Deployments von Scripts auf mehreren Datenbanken zur Herausforderung.

CREATE TABLE foo (x NUMBER);
ORA-00955: name is already used by an existing object

CREATE TABLE IF NOT EXISTS foo (X NUMBER);
Table created.

Auch wenn die Tabelle bereits existiert, kommt „Table created.“ und somit keine Fehlermeldung. Das gleiche gilt auch bei DROP:

DROP TABLE foo;
ORA-00942: table or view does not exist

DROP TABLE IF EXISTS foo;
Table dropped.

Error Messages liefern sinnvolle Informationen

Als DBA und Entwickler ärgert man sich oft über grundsätzlich korrekte Fehlermeldungen, die nicht besonders hilfreich sind. Oracle 23c macht es deutlich besser – hier einige Beispiele:

Statt ORA-00979: not a GROUP BY expression
kommt ORA-00979: "SALES"."STATE": does not match a GROUP BY expression

statt ORA-01722: invalid number
kommt ORA-01722: unable to convert value 'abc' to a number

statt ORA-00942: table or view does not exist
kommt ORA-00942: table or view ("SYSTEM"."FOO") does not exist

statt ORA-12514: TNS Listener does not currently know of service requested in connect descriptor
kommt ORA-12514: Cannot connect to database. Service TYPO_SERVICE_NAME is not registered with the listener at host 10.10.10.10 port 1521.

Tabellen und Views mit 4096 Spalten

Aktuell unterstützt Oracle für Tabellen und Views maximal 1000 Spalten. Das ist für die bei ML (Machine Learning) und anderen Aufgabenstellungen in der Datenanalyse potentiell zu wenig. Mit Oracle 23c werden Tabellen und Views bis zu 4096 Spalten enthalten können.

ALTER SYSTEM SET MAX_COLUMNS=EXTENDED;

SQL Domains – Erweiterte Datenintegrität und Behandlung in Sorts

Mit SQL Domains kann man für bestimmte Inhalte wie eMail- und Web-Adressen, Kreditkartennummern, Postleitzahlen, Telefonnummern usw. definieren, welche Eigenschaften diese haben müssen und wie sie sortiert werden sollen.

Nehmen wir eine eMail Adresse als Beispiel:

CREATE DOMAIN email AS VARCHAR2(100)
       CONSTRAINT eMail_check CHECK (REGEXP_LIKE (email,'^(\S+)\@(\S+).(\S+)$))
       DISPLAY SUBSTR(email,1,1) || '***' SUBSTR(email,INSTR (email,'@')-1)
       ORDER   SUBSTR(email, INSTR(email,'@')+1) || '/' ||  SUBSTR(email,1, INSTR(email,'@'));

Schauen wir uns die einzelnen Teile an:

AS VARCHAR2(100) definiert den Datentype und die Mindestlänge, die man einhalten muss.
Der CONSTRAINT prüft, ob es sich um eine gültige eMail Adresse handeln kann: es gibt Zeichen vor einem ‚@‘ und nach dem ‚@‘ muss zumindest ein ‚.‘ vorhanden sein.
Mit DISPLAY wird festgelegt, wie die Dateninhalte angezeigt werden sollen. In diesem Beispiel das erste Zeichen und das letzte Zeichen der eMail Adresse gefolgt vom „@DNS-Domain“.
Im ORDER wird die Sortierreihenfolge für eMail Adressen festgelegt. Diese werden zuerst nach der DNS-Domain und erst danach nach dem Namen sortiert.

Implementierungsbeispiel der SQL Domain eMail in einer Tabelle

CREATE TABLE Kunde 
( KundenID    NUMBER        NOT NULL PRIMARY KEY,
  Name        VARCHAR2(64)  NOT NULL,
  eMail       VARCHAR2(200) DOMAIN eMail NOT NULL
);

In dem Beispiel sehen wir, dass für die Spalte eMail VARCHAR2(200) gewählt wurde. Da die angegebene Länge größer als die in der SQL Domain eMail geforderten VARCHAR2(100) ist, ist alles OK.

Nutzung/Auswirkung der SQL Domain

INSERT INTO Kunde (1, 'Test', 'blabla');
ORA-02290: check Constraint (EMAIL_CHECK) violated

SELECT name, DOMAIN_DISPLAY(eMail) AS email  
  FROM Kunde 
 ORDER BY DOMAIN_ORDER(eMail)

NAME                eMail
------------------- ---------------------
Xaver Blei          x***i@aaa.com
Christian Pfundtner c***r@dbmasters.at
Martin Eile         m***e@oracle.com

Mit DOMAIN_DISPLAY() sorgt man dafür, dass die DISPLAY Definition auf der SQL Domain für die Datenanzeige genutzt wird. Durch DOMAIN_ORDER() wird die Sortierreihenfolge wunschgerecht umgesetzt – zuerst nach der Domain und erst danach nach dem Namen der eMail Adresse.

ANNOTATIONS

Annotations sind zusätzliche Metadaten für Daten und Daten Modelle auf Tabellen, Views, Spalten, Indizes usw. Damit kann man beliebige weitere Informationen über Daten bzw. Objekte und Daten Modelle in der Datenbank ablegen, die dann die Entwicklungstools entsprechend auswerten und nutzen können.

In diesem Beispiel sehen wir ANNOTATIONS auf einem Datenbank-Objekt. Hier kann man beliebige Key/Value Paare definieren bzw. beliebigen Freitext (Kundendaten) spezifizieren.

CREATE TABLE konto (....)
  ANNOTATIONS ( Datenschutz 'Hoch',
                Abteilungen 'Vertrieb, Buchhaltung, Vorstand',
                Kundendaten );

Dadurch werden Anwendungen und Tools in die Lage versetzt, Entscheidungen basierend auf diesen Anmerkungen zu treffen. Beispielsweise dürfen auf Grund des Datenschutzes Informationen aus dieser Tabelle nur dann angezeigt werden, wenn man in der Applikation entsprechende Berechtigungen hat.
Alle anderen Benutzer bekommen anonymisierte Informationen. Welche Abteilungen den Zugriff auf diese Daten erhalten dürfen, kann hier spezifiziert werden. Dadurch ermöglicht man den interaktiven Tools (zb: Query Builder) sicherzustellen, dass nur berechtigte Benutzer überhaupt auf die Daten zugreifen dürfen.

Ein weiteres Beispiel für den Einsatz von ANNOTATIONS auf Spalten könnte so aussehen:

CREATE TABLE mitarbeiter
( oid    NUMBER(10)   ANNOTATIONS (Identity, Display 'Mitarbeiternummer', Group 'MA_Info'),
  name   VARCHAR2(30) ANNOTATIONS (Display 'Mitarbeitername', Group 'MA_Info'),
  gehalt NUMBER       ANNOTATIONS (Display 'Gehalt', UI_Hidden)
) ANNOTATIONS ( Display 'Mitarbeitertabelle');

Damit gibt man dem Tool Informationen, wie die Spalten benannt werden sollen (Display) oder dass sie überhaupt nicht angezeigt werden dürfen (UI_Hidden).

Das waren nur einige Beispiele von den Neuheiten in Oracle 23c – stay tuned!