Sep 24

IN-Listen lassen sich bei einer Oracle leider nicht als Bind-Var übergeben. Wirklich nicht ? Doch, geht mit Trick:

SELECT   REGEXP_SUBSTR (:txt,'[^,]+',1,LEVEL) as token
   FROM   dual
   CONNECT BY   LEVEL <= LENGTH (:txt) - LENGTH (REPLACE (:txt, ',', '')) + 1
									

Durch den RegEx und das Connect by Prior macht die ORACLE eine Tabelle aus jedem Element, welches in :txt übergeben wird. Getrennt wird per „,“. Einsetzen lässt sich das ganze dann wie folgt:

SELECT * FROM emp WHERE emp_id IN (SELECT   REGEXP_SUBSTR (:txt,'[^,]+',1,LEVEL) as token
   FROM   dual
   CONNECT BY   LEVEL <= LENGTH (:txt) - LENGTH (REPLACE (:txt, ',', '')) + 1)
									

Nicht schön, aber funktional; Und am performantesten von all‘ den Workarounds, die ich bisher so gesehen habe.

Tagged with:
Okt 26

Kennwort unter Solaris „expired“ ?

Beim Login per ssh per key-auth kommt nix, und bei einem su – xxx kommt die Meldung „Password for user ‚xxx‘ has expired – use passwd(1) to update it“ ? Da gibts eine Loesung:

Einfach auf der Shell (als anderer User) das Tool „login“ aufrufen, mit den „alten“ Credentials einloggen, und schont kommt der Password-Change-Prompt. Gut versteckt das ganze !

Tagged with:
Feb 17

Gegeben sei folgende Tabelle:

Wert Orderer
A 10
B 20
C 30
D 40
E 50
F 60
G 70
H 80
I 90

Nun mal angenommen folgendes möchten wir irgendwie aus der DB bekommen (Um eine 3 spaltige Tabelle mit tr,td aufzubauen:

Wert Wert Wert
A D G
B E H
C F I

Dann benötigen wir die Daten ja in folgender Reihenfolge:

A,D,G,B,E,H,C,F,I

Nach langem herumprobieren habe ich alle Versuche über Bord geworfen, und bin zu folgendem, suboptimalen (aufgrund der Subselects), Ergebnis gekommen:


SELECT wert
FROM (SELECT wert,
x,
ROW_NUMBER () OVER (PARTITION BY x ORDER BY orderer) AS y
FROM (SELECT wert,
orderer,
NTILE (:3) OVER (ORDER BY orderer) AS x
FROM tabelle))
ORDER BY y, x

Hat jemand einen besseren Vorschlag… Ich meine: Funktionieren tut das ganze (In BIND-Variable 3 wird die Anzahl der Spalten übergeben). Aber Sexy geht irgendwie anders…

Tagged with:
Okt 07

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…

Sep 03

Ein Update des DBD::Oracle Treibers von 1.16 auf 1.21 klingt zunaechst unspektakulaer. Aber wie so oft steckt der Teufel im Detail. Benutzt man nämlich persistente Verbindungen unter mod_perl mit dem Modul Apache::DBI, ändert sich das Verhalten beim disconnecten von der DB. In der Regel cached das mod_perl die Verbindungen ja, daran hat sich auch nichts geaendert.

Bei der 1.16er Version jedoch impliziert das disconnect ein commit. Das hängt laut Modul-Doku von gutdünken des (Modul-)Entwicklers ab. Hat man seine Applikation jetzt darauf ausgerichtet, dann macht man nie ein Commit (sollte man nicht tun !). Dumm nur, dass dieses Verhalten bei der 1.21er geändert wurde. Nun impliziert das Modul beim disconnecten nämlich ein Rollback. Und das macht es selbst dann, wenn ein commit vorrausgeht.

Im ersten Ansatz ist das verschmerzbar. Wenn die DB allerdings, selbst bei Skripten die nur SELECTen, jedesmal mit einem Rollback belaestigt wird, kommt da schon einiges zusammen. Das Verhalten lässt sich mit folgendem „Workaround“ aendern:

Aus einem $dbh->disconnect() wird ab sofort ein

$dbh->{AutoCommit}=1;
$dbh->disconnect();

Das AutoCommit wird also kurz vorm disconnecten gesetzt, und beeinflusst das sonstige Rollback/Commit-Verhalten im gesamten Skript ueberhaupt nicht. Sobald ein neues Connect (auch in einer persistenten mod_perl Umgebung) kommt, werden (auch bei gecachter DB-Verbindung) die Parameter AutoCommit, etc., eh neu gesetzt. Damit verhält sich alles wieder wie vorher.

Aug 28

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')
Apr 16

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.)

Nov 19

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 😉

Apr 14

Oracle-Jobs via DBMS_JOB sind eine feine Sache. Würde nur nicht das Intervall erst am Ende, also nach der Ausführung des Jobs, gesetzt werden.
Das nervt. Also wenn man einen Job hat, der sagen wir alle 10 Minuten laufen soll, so verschiebt sich die Ausführung grundsätzlich um die Zeit, die der Job selbst zur Abarbeitung benötigt. Das heisst, bei einem 60sek. Job, der via Intervall „sysdate+(1/1440)“ alle 10min laufen soll, und um, sagen wir 10:10 das erste mal rennt, beginnt dieser die nächsten Ausführungen um 10:21, 10:32 usw…

Abhilfe schafft ein wenig Mathematik im Intervall:

Man nehme:
to_date(
floor(
to_number(
to_char(sysdate,’YYYYMMDDHH24MI‘)
)/10
)*10,’YYYYMMDDHH24MI‘
)+10/1440

Was passiert hier ?
– Das Datum wird MSB -> LSB geholt und durch 10 geteilt. Bedeutet die „Minute“ steht hinter dem Komma.
– Jetzt nutzen wir „FLOOR“ um alles hinter dem Komma auf „0“ zu setzen.
– Das ganze wieder mal 10 nehmen, um die 0 vor das Komma zu bekommen
– Jetzt noch in ein Datum konvertieren und das 10min. Intervall (+(10/1440)) draufschlagen
– Fertig.

Doch Vorsicht: Bei Jobs die länger als 10min. laufen, könnte das nächste Intervall in der Vergangenheit liegen ;-(

Apr 11

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…

Mrz 29

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.

Mrz 07

Ü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…

Jan 31

Meist muss man Oracle DBs an der Basis nach-optimieren (Das Speichermanagement in der Grundeinstellung ist grausig).
Da man das nicht jeden Tag macht, vergisst man schonmal die Basics. Ich zumindest muss immer wieder nachschlagen, wie ich an die config überhaupt herankomme. Daher nun ein mini-Howto für SPFile gesteuerte Oracles:

# sqlplus /nolog

> connect sys as sysdba
> create pfile from spfile;

– Jetzt Pfile (liegt in $ORACLE_HOME/dbs) editieren und anpassen

# sqlplus /nolog

> connect sys/password@DB as sysdba
> shutdown
> create spfile from pfile;
> startup

Und alles wird gut. Ist zwar ein extrem kurzer Weg, sollte aber 99% aller Fälle reichen. Der Sicherheit halber sollte man sich vor dem Editieren eine Sicherheitskopie vom PFILE ziehen.

Good luck…

Jan 17

ist ein Problem. Derzeit gibt es nämlich den Oracle Client lediglich für PPC basierte Macs.
Wie es trotzdem funktioniert verraten folgende Links:

Schliesslich und am Ende stösst der Kompiliervorgang von DBD::Oracle trotzdem auf Fehler, die aber zu beheben sind, in dem man im Makefile.PL des Modules in der Zeile 1400 (Version 1.19) den Pfad des instant-Clients hinzufügt.

Danach sollte es sich dann kompilieren lassen. So ganz rund läuft die Sache dann immer noch nicht, da man nun ein Rosetta-Perl mit DBD gebaut hat, alle anderen Perl-Module aber immer noch iNTEL-Binaries sind. Sprich: Entweder man benutzt das DBD::Oracle und nichts anderes, oder alles andere, aber kein DBD::Oracle…
Ist irgendwie suboptimal. Da hilft es dann nur noch auf eine iNTEL-Oracle Version zu warten.

Nov 27

Da habe ich vorhin ein LOB-Table angelegt und dachte es wäre alles gut. War es aber nicht. Trotz einem halben GB „geINSERTeter“ Daten wollte der Tablsespace so gar nicht voll werden.

Ein flockiges

CREATE TABLE BLOB_TABLE
(
ZEITPUNKT DATE,
DESCRIPTION VARCHAR2(30 BYTE),
DATEI BLOB
)
TABLESPACE MYSPACE

heisst noch lange nicht, das die Dateien, die in „DATEI“ abgelegt werden auch auf den Tablespace „MYSPACE“ geschrieben werden. Nein nein … Hier mag es die DB gerne etwas genauer, der Zusatz

LOB (DATEI) STORE AS (TABLESPACE MYSPACE)

wirkt hier nämlich ware Wunder. Ohne diesen legt die olle DB das Ding die BLOBs (und nur diese) im Default-Tablespace ab. Das kann zu Vewirrungen führen, muss es aber nicht (in meinem Falle schon). Ist das Kind in den Brunnen gefallen, sprich man hat die Tabelle schon befuellt und stellt das Desaster hinterher fest, hilft folgendes:

ALTER TABLE BLOB_TABLE
MOVE LOB(DATEI) STORE AS (TABLESPACE MYSPACE)


Hier gilt allerdings: Genügend Zeit, Kaffee usw. mitbringen, je nach Tabellengröße kann das dauern (muss es aber nicht) 😉

preload preload preload