CC::Blog

Auch nett…

wer weiss wofuer man es mal gebrauchen kann.

Gegeben sei folgende Tabelle:

SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;

DEPTNO ENAME
------ ----------
    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD

14 rows selected.

Wir moechten jetzt die Employees zu einem department aber in je einer Zeile haben. Man nehme: wm.concat aus wmsys

SELECT deptno,wmsys.wm_concat(ename) as concatenated FROM dept group by deptno;

    DEPTNO CONCATENATED
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
...

Ist doch schick, oder ?

[Update 2017-04-25]: Habe das heute mal wieder gebraucht. wm_concat ist wohl nicht mehr vorhanden. Stattdessen gibt es was offizielles. Funktioniert fast genau so: listagg. Hier ein adaptiertes Beispiel:

SELECT deptno,wmsys.listagg(ename,', ') within group (order by ename) as concatenated FROM dept group by deptno;

Man kann sogar den Delimiter mit angeben (hier ein ,) und sortieren…

Schon mal versucht einen Aggregations-SQL nach anderen Werten als den Format-Strings die mit to_date, to_char benutzt werden duerfen zu bauen ?

Ist garnicht so simpel. Ein

select count(*),attribut,to_char(creationdate,'DD.MM.YYYY')
 from tabelle
 group by attribut,trunc(creationdate,'DD.MM.YYYY')

ist noch fuer jedermann verstaendlich. Auch wenn man nach Stunden gruppieren will, geht das noch schmerzfrei (‘HH’). Was aber wenn man auf 15Minuten Ebene Aggregieren soll ? Ohne Kunstgriff geht es nicht. Hier der to_date String:

 to_date(to_char(sysdate,'DD.MM.YYYY') ||
         ' ' ||
         trunc(to_number(to_char(sysdate,'SSSSS'))/900)*900,
         'DD.MM.YYYY SSSSS')

Was passiert hier ?

Zunächst wird die aktuelle Zeit (im Select ist es natuerlich das zu gruppierende Feld) in Sekunden nach Mitternacht umgewandelt. Nun dividieren wir das Ergebniss durch 900 (15 Minuten = 900 Sekunden) und schnibbeln die Nachkommastellen ab. Anschliessend das ganze wieder mit 900 Multiplizieren, und wir haben jede Erdenkliche Uhrzeit auf die letzte Viertelstunde (ab-)gerundet. (Schlaue Menschen koennten jetzt sagen, Mensch Joerch, nimm doch DIV, MOD gibts unter Oracle schliesslich auch -> DIV gibts leider NICHT). So, jetzt packen wir noch das Datum wieder davor (ist ja durch die Umwandlung in Sek. nach Mitternacht verloren gegangen) und konvertieren das ganze wieder in ein Datum. Fertig ist der Viertelstundenaggregator 🙂 Bezogen auf das obige Beispiel lautet unserer (zugegebenermassen unlesbarerer Spruch) wie folgt:

select count(*),attribut,
 to_date(to_char(creationdate,'DD.MM.YYYY') ||
         ' ' ||
         trunc(to_number(to_char(creationdate,'SSSSS'))/900)*900,
         'DD.MM.YYYY SSSSS')
 from tabelle
 group by attribut,
 to_date(to_char(creationdate,'DD.MM.YYYY') ||
         ' ' ||
         trunc(to_number(to_char(creationdate,'SSSSS'))/900)*900,
         'DD.MM.YYYY SSSSS')

LOBs unter Oracle sind, wie schon an einigen Stellen im Blog hier erwaehnt, wirklich eine suboptimale Storage-Loesung unter Oracle. Vor allem, wenn eine solche Tabelle auch noch alle x Monate bereinigt werden soll. Nach dem entsprechenden DELETE belegt das LOBSEGMENT naemlich immer noch gut Platz auf dem Tablespace. Was bei “normalen” Tabellen ueber ein “ALTER TABLE ENABLE ROW MOVEMENT” mit anschliessendem “ALTER TABLE SHRINK SPACE” geht, ist bei LOBSEGMENTEN nicht ganz so simpel. Und weil ich die beiden Sprueche immer wieder verwechsele, schreibe ich Sie jetzt hier auf:

Gegeben sei ein Table “DATASTORE” mit einem LOB der “FILEDATA” heisst.

Nur Shrinken (Nicht Highwatermark zuruecksetzen, also auch nicht Table “locken”):

SQL> alter table datastore modify lob (filedata) (shrink space compact);

Im Anschluss Highwatermark zuruecksetzen / Bzw. direkt vollstaendig shrinken (VORSICHT: Table wird gelockt):

SQL>alter table datastore modify lob (filedata) (shrink space);

PS: Das ganze laesst sich noch mit einem CASCADE erweitern, was dann bedeutet dass auch unterliegende Objekte mit geshrinkt werden (Indizes, etc.)

Schön dieses RAID. Hat nur einen Schönheitsfehler. Alle Nasen lang (genau genommen ca. alle 3 sekunden) rödeln die Platten ohne ersichtlichen Grund los. Nachdem ich erst das commit-interval des Filesystems im Verdacht hatte, dieses sich aber nicht bestätigt hatte, habe ich mir die, auf dem RAID laufende, ORACLE mal vorgenommen. (Was ein Schachtelsatz ;-))
Bingo!
Anscheinend schreibt die DB in kurzen Intervallen etwas in Ihre control-Files.
Wie verlegt man jetzt aber so ein ControlFile bei einer bereits betankten Datenbank ??
Nun auch hier gibts es eine Lösung. Also noch ein (Superkurzes) Mini-Howto im CCBlog-Style:

1. PFile erzeugen (siehe hier im Blog)
2. DB herunterfahren (shutdown immediate)
3. Control-Files an die neue Stelle bewegen (per mv)
4. PFile editieren. Und zwar die Stelle “controlfiles” – hier der DB die NEUE Position bekanntgeben
5. SPFile bauen (siehe hier im Blog)
6. DB wieder hochfahren (startup).
7. Fertig

Vorsichtshalber habe ich die REDO-Logs auch noch auf die ungeRAIDete Platte verschoben – ist eh schneller 😉

Böse Böse …
Ein netter Tip, der da von Muniqsoft publiziert wurde.
Kurzfassung: Mit Hilfe einer Materialized View, die “on Commit” refreshed wird, und auf die dann via “Query rewrite” zugegriffen wird, grosse Aggregationen etwas zügiger von Statten gehen zu lassen.
Irgendwie ging der Schuss bei mir allerdings VOLL nach hinten los. Die gute DB hat, gemäss Ihrem Auftrag, fein bei jedem Commit einen FULL TABLE SCAN (auf ‘ne halbe Mio. Rows) durchgeführt und damit die DB extremst in die Tiefe gezogen… Na super. Droppen liess sich das gute Stück auch nicht mehr, weil: War ja gelocked durch das andauernde Commit der User 😉
Was lernen wir daraus: Nicht alles was gut aussieht, tut auch gut…

Oha…
Nicht alles wird gut wenn man von einer 10.1 auf 10.2 updaten will. So steigt die Ora-DB bei der Einstellung “CURSOR_SHARING=FORCE” in manchen Fällen nach einem SQL-Statement mit ORA-01008 (not all variables bound) aus, obwohl alle Variablen brav gebunden sind.
Workaround: CURSOR_SHARING auf “EXACT” stellen, sofern möglich. Oder halt auf ein Fixing des Bugs mit der Nummer #5863277 warten (siehe Metalink)

PS: Der Fehler tritt nur mit dem neuesten Patchset, also der 10.2.0.3 auf…

Nachtrag: Es gibt einen Patch bei Oracle im Metalink – Knappe 80kB gross patcht das Teil den Ora-Kernel und danach sollte es wieder tun… Der Fehler trat übrigens nur bei Zugriff via OCI o.ä. auf. Böse wenn man mit DBD::Oracle unter Perl arbeitet.

Über Oracle und Blobs könnte man, glaube ich, Bücher schreiben.
Wieder mal etwas interessantes:

Versucht man von einem Schema, nennen wir es A, Daten in eine Blob-Tabelle, sagen wir B.B_TABLE mit Perls DBD/DBI zu “inserten”, quittiert Oracle dies mit einem netten ORA-010321 (insufficient privileges).
Kurz nachgesehen: Hmm, A verfügt über INSERT-Grants für B.B_TABLE. Wer jetzt anfängt den Fehler an anderer Stelle zu suchen, hat schon verloren.

User A benötigt ZWINGEND das UPDATE-Recht auf B.B_BLOB Table. Dann klappts auch mit dem Inserten…