Network Encryption in Oracle

Today I want to share with you one of my security projects which Iwas working on at DBConcepts GmbH.

One of the topics was the encryption of Oracle’s Network Traffic.

The purpose of a secure cryptosystem is to convert plaintext data into unintelligible ciphertext based on a key, in such a way that it is very hard (computationally infeasible) to convert ciphertext back into its corresponding plaintext without knowledge of the correct key.

The setup is as followed:

A high availablity setup using dataguard with a primary and standby database 19c EE Edition. Enterprise Manager Cloud Control 13c, a recovery catalog database and a application server corresponding to each database server are also in the IT Infrastructure and to be secured.

The task is as follows:

  • the secure communication between the application server to the database server
  • the encryption of Dataguard logshipping from the primary to the standby database
  • the encryption of network traffic from the database hosts to the recovery catalog
  • the encryption of JDBC-thin clients connecting to the database

I want to give you a quick overview about the possibilities which oracle offers and how unsecure a not encrypted communication is.

First, there are two encryption options which oracle provides:

  • Oracle’s Native Network Encryption (using TCP Port 1521)
  • TLS/SSL Encryption Standard (using custom TCPS Port f.e. 1522)

Oracle Database 19c supports the usage of a combination of those encryption options.

Let’s get into action:

I did a tcpdump from our monitoring host where an oracle client is installed to connect to a 19c database. It sends select statements to gather information for specific metrics.The tcpdump listens to all outgoing connections with the target database specified as the destination.

(Due to privacy policies hostnames and ip adresses are changed)

The result without a secure communication, which is the default setup for all database installations:

[root@<monitoring01> ~]# tcpdump -i eth0 dst <target db ip adress> -A -v

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes




…

<start of packet>

11:12:34.455642 IP (tos 0x0, ttl 64, id 62432, offset 0, flags [DF], proto TCP (6), length 1161)

    <monitoring01>.30585 > <target db ip adress>.ncube-lm: Flags [P.], cksum 0x5599 (incorrect -> 0xf31b), seq 1684:2793, ack 2135, win 501, options [nop,nop,TS val 3707091369 ecr 1327380418], length 1109

E.....@.@..p

3.



zjfwy... /9.&......U......

................................................................................................................................................@select replace(WAIT_CLASS,'/','') WAIT_CLASS,ROUND(FG,2) FG,ROUN@D(BG,2) FG_AND_BG,DBTIME,to_char(end_time,'yyyy-mm-dd hh24:mi:ss@') end_time from (

select sw.wait_class,wc.END_TIME, wc.WAIT_CLA@SS#, (wc.TIME_WAITED_FG)/(wc.INTSIZE_CSEC/1) fg, (wc.TIME_WAITED@)/(wc.INTSIZE_CSEC/1) bg, 0 dbtime

from V$WAITCLASSMETRIC WC,V$@SYSTEM_WAIT_CLASS SW

where WC.WAIT_CLASS#=SW.WAIT_CLASS#

and wai@t_class!='Idle'

union

select 'CPU',FG.END_TIME, -1, FG.value/100@, BG.value/100, DBTIME.value from V$SYSMETRIC FG, V$SYSMETRIC BG@, V$SYSMETRIC DBTIME

where BG.METRIC_NAME = 'Background CPU Usag@e Per Sec'

and BG.GROUP_ID = 2

and FG.METRIC_NAME = 'CPU Usage P@er Sec'

and FG.GROUP_ID = 2

and DBTIME.METRIC_NAME = 'Average Ac@tive Sessions'

and DBTIME.GROUP_ID = 2

and BG.END_TIME = FG.END_3TIME

and FG.END_TIME = DBTIME.END_TIME

order by 1)

.....................................................

<End of packet>

11:12:34.509827 IP (tos 0x0, ttl 64, id 62433, offset 0, flags [DF], proto TCP (6), length 52)

…v

As you see this is pretty scary. Every SQL-Statement and every response from the database is sent over the network in plaintext. In terms of privary and security Data modification attacks and replay attacks are possible.

Examples:

  • Intercepting a $100 bank deposit, changing the amount to $10,000, and retransmitting the higher amount is a data modification attack.
  • Repetitively retransmitting an entire set of valid data is a replay attack, such as intercepting a $100 bank withdrawal and retransmitting it ten times, thereby receiving $1,000.

This is were encryption comes to place to secure your company and enviroment against unauthorized parties intercepting data in transit.

After the implementation of security and encryption, this is the final result:

There is no plain text send over the network, only ciphertext !

[root@<monitoring01> ~]# tcpdump -i eth0 dst <target db ip adress> -A -v

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes



<start of packet>

11:23:46.582209 IP (tos 0x0, ttl 64, id 3746, offset 0, flags [DF], proto TCP (6), length 696)

<monitoring01>.59236 > <target db ip adress>.ssh: Flags [P.], cksum 0x53c8 (incorrect -> 0xb685), seq 1633:2277, ack 3158, win 130, options [nop,nop,TS val 3707763496 ecr 1328052548], length 644

E.....@.@...

3.



zjf.d..............S......

...(O(yD...U

J3R.Ok.J._.W/....ZpZ

*.:/..d.#.......Bl.&.Z5a.3.....B.....m.S....U..NtS.......y..x......*>.b....$...^ij........Z6..KPK......~v\.WvHm.6.$&9.%;.x..x#a.:..5.... .X=[M...?......xv3i&(.|.-+.p.~.n...i.J.wW...qS"d....Z...........eY1.L..V..F5..(..E.O...'.os.

Y.xIG8......K....5rh.F@..D=..~............].U.Fi..7.u......e..y/..C'..... |...{o_..6b>`..}.X.f...H..K=`v.>

.....i.....B ..d*os.h&.....:....W.G....C.s...2.V..HWA(o..e ....$x.....|. ....Kwz.....:p.Nz.Aqz..5..+V+dtria..lv7T..k.n..5E.....



<end of packet>

11:23:46.582899

…v

Hardening the systems and implementing security features is a must !

 

to be continued …

Oracle SQL MATCH_RECOGNIZE

Ein sehr mächtiges und effizientes Konstrukt gibt es seit der Oracle Version 12C: MATCH_RECOGNIZE. Vereinfacht gesagt lassen sich mit MATCH_RECOGNIZE regular expressions auf Zeilen anwenden, um so bestimmte Muster zu erkennen. Beispielsweise lassen sich somit in der Finanzwelt bestimmte Aktienkurs-Verläufe erkennen. Dieser Blogbeitrag soll dazu dienen, sich diesem Thema anhand einfacher und fiktiver Beispiele langsam anzunähern.
Grundlegender Aufbau:

 

 

PATTERN_PARTITION_CLAUSE

Diese ist optional. Hier kann man die Daten sortieren (ORDER BY) und in Gruppen aufteilen (PARTITION BY). Um bei jeder Ausführung der Query konsistente Ergebnisse zu erhalten, sollte zumindest ein ORDER BY eingebaut werden.

PATTERN_MEASURES_CLAUSE

Dieser Abschnitt definiert die Spalten, welche im SELECT Teil ausgegeben werden. Es gibt einige inbuilt functions, welche hier verwendet werden können. Dazu später mehr.

PATTERN_DEF_DUR_CLAUSE

Hier wird die eigentliche magic definiert. In diesem Abschnitt werden die patterns definiert, welche über jede Zeile ausgeführt werden. Gibt es ein match, sprich wird genau dieses Muster identifiziert, wird die Zeile ausgegeben.

 

Wir schauen uns anhand einiger Beispiele die einzelnen Abschnitte näher an. Zu diesem Zwecke habe ich eine Tabelle mit Aktiendaten erstellt, welche wie folgt definiert ist (Das Skript kann am Ende des Artikels heruntergeladen werden):

 

 

 

 

 

 

Die Tabelle beinhaltet Aktienkurse eines fiktiven Unternehmens. Wir starten mit einer simplen Abfrage, anhand welcher ich auf die Struktur näher eingehen möchte: Alle Handelstage, an denen es einen oder mehrere Handelstage gibt, welche einen Startkurs größer als 17 haben:

 

 

 

 

 

 

 

 

 

Nachfolgend eine Erklärung der einzelnen Codeteile:

PARTITION BY: Unterteilt die Daten je nach Titel in einzelne Gruppen. In unserem Fall haben wir nur einen Titel in unseren Testdaten, wodurch es sowieso nur eine Gruppe gibt, nämlich die der „Datenbank AG“.

ORDER BY: Sortiert die Daten in der jeweiligen Gruppe je nach Handelstag aufsteigend.

MEASURES: Definiert, welche Spalten im result set zusätzlich ausgegeben werden. Hier sehen zwei inbuilt functions: CLASSIFIER() und MATCH_NUMBER(). CLASSIFIER () gibt an, welche pattern variable für diese Zeile gematched hat. MATCH_NUMBER () vergibt für gematchte Gruppe eine eigene Nummer, welche bei 1 startet und sich für jede neue Gruppe um 1 erhöht.

ALL ROWS PER MATCH: Definiert, dass bei aufeinanderfolgenden matches jede Zeile ausgegeben wird. Das pendant dazu wäre ONE ROW PER MATCH, welches auch der Default Wert ist. Hier wird dann nur die erste Zeile der jeweiligen Gruppe ausgegeben. In unserem Beispiel sehen wir, dass für Gruppe 4 jede einzelne Zeile ausgegeben wird; würden wir stattdessen ONE ROW PER MATCH verwenden, würden wir für Gruppe 4 nur die erste Zeile sehen. Das Ganze hat dann auch Auswirkungen auf die oberen drei Bereiche: Bei ALL ROWS PER MATCH, so wie wir es verwenden, könnte ich den den PARTITION BY, ORDER BY und MEASURES Bereich ganz weglassen, da sowieso alle Spalten im result set ausgegeben werden. Verwende ich hingegen ONE ROW PER MATCH, muss ich entweder Spalten im MEASURES Bereich angeben und/oder die ORDER BY / PARTITION BY Klausel angeben. Das Prinzip ist dem GROUP BY also sehr ähnlich.

PATTERN: Hier liste ich die einzelnen Variablen auf, welche im DEFINE Bereich definiert sind und auf die jeweilige Zeile zutreffen müssen. In unserem Beispiel ist das die Variable „start_1“ mit einem regular expression chracter „+“. Dies bedeutet, dass es einen oder mehrere Handelstage geben muss, an denen der Startkurs höher als 17 ist.

DEFINE: Hier werden die Variablen mit ihren conditions definiert, welche im PATTERN Bereich verwendet werden können.

 

 

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Auf die Handelstage 01.12.2022, 03.12.2022 und 09.12.2022 folgen jeweils Tage, an denen der Startkurs kleiner als 17 ist. Der 13.12. ist der erste Tag einer Reihe von Handelstagen, wo der Startkurs höher als 17 ist.

Im nächsten Beispiel wollen wir alle Handelstage ausgeben, an denen exakt zwei Mal in Folge der Startkurs höher ist als der Schlusskurs am Vortag.

 

 

 

 

 

 

 

Hier sehen wir, dass das SQL im Prinzip ähnlich aufgebaut ist als im Beispiel eins. Im DEFINE Bereich ist unsere pattern Variable mit der dazugehörigen condition definiert. Diese besagt, dass der Startkurs der jeweiligen Zeile höher sein muss als der Schlusskurs der darüberliegenden Zeile. Hierzu verwende ich die Funktion PREV(), welche dann genau auf die darüberliegende Zeile verweist. An diesem Beispiel sehen wir auch, dass es Sinn macht, die Daten zu sortieren, da wir nicht immer wissen, in welcher Reihenfolge die Daten aus der Datenbank abgefragt werden (das Ergebnis ist also nicht deterministisch). Im PATTERN Bereich holen wir uns die Variable und wandeln diese nun mit „{2}“ zu einem regulären Ausdruck um. Dies bedeutet, dass wir zwei Handelstage in Folge haben wollen, auf welche die condition zutrifft. Zur Kontrolle definieren wir im MEASURES Bereich die Spalte „schlusskurs_vortag“. Da MATCH_RECOGNIZE wie eine INLINE VIEW zuerst ausgeführt wird, können wir diese im SELECT Teil verwenden und ausgeben.

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen nun immer zwei Handelstage in Folge, an denen der Startkurs höher ist als der Schlusskurs am Vortag.

Für das nächste Beispiel überlegen wir uns folgendes Muster: An Handelstag eins soll der Startkurs zwischen 16 und 17 sein, an Handelstag zwei soll der Startkurs größer als 17 sein, an Handelstag drei soll der Startkurs wieder zwischen 16 und 17 sein.

 

 

 

 

 

 

 

 

Dieses SQL enthält eine Erweiterung, nämlich AFTER MATCH SKIP TO LAST. Diese Syntax bedeutet, dass wenn es ein match gibt, die Suche nach einem neuen Match bei der letzten pattern Variable beginnt.

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen nun alle Handelstage, die genau unserem Muster entsprechen. Würden wir AFTER MATCH SKIP TO LAST weglassen, würden nur die ersten drei Handelstage im result set erscheinen, da die Suche erst am 22.12.2022 weitergehen würde.

In unserem letzten Beispiel wollen wir eine typische V-Formation erkennen. Dies bedeutet, dass der Aktienkurs zunächst einbricht und sich nach Erreichen der Talsohle wieder erholt.

 

 

 

 

 

 

 

 

 

 

 

 

Hier haben wir im DEFINE Bereich drei pattern Variablen definiert, wobei man die Definition der ersten Variable „strt“ auch weglassen könnte, da sie sowieso jede Zeile matched. Diese dient für uns lediglich als Einstiegspunkt in unsere Mustersuche.

 

 

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen in unserem result set anhand der Gruppennummer, welche Handelstage eine V-Formation bilden und somit zu einer Gruppe gehören. Das Ende der jeweiligen V-Formation ist zugelich der Beginn einer neuen Kursbewegung nach unten.

Wir haben uns nun anhand einiger Beispiel angesehen, was MATCH_RECOGNIZE ist und was für Abfragen wir damit durchführen können.

 

 

Oracle Database 19c SIG

Underestimated Powertools – Yes we can!

They have a nimbus of substitute solutions: Partition View , DBMS_PARALLE_EXECUTE , and Snapper. They are used, for example, in the Standard Edition, or when the Oracle options are not available. The exact application of the tools is sometimes poorly documented. The nimbus does not do these tools justice. Each of these can do things that the official option cannot. In this lecture you will not only learn how to really use these tools in practice, but you will also learn about their hidden strengths.

Check out the video here:

and the slides here

CBQT-ORE and its FIRST_ROWS optimization inability

CBQT-ORE and its FIRST_ROWS optimization inability

Terminology:

CBQT: cost based query transformation
ORE: or expansion

Some days ago, I took a look on a customer’s performance issue which was introduced after their upgrade from 12.1 to 19c. Quite fast we could narrow it down to a new optimizer feature „cost based or expansion“.

To get a basic understanding of the feature, I’ll recommend reading Optimizer Transformations: OR Expansion by Oracle’s CBO PM Nigel Bayliss.

As a quick fix we simply disabled the feature by setting „_optimizer_cbqt_or_expansion“ = off and the performance went back to good again.

Afterwards I wanted to understand the root cause and built a model to reproduce the problem.

rem ######################################
rem # set environment                    #
rem ######################################
alter session set statistics_level=ALL;

rem ######################################
rem # prophylactic cleanup               #
rem ######################################
drop table asc_t2;
drop table asc_t1;
drop table asc_t3;

rem ######################################
rem # create testdata                    #
rem ######################################

--will hold 1M rows with a unique ID
create table asc_t1
as
with gen as
(
   select rownum dummy from dual connect by level <= 1e4
)
select rownum id
  from gen, gen
 where rownum <= 1e6;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t2
as
select id, mod(id, 500) t1_id, lpad('*', 250, '*') pad
  from asc_t1;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t3
as
select id, t1_id, pad
  from asc_t2;

--indexes
create unique index asc_t1_uk on asc_t1 (id);
create unique index asc_t2_uk on asc_t2(id);
create index asc_t2_i1 on asc_t2(t1_id);
create unique index asc_t3_uk on asc_t3(id);
create index asc_t3_i1 on asc_t3(t1_id);

And this is the query to be examined:

select t1.*
  from asc_t1 t1
 where id between 1 and 10
   and exists (select 1
                 from asc_t2 t2, asc_t3 t3
                where t2.id = t3.id
                  and (t2.t1_id = t1.id or t3.t1_id = t1.id)
              );

With no hints or further parameters set, this is the plan including some rowsource execution statistics pulled from memory after its execution:
So at first we can see that ORE was used and the query was split into two disjunct union all branches. What’s really interesting for me at a first sight is the presence of a blocking operation in the VW_ORE block (HASH JOIN at ID 4), despite the fact it is called in an exists clause and therefore has the ability to leave after the first row is found.

So for each row we got from ASC_T1 an in-memory hash table was build after scanning the appropriate index on T2 and visiting the table block (IDs 6 and 5).  In total 20.000 rows on 20.078 buffers were read on these operations. After that index ASC_T3_UK was probed against that hash table and here we see the effects of “exists” very cleary:  despite the index contains 1 million entries just 55 rows over all 10 calls needed to be read to find a first match and therefore be able to quit, because the exists clause was fulfilled. The second union-all branch wasn’t called at all.

Let’s look at two more examples.

First: forbid CBQT-ORE from kicking in.

select t1.*
  from asc_t1 t1where id between 1 and 10 
 and exists (select /*+ no_or_expand */1
                          from asc_t2 t2, asc_t3 t3
                        where t2.id = t3.id
                              and (t2.t1_id = t1.id or t3.t1_id = t1.id)
                            );

And its rowsource execution statistics:
It has got a much lesser cost than the ORE plan and much less buffers were visited to execute the query. It gives also the impression that the CBO is now aware that it has to deal with an “exists” clause here, because that part of the plan was optimized to find a first matching row very quickly. E.g low cost for the FTS on ASC_T2 or the absence of blocking operations are indicators for this strategy.

Second: Switch back to LORE (Legacy OR Expansion)

select /*+ opt_param('_optimizer_cbqt_or_expansion', 'off') */ t1.* 
  from asc_t1 t1
 where id between 1 and 10
    and exists (select 1
                             from asc_t2 t2, asc_t3 t3
                          where t2.id = t3.id
                                and (t2.t1_id = t1.id or t3.t1_id = t1.id)
                            );

Again rowsource execution statistics:
This one now shows the lowest cost and least buffers visited overall. It also seems to be aware of the exists clause and adapts a first_rows strategy in the relevant parts of the execution plan! This is basically what I would have expected from the CBQT-ORE.

So we now have a theory that CBQT-ORE loses track that its query block is called in an “exists” clause and provides an execution plan as if it was a standalone query, where it would need to fetch all the rows.

There’s some more evidence to this theory.

1.) The transformed query from the VW_ORE query block shows the same cost and execution plan when it is costed as a standalone query. The correlated value from the outer rowsource was replaced with a bind variable in the following example:

select 0
  from (
             select 1
                 from asc_t3 t3, asc_t2 t2
              where t2.id = t3.id and t2.t1_id = :b1
            union all
           select 1
               from asc_t3 t3, asc_t2 t2
            where t2.id = t3.id      
                 and t3.t1_id = :b1
                 and lnnvl (t2.t1_id = :b1)               );

generates this plan, which matches the “VW_ORE_82971ECB” from our first query.



2.) The 10053 traces for both the disabled CBQT-ORE and the old-style OR-Expansion (LORE) show lots of references that a first_rows optimization approach was chosen for the query blocks in question.

Like:

On the other hand when looking at the 10053 trace of the first query, interestingly before the ORE checks kicked in, the same plan was already found which was used in query 2, where I explicitly disabled the transformation.

Final cost for query block SEL$2 (#2) – First K Rows Plan:
  Best join order: 1
  Cost: 506.142990  Degree: 1  Card: 2.000000  Bytes: 71964.000000
  Resc: 506.142990  Resc_io: 506.000000  Resc_cpu: 4233492
  Resp: 506.142990  Resp_io: 506.000000  Resc_cpu: 4233492

Later in the same tracefile, the ORE transformation and costing was performed and produced a final cost (8591) which was (way) higher than the formerly calculated plan with a final cost of 506. But however at the end it was not picked and the CBO stayed with the cheapest ORE-plan.

Summary:
CBQT-ORE does not seem to be aware when its query block resides in an “exists” clause and therefore doesn’t optimize for first rows access patterns. The query block gets optimized as if all rows would be needed to fetch. Additionally in certain scenarios it seems to “forget” if cheaper plans were found during the whole optimization process. If this effects you in a negative way, as a first action the cost based transformation can be turned off and the legacy version used instead. Additionally an SR was raised to tackle this issue.

Further Reading:
Excellent articles on the topic of CBQT Or-Expansion can also be found on the blogs of Patrick Joliffe, Mohamed Houri and Nenad Noveljic

Update 1:
After playing with the same testcase in my 21c lab I noticed the that the problem went away. After quick look into v$system_fix_control I found bug 28414968 “expansion with some constant branches;fkr1 in (NOT)EXISTS subque” which is first available in 19.11 and has to be activated proactively. That plan now gets produced in in 21c and after setting alter session set „_fix_control“=’28414968:3′; in >=19.11
10 buffers less visited compared to the LORE plan, as ID 8 was superfluous in the LORE plan.

Funnily, if I code the union-all instead of the or-predicate myself, I still get the old plan. But that’s one topic for another post I guess.

select t1.id id
  from demo.asc_t1 t1
 where t1.id >= 1
   and t1.id <= 10
   and exists (select 0
                            from demo.asc_t3 t3, demo.asc_t2 t2
                         where t2.id = t3.id
                               and t2.t1_id = t1.id 
                         union all
                         select 1
                             from demo.asc_t3 t3, demo.asc_t2 t2
                          where t2.id = t3.id
                                and t3.t1_id = t1.id
                                and lnnvl (t2.t1_id = t1.id)
                            );

Sommerparty Banner

Das war die DBConcepts Sommer-Party 2021

Zum vierten Mal fand heuer wieder unsere legendäre DBConcepts Sommerparty in der La Creperie an der Alten Donau statt. Nach einem langen Jahr der Zwangspausierung haben wir eine Rekordzahl an Anmeldungen für unsere Sommer Party entgegengenommen und über 70 TeilnehmerInnen begrüßen können.

Doch diese Party war nicht nur besonders da es unsere erste nach dem Corona Jahr sein sollte. Gemeinsam mit unseren Kunden, Partnern und Kollegen haben wir das 20jährige Jubiläum der DBConcepts gefeiert, zwar nachgefeiert.. aber zumindest gebührend.

Gemeinsam haben wir einen entspannten Abend mit einigen Highlights verbracht. Als Willkommensgeschenk und gab es heuer nicht nur unsere sehr beliebten Sonnenbrillen, sondern auch ein großartiges Strandtuch mit Unterstützung der Arrow.

Nach Meteorologischer Ansage sollte unsere Tretbootchallenge auch dieses Jahr eher ins Wasser fallen, doch dieses Mal war auch für eine Alternative gesorgt – das erste DBConcepts PubQuiz.

Insgesamt 13 Teams haben es sich zur Aufgabe gemacht, die fünf verschiedenen Themenblöcke in kürzester Zeit zu beantworten. Die Themen rangierten von DBConcepts Wissen über Popkultur bis hin zu geografischen Kenntnissen. Die glorreichen Sieger durften sich über tolle Preise freuen!

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!

Business Continuity Management

Business Continuity Management

Postgresql Cheat Sheets

PostgreSQL Cheat Sheets – Sammlung

Für PostgreSQL finden sich einige sehr nützliche PostgreSQL Cheat Sheets, um im Alltag mit der Datenbank eine schnelle und nützliche 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

Informationen zu unseren professionellen PostgreSQL Managed Services finden Sie hier 

 

 

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.

Das war die Sommer-Party 2018 mit Tretboot-Challenge

Circa siebzig Kunden, Partner, Freunde und Kollegen feierten am 30. August mit uns bei coolen Grooves und köstlichem Grill-Buffet auf unserer Sommer-Party.

Aus der Sommer-Party wurde zur späteren Stunde durch leichten Regen leider eine Sommer-Abschieds-Party.

Die Location an der Alten Donau bot aber auch Indoor genügend Platz für alle Party-Gäste und so nahm die Feier trotz Regen keinen Abbruch.

Auch heuer wurde wieder unsere DBConcepts-Tretboot Challenge ausgetragen, wo 14 tapfere Teams um den Sieg kämpften.

Der Wettergott zeigte sich gnädig mit allen Athleten, sodass erst pünktlich nach dem letzten Team die ersten Regentropfen fielen.

Da es bei der Tretboot-Challenge nur zwei Regeln gibt, blieb für jedes Team genügend Spielraum um die eigene Taktik zu optimieren. Daher sahen wir heuer zum Ersten Mal auch einen waghalsigen Les-Mans Start und viele abenteuerliche Anlegemanöver.

Der Kampfgeist wurde auf jeden Fall bei allen Teilnehmern geweckt und die Pedale bis zum Wadlbrennen getreten.

Schließlich wurden zwei Teams mit der exakt gleichen Siegerzeit von 1:46 gemessen!

Wir gratulieren den beiden Siegerteams „Cyclist“ und “ France“ und auch allen anderen Wettkämpfern, die keine Herausforderung scheuen und den Wettkampf auf sich genommen haben.

Unglaublich wie knapp die Abstände der einzelnen Teams waren. Letztlich lag es meistens nur am Ab- und Anlegemanöver wo wertvolle Sekunden verloren gingen.

Die Rangliste der Challenge finden Sie weiter unten in diesem Blog Beitrag.

Wir freuen uns, dass so viele Kunden, Partner, Freunde und Kollegen trotz Schlechtwetter den Weg zu uns gefunden haben und schönen und lustigen Abend mit uns verbracht haben!

Aus dem Feedback der Teilnehmer können darauf schließen, dass auch nächstes Jahr eine Sommer-Party stattfinden soll 😉

Ein paar Impressionen zur Sommer-Party 2018:

 

 

Rang Team Name Zeit
1 Cyclist 01:46
1 France 01:46
3 Alex&Alex 01:47
4 Deine Mudda 01:54
5 Kittl & Kittl 01:58
6 TurboKurbler 01:59
7 Titanic 02:02
7 Nix überlegt 02:02
9 Spontan 02:06
10 Vergessen 02:07
11 Borgs 02:17
12 Old Stars 02:24
13 AKH01 02:35

see you next year!

Workaround: Unpivot mit ORA-00942 oder ORA-00904

Ausgangslage

Kürzlich trat ein Problem mit einem UNPIVOT Operator in der Entwicklung einer Statistik-Abfrage auf

Select [...]
 from VIEW1 unpivot(val for year in ([...]);
ORA-00942: table of view does not exist

Die Abfrage wurde im gleichen Schema ausgeführt wo auch der View sein sollte. Folgendes Select funktionierte zum Beispiel problemlos:

select * from VIEW1;

Lösung

Eine Recherche ergab anfangs wenig Lösung. Die üblichen Lösungsvorschläge bezogen sich auf Problem mit Arbeiten in unterschiedlichen Schemas oder über Datenbank-Links. Da die Tabelle aber grundsätzlich als vorhanden erkannt wurde musste der Grund woanders liegen. Im Oracle Support habe ich die Lösung gefunden, im Dokument Doc ID 22339954.8.

Laut diesem Dokument handelt es sich hierbei um einen Bug in potentiell allen Datenbankversionen vor 12.2.0.1. Laut diesem Dokument gibt es keinen Workaround. Das Problem kann ich außerdem in langen Laufzeiten äußern selbst wenn keine Fehler auftreten. Die Probleme treten lt. Dokument auf wenn eine Abfrage eine UNPIVOT Operation auf Views mit mehrfachen verschachtelten Abfrageblöcken und/oder einer großen Anzahl von Tabellen macht.

Wir haben einen Workaround gefunden der in unserem Fall das Problem recht leicht und zufriedenstellend gelöst hat:

with base as (select /*+ materialize */ * from VIEW1)
Select [...]
from base unpivot(val for year in ([...]);