PostgreSQL Cheat Sheets – Sammlung

Für PostgreSQL finden sich einige sehr nützliche Cheat-Sheets, um im Alltag eine schnelle Referenz zur Hand zu haben.

Die korrekte Übersetzung von Cheat-Sheet lautet „Schummelzettel“.

Allerdings zielt die Verwendung in erster Linie nicht auf das Schummeln ab, sonder es handelt sich vielmehr um eine übersichtliche Zusammenstellung von wichtigsten Details zu einem ganz bestimmten Thema.

Im Idealfall sollte die Länge von einem A4 Blatt nicht überschritten werden, aber es gibt natürlich auch umfassendere Cheat-Sheets.

Zum Thema PostgreSQL haben wir folgende nützliche Cheat-Sheets gefunden:

[Link ] PostgreSQL Cheat Sheet von postgresqltutorial.com – 3 Seiten

In diesem Cheat Sheet werden folgende PostgreSQL Themen behandelt:

  • quering data from table
  • quering from multible tables (verschiedene Joins – left join, right join, outer join, cross join, usw..)
  • SQL operatoren – union, intersect, except, like – not like, in – not in, between and, is null – is not null
  • Table Management – create, drop, alter, truncate, etc…
  • SQL Constraints – primary key, foreign key, unique, check, usw..
  • Data Modification – insert into values, insert into select * from table, update set, delete from, etc…
  • View Management – create view as select, create recursive view, create temporary view, etc…
  • Index Management – create index, create unique index, drop index
  • SQL Aggreate Functions – avg, count, sum, max, min
  • Trigger Management – create trigger when event, etc…

[ Link ] Postgresql Cheat Sheet von alberton.info – 1 Seite

In diesem Cheat-Sheet werden folgende PostgreSQL Themen behandelt:

  • Data Types
  • Internal Functions
  • Usefull Queries
  • Information Shema

[ Link ] PostgreSQL Sting Functions Cheat Sheet von SQLBackupAndFTP.com – 1 Seite

In diesem Cheat-Sheet werden PostgreSQL String Functions behandelt:

  • Conversion
  • Measurement
  • Modification

[Link ] Postgresql terminal commands – cheatography.com – 1 Seite

In diesem Cheat-Sheet werden PostgreSQL terminal commands behandelt:

  • Connecting
  • PSQL
  • Roles and database management
  • Database backup

 

 

 

 

Das war die DBConcepts Sommer-Party 2019

Zum dritten Mal in Folge fand heuer wieder unsere beliebte DBConcepts Sommerparty in der Crêperie an der Alten Donau statt.

Über fünfzig TeilnehmerInnen konnten wir heuer begrüßen, um gemeinsam in entspannter Atmosphäre einen gemütlichen und lustigen Abend zu verbringen.

Als Willkommensgeschenk gab es coole Sonnenbrillen für jeden Gast um bei starker Sonnenstrahlung entsprechend geschützt zu sein. 😉

Zwischendurch sorgte ein kleiner aber heftiger Hagelschauer für Weltuntergangsstimmung und eine kurze Unterbrechung der Tretboot-Challenge.

Die unerschrockenen Teams kämpften gleich danach wieder um jede Zehntelsekunde, die Gewinner von Verlierern trennten.

Obwohl bei einigen Gästen alte Blessuren verheilen mussten und eine Teilnahme an der Tretboot-Challenge unmöglich machten, fanden sich 11 Teams um den Champion Titel untereinander auszumachen.

Im Anschluss verwöhnte der Grillmeister mit vielen verschiedenen Köstlichkeiten unseren Gaumen.
Die durstigen Kollegen, Kunden und Freunden von DBConcepts wurden den ganzen Abend mit entsprechende Erfrischungen versorgt und mit cooler Musik von DJ Brain abgekühlt.

Sehr besonders freut uns das begeisterte Feedback aller Gäste, die uns zur gelungen Sommer-Party gratuliert haben.

Nach der Sommer-Party ist immer vor der Sommer-Party.

In diesem Sinne freuen wir uns schon auf nächstes Jahr!

 

 

Migration Oracle Reports auf Oracle BI Publisher

Im April 2019 gab Oracle ein Statement of Direction bezüglich Oracle Reports heraus.

Die wichtigen Punkte grob zusammengefasst:

  1. Fusion Middleware Release 12c R2 (Version 12.2.1.3) ist die letzte Oracle Reports Release
  2. Premier Support gibt es nur mehr für Release 12c R2 bis August 2022
  3. Extended Support gibt es nur mehr für Release 12c R2 bis August 2025
  4. Oracle empfiehlt von Reports auf BI Publisher umzusteigen, welcher die zukünftige Reporting Lösung ist

Die Möglichkeit von Reports auf BI Publisher zu migrieren gab es bereits für BI Publisher Version 11.

Es finden sich Anleitungen dazu in der Oracle Dokumentation „Fusion Middleware Administrator’s Guide for Oracle Business Intelligence Publisher“ unter dem Punkt „Converting Oracle Reports“ (siehe https://docs.oracle.com/cd/E80149_01/bip/BIPAD/GUID-B66E8D0D-7C18-4E17-9394-D889F58088E7.htm#BIPAD4799)

Der zentrale Punkt in dieser Anleitung ist ein Konvertierungstool, welches aus den Reports, welche im XML Format gespeichert sein müssen, entsprechende Dateien für den BI Publisher erzeugt.

Dieses Tool wurde allerdings Anfang 2019 als deprecated eingestuft und wird nicht mehr von Oracle unterstützt (siehe Note Doc ID 2442584.1).

Der einzige Migrationspfad der damit bleibt ist die manuelle Übernahme.

Je nach Komplexität des Reports kann das einige Zeit in Anspruch nehmen, da der Report in BI Publisher neu erstellt werden muss. Das geschieht in 2 Schritten:

  1. Übernahme der SQL Abfragen für die Daten (falls Variablen verwendet werden, erkennt der BI Publisher das automatisch und erstellt sie ggf.) – hier muss auf folgendes geachtet werden:
    Abfragen welche z.B. so beginnen: Select v.* – also einen Table-Alias mit dem Universal Selektor kombinieren – funktionieren nicht korrekt, wenn in weitere Folge Joins für diese Tabelle angelegt werden
  2. Erstellen des Reports mit Design, Struktur und allem anderen.
    Für diesen Schritt kann keine wirkliche Anleitung gegeben werden, da die Möglichkeiten zu vielfältig sind.

Grundsätzlich sollte vorab definiert werden ob die Reports im BI Publisher im RTF oder im XDO Format erstellt werden, damit sie später einheitlich sind.

Parsen von JSON-Daten in einer Oracle-Datenbank

Wer als (APEX-)Entwickler heutzutage Web-Applikationen erstellt, stößt immer häufiger auf Anforderungen der Art „Integration von Daten einer fremden Quelle“.

Also beispielsweise die Integration von Wetterdaten eines Wetterservices. Während vor einigen Jahren überwiegend XML als Format für derartige Datenaustausche verwendet wurde, gehen aktuelle Entwicklungen immer mehr dazu über an dieser Stelle JSON-Strukturen zu verwenden.

Vor allem in etwas älteren APEX-Applikationen kann das mitunter zu Performance-Problemen führen.

In Oracle Datenbank Version 11 gab es etwa noch gar keine native Unterstützung für die Verarbeitung von JSON-Daten – weder in PL/SQL noch in SQL.

Die erste Möglichkeit überhaupt war das Package APEX_JSON, das mit APEX 5.0 eingeführt wurde und mangels Alternativen recht weite Verbreitung fand.

Erst mit 12.1.0.2 begann die Unterstützung in SQL mit der Einführung von JSON_TABLE, mit 12.2 in PL/SQL durch die Einführung der Types JSON_ELEMENT_T, JSON_OBJECT_T etc.

Seither werden diese Funktionalitäten laufend verbessert und erweitert, so dass es sich je nach konkretem Anwendungsfall also durchaus lohnen kann, früher implementierte JSON-Verarbeitung mittels APEX_JSON zumindest zu hinterfragen und bei Bedarf durch native Funktionalitäten abzulösen.

Ein kleiner Test soll die Unterschiede in der Performance aufzeigen:

Vorbereitungen

Um keine Test-Daten generieren zu müssen, verwenden wir an dieser Stelle einen Service des US Geological Survey (USGS): den Erdbeben-Katalog (API Dokumentation: https://earthquake.usgs.gov/fdsnws/event/1/ ). Ein kleines Script erzeugt eine Tabelle für die Testdaten und befüllt diese auch gleich mit einem CLOB mit 20.000 Einträgen im JSON-Format:

CREATE TABLE test_json (id NUMBER GENERATED ALWAYS AS IDENTITY,
data CLOB);
INSERT INTO test_json (data)
VALUES (apex_web_service.make_rest_request(p_url => 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&limit=20000',
p_http_method => 'GET'));
SELECT t1.id,
dbms_lob.getlength(t1.data)
FROM test_json t1;

ID DBMS_LOB.GETLENGTH(DATA) 
--- -------------------------
1 15727921

Zur Verdeutlichung der Performance-Unterschiede reicht folgende Anforderung aus: Wir wollen ermitteln wie viele Elemente/Erdbeben das Array FEATURES innerhalb der JSON-Daten enthält (auch wenn wir aufgrund der Parameter des Webservice-Calls bereits erahnen können, dass die Antwort 20.000 lauten wird).

Bei der Verwendung von APEX_JSON gehen wir wie folgt vor:

DECLARE
v_count NUMBER;
v_json_data CLOB;
v_time TIMESTAMP;
BEGIN
SELECT t1.data
INTO v_json_data
FROM test_json t1
WHERE t1.id = 1;

v_time := SYSTIMESTAMP;
apex_json.parse(p_source => v_json_data);
dbms_output.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));

v_time := SYSTIMESTAMP;
v_count := apex_json.get_count(p_path => 'features' );
dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Counting Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Parsing Time: 23,200156
Feature Count: 20000
Counting Time: 0,000012

Für eine Applikation, die „nur mal eben live ausgeben soll“, wie viele Erdbeben es in einem bestimmten Zeitraum gegeben hat, sind 23 Sekunden zu viel.

Sehen wir uns daher die Verwendung neuerer Funktionalitäten in PL/SQL an:

DECLARE
v_count NUMBER;
v_json_data CLOB;
v_json_object json_object_t;
v_time TIMESTAMP;
BEGIN
SELECT t1.data
INTO v_json_data
FROM test_json t1
WHERE t1.id = 1;

v_time := SYSTIMESTAMP;
v_json_object := json_object_t.parse(jsn => v_json_data);
dbms_output.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));

v_time := SYSTIMESTAMP;
v_count := v_json_object.get_array('features').get_size;
dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Counting Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Parsing Time: 6,513685
Feature Count: 20000
Counting Time: 0,000067

Das ist schon deutlich schneller als in der Variante mit APEX_JSON – für moderne Applikationen aber sind 6sec für vermeintlich einfache Datenabfragen zu lange.

Daher versuchen wir nun die Anzahl primär per SQL zu ermitteln:

DECLARE
v_count NUMBER;
v_time TIMESTAMP;
BEGIN
v_time := SYSTIMESTAMP;

SELECT COUNT(*)
INTO v_count
FROM test_json t1, JSON_TABLE(data, '$.features[*]' COLUMNS (row_number FOR ORDINALITY,
mag NUMBER PATH '$.properties.mag',
eq_id VARCHAR2(30) PATH '$.id')) AS t2
WHERE t1.id = 1;

dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Total Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Feature Count: 20000
Total Time: 0,165338

Obwohl hier alles in einem einzigen Schritt ausgeführt wird, braucht die gesamte Abarbeitung der Anfrage nur mehr knappe 0,2sec und ist damit um einen Faktor deutlich jenseits der 100 schneller als die APEX_JSON-Implementierung.

Wer also größere JSON-Objekte in 12.2+ noch mit APEX_JSON verarbeitet, sollte dringend einmal über eine kleine Frischzellenkur nachdenken…

Webservices direkt aus der DB (XMLDB)

Übersicht

Web Services werden heutzutage immer öfter benutzt um Daten unabhängig vom Standort, zwischen zwei Applikationen auszutauschen und Funktionen aufzurufen.

Oracle stellt mit den Native Oracle XML DB Web Services eine Möglichkeit zur Verfügung, die es erlauben SQL und XQuery Abfragen an einen Host zu senden.

Außerdem kann auf PL/SQL Stored Procedures und Functions zugegriffen werden.

Dabei unterstützt Oracle XML DB das Netzwerkprotokoll SOAP 1.1. Durch die HTTP POST Methode werden die SOAP Requests an die Oracle XML DB Web Services übermittelt.

Der Standort der Web Services und WSDL Dokumente können in der Oracle XML DB Datei xdbconfig.xml konfiguriert werden.

Konfiguration

Um die Web Services zu aktivieren, ist es zuallererst notwendig dem Datenbankbenutzer als SYS User die XDB_WEBSERVICES Rolle zuzuweisen.

Durch die Zuweisung der Rolle können die Web Services benutzt werden. Standardmäßig ist die Benutzung via HTTPS freigeschalten.

Dazu können noch weitere Rollen vergeben werden:

XDB_WEBSERVICES_OVER_HTTP – Benutzung via http

XDB_WEBSERVICES_WITH_PUBLIC – Zugriff auf PUBLIC Datenbank-Objekte

Hat ein User die Zugangsberechtigung auf eine Datenbank mittels Web Services so kann er nur auf die ihm zugewiesenen Datenbank-Objekte zugreifen.

Mit XDB_WEBSERVICES_WITH_PUBLIC kann er nun auch auf PUBLIC Objekte zugreifen.

Zugriff

Das Web Service für Datenbank-Abfragen befindet sich auf http://host:port/orawsv.

Bei host:port handelt es sich um den Datenbank-Host und HTTP(S)-Port. Der Pfad enthält eine WSDL-File, die eingehende und ausgehende Dokumente in XML spezifiziert.

Um auf Stored Procedures und Functions zugreifen zu können, muss http://host:port/orawsv/dbschema/package/fn_or_proc angewählt werden. Host:port enthält wieder den Datenbank-Host und  HTTP(S)-Port. Fn_or_proc gibt dabei die Procedure bzw. Function an.

Mit dbschema wird das Datenbank-Schema angegeben.

Sollte eine Procedure oder Function außerhalb eines Packages sein, so kann package ausgelassen werden.

Anmeldung zur DBConcepts Sommer-Party 2019

Es ist wieder so weit!
Feiern Sie mit uns den Sommer ganz relaxed an der Alten Donau bei einem fantastischen Grill-Buffet und kühlen Getränken!

Für besonders gute Laune sorgt auch heuer wieder die beliebte DBConcepts Tretboot-Challenge.

Weiterlesen

DBConcepts auf den DOAG Datenbank Tagen in Düsseldorf

Zum ersten Mal war heuer DBConcepts auf den DOAG Datenbank Tagen als Sponsor vertreten, welche vom 3. Juni bis 4. Juni in Düsseldorf stattgefunden haben.

In Summe über 200 Teilnehmer/innen informierten sich in zahlreichen Vorträgen über aktuelle Entwicklungen und Trends im Oracle Datenbank Umfeld.

Unser Kollege und Oracle Performance Tuning Spezialist Lothar Flatz gab in seinem Vortrag mit dem Titel „Trouble im Shared Pool“ seine sehr interessanten Einblicke und Erfahrungen preis.

Durch den hohen Bekanntheitsgrad und seine besonders geschätzte Expertise kam es zur erwarteten Überlastung des Vortragraumes.

Leider konnten die Veranstalter nicht rechtzeitig reagieren, woduch circa 30 Teilnehmer den Ausführungen stehend folgen mussten.
Dieser Umstand tat aber der Begeisterung über den Vortrag keinen Abbruch.

Viele Besucher am DBConcepts Ausstellerstand informierten sich über das Leistungsspektrum der „Oracle Experten“ aus Österreich und meldeten sich zusätzlich zum DBConcepts Newsletter an.

Bei den zahlreichen Gesprächen kristallisierte sich heraus, dass der Großteil der Besucher DBConcpts bereits kannten und als Experten im Oracle Technologie Umfeld in Verbindung bringen.

Wir halten allen Teilnehmer/innen unseres Euromillionen-Gewinnspiel die Daumen, dass die Glücksfee bei ihnen vorbei kommt 🙂

DOAG Datenbank Tag 2019 Messestand von DBConcepts

 

Überfüllter Vortrag von Lothar Flatz Trouble im Shared Pool

 

 

Ändern des Passwort Hash Algorithmus in PostgreSQL

PostgreSQL: Ändern des Passwort Hash Algorithmus

Standardmäßig verwendet PostgreSQL den MD5-Algorithmus, um Passwörter von Benutzern in der Datenbank abzulegen.

Da MD5 mittlerweile nicht mehr als all zu sicher gilt, wird empfohlen, andere Hash Algorithmen für die Passwortablage zu verwenden.
PostgreSQL hat dazu ab Version 10 die Möglichkeit geschaffen, auf den SCRAM-SHA-256-Algorithmus umzustellen.

Wichtig ist, wie bereits erwähnt, dass die PostgreSQL Datenbank Version 10 oder höher ist, sowie, dass die Programme, die auf die Datenbank zugreifen, diesen Algorithmus unterstützen.

Sind die Bedingungen erfüllt, kann man den Default-Algorithmus in der Datenbankkonfiguration umstellen.

Dies muss an zwei Stellen getan werden:

1.       In der postgresql.conf (per Default im Verzeichnis /etc/postgresql/10/main/postgresql.conf) muss der Parameter password_encryption auf ’scram-sha-256′ gesetzt werden.
Die resultierende Zeile muss in der postgresql.conf folgendermaßen aussehen:
password_encryption = ’scram-sha-256′

2.  Außerdem muss in der pg_hba.conf (per Default im Verzeichnis /etc/postgresql/10/main/pg_hba.conf) der gewünschte Algorithmus für die gewünschte Socketmethode angepasst werden. Die Konfiguration befindet sich am Ende der Datei, für IPv4 müsste die Zeile danach so aussehen:
# IPv4 local connections:

host    all             all             127.0.0.1/32            scram-sha-256

Ist dies getan, werden alle zukünftigen Passwörter nun mit dem SCRAM-SHA-256-Algorithmus gehashed.

WICHTIG: Alle bereits gesetzten Passwörter von Datenbankbenutzern bleiben in dem zum jeweiligen Zeitpunkt konfigurierten Hash-Algorithmus bestehen.

Wenn also bereits vorhandene User in der Datenbank existieren, müssen die Passwörter dieser User neu gesetzt werden, um mit dem neuen Hashalgorithmus abgespeichert zu werden.

Ansonsten würde ein Login via PSQL auf die Datenbank den Hashwert des eingegebenen Passwortes mittels SCRAM-SHA-256 ermitteln und diesen Wert mit dem MD5-gehashten abgelegten Passwort in der Datenbank vergleichen.
Da diese Werte nicht übereinstimmen, würde der Zugriff verweigert werden.

NEU: Java Delvelopment Kit Java SE 8u211 / Java SE 8u212 verfügbar

Die beiden Java Delvelopment Kit Java SE 8u211 / Java SE 8u212 sind seit kurzer Zeit erhältlich und enthalten wichtige Fehlerbehebungen.

Oracle empfiehlt dringend, dass alle Java SE 8-Benutzer ein Upgrade auf diese Version durchführen.

Leider ist dieses Update für Unternehmen bereits mit dem Erwerb der Java Subscription verbunden. Details zur kostenpflichtigen Java Subscription finden Sie hier.

Für Unternehmen ist es jetzt an der Zeit, eine sinnvolle Strategie für den Umgang mit Java zu haben um die Sicherheit der Applikationen zu gewährleisten.

Die auf der Hand liegenden Möglichkeiten wie…

  • Umstieg auf OpenJDK
  • Isolierung der Applikation
  • Erwerb der Subscription

…sollten nun gemeinsam mit den Applikationsverantwortlichen evaluiert werden.

Wie wichtig die Fehlerbehebungen sind werden die nächsten Monate zeigen, indem es Meldungen über Hacks gibt – oder auch nicht.

Auf jeden Fall bleibt es spannend!

Oracle 19c SE2 ohne RAC Funktionalität

Das (faule) Oracle19c SE2 Osterei – Standard Edition 2 ohne RAC

Die neue Oracle19c Datenbank ist gleichzeitig die Long Term Support Release bzw. das Terminal Patch Set für die Oracle Datenbank Version 12.2.

Anders als bei der Annual Release Oracle18c (2 Jahre ab Release von Oracle19c) wird es für diese Version Support bis zum 31. März 2026 geben.

Oracle 19c SE2 ohne RAC

Ein Blick in den aktuellen Oracle Lizenz Guide der Long Term Version 19c bringt Ernüchterung – der Einsatz von Oracle Real Application Clusters (RAC) ist ab Oracle19c in der Standard Edition 2 (SE2) nicht mehr erlaubt.

Oracle SE2 RAC Kunden, die ein Software Upgrade auf Oracle19c durchführen, dürfen dann die RAC Funktionalität nicht mehr einsetzen.

SE2 RAC Kunden, die bereits auf der wesentlich kürzeren Annual Release 18c sind, stehen damit auf einem bereits sehr kurzen Abstellgleis für Standard Edition 2 RAC.

Unklare Strategie

Welche Oracle Strategie hinter dieser Entscheidung steckt ist unklar.

Eventuell will man die Oracle Kunden mit sanftem Druck in die Oracle Autonomous Database Cloud bewegen indem man die On-Premise Lösungen für die Kunden nicht unmöglich, aber zunehmend unbequem macht.

Ob solch eine Strategie aufgehen würde ist fraglich, weil Oracle damit den Kunden wieder ein klares Zeichen der Unberechenbarkeit gäbe.

Die Kunden verlieren zunehmend das Vertrauen in die Kontinuität der Lizenzgewährung des Herstellers und der eine oder andere wird sich wohl oder übel dadurch nach Alternativen zu Oracle umsehen.

Offene Fragen aus lizenzrechtlicher Sicht

Aus lizenzrechtlicher Sicht ist diese Entwicklung ebenfalls fragwürdig.

Aus unserer Sicht stellen sich folgende Fragen bzw. Überlegungen:
Oracle SE2 Bestandskunden haben mit Oracle in der Vergangenheit vertraglich (Oracle TOMA und LDR – Lizenz Definitionen und Regeln) vereinbart, dass die Verwendung von Oracle RAC in der SE2 Lizenz unter definierten Regeln erlaubt ist!

Die Version 19c ist aber kein neues Produkt mit neuem Oracle LDR Vertrag, somit müsste der damals akzeptierte LDR gelten und die Verwendung von SE2 RAC für alle Bestandskunden weiterhin möglich sein.
Auch dann, wenn der aktuelle 19c Lizenz-Guide es für Neukunden nicht mehr vorsieht.

Fazit

Die obigen Schlussfolgerungen und Überlegungen basieren auf dem aktuellen Stand der uns zugänglichen Informationen.
Ausgelöst durch ein simples „N“ wo im Lizenz-Guide der Datenbank früher ein „Y“ stand.

Derzeit ist noch vieles unklar und es gilt abzuwarten, wie Oracle diese doch sehr eingreifende Änderung letztendlich präsentieren wird.