Ab der Version Oracle 21c wird zusätzlich zum alert.log noch eine neue Datei ausgeliefert: das Attention.log. Hier sollen wichtige Information als Zusammenfassung der Alert.log stehen.
Naja, ich hoffe das wird nicht etwas verbessert, aber trotzdem wäre es praktisch die Datei mit einem Select auszulesen. Ich hatte gehofft, jemand hat sich die Arbeit schon gemacht (Google wo bist Du, wenn man Dich braucht…), aber leider habe ich nichts gefunden.
Ok, dann mach ich es halt …
Wir geben die Objekte dem Benuter system, sie können aber natürlich auch einen anderen Benutzer dafür einrichten.
ALTER SESSION SET current_schema=system; col adir new_value adir col afile new_value afile with diag as (select sys_context('userenv','PLATFORM_SLASH') as ps,value from v$diag_info where name='Attention Log') select substr(value,1,instr(value,ps,-1)-1) as adir, substr(value,instr(value,ps,-1)+1) as afile from diag;
CREATE OR REPLACE DIRECTORY attention_dir as '&adir.'; GRANT READ ON DIRECTORY attention_dir to system;
CREATE OR REPLACE TYPE attention_type AS OBJECT ( NOTIFICATION VARCHAR2(4000), ERROR VARCHAR2(4000), URGENCY VARCHAR2(4000), INFO VARCHAR2(4000), CAUSE VARCHAR2(4000), ACTION VARCHAR2(4000), CLASS VARCHAR2(4000), TIME TIMESTAMP WITH TIME ZONE ); /
CREATE OR REPLACE TYPE attention_tab_type AS TABLE OF attention_type; /
CREATE OR REPLACE FUNCTION read_attention_log RETURN attention_tab_type PIPELINED IS f_handle utl_file.file_type:=utl_file.fopen( location=>'ATTENTION_DIR', filename=>'&afile.', open_mode=>'r', max_linesize=>32767); text varchar2(32767); v_a_log attention_type:=attention_type(null,null,null,null,null,null,null,null); v_i INT:=1; BEGIN LOOP BEGIN utl_file.get_line(f_handle,text); -- Neue Zeile lesen IF substr(text,1,16)=' "NOTIFICATION"' THEN v_a_log.NOTIFICATION:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,9)=' "ERROR"' THEN v_a_log.ERROR:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,11)=' "URGENCY"' THEN v_a_log.URGENCY:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,8)=' "INFO"' THEN v_a_log.INFO:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,9)=' "CAUSE"' THEN v_a_log.CAUSE:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,10)=' "ACTION"' THEN v_a_log.ACTION:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,9)=' "CLASS"' THEN v_a_log.CLASS:=rtrim(rtrim(substr(text,21),','),'"'); END IF; IF substr(text,1,8)=' "TIME"' THEN v_a_log.TIME:=to_timestamp_tz(rtrim(substr(text,21),'"'),'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'); PIPE ROW (v_a_log); v_a_log :=attention_type(null,null,null,null,null,null,null,null); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; -- Keine Zeile im Attention.log mehr gefunden oder Fehler=> Schleife verlassen WHEN OTHERS THEN RAISE; END; END LOOP; utl_file.fclose(f_handle); END; /
Un nun können Sie das attention.log via SQL auslesen …
SELECT * FROM table(read_attention_log);
Haben Sie sich auch schon darüber geärgert, dass gerade Oracle keine Boolean Datentypen in SQL erlaubt?Wenn Sie also eine PL/SQL Funktion besitzen, die nur TRUE oder FALSE zurückgibt, muss man sich einen Wrapper in PL/SQL schreiben.
Aber in unserem Tipp des Monats, bekommen wir das mit einem Trick auch nur in SQL hin.
Sagen wir mal, wir hätten eine Funktion wie diese hier:
Dann könnten wir mit einer einfachen WITH Klausel das auch in reinem SQL hinbekommen.
Viele weitere Tipps & Tricks bekommen Sie in einem unserer bewährten PL/SQL Kurse (PL/SQL, PL/SQL II, PL/SQL Packages, ...)
Ich liebe das Package dbms_output, wenn es nur nicht ...
hätte.
Aber den letzen Punkt können wir ändern. In diesem Tipp holen wir die Daten, die wir in den Puffer von dbms_output geschrieben haben und legen Sie woanders hin.
##################################################Umleitung von dbms_output zu pipe##################################################
CREATE OR REPLACE PROCEDURE get_dout_from_pipe (pipe_name IN VARCHAR2 DEFAULT 'DBMS_OUTPUT_PIPE')
IS
v_message VARCHAR2(32767);
v_timeout NUMBER:=120;
BEGIN
IF (DBMS_PIPE.receive_message(pipe_name,v_timeout)) <> 0 THEN
RAISE_APPLICATION_ERROR(-20501,'Fehler beim Lesen aus Pipe '||pipe_name||' aufgetreten !');
END IF;
LOOP
EXIT WHEN DBMS_PIPE.NEXT_ITEM_TYPE = 0;
DBMS_PIPE.unpack_message(v_message);
DBMS_OUTPUT.PUT_LINE(v_message);
END LOOP;
END;
/
##################################################Umleitung von dbms_output zu htp##################################################
CREATE OR REPLACE PROCEDURE test_ausgabe IS PROCEDURE dop2htp IS lines dbms_output.chararr; num_lines number:=1000000; BEGIN dbms_output.get_lines(lines, num_lines); FOR i IN 1..num_lines LOOP htp.p(lines(i)||'<BR>'); END LOOP; END; BEGIN dbms_output.enable(null); FOR i IN 1 .. 10 LOOP dbms_output.put_line('Zeile='||i); END LOOP; dop2htp; -- <=######## Umwandelung von dbms_output in htp.p Ausgabe END;
Ich hoffe dies konnte Ihnen weiterhelfen, bei sonstigen Fragen melden sie sich gerne bei uns und oder besuchen sie einen der Kurse. :-)
Oracle hat das Berechtigungskonzept zu PL/SQL in Hinblick auf zwei gegensätzliche Szenarien in 12c ausgebaut:
Die Änderung zu diesem Szenario (Vergabe von Rollen) betrifft Prozeduren, die mit Invoker Rights arbeiten oder mit dynamischem SQL.
Beiden Neuerungen gemeinsam ist, dass es um die Überprüfung von Rechten zur Laufzeit geht. Auch weiterhin benötigt ein Entwickler alle entsprechenden Berechtigungen selber, um eine Prozedur erfolgreich kompilieren zu können. Dynamisches SQL allerdings wird zur Kompilierzeit nicht ausgewertet.
Hinweis: Hier und im weiteren ist "Prozedur" als Sammelbegriff zu verstehen, der auch Funktionen und Packages mit einschließt.
Hier kommt Szenario 1 ins Spiel:
Angenommen, der User ADMIN hat weitreichende Rechte.
Weiter angenommen, der Programmierer SCOTT hat vergleichsweise wenig Rechte.
Nun schreibt SCOTT eine Prozedur mit Invoker Rights (bei Definer Rights spielt das neue Privileg keine Rolle), die er ADMIN zur Verfügung stellt. Solange es sich um statisches SQL handelt, kann nicht viel passieren, da ja SCOTT's Berechtigungen zur Compile-Zeit überprüft werden.
Nun könnte aber SCOTT bösartigerweise Befehle in dynamisches SQL verpacken, die weit über seine eigenen Berechtigungen hinausgehen (z. B. an sich selbst Admin-Rechte vergeben), nicht jedoch über diejenigen von ADMIN. SCOTT kann so eine Prozedur problemlos erstellen - aber nicht ausführen. ADMIN dagegen kann sie ausführen.
Um einen solchen Missbrauch ggf. unterbinden zu können, wurde mit Version 12c die Berechtigung INHERIT [ANY] PRIVILEGES eingeführt. Das Konzept dabei sieht folgendermaßen aus:
Der Eigentümer der Prozedur (in obigem Szenario SCOTT) braucht, sofern er nicht über das Systemprivileg INHERIT ANY PRIVILEGES verfügt, von dem Benutzer, der die Prozedur später ausführen soll - also hier ADMIN - , das Objektprivileg INHERIT PRIVILEGES. Hat er das nicht, so kann ADMIN später die Prozedur nicht ausführen.
Damit sich das Verhalten zwischen 11g und 12c nicht ändert, erhält PUBLIC automatisch für jeden neu angelegten User dieses Recht. Das können Sie leicht nachprüfen über dba_tab_privs:
Oracle empfiehlt jedoch PUBLIC diese Rechte zu entziehen.
Für das oben beschriebene Szenario bedeutet dies:
Hat PUBLIC das Recht INHERIT PRIVILEGES ON USER ADMIN, wie das dem Default entspricht, so wird ADMIN die Prozedur von SCOTT ausführen können, wie auch schon in 11g. Entzieht ADMIN dieses Recht mit
und vergibt das Recht auch nicht explizit an SCOTT, dann kann er keine Prozedur von SCOTT mehr ausführen, die mit INVOKER RIGHTS angelegt wurde. Beim Versuch erhält er die Fehlermeldung
Szenario 2 sieht so aus:
User SCOTT stellt wiederum Invoker Rights-Prozeduren zur Verfügung, in diesem Fall für den User LEHRLING. Eine der Prozeduren greift auf ein Tabelle im Schema SCOTT zu. SCOTT will aber nicht, dass LEHRLING direkten Zugriff auf diese Tabelle bekommt. Den bräuchte LEHRLING aber bis einschließlich Version 11g, um diese Prozedur ausführen zu können.
SCOTT erstellt z. B. folgende Funktion:
LEHRLING wird diese Funktion bis jetzt nicht erfolgreich ausführen können:
In Version 12c stellt nun ein DB-Administrator SCOTT eine Rolle zur Verfügung:
SCOTT vergibt das benötigte Select-Recht and die Rolle:
Und die Rolle an die Funktion(!):
Nun kann LEHRLING die Funktion erfolgreich ausführen ohne direkten Zugriff auf SCOTT.dept zu erhalten.
An dem Grundprinzip, dass Rollen in PL/SQL unwirksam sind, ändert sich nichts: Eine Prozedur kann auch weiterhin nur dann erfolgreich kompiliert werden, wenn ihr Eigentümer alle dafür erforderlichen Rechte DIREKT gegrantet bekommen hat.
Ein Beispiel zur Anwendung dieses Rollenkonzepts bei Definer Rights und dynamischem SQL hat Tom Kyte in seinem Blog beschrieben.
Wer je Daten für eine Web-Applikation bereitstellen musste, kennt das Problem der Pagination. In der Regel holt das Frontend ja nur die Daten, die auf eine Seite passen, und erst wenn mehr angefordert werden, werden auch mehr geholt. Das stellt den Programmierer vor die Schwierigkeit, die Daten entprechend "mundgerecht" zu liefern. Entscheidend dabei ist in jedem Fall eine absolut eindeutige Sortier-Reihenfolge.
Ein klassischer von Tom Kyte u. a. hier Öffnet externen Link in neuem Fenster hier beschriebener Ansatz über ROWNUM sieht dann so aus:
Anmerkung: Für die folgenden Beispiele wurde eine Tabelle OBJECT_TAB als Kopie von ALL_OBJECTS erstellt.
Angewendet auf diese Tabelle sähe ein Select, der die Zeilen 11 bis 20 holt, z. B. so aus:
Der Ausführungsplan zeigt, dass der Optimizer hier mit Stopkeys arbeitet:
Anmerkung: Um Zeilenumbrüche in der Darstellung zu verhindern, wurden überflüssige Leerzeichen und Schema-Angaben aus dem Ausführungsplan entfernt.
Im PL/SQL-Umfeld würde eine entsprechende Prozedur - stark vereinfacht - z. B. so aussehen:
Da man jedoch gerade bei Suchmasken in der Regel mit dynamischen SQL arbeiten muss, ist das wohl etwas näher dran:
Das funktioniert zwar, ist aber nicht direkt intuitiv.
Mit Version 12c hat nun Oracle eine neue Klausel zur Begrenzung der Zeilen eingeführt, die die Syntax in solchen Fällen deutlich vereinfacht.
Angegeben werden können dabei:
Das obige Beispiel würde in 12c so aussehen:
Glaubt man dem Ausführungsplan, so ist diese Abfrage nicht nur wesentlich lesbarer, sondern auch noch performanter:
Anmerkung: Auch hier wurden überflüssige Leerzeichen und Schema-Angaben aus dem Ausführungsplan entfernt.
Eine Prozedur analog zu oben würde dann so aussehen:
Wesentlich lesbarer, oder?
Welche Möglichkeiten bietet die neue Klausel noch? Neben dem oben angegebenen <n> ROWS kann auch ein Prozentsatz mitgegeben werden mit <n> PERCENT ROWS. Das würde dann so aussehen:
Sollte die Sortierung nicht eindeutig sein (und auch nicht sein müssen), so kann man angeben, dass alle Datenätze mit ausgegeben werden sollen, die den gleichen Wert haben wie der zuletzt geholte. Dazu gibt man statt ONLY an WITH TIES. Der Effekt sei hier gezeigt an der allseits bekannten Tabelle SCOTT.EMP:
Diese neue Klausel ist prinzipell nicht abhängig von der ORDER BY-Klausel. Ihre vollen Möglichkeiten entfaltet sie aber nur hier.
Mehr zu diesem Thema erfahren Sie in unserer Schulung Oracle Neuerungen 12c, schauen Sie doch einfach vorbei :-)
Die Parallelisierung von größeren DELETE- und UPDATE-Aktionen bietet diverse Vorteile:
Wenn man selber prozedurale Lösungen zum parallelen Löschen oder Aktualisieren großer Datensatzmengen erstellen will, wird das allerdings schnell mühsam, weil man erst einmal sicherstellen muss, dass die Pakete so aufgeteilt werden, dass sich die einzelnen Transaktionen nicht gegenseitig behindern.Ab der Oracle Version 11 Release 2 gibt es eine Oracle-eigene Lösung für die Parallelisierung von DML-Aktionen über den Scheduler, die den Anwendern sehr viel Arbeit abnehmen kann, das Package DBMS_PARALLEL_EXECUTE.In diesem Fall werden die Tabellen über einen DBMS_SCHEDULER-Prozess automatisch in Teilbereiche, sog. Chunks, unterteilt (die aber nichts mit den Chunks bei der Speicherung von LOBs zu tun haben). Auf diese Abschnitte werden die DML-Befehle parallel abgesetzt. Ein COMMIT erfolgt nach jeder Fertigstellung des DML-Befehls auf dem jeweiligen Tabellenbereich. Dadurch werden die Undo-Segmente weniger stark belastet.Um das Fehlerlogging und automatische Wiederholungen im Fehlerfall kümmert sich ebenfalls der Scheduler.Die einzige Voraussetzung für die Nutzung ist das CREATE JOB-Recht. Das Package an sich ist an Public gegrantet. Im Gegensatz zu den meisten anderen Optionen, die mit Parallelisierung zu tun haben, ist die Nutzung des Packages nicht auf die Enterprise Edition beschränkt!!Das Package besteht aus folgenden Prozeduren, die alle einen Commit beinhalten.
Die Funktion TASK_STATUS kann zum Monitoren und zur Fehlerbehandlung genutzt werden.
Probieren's wir mal aus. Für den ersten Test nehmen wir Tom Kytes bewährte Tabelle Initiates file downloadbig_tab mit 2 Millionen Datensätzen. Weil wir später die Session_ids bei der parallelen Ausführung ermitteln wollen, benennen wir die data_object_id-Spalte in session_id um.
Zunächst braucht Scott das Create Job-Recht:
Jetzt kann Scott einen Task erstellen. Der Taskname ist einfach ein VARCHAR2-Parameter. Er muss nicht den Regeln für Oracle-Bezeichner entsprechen:
Ob die Erstellung geklappt hat, kann man über die View USER_PARALLEL_EXECUTE_TASKS herausfinden:
Falls einem grad kein passender Name einfällt, kann man die Funktion GENERATE_TASK_NAME verwenden, die eine interne Sequenz hochzählt:
Der nächste Schritt ist die Unterteilung der Tabelle. Wenn der Parameter by_row auf TRUE gesetzt wird, bezieht sich die chunk_size auf die Anzahl der Zeilen, wenn er auf FALSE steht, auf die Anzahl der Blöcke.Optimale Werte für die chunk_size muss man selber ermitteln. Je kleiner die chunk_size, desto schneller sind die Tabellenabschnitte wieder frei von Sperren:
Genauere Informationen über die einzelnen Abschnitte liefert die View USER_PARALLEL_EXECUTE_CHUNKS:
Jetzt folgt die eigentliche Ausführung:In diesem Beispiel werden 10 parallele Prozesse (Scheduler-Jobs) gestartet, die sich jeweils einen der nicht zugeordneten (unassigned) Abschnitte vornehmen, die über den Parameter sql_stmt vorgegebene DML-Anweisung durchführen, festschreiben und zum nächsten freien Chunk übergehen. Wenn man z. B. nur 4 Prozessoren hat, laufen die Jobs natürlich teilweise seriell.Der Quotation-Operator (q'[...]') erlaubt die Schreibweise ohne Maskierung der inneren Hochkommata:
Die Bindvariablen :start_id and :end_id beziehen sich auf die erste bzw. letzte Rowid jedes Chunks.Der Eintrag der Session_id über die Sys_constext-Funktion ermöglicht auch nachträglich, festzustellen, welcher Anteil der Zeilen in welcher der parallelen Sessions erledigt wurde:
Ob alles glatt gegangen ist, erfährt man über die Data Dictionary-Views:
Aufschlussreich ist auch die Scheduler-DD-View user_scheduler_job_run_details. Hierfür muss man nur das Job-Präfix aus der View user_parallel_execute_tasks für die Job-Namen einsetzen:
Nach der erfolgreichen Ausführung kann man den Task löschen:
Was macht man, wenn bei der Prozessierung Fehler auftreten? Wir bauen ein paar Fallen in die Tabelle ein. Die Spalte object_type ist vom Datentyp VARCHAR2(19). Während des Updates wird der Wert mit einem Sternchen konkateniert, also muss ich nur ein paar Einträge verlängern, so dass es während der DML-Aktion kracht:
In der PL/SQL Packages and Types Reference findet sich ein Beispiel für die Ausführung der Prozedur incl. Fehlerbehandlung. Statt RUN_TASK wird hier die Prozedur GET_ROWID_CHUNK eingesetzt:
Der Export von Tabellen als csv-Files ist immer wieder ein Thema in unseren PL/SQL- und Packages-Kursen, weil die meisten gerne mit Excel arbeiten. Deshalb gebe ich unseren Teilnehmern immer eine einfache Prozedur mit, über die man Inhalte beliebiger Tabellen (solange sie keine LOB-Spalten oder ähnliches enthalten) mittels UTL_FILE als semikolon-separierte Ascii-File exportieren kann. Der Einsatz von UTL_FILE ist nicht der schnellste Weg, aber man kann diese Prozeduren sehr gut in Datenbank-Jobs einbinden oder für andere PL/SQL-Programme verwenden.
Bei Tom Kyte findet man übrigens eine schöne Übersicht seiner diversen Export-Utilities. Für seine PL/SQL-Funktion dump_csv verwendet er DBMS_SQL
Das Problem ist allerdings: Mit den üblichen Beispieltabellen wie scott.emp oder all_objects klappt das alles natürlich prima, aber wie sieht's denn aus, wenn man "historisch gewachsene" Tabellen mit fast 100 oder mehr Spalten vor sich hat, in denen wegen der Verwendung von CHAR-Datentypen u. U. viel heiße Luft gespeichert ist? Deshalb wollte ich mal ausprobieren, wie man einserseits die kleine Prozedur ausbauen müßte, um solche Problemfälle in den Griff zu kriegen und welche Möglichkeiten es gibt, das Ganze schneller zu machen.
Von Adrian Billington stammt ein sehr lesenswerter Artikel von 2008: PL/SQL-File-IO über verschiedene Ansätze, die einfache Ausgabe über UTL_FILE zu beschleunigen und Performance-Vergleiche zwischen dem Einsatz von UTL_FILE auf der einen und der Kombination von DBMS_LOB und DBMX_XSLPROCESSOR auf der anderen Seite. Seine Codebeispiele für UTL_FILE habe ich leicht abgewandelt, in meine alte Exportprozedur eingebaut und mit verschiedenen Tabellen getestet.
Die Erstellung der Spaltenlisten wird in eine Funktion ausgelagert. Die TRIM-Funktion macht beim späteren Export die CHAR- und VARCHAR2-Spalten-Inhalte "schlanker", indem sie Leerzeichen von beiden Seiten löscht.
Diese Prozedur exportiert den Inhalt einer Tabelle als csv-File (mit der Angabe von Datum und Uhrzeit). Das Directory muss natürlich existieren und der Ersteller der Prozedur muss Lese- und Schreibrechte darauf haben.
In der folgenden Prozedur habe ich Adrian Billingtons Idee zur Zwischenspeicherung der Zeilen in einer 32KB-VARCHAR2-Variable verwendet. Erst wenn der Puffer voll ist, wird die Zeile geschrieben. Vor allem für größere Tabellen macht das einen Unterschied.
Und jetzt Billingtons Turboversion mit Parallel-Antrieb (etwas vereinfacht). Die parallele Ausgabe funktioniert nur mit einer Table Function und ist deshalb etwas komplexer. Zudem ist die Parallelisierung nur bei der Enterprise-Edition möglich.
Statt mit einem Cursor durch die Ergebnismenge zu laufen, wird hier eine nested Table verwendet, die in Portionen von je 100 Zeilen befüllt wird (Dies allein bringt noch keinen Geschwindigkeitsgewinn, da auch bei der Ref Cursor-Version im Hintergrund ein Bulk Collect von je 100 Zeilen durchgeführt wird). Diese 100 Zeilen werden dann über die Puffermethode ins File geschrieben, dann holt sich die PL/SQL Engine die nächsten 100.Nach dem Schließen des Files wird als Dummy ein Sequenzwert über PIPE ROW ausgegeben, damit die Funktion einen Rückgabewert hat.
Der Workspace Manager dient zur Versionierung von Tabellendaten in verschieden Versionen (Workspaces)Vorteile
Vorbereitungen:
Sie können eine Administrationsrolle mit allen Rechten für die Workspacebearbeitung vergeben:
Folgende Einzelrechte können vergeben werden:
Beispiel:
Folgendes gilt für die Tabellen:
Beispiel:Tabelle für den Workspace Manager aktivieren:
Zwei Workspaces einrichten:
In den ersten Workspace wechseln:
Durchführen von Änderungen in der Tabelle emp im Workspace my_workspace_1:
In den Haupt-Workspace wechseln:
Dort ist die Tabelle in Ihrem Ursprungszustand zu sehen (ohne die 3 DML Änderungen)In welchem Workspace sind wir gerade?
Die Live Tabelle kann nun auf die Workspace Tabellen-Variante refreshed werden:
Oder die Workspace Variante wird auf Live synchronisiert:
Die Tabelle kann wieder aus der Versionsverwaltung herausgenommen werden durch:
Mit der Option FORCE wird das Kommando auch mit geänderten Workspacedaten durchgeführt, sonst erhält man einen Oracle Fehler:
Zum Löschen eines Workspace verwenden Sie:
Weitere Informationen zum Workspace Manager erhalten Sie in unserem PL/SQL II Kurs.
Wer schon mal mit dem JSON Date Datentyp gearbeitet hat, ist sich dessen Problemen sicherlich bewusst.Wir wollen uns im nachfolgenden Atrikel dem Problem mal annehmen:Nehmen wir mal die in Oracle 21c neue eingeführte Funktion json_scalar und json_serializeJSON_SCALAR wandelt einen Text/ ein Datum oder eine Anzahl in eine interne BLOB Repräsentanz.
Wenn wir das zurückwandeln passiert folgendes:
Da fallen einem gleich zwei Problemzonen ins Auge:1. "" am Anfang / Ende2. ein T zwischen Datum und UhrzeitDer erste Versuch scheitert deswegen auch:
Das liegt an der etwas eigenartigen Behandlung von Gänsefüßen bei Oracle Strings
oder auch
auch das geht schief:
Deshalb lösen wir das Problem mit zwei Funktionen:1. Wir ersetzen "" durch nix2. Wir konvertieren den String mit Hilfe der Funktion to_date und dem Format String 'YYYY-MM-DD"T"HH24:MI:SS'
Weitere Tipps erhalten Sie in einem unserer Oracle Kurse...
In unseren Kursen stöhnen die Teilnehmer häufig über Oracles eigene Arrays, die man Collection nennt.Der Vorwurf lautet:
Aber eigentlich sind sie doch ganz schön. Ein paar Beispiele sollen das verdeutlichen:
1. Associative Array vom Typ String wird mit drei Werten gefüllt (und wieder ausgegeben):
Nun schreiben wir die Daten aus dem Array in eine Tabelle:
Beispiel 2: ASCII Tabelle:
Basis des Arrays ist nun ein RECORD:
Basis des Records ist eine Tabellenstruktur
Es kann auch ein VARCHAR2 Wert als Index benutzt werden:
Beispiel Bulk Cursor mit SAVE EXCEPTIONS:
Eigentlich hat doch schon ein jeder PL/SQL-Entwickler (und natürlich auch jede Entwicklerin ) sich über das Package dbms_output.put_line geärgert.
Denn:
Ok, schauen wir mal, welche Probleme wir lösen können:
Oder als eigenständige Prozedur:
Umleitung von DBMS_OUTPUT zu PIPE: Damit können Sie am Ende des Programms einen Aufruf platzieren, der den DBMS_OUTPUT Puffer ausliest und in eine Pipe schickt. In einer anderen Session kann dann die Ausgabe erfolgen.
Umleitung von dbms_output zu htp
DBMS_OUPUT in CLOB speichern
Weitere Tipps & Tricks erhalten Sie z.B. im PL/SQL, PL/SQL II und PL/SQL Packages Kurs.
Wer hat nicht schon mal vor dem Problem einer Datumskonvertierung bei Oracle Datenbanken gestanden?Da kann sehr viel Zeit verstreichen, bis man eine Lösung gefunden hat. Wir wollen Ihnen hier ein Paar Fallstricke erklären.
Das hängt vom Parameter nls_date_format ab, der auf Clientseite im Betriebssystem gesetzt wird.
z.B. können Sie unter DOS in einem Fenster angeben:
Für Linux:
Im SQL*Developer kann stattdessen auch unter Extras/Voreinstellungen/Datenbank/NLS das Datumsformat eingestellt werden.
Wenn Sie sich in einzelnen Befehlen nicht an den Default halten möchten, können Sie eine Ausgabeformatierung verwenden:
Achten Sie darauf, dass die Minuten mit "MI" gekennzeichnet sind. Für Oracle wäre auch folgendes Format OK:
Nur leider ist das Format falsch, weil im String "HH:MM:SS" Stunden:Monat:Sekunden ausgegeben werden, und das stimmt nur ganz selten mit der tatsächlichen Uhrzeit überein (eine Minute pro Stunde :-)
Es gibt eine riesige Liste an möglichen Datumskonvertierungen die hier nachgelesen werden können.
Nur ein paar Beispiele für Oracle Datumsformate:
Wenn Sie ein Datum in die Oracle Datenbank einpflegen möchten, sollten Sie das idealerweise auch mit einer expliziten Datums-Konvertierung tun.
Hier werden Stunden, Minuten und Sekunden auf 0 gesetzt. Das spart zwar keinen Speicherplatz, erleichtert aber später die Suche nach einem Einstellungstag. Wenn Sie stattdessen schreiben:
Dann wird die aktuelle Uhrzeit im Datum mit gespeichert. Jetzt versuchen Sie mal nach Mitarbeitern zu suchen, die heute eingestellt wurden:
Nur sollte es einen normalen Index auf der Spalte hiredate geben, wird er nicht benutzt werden. Deswegen könnte man folgende Variante benutzen:
Hier suchen Sie nach allen Mitarbeitern, die zwischen 00:00.00 und 23:59:59 des aktuellen Tages eingestellt wurden.
Für Freunde des JSON Datentyps: JSON verwendet gerne folgendes Datumsformat: 01.08.2022T12:30:39ZMan beachte das T in der Mitte und das Z am Ende.Das können wir wieder in ein Oracle Datumsformat zurückwandeln:
Hat das Ihre Lust auf noch mehr SQL Tipps geweckt? Wir haben bestimmt auch die passende Schulung für Sie!
Der SQL Developer als Oracle-proprietäres Tool erfreut sich mittlerweile großer Beliebtheit. Entwicker, die den SQL Developer auch zum Debuggen nutzen, werden bei 12c-Datenbanken jedoch erst einmal eine neue Hürde überwinden müssen. Während für andere Tools, wie TOAD oder PL/SQL Developer, auch in Version 12c die Standard-Grants (DEBUG ANY PROCEDURE, DEBUG CONNECT SESSION) dafür ausreichen, wird für den SQL Developer zusätzlich eine ACL-Freigabe benötigt. Ohne ACL erhalten Sie folgende Fehlermeldung:
Der Grund liegt darin, dass der Debugger des SQL Developers - im Gegensatz zu den oben genannten Tools - mit dem Package DBMS_DEBUG_JDWP ("Java Debug Wire Protocol") arbeitet. Und ab Version 12c ist dafür eine entsprechende Freigabe erforderlich. Diese wird über das Package DBMS_NETWORK_ACL_ADMIN erteilt, das hier schon einmal erwähnt wurde. Die Art der Freigabe lautet in diesem Fall jdwp:
Als Host muss selbstverständlich der Rechner angegeben werden, auf dem der SQL Developer läuft. Die Angabe von Ports hat Tücken; am einfachsten ist es, Sie lassen sie ganz weg, wie hier gezeigt. Zwar können Sie im SQL Developer eine Port-Range für den Debugger einstellen, aber wenn Sie beim Aufruf von DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE sowohl lower_port als auch upper_port angeben, wird das mit dieser Fehlermeldung
quittiert. Geben Sie NUR den lower_port an, so ist auch nur dieser EINE Port freigegeben. Nach erfolgreicher Freigabe können Sie - an diesem Rechner - den Debugger starten:
An jedem anderen Rechner bekommen Sie weiterhin die Fehlermeldung, dass die ACL abgelehnt wurde:
Das Package DBMS_DEBUG_JDWP enthält übrigens noch zwei weitere, interessante Funktionen, über die sich SID und serial# der eigenen Session - auch ohne Admin-Rechte - ermitteln lassen. Und das nicht erst in 12c:
ACEs sind dafür nicht erforderlich, da ja auf keine externe Ressource zugegriffen wird.
Sie möchten wissen, was ist denn eigentlich neu in der Oracle Datenbank Version 12c? Dann besuchen Sie unsere Schulung Neuerungen 12c und stöbern Sie in der Rubrik Oracle Neuerungen zu 12c Release 1 unserer Tipps & Tricks, hier finden Sie weitere kleine Hilfen, die das Leben mit Oracle 12c erleichtern. ;-)
In diesem Tipp zeigen wir eine Möglichkeit zur Bewertung verschiedener potentieller Gefahren in der Oracle Datenbank, welche individualisiert überprüft werden können. Da wären SQL Injection-anfällige Eingaben oder auch die Gefährlichkeit der Rechte bzw. Rollen von Oracle Datenbanknutzern.
SQL INJECTIONUm SQL Injection-Angriffe abzuwehren, bietet Oracle das Package dbms_assert welches Eingaben bzw. Strings auf Gültigkeit überprüft. Möchte man allerdings eine Unterscheidung bzw. Einschätzung der potentiellen Gefahr haben, hilft einem dieses Package nicht weiter. Deshalb erstellen wir die Funktion "get_score", die einen zu überprüfenden String entgegennimmt. Zurückgegeben wird ein Scoring-Wert.
Innerhalb dieser Funktion legen wir uns ein Array mit allen Schlüsselwörter an, die als verdächtig erachtet werden. Da "REGEXP_COUNT" nur ein Schlüsselwort erwartet, konkatenieren wir die Schlüsselwörter jeweils mit einem Pipe "|". Der Parameter "1" legt den ersten Buchstaben als Start fest und "i" beachtet keinen Unterschied zwischen Groß- und Kleinschreibung. Sobald "REGEXP_COUNT" einen Match gefunden hat, wird der Score um eins erhöht.
Die Scoring-Abfrage auf einen typischen SQL Injection-String sieht folgend aus:
Hinweis: Oracle kann nur einen Befehl dynamisch ausführen, der DROP TABLE würde sowieso nicht funktionieren. Aber manche Hacker gehen von SQL Server oder MYSQL Datenbanken aus, da geht so etwas.
Folgende Schlüsselwort wurden also abgefangen: "OR", "1=1", "DROP", "TABLE", ";", "--"
Regex aus dem oberen Beispiel für Schlüsselwörter erklärt:"(1)=\1": Wahrheitsvergleich 1=1 der sehr beliebt ist"[;].[--]": Typisches Ende einer SQL Injection: ";--" oder auch "; DROP TABLE wichtig -------"
Da das Thema Reguläre Ausdrücke sehr komplex ist, verweisen wir auf eine gute Stelle im Internet: Opens external link in new windowhttp://www.sqlsnippets.com/en/topic-10764.html
RECHTE & ROLLENDas oben beschriebene Prinzip bleibt das selbe:In ein Array speichern wir potentiell gefährliche Rechte, die wir abfragen möchten. Die Funktion bekommt einen Benutzernamen übergeben, den es zu überprüfen gilt, und zurückgegeben wird ein Scoring-Wert. Es werden nicht nur direkte Rechte abgefragt, sondern auch Rechte die über eine Rolle erteilt wurden.
Der administrative Benutzer "SYS" besitzt logischerweise den höchsten Scoring-Wert.
Um überhaupt herauszufinden welche Benutzer direkt oder über eine Rolle solche Rechte besitzen, kann man dies mithilfe von folgendem Statement herausfinden. Der SELECT listet alle betroffenen Benutzer und zeigt diese in hierachischer Struktur an.
Manchmal umfassen Applikationen nicht nur viel Quelltext, sondern auch sehr viele Views, in denen u. a. auch Funktionen aus diversen Packages verwendet werden. Wenn nun eine bestimmte Funktion geändert werden soll / muss, so sollte man dringend im Vorfeld klären, welche Stellen davon betroffen sind. So kann man sichergehen, dass die gewünschte Änderung keine unerwünschten Nebenwirkungen hat.
Dazu muss man aber erst einmal herausfinden, wo die betreffende Funktion überall aufgerufen wird.Sofern sich die Signatur der Funktion hinreichend ändert, z. B. weil ein weiterer Parameter dazu kommt, kann man dazu theoretisch in einer Testumgebung einfach die Funktion abändern und schauen, was dadurch INVALID wird und es auch nach Neukompilierung bleibt.
Wenn sich aber die Signatur NICHT ändert, oder wenn keine vernünftige Testumgebung zur Verfügung steht (sowas soll es geben), dann muss man sich anderweitig auf die Suche begeben.
Abhängigkeiten helfen hier in aller Regel nicht wirklich weiter: USER_DEPENDENCIES listet nur das Package auf, nicht die einzelne Funktion. Und wenn das Package eine Reihe von Hilfsfunktionen beinhaltet, ist man - fast - genau so schlau wie vorher.
Solange es sich um Aufrufe innerhalb von PL/SQL handelt, sind die Stellen noch relativ einfach zu finden:
SELECT * FROM user_source WHERE LOWER(text) LIKE '%<funktionsname>%';
Bei Views dagegen wird die Sache schwieriger. Rein intuitiv würde man es ja analog probieren mit:
SELECT * FROM user_views WHERE LOWER(text) LIKE '%<funktionsname>%';
Das funktioniert aber nicht, weil text vom Typ LONG ist. Und so wird die Abfrage quittiert mit:
ORA-00932: Inkonsistente Datentypen: CHAR erwartet, LONG erhalten
Und jede View einzeln anschauen, kann sehr mühsam werden. Da hilft am besten ein kleiner Umweg über PL/SQL:
CREATE OR REPLACE FUNCTION read_view_text ( p_viewname IN VARCHAR2) RETURN VARCHAR2 IS v_text VARCHAR2 (32767); BEGIN SELECT text INTO v_text FROM user_views WHERE UPPER(view_name) = UPPER(p_viewname); RETURN v_text; EXCEPTION WHEN OTHERS THEN RETURN NULL; END read_view_text; /
Und dann sucht man mit Hilfe dieser Funktion:
SELECT * FROM user_views WHERE LOWER(read_view_text(view_name)) LIKE '%<funktionsname>%';
Das Prinzip ist natürlich auch auf andere LONG-Spalten anwendbar, z. B. query in user_snapshots / user_mviews.
Ab Version 12.1 hat user_views ein paar neue Spalten erhalten, u. a. auch text_vc vom Typ VARCHAR2(4000). Und damit funktioniert das ganze dann direkt (zumindest für die ersten 4000 Bytes):
SELECT * FROM user_views WHERE LOWER(text_vc) LIKE '%<funktionsname>%';
Hatten Sie auch schon einmal das Problem, dass Sie einen Datensatz gesucht haben, aber nicht wussten, in welcher Spalte dieser steht? Wir haben noch eines drauf gesetzt und können sogar Werte suchen, von denen man nicht weiß, in welcher Tabelle sie stehen. Diese Fragestellung kann auftreten, wenn man mit einer Applikation arbeitet, deren genaues Datenbank-Layout nicht bekannt ist.
Es sollte jedoch erwähnt werden, dass hier mehrfach ein Full-Tablescan erzeugt wird, der bei einer großen Tabellenanzahl zu einer SEHR GROSSEN Performance-Belastung führen kann. Besonders die Option ALL wird sehr viel Ressourcen verbrauchen, deswegen sollte man die Procedure nur auf Test-Maschinen absetzen.
In dieser Procedure werden zwei Data-Dictionary Tabellen (dba_objects und dba_tab_columns) benutzt. Leider sind Rechte an DD-Objekten über Rollen an die Benutzer vergeben, die wiederum in PL/SQL nicht verwendet werden können.
Drei Möglichkeiten stehen als Problemlösung zur Verfügung:
GRANT SELECT on dba_tab_columns TO system;GRANT SELECT on dba_objects TO system;
ACCEPT v_schema_name PROMPT "Schema eingeben (ALL für Alle): "ACCEPT v_search_string PROMPT "Suchstring eingeben (Bsp: Hallo%): "ACCEPT v_table_name "Tabellennamen eingeben (Leer = alle Tabellen): "ACCEPT v_type "Datentyp STRING oder NUMBER eingeben: "
DECLARE p_schema_name VARCHAR2(30):=upper('&&v_schema_name'); p_search_string VARCHAR2(30):='&&v_search_string'; p_table_name VARCHAR2(30):=upper('&&v_table_name'); p_type VARCHAR2(30):='&&v_type'; TYPE ref_curs ...
Folgende Parameter können übergeben werden:
CREATE OR REPLACE PROCEDURE find_in_tables (p_search_string IN VARCHAR2, p_schema_name IN VARCHAR2 DEFAULT NULL, p_table_name IN VARCHAR2 DEFAULT NULL, p_type IN VARCHAR2 DEFAULT 'STRING') uthid current_user IS TYPE ref_curs_type IS REF CURSOR; refc ref_curs_type; sql_str VARCHAR2(200); row_ret ROWID; v_user VARCHAR2(30);BEGIN /* Wenn Benutzer NULL ist wir aktueller Benutzer verwendet */ SELECT sys_context('USERENV', 'SESSION_USER') INTO v_user FROM dual; v_user := nvl(p_schema_name, v_user); dbms_output.put_line ('Suche wurde ausgeführt mit: User='||v_user||' Table='||p_table_name); FOR rec IN (select do.owner,table_name, column_name, data_type FROM sys.dba_tab_columns dtc, dba_objects do WHERE dtc.table_name = do.object_name and do.object_type = 'TABLE' and dtc.owner = decode(v_user,'ALL',dtc.owner,v_user) and dtc.owner not in ('SYS','SYSTEM','OUTLN','SYSMAN', 'MDSYS','ORDSYS','WMSYS','CTXSYS', 'OLAPSYS') and do.object_name = nvl(p_table_name, do.object_name)) LOOP IF upper(p_type) = 'STRING' AND (rec.data_type = 'CHAR' OR rec.data_type = 'VARCHAR2' or rec.data_type = 'CLOB') THEN sql_str := 'SELECT rowid FROM ' || rec.owner || '.' || rec.table_name ||' WHERE ' || rec.column_name || ' like ' || chr(39) || p_search_string || chr(39); BEGIN OPEN refc FOR sql_str; LOOP FETCH refc INTO row_ret; EXIT WHEN refc%NOTFOUND; dbms_output.put_line('Owner='||rec.owner||' Table=' || rec.table_name || ' Col=' ||rec.column_name || ' Rowid=' || row_ret); END LOOP; row_ret := null; CLOSE refc; EXCEPTION WHEN OTHERS THEN IF v_user<>'ALL' THEN dbms_output.put_line('Fehler in Table=' || rec.table_name); END IF; END; ELSIF p_type = 'Number' and rec.data_type = 'NUMBER' THEN sql_str := 'SELECT rowid FROM ' || p_schema_name || '.' || rec.table_name || ' WHERE ' || rec.column_name || '=' || p_search_string; BEGIN OPEN refc FOR sql_str; LOOP FETCH refc INTO row_ret; EXIT WHEN refc%NOTFOUND; dbms_output.put_line('Owner='||rec.owner||' Table=' || rec.table_name || ' Col=' ||rec.column_name || ' Rowid=' || row_ret); END LOOP; row_ret := null; CLOSE refc; EXCEPTION WHEN OTHERS THEN IF v_user<>'ALL' THEN dbms_output.put_line('Fehler in Table=' || rec.table_name); END IF; END; END IF; END LOOP;END;/
Beispielaufrufe:
SQL> EXEC find_in_tables ('PRESI%','SCOTT','EMP');
Ausgabe: Table=EMP Col=JOB Rowid=AAANOxAAEAAAAAdAAI
SQL> EXEC find_in_tables ('DALL%','ALL');
Ausgabe: Table=DEPT Col=LOC Rowid=AAANOvAAEAAAAANAAB
Dynamische Selects zu schreiben ist dank des Ref Cursoren mittlerweile ziemlich einfach geworden. Man muss jedoch wissen, wie viele Spalten man ausliest, und welchen Datentyp sie haben. In der Regel ist das kein Problem, wenn man über die Dynamik nur erreichen will, dass je nach übergebenen Parametern auf unterschiedliche Tabellen zugegriffen wird oder nach unterschiedlichen Spalten sortiert werden soll.
Was aber tut man, wenn man die Spaltenliste des Select-Befehls nicht kennt?
In diesem Fall kann man auf das altbewährte und teilweise bereits als veraltet belächelte DBMS_SQL-Package zurückgreifen. Es ermöglicht, über die Prozedur DESCRIBE_COLUMNS die Spaltenliste zu ermitteln. Diese Prozedur liefert die Anzahl der Spalten zurück und eine Beschreibung jeder Spalte in Form einer INDEX BY-Tabelle, die auf einem Record-Datentyp (DBMS_SQL.DESC_REC) beruht. Ausgelesen werden u. a. Name und Datentyp der Spalten.
Um auch die Anzahl der Spalten komplett dynamisch halten zu können, wurde im unten gezeigten Beispiel mit INDEX BY-Tabellen der wichtigsten Datentypen als Variablen gearbeitet.
Das soll sich ändern, wir haben hier nun die wichtigsten Punkte zu Oracle LONG Spalten zusammengefasst:LONG Spalten wurden in Oracle 7.3 eingeführt und sollten die Beschränkung von VARCHAR2 Spalten die nur 4000 Zeichen aufnehmen können, beheben.Deswegen wurden zwei neue Datentypen geschaffen LONG (kann bis 2 GB an Textdaten aufnehmen) und LONG RAW (kann bis zu 2 GB an Binärdaten speichern).
Leider hat man keine Routinen dazu entwickelt, die mit dieser Speichermenge umgehen konnten. Man hat deshalb mit 2 Speicherbegrenzungen zu kämpfen:
SQL Funktionen können (wenn überhaupt) nur die ersten 4000 Zeichen einer LONG-Spalte nutzen.In PL/SQL kann man die ersten 32760 Zeichen lesen/schreiben. Mit einem Trick (dazu später mehr) lesen wir aber auch darüber hinaus.Wo kann man LONG Spalten verwenden?
1.) Beschränkungen von LONG Spalten
Das schauen wir uns mal genauer an ...
CREATE TABLE t ( id NUMBER, text LONG, bild LONG RAW);
FEHLER in Zeile 4:ORA-01754: Eine Tabelle darf nur eine einzige Spalte des Typs LONG aufweisen
Zweiter Versuch funktioniert, da nur eine LONG Spalte verwendet wird:
CREATE TABLE t ( id NUMBER, text LONG);
Das gleiche kann man auch mit einer LONG RAW Spalte machen:
CREATE TABLE test_LONG_raw ( id NUMBER, long_col_raw LONG RAW);
2.) Constraints auf LONG-Spalten
Nur NOT NULL Constraints sind erlaubt Die Constraint-Typen P (Primary Key), U (Unique Key), C (Check) können nicht verwendet werden.
CREATE TABLE t ( id NUMBER, l LONG NOT NULL);
... alle anderen Constraints sind nicht erlaubt
CREATE TABLE t ( id NUMBER, l LONG PRIMARY KEY);
ORA-02269:Schlüsselspalte kann nicht vom Datentyp LONG sein02269: "key column cannot be of LONG datatype"*Cause: Self-evident.*Action: Change the datatype of the column, or remove the column from the key.
3.) Indizierung von LONG-Spalten
Ein normaler Index kann nicht auf eine LONG-Spalte gesetzt werden, aber ein Text-Index schon.
CREATE INDEX i ON t(text);Error: ORA-00997: Unzulässige Verwendung des Datentyps LONG
Workaround: Text-Index setzen
CREATE INDEX i ON t(text) INDEXTYPE IS ctxsys.context;SELECT * FROM t WHERE CONTAINS( text, 'Muniqsoft GmbH' ) > 0;
4.) LONG Spalte füllen (bis zu 4000 Zeichen)
INSERT INTO t VALUES (1,lpad('#',4000,'#'));
LONG RAW Spalte füllen
INSERT INTO test_LONG_raw (id,LONG_col_raw) VALUES(1,utl_raw.cast_to_raw('abcdefghijklmnopqrstuvwxyz'));
5.) Füllung einer LONG-Spalte mit 32760 Bytes in PL/SQL
DECLARE v_l LONG:=lpad('#',32760,'#');BEGIN INSERT INTO t VALUES (1,v_l); COMMIT;END;/
Update eines LONG-Datentyps mit bis zu 32760 Bytes
DECLARE v_l LONG; CURSOR c IS SELECT text FROM t WHERE id=2 FOR UPDATE;BEGIN FOR r IN c LOOP v_l:=r.text || ' Neuer Text'; UPDATE t SET text = v_l WHERE CURRENT OF c; END LOOP; COMMIT;END;/
6.) Die Funktionen INSTR, SUBSTR und LENGTH..
...können nicht auf LONG-Spalten angewendet werden
SELECT LENGTH(text) FROM t WHERE id=1;
FEHLER in Zeile 1:ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten
SELECT SUBSTR(text,1,5) FROM t;
FEHLER in Zeile 1:ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalt
SELECT INSTR(text,1,'#') FROM t;
Aber mit einem kleinen Trick bekommen wir das doch noch hinWenn Sie z.B. auf die Spalte view_text aus DBA_VIEWS zugreifen möchten, gehen Sie wie folgt vor:1. rufen Sie die Funktion sys_dburigen mit den Parametern: Eigentümer der Tabelle/View, Name der Tabelle/View und Spaltenname auf:
SELECT sys_dburigen( owner,view_name, text, 'text()').getclob()FROM dba_viewsWHERE owner='SCOTT';
2. verwenden Sie nund ie gewünschte Funktion aus 6. (z.B. substr)
SELECT substr(sys_dburigen( owner,view_name, text, 'text()').getclob(),1,100)FROM dba_viewsWHERE owner='SCOTT';
Das Ganze geht natürlich mit der Long Spalte:TRIGGER_BODY aus Tabelle DBA_TRIGGERSSEARCH_CONDITION aus Tabelle DBA_CONSTRAINTSu.v. weiteren LONG SpaltenAchtung: Die 3 Parameter müssen genau eine Zeile zurückgeben, sonst erhält man den Fehler:ORA-19003 XML document must have a top level elementDeswegen funktioniert der TRcik auch leider nicht bei:
SELECT sys_dburigen( table_owner,partition_name,HIGH_VALUE, 'text()').getxml() FROM all_tab_partitionsWHERE table_owner='AUDSYS' AND table_name='AUD$UNIFIED' ;
7.) Trigger auf LONG Spalten
Ein SQL Statement innerhalb eines Trigger kann Daten in eine LONG oder LONG RAW Spalte einfügen.Die maximale Länge einer Text-Variable (VARCHAR2 oder LONG) in einem Trigger beträgt 32000.:NEW und :OLD dürfen nicht auf LONG oder LONG RAW Spalten gesetzt werden.
LONG oder LONG RAW Spalten dürfen nur referenziert werden, wenn sie in CHAR oder VARCHAR2 konvertierbar sind.
CREATE OR REPLACE TRIGGER tr BEFORE UPDATE OR INSERT OR DELETE ON t FOR EACH ROWBEGIN :new.text:='A';END;/Error: ORA-04093: Referenzen auf Spalten von Typ LONG sind in Triggern nicht zulässig.
8.) Ausgabelänge für Oracle LONG-Spalten in SQL*Plus auf 2 Mio Zeichen setzen (Default= 80)
Wenn Sie diesen Parameter nicht umsetzen, werden bei einer Ausgabe in SQL*Plus nur die ersten 80 Zeichen einer LONG Spalte ausgegeben.
SET LONG 200000
9.) Tabellen mit LONG Spalten kopieren
Eine Tabelle kann mit einem CREATE TABLE AS SELECT ... nicht kopiert werden, wenn LONG Spalten enthalten sind.
CREATE TABLE t2 AS SELECT * FROM t;
FEHLER in Zeile 1:ORA-00997: Unzulässige Verwendung des Datentyps LONG
Workaround mit Konvertierung in einen CLOB Datentypen
CREATE TABLE t2 AS SELECT to_lob(text) as text FROM t;
Tabelle wurde erstellt.
10.) LONG Spalten konvertieren
Oracle empfiehlt, möglichst zeitnah auf die Datentypen CLOB, BLOB und NCLOB zu konvertieren. Diese stehen ab Version 8.0 zur Verfügung.
Beispiel: Umwandeln von LONG in CLOB und LONG Raw in BLOB
ALTER TABLE test_LONG MODIFY (LONG_col CLOB);ALTER TABLE test_LONG_raw MODIFY (LONG_col_raw BLOB);
11.) LONG Spalten in PL/SQL
In PL/SQL wird ein LONG-Datentyp als VARCHAR2(32760) abgebildet. Das heißt folgende beiden Deklarationen sind identisch:
DECLARE v_a LONG; v_b VARCHAR2(32760);BEGIN NULL;END;/
12.) PL/SQL Package zum Berechnen der LONG Spaltenlänge und zum Auslesen von Daten
Das nachfolgende kleine PL/SQL Beispiel zeigt, dass man mit einem Spezial-Package (dbms_sql) auch an die Daten einer LONG Spalte herankommt, die hinter der magischen Grenze von 32760 liegen.
Das Package LLONG bietet Ihnen die folgenden Möglichkeiten:
Berechnung der Länge eines LONG Feldes. Damit können Sie feststellen, ob Sie die Daten auch in VARCHAR2(4000) bekommen könnten.
Der Aufruf dazu lautet:
LLONG.LEN(<rowid>)
SELECT LLONG.LEN(rowid) FROM t;
Herausschneiden von beliebigen 400 Zeichen in der LONG-Spalte
Der Aufruf lautet:
LLONG.SUB_STR(<rowid>, <start_pos>, <end_pos>)
Der Default für die Startposiion ist 1 und der Default für die Endposition ist 4000.
Beispiele:
SELECT LLONG.SUB_STR(rowid) FROM t; SELECT LLONG.SUB_STR(rowid, 400000,404000) FROM t;
Das Suchen innerhalb einer Long Spalte mit Like
LLONG.LIKE2(<rowid>,'<such_str>')
SELECT * FROM t WHERE LLONG.LIKE2(rowid,'MARCO%') >0
Das Anhängen von Daten an eine bestimmte Zeile
LLONG.APPEND(<rowid>,'<TEXT>')
EXEC LLONG.APPEND('AAAUfXAAEAAAAEAAAC',' Anhänger')
Das Anzeigen der Position eines Suchstring:
LLONG.IN_STR(<rowed>,'<TEXT>',<pos>)
SELECT LLONG.IN_STR(rowid,'MARCO',1) FROM t;
Und so sieht das komplette Package aus:
CREATE OR REPLACE PACKAGE LLONG AS FUNCTION sub_str( p_rowid in rowid, p_startpos IN NUMBER DEFAULT 1, p_endpos IN NUMBER DEFAULT 4000) RETURN CLOB; FUNCTION len( p_rowid in rowid) RETURN VARCHAR2; PROCEDURE append ( p_rowid IN ROWID, p_value IN VARCHAR2); FUNCTION like2 ( p_rowid IN ROWID, Like_str In VARCHAR2) RETURN NUMBER; FUNCTION in_str ( p_rowid IN ROWID, search_char In VARCHAR2, position IN NUMBER DEFAULT 1) RETURN NUMBER;
END;/SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY LLONG AS l_cursor integer default dbms_sql.open_cursor; l_n number; --l_LONG_val varchar2(4000); l_LONG_val CLOB; l_LONG_len number; l_buflen number := 4000; l_curpos number := 0; v_tab VARCHAR2(62); v_col VARCHAR2(30); offset integer; v_length integer; v_collen integer;
FUNCTION get_obj_name (r ROWID) RETURN VARCHAR2 AS BEGIN select owner||'.'||object_name INTO v_tab from all_objects where object_id=( select dbms_rowid.rowid_object(r) from dual); RETURN v_tab; END; FUNCTION get_col_name (r ROWID) RETURN VARCHAR2 AS BEGIN select column_name INTO v_col from all_tab_columns where (owner,table_name) IN (selectowner,object_name from all_objects where object_id=( select dbms_rowid.rowid_object(r) from dual)) AND data_type='LONG'; RETURN v_col; END;
PROCEDURE open_curs (p_rowid IN ROWID) AS BEGIN v_tab:=get_obj_name(p_rowid); v_col:=get_col_name(p_rowid); l_cursor:=dbms_sql.open_cursor; dbms_sql.parse( l_cursor,'select ' || v_col || ' from ' || v_tab || ' where rowid = :x',dbms_sql.native ); dbms_sql.bind_variable( l_cursor, ':x', p_rowid ); dbms_sql.define_column_LONG(l_cursor, 1); l_n := dbms_sql.execute(l_cursor); END;
FUNCTION sub_str( p_rowid in rowid, p_startpos IN NUMBER DEFAULT 1, p_endpos IN NUMBER DEFAULT 4000) RETURN CLOB ASBEGIN IF p_endpos-p_startpos > 4000 THEN RETURN 'Max Return Length 4000 Bytes !'; END IF; open_curs(p_rowid); IF (dbms_sql.fetch_rows(l_cursor)>0) then dbms_sql.column_value_LONG( l_cursor, 1, p_endpos-p_startpos, p_startpos ,l_LONG_val, l_LONG_len); END IF; dbms_sql.close_cursor(l_cursor); return l_LONG_val; EXCEPTION WHEN OTHERS THEN RETURN sqlerrm;end; --sub_str
FUNCTION len (p_rowid in rowid) RETURN VARCHAR2 AS BEGIN open_curs(p_rowid);° IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN offset := 0; v_collen := 0; LOOP dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length); v_collen := v_collen + v_length; EXIT WHEN v_length < 32767 OR v_length IS NULL; offset := offset + v_length; END LOOP; END IF;dbms_sql.close_cursor(l_cursor);return v_collen;EXCEPTION WHEN OTHERS THEN RETURN sqlerrm;END; --len
PROCEDURE append ( p_rowid IN ROWID, p_value IN VARCHAR2)ASv_app VARCHAR2(32760);s VARCHAR2(1000);BEGINopen_curs(p_rowid);IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN offset := 0; LOOP dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length); v_app := v_app || l_LONG_val; EXIT WHEN v_length < 32767 OR v_length IS NULL; offset := offset + v_length; END LOOP; END IF;dbms_sql.close_cursor(l_cursor); BEGIN v_app:=v_app||p_value; s:='UPDATE '||get_obj_name(p_rowid)||' SET '||get_col_name(p_rowid)||' =:x WHERE rowid=:y'; EXECUTE immediate s USING v_app,p_rowid; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000,'Max Col Length Limit 32767eached'); END;END; --append
FUNCTION like2 ( p_rowid IN ROWID, Like_str In VARCHAR2)RETURN NUMBERISBEGINopen_curs(p_rowid);IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN offset := 0; LOOP dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length); IF l_LONG_val LIKE like_str THEN RETURN 1; END IF; EXIT WHEN v_length < 32767 OR v_length IS NULL; offset := offset + v_length; END LOOP;END IF;dbms_sql.close_cursor(l_cursor);RETURN 0;END; --like2
FUNCTION in_str ( p_rowid IN ROWID, search_char IN VARCHAR2, position IN NUMBER DEFAULT 1) RETURN NUMBERISpos NUMBER;BEGINopen_curs(p_rowid);
IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN offset := 0; LOOP dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length); pos:= instr(l_LONG_val,search_char,mod(position,32767)); IF pos>0 and pos+offset>position THEN RETURN pos+offset; END IF; EXIT WHEN v_length < 32767 OR v_length IS NULL; offset := offset + v_length; END LOOP;END IF;dbms_sql.close_cursor(l_cursor);RETURN pos; END; -- instr Function
END; -- PACKAGE/SHOW ERROR
Beispielszenario:
CREATE TABLE t ( text LONG );
INSERT INTO t VALUES ( RPAD( '*', 32000, '*' ) );
BEGIN INSERT INTO t VALUES('#'); FOR i in 1.. 10000 LOOP UPDATE T SET text=text||';'||to_char(i); END LOOP; COMMIT;END;/
SELECT LLONG.LEN( rowid) FROM t;
GETLONG(ROWID)---------------------------------4000
SELECT LLONG.SUB_STR(rowid, 400000, 404000) FROM t;
Suchen in LONG Data Dictionary Spalten:
Als Beispiel wird hier in der Tabelle user_constraints Spalte search_condition nach einem Wert gesucht. Das Beispiel ist leich abänderbar, damit auch die Tabellen DBA_VIEWS (Spalte text) oder DBA_TRIGGERS (Spalte trigger_body) mit ihren LONG Spalten ausgelesen werden können.
CREATE OR REPLACE FUNCTION get_search_condition( p_cons_name IN VARCHAR2 )RETURN VARCHAR2 authid current_user IS l_search_condition user_constraints.search_condition%type; BEGIN SELECT search_condition into l_search_condition FROM all_constraints WHERE constraint_name = p_cons_name; RETURN l_search_condition;END;/
SELECT constraint_name FROM all_constraintsWHERE owner='SYSTEM'AND get_search_condition(constraint_name) LIKE '%NOT NULL%';
PS: Die Konvertierung der Funktion kann einige Zeit in Anspruch nehmen!
CONSTRAINT_NAME------------------------------SYS_C002892.....
Zum Vergleich ohne Funktion:
SELECT constraint_name FROM user_constraints; WHERE search_condition LIKE '%NOT NULL%'; where search_condition like '%NOT NULL%' *FEHLER in Zeile 2:ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten
Monatskalender in SQL*Plus
Haben Sie sich auch schon mal gewünscht, dass SQL*Plus einen Monat als schönen Kalender darstellt? Mit der folgenden Funktion können Sie sich den aktuellen Monat (Default) oder einen beliebig anderen Monat anzeigen lassen:
CREATE OR REPLACE FUNCTION show_cal (month IN VARCHAR2 DEFAULT sysdate)RETURN VARCHAR2 IS v_week VARCHAR2(4096); v_firstday NUMBER; v_lastday NUMBER;BEGIN v_firstday:=to_char(trunc(to_date(month),'MM'),'D'); v_lastday:=to_number(to_char(last_day(to_date(month)),'DD')); v_week:='Monat: '||to_char(to_date(month),'FM Month RRRR')||chr(10); v_week:=v_week||'Mon Die Mit Don Fre Sam Son'||chr(10)|| '-------------------------------------'||chr(10); FOR i IN 1.. v_lastday+v_firstday-1 LOOP IF i>=v_firstday then v_week:=v_week||rpad((i-v_firstday+1),4,' '); ELSE v_week:=v_week||rpad(chr(32),4,chr(32)); END IF; IF mod(i,7)=0 THEN v_week:=v_week||chr(10); END IF; END LOOP; RETURN v_week;END;/show errorsSELECT show_cal FROM dual;
Ausgabe:
SHOW_CAL---------------------------------------------------Monat: März 2007 Mon Die Mit Don Fre Sam Son--------------------------------------------------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Da viele grafische Tools (Toad, SQL Developer, ...) mit einem Zeilenumbruch nichts anfangen können, hier eine zweite Alternative mit einer Pipelined Function (nur ab Oracle 10g).
CREATE OR REPLACE TYPE calender_week_typeAS OBJECT (woche VARCHAR2(64));/CREATE OR REPLACE TYPE calender_type as table of calender_week_type;/CREATE OR REPLACE FUNCTION show_cal (month IN VARCHAR2 DEFAULT sysdate)RETURN calender_type PIPELINED IS v_week VARCHAR2(4096); v_firstday NUMBER; v_lastday NUMBER;BEGIN v_firstday:=to_char(trunc(to_date(month),'MM'),'D'); v_lastday:=to_number(to_char(last_day(to_date(month)),'DD')); PIPE ROW (new calender_week_type('Monat: '||to_char(to_date(month), 'FM Month RRRR') )); PIPE ROW (new calender_week_type('Mon Die Mit Don Fre Sam Son')); PIPE ROW (new calender_week_type('--- --- --- --- --- --- ---')); FOR i IN 1.. v_lastday+v_firstday-1 LOOP IF i>=v_firstday then v_week:=v_week||rpad((i-v_firstday+1),4,' '); ELSE v_week:=v_week||rpad(chr(32),4,chr(32)); END IF; IF mod(i,7)=0 THEN PIPE ROW (new calender_week_type(v_week)); v_week:=''; END IF; END LOOP;END;/show errorsselect * from TABLE(CAST(show_cal AS calender_type));
Woche--------------------------------------------------Monat: März 2007 Mon Die Mit Don Fre Sam Son-------------------------------------------------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 7 Zeilen ausgewählt.
Diesmal soll es vor allem um einfache Anwendungsbeispiele für die Funktionen REGEXP_LIKE, REGEXP_SUBSTR und REGEXP_REPLACE gehen.
Wenn man sich mit der gewöhnungsbedürftigen Syntax (einen Überblick finden Sie am Schluss) erst einmal vertraut gemacht hat, können reguläre Ausdrücke beim Formatieren und Bereinigen von Tabelleninhalten, bei der Formulierung von komplizierten Check-Constraints u.a sehr gute Dienste leisten.
Ausgangspunkt für die Beispiele ist die folgende Tabelle (s. Anhang), die mit den regexp-Funktionen überprüft, bereinigt und ggf. vor falschen Eingaben geschützt werden sollte:
NAME TELEFON EMAIL PLZ STRASSE Maier +49/012-345678-012 johann.maier@chaos.de 00000 24 a, Langstr. meyer 012-345678-013 peter.meyer@chaos.de a0674 1a,Langstr. G Mayerhöfer 012-345678-014 frank.mayerhoefer@chaos.de 00345 10-11, Langstr. MEYR 012-345678-015 tomas.meyr@chaos.de 19387 16b, Waldgasse Mayer 012-345678016 richard.mayer@chaos.de 11028 22, Richterstr. Müller 012-345678-017 martin.mueller@chaos.de - 235b, Langstr. Mair 012-345678-018 wilhelmine.mair@chaos.de 12345 35a, Langstr. meir 0049/012-345678-019 sabine.meir@chaos.de 12345 101, Langstr. myers 0049-12-345678-029 jeff.myers@chaos.de 03927 10, Schlossallee müller D012-345678-021 kurt.müller2@chaos.de 12098 10 c, Langstr. Hintermeier 012-345678-022 hansi.hintermeier@chaos.de 11937 180, Holzweg MEIER #012-345678023 eva.meier@chaos.de 12346 301, Langstr. mAYR ?49-12-345678-024 klaus.mayr@chaos.de 19207 5d, Gärtnerplatz MILLER 345678025 oscar.miller@chaos.de 1525 10 a, Langstr.
1. Prüfung auf unerwünschte Zeichen mit REGEXP_LIKE
REGEXP_LIKE vergleicht den Suchstring mit einem Muster und gibt true oder false zurück.
REGEXP_LIKE(Suchstring, Muster, Match-Parameter)
Die Angabe des Match-Parameters (siehe Anhang) ist optional.
Fangen wir mit der PLZ-Spalte an, in der nur 5 Ziffern stehen sollen und nichts anderes (wenn die PLZ mit einer 0 anfängt, darf an der 2. Stelle keine stehen):
SELECT name, plz, strasse FROM firma WHERE NOT REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$');
NAME PLZ STRASSE Maier 00000 24 a, Langstr. meyer 0674 1a,Langstr. Mayerhöfer 00345 10-11, Langstr. Müller - 235b, Langstr. meir 12345 101, Langstr. müller 12098 10 c, Langstr. Hintermeier 11937 180, Holzweg mAYR 19207 5d, Gärtnerplatz MILLER 1525 10 a, Langstr.
Erläuterungen:
eckige Klammern schließen eine Auswahl von Zeichen ein, [0-9] steht für die Ziffern von 0 bis 9, (alternative Formulierungen sind \d (ab 10g2) oder [[:digit:]]). Die Zahlen in geschweiften Klammern geben die Anzahl der Wiederholungen an, [0-9]{3} steht für 3 Ziffern. ^ und $ stehen für Anfang und Ende eines Ausdrucks. ^0 passt auf alle Muster, die mit einer 0 anfangen,[0-9]{3}$ auf alle, die mit 3 Ziffern aufhören. Vorsicht: Das Caret-Zeichen ^ hat diese Bedeutung nur, wenn es außerhalb von eckigen Klammern steht. Die Postleitzahlen mit einer Null am Anfang passen auf das Muster ^0[1-9][0-9]{3}$ (hier darf an der 2. Stelle keine 0 vorkommen), alle anderen auf ^[1-9][0-9]{4}$. Die beiden alternativen Muster werden durch das Oder-Zeichen | getrennt.
2. Beseitigung von unerwünschten Zeichen mit REGEXP_REPLACE
Die Oracle-Funktion REGEXP_REPLACE ersetzt das Muster im Suchstring durch den Ersatzstring.
REGEXP_REPLACE(Suchstring, Muster, Ersatzstring, Position, Vorkommen, Match-Parameter)
Die Angabe von Position, Vorkommen und Match-Parameter ist optional.
Die störenden Leerzeichen und Buchstaben in der PLZ-Spalte kann man mittels REGEXP_REPLACE in einem Rutsch entfernen. (Wenn der Ersatzstring leer bleibt, kann man ihn auch weglassen):
UPDATE firma SET plz = REGEXP_REPLACE(plz, '[^0-9]','') WHERE NOT REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$'); 9 Zeilen wurden aktualisiert.
Ein Caret-Zeichen innerhalb eines Klammerausdrucks kehrt die Zeichenauswahl um, [^0-9] steht also für alle Zeichen außer Ziffern, statt [^0-9] kann man auch \D oder [^[:digit:]] schreiben (s. Anhang).
Kontrolle:
SELECT name, plz, strasse FROM firma WHERE not REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$')OR plz IS NULL;
NAME PLZ STRASSE Maier 00000 24 a, Langstr. meyer 0674 1a,Langstr. Mayerhöfer 00345 10-11, Langstr. Müller 235b, Langstr. MILLER 1525 10 a, Langstr.
Diese 5 Felder muss man von Hand korrigieren, bevor man einen Check-Constraint einrichten kann:
UPDATE firma SET plz = ... WHERE name = ...
3. Verwendung von REGEXP_LIKE in Check-Constraints
Nach der Korrektur lässt sich durch Hinzufügen eines check-Constraints sicherstellen, dass in Zukunft keine Buchstaben, Sonder- oder Leerzeichen eingegeben werden:
ALTER TABLE firma ADD CONSTRAINT firma_plz_check CHECK (REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$'));
Ähnlich funktioniert die Einrichtung eines Check-Constraints der e-mail-Adressen, z.B.:
ALTER TABLE firma ADD CONSTRAINT firma_mail_check CHECK(REGEXP_LIKE(email, '^\w+\.\w+@\w+\.de$'));
\w (ab 10g2) steht für alle alphanumerischen Zeichen inklusive "_", + für eine beliebige Anzahl davon. Oracle akzeptiert \w nicht innerhalb der Zeichenlisten in eckigen Klammern. Der Punkt muss durch einen Backslash maskiert werden, weil er sonst als beliebiges Zeichen interpretiert würde. (Nur innerhalb von eckigen Klammern muss man die Metazeichen wie Punkt, Stern und Plus etc. nicht maskieren). Das @ steht für sich selbst. Statt de könnte man auch eine Auswahl angeben: [de|com|org] oder dem User mit \d{2,4} noch freiere Wahl lassen.
Wenn man auch Bindestriche oder ähnliches in den e-mail-Adressen zulassen will, muss man das Muster anders formulieren (dafür kann man Name und Vorname in einem Klammerausdruck unterbringen), z. B.:
^[-._0-9a-zA-Z]+@[-_0-9a-zA-Z]+\.[A-Za-z]{2,4}$'
4. Einfache Extraktion von Teilstrings mit REGEXP_SUBSTR
REGEXP_SUBSTR gibt einem dem Muster entsprechenden Teilstring des Suchstrings aus.
Was vor 10g mitunter komplizierte Kombinationen der Funktionen instr, SUBSTR und length erforderte, läßt sich mit REGEXP_SUBSTR in sehr kurzen Ausdrücken bewerkstelligen.
REGEXP_SUBSTR(Suchstring, Muster, Position, Vorkommen, Match-Parameter)
Die Angabe von Position, Vorkommen und Match-Parameter ist wiederum optional.
Die Sekretärin möchte eine Adressenliste der Mitarbeiter als View erstellen, die folgende Informationen enthalten soll: Vorname, Nachname, PLZ, Straße und Hausnummer.
Die Vornamen kann man aus den e-mail-Adressen extrahieren und mit den Nachnamen verknüpfen:
SELECT INITCAP(REGEXP_SUBSTR(email, '[^.]+')||' '||name) AS Name FROM firma;
Aus johann.maier@chaos.de wird somit Johann Maier u.s.w. ...
[^.] steht für alle Zeichen außer dem Punkt (innerhalb der Klammer steht der Punkt für sich selbst), per default wird der erste Teilstring ausgegeben, der dem Muster entspricht (vorkommen = 1). Wollte man den Nachnamen aus der Adresse holen, so müsste man REGEXP_SUBSTR(email, '[^.@]+',1,2) schreiben, (Position = 1, Vorkommen =2) REGEXP_SUBSTR(email, '[^.@]+',2) dagegen gibt den Vornamen ab dem 2. Zeichen aus (Position = 2, Vorkommen = default =1). Die Oracle-Funktion INITCAP sorgt für Großbuchstaben am Anfang und Kleinschreibung für den Rest.
5. Drehung am Komma mit REGEXP_REPLACE
Die umgedrehte Reihenfolge von Hausnummer und Strasse im amerikanischen Stil läßt sich durch mittels REGEXP_REPLACE umkehren:
SELECT LTRIM(REGEXP_REPLACE(strasse, '([^,]+),([^,]+)','\2 \1')) AS strasse FROM firma;
Langstr. 24 a Langstr. 1a Langstr. 10-11 ......
Das Komma teilt den String in 2 Hälften, die runden Klammern dienen der Gruppierung. [^,]+ steht für ein bis beliebig viele Zeichen mit Ausnahme des Kommas. Durch die negative Formulierung läuft man nicht Gefahr, eine Art von Zeichen zu vergessen, zudem kann man sie für beide Seiten verwenden. Positiv formuliert müsste man das Muster z.B. so schreiben: '([- 0-9a-z]+),([a-zA-Z. ]+)' \1 und \2 sind sogenannte Rückwärtsreferenzen (back references), die sich auf die Gruppierungen im Suchstring beziehen. Der Ausdruck '\2 \1' zieht aus dem Muster die Gruppen 1 und 2 heraus (ohne das Komma), dreht sie um und fügt zwischen beiden ein Leerzeichen ein. Die Oracle-Funktion LTRIM entfernt führende Leerzeichen.
Jetzt können wir die View erstellen:
CREATE OR REPLACE VIEW adressen AS SELECT INITCAP(REGEXP_SUBSTR(email, '[^.]+')||' '||name) AS Name, plz, LTRIM(REGEXP_REPLACE(strasse, '([^,]+),([^,]+)','\2 \1')) AS strasse FROM firma;
SELECT * FROM adressen; NAME PLZ STRASSE Johann Maier 12345 Langstr. 24 a Peter Meyer 12345 Langstr. 1a Frank Mayerhöfer 12345 Langstr. 10-11 .......
6. Aufteilung von Strings in 2 Spalten mit REGEXP_SUBSTR
Die folgende View stellt alle Informationen in getrennten Spalten dar:
CREATE OR REPLACE VIEW adressen AS SELECT INITCAP(REGEXP_SUBSTR(email, '[^.]+')) AS Vorname, INITCAP(name) as Nachname, plz, LTRIM(REGEXP_SUBSTR(strasse, '([^,]+)',1,2)) AS strasse, LTRIM(REGEXP_SUBSTR(strasse, '([^,]+)')) AS Nr FROM firma;
Aus einem String wie z.B. '24 a, Langstr.' extrahiert
REGEXP_SUBSTR(strasse, '([^,]+)',1,2))das 2. Vorkommen des Musters (Strasse), und REGEXP_SUBSTR(strasse, '([^,]+)',1,1)) das 1. Vorkommen (Hausnummer, die beiden Einser können hier auch wegfallen, weil sie der Default sind):
SELECT * FROM adressen; VORNAME NAME PLZ STRASSE NR Johann Maier 12345 Langstr. 24 a Peter Meyer 12345 Langstr. 1a Frank Mayerhöfer 12345 Langstr. 10-11 ....
7. Sortierung von alphanumerischen Einträgen mit REGEXP_SUBSTR
Alle Angestellten, die in der Langstrasse wohnen, möchten eine Fahrgemeinschaft bilden. Der Fahrer hätte gern eine nach Hausnummern geordnete Liste der Namen. Wegen der Buchstaben und Leerzeichen etc. funktioniert der direkte Ansatz nicht, auch wenn man die vorangehenden Leerzeichen mit LTRIM eliminiert:
SELECT name, strasse FROM firma WHERE strasse LIKE '%Lang%' ORDER BY LTRIM(strasse); NAME STRASSE meyer 1a,Langstr. MILLER 10 a, Langstr. müller 10 c, Langstr. meir 101, Langstr. Mayerhöfer 10-11, Langstr. ....
Eine Kombination dieser alphabetischen Sortierung mit der Sortierung nach dem Zahlenteilstring bringt dagegen das erwünschte Ergebnis:
SELECT INITCAP(name) Name, LTRIM(REGEXP_SUBSTR(strasse,'[^,]+')) Nr FROM firma WHERE strasse LIKE '%Lang%' ORDER BY TO_NUMBER(REGEXP_SUBSTR(strasse, '[0-9]+')), LTRIM (strasse);
NAME NR Meyer 1a Miller 10 a Müller 10 c Mayerhöfer 10-11 ....
REGEXP_SUBSTR(strasse, '[0-9]+') extrahiert die erste zusammenhängende Zahl aus dem String,(z.B. 10-11 ' 10) was die Umwandlung mit TO_NUMBER und damit die richtige Sortierung ermöglicht.
8. Entfernung von doppelten Leerzeichen mit REGEXP_REPLACE
Die Ausgabe kann man noch etwas verschönern, indem man die doppelten Leerzeichen in der Hausnummern-Spalte entfernt:
SELECT INITCAP(name) Name, LTRIM(REGEXP_REPLACE(REGEXP_SUBSTR(strasse,'[^,]+'),'( ){2,}', ' ')) Nr FROM firma WHERE strasse LIKE '%Lang%' ORDER BY TO_NUMBER(REGEXP_SUBSTR(strasse, '[0-9]+')), LTRIM (strasse);
NAME NR Meyer 1a Miller 10 a Müller 10 c ....
'( ){2,}' findet Gruppen von mindestens 2 Leerzeichen und ersetzt sie durch eins: ' '. Die runden Klammern dienen nur der Übersichtlichkeit.
9. Formatierung von Ausdrücken mit REGEXP_REPLACE
Die Telefonnummern sollen von Leer-, Sonderzeichen u. ä. befreit werden und folgendes Format erhalten: (+49-12)345-6789-029. Die zu bereinigenden Einträge findet man mit:
SELECT telefon FROM firma WHERE REGEXP_LIKE(telefon, '[^-0-9]');
TELEFON +49/012-345678-012 0049/012-345678-019 0049-12-345678-029 D012-345678-021 012-345678-022 #012-345678023 ?49-12-345678-024
[^-0-9] steht für alles außer Bindestrich und Ziffern. Mit REGEXP_REPLACE kann man so alle Zeichen außer den Ziffern entfernen, indem man den Ersatzstring einfach wegläßt.:
UPDATE firma SET telefon = REGEXP_REPLACE(telefon, '[^0-9]'); 14 Zeilen wurden aktualisiert
Für die Formatierung gibt es 2 Möglichkeiten:
a) Verwendung der Oracle-SUBSTR-Funktion (weil sich nur die Durchwahl ändert):
UPDATE firma SET telefon = '(+49-12)345-678-' || SUBSTR(telefon,-3);
SUBSTR(telefon,-3) startet beim dritten Zeichen von hinten und gibt den Rest aus.
b) Verwendung von REGEXP_REPLACE, komplizierter, aber allgemeingültiger:
UPDATE firma SET telefon = REGEXP_REPLACE(telefon, '(0*49)?(0?12)?(\d{3})(\d{3})(\d{3})', '(+49-12)\3-\4-\5'); Gruppe: 1 2 3 4 5
Hier werden wieder Backreferences verwendet. Die Gruppen von Zeichen, die man umformatieren will, werden durch runde Klammern gekennzeichnet.
Die Gruppen 1 (beliebig viele Nullen gefolgt von 49) und 2 (eine oder keine Null gefolgt von 12) die jeweils ein oder kein Mal vorkommen, werden durch (+49-12) ersetzt, die folgenden 3 Gruppen 3 - 5 mit Bindestrich aneinandergehängt.
Hinweis; Oraclle unterstützt maximal 9 Gruppen (1-9)
Die Säuberung und Formatierung kann man mit einem geschachtelten REGEXP_REPLACE auch in einem Zug erledigen:
ROLLBACK; UPDATE firma SET telefon = REGEXP_REPLACE(REGEXP_REPLACE(telefon,'[^0-9]'), '(0*49)?(0?12)?(\d{3})(\d{3})(\d{3})','(+49-12)\3-\4-\5'); SELECT telefon FROM FIRMA; TELEFON (+49-12)345-678-012 (+49-12)345-678-013 (+49-12)345-678-014 (+49-12)345-678-015
10. Suche nach Wörtern mit verschiedener Schreibweise über REGEXP_LIKE:
Angenommen, die Tabelle hätte 100000 Datensätze und wir wollen alle Angestellten suchen, die Maier oder so ähnlich heißen, unabhängig von Groß- und Kleinschreibung und Schreibweise:
SELECT name FROM firma WHERE REGEXP_LIKE (name, '^m[ae][yi].?r$','i');
Hier wird nach einem m am Anfang, gefolgt von a oder e, gefolgt von y oder i gefolgt von 0-1 weiteren Buchstaben und einem r am Schluss gesucht. Der Parameter 'i' steht für case insensitive.
Zusammenfassung der wichtigsten Metazeichen und ihrer Bedeutung:
Der Punkt steht für ein beliebiges Zeichen (ausser einen Zeilenumbruch). eckige Klammern stehen für eine Auswahl von Zeichen, [g-l] z. B. für g, h, i, j, k oder l [0-9A-G] für eine beliebige Ziffer oder einen Großbuchstaben von A bis G . Statt die Zeichenauswahl explizit anzugeben, kann man mit den sog. Charakterklassen arbeiten. Die wichtigsten sind: [:alnum:] alle alphanumerischen Zeichen, [[:alnum:]] entspricht also [0-9A-Za-z] \w alle alphanumerischen Zeichen und der Unterstrich "_" [:alpha:] alle Buchstaben, [[:alpha:]] entspricht [A-Za-z] [:digit:] bzw. \d alle Ziffern, [[:digit:]] entspricht [0-9] [:space:] bzw. \s alle Zeichen, die man nicht sieht, Leerzeichen, Tabzeichen, Enter... \W, \D und S\ sind die Umkehrungen von \w, \d und \s Die Abkürzungen \d, \w etc. stehen seit Version 10.2 zur Verfügung. Das pipe-Symbol | bedeutet "oder", z.B. [ae|ä], [Otto|Emma], etc. Das Dollarzeichen $ verankert das vorausgehende Zeichen am Ende einer Zeile bzw. des Suchstrings, 'r$' passt z.B. auf 'Müller' oder 'Herr', aber nicht zu 'rau' oder 'Karren' Das Caret-Zeichen ^ hat je nachdem, wo es steht, unterschiedliche Bedeutungen ein Caret-Zeichen außerhalb eckiger Klammern verankert das nachfolgende Zeichen am Beginn einer Zeile bzw. des Suchstrings ('^z.*' passt auf 'ziel', 'zeichen' etc, aber nicht auf 'platz', 'setzen') ein Caret-Zeichen innerhalb eckiger Klammern schließt die nachfolgenden Zeichen aus. dementsprechend bedeutet: ^[:alnum:]] alle nicht-alphanumerischen Zeichen (Sonderzeichen, Leerzeichen...) [^0-9] alles ausser Ziffern Der Stern * steht für keine oder beliebig viele Wiederholungen des vorausgehenden Elements (Zeichen oder Gruppe), '.*' passt also auf jeden String Das Fragezeichen ? steht für keine oder eine Wiederholung Das Pluszeichen + steht für eine oder beliebig viele Wiederholungen {n} steht für n Wiederholungen {min,max} steht für min bis max Wiederholungen des Elements , {3,6} z. B für 3 bis 6, {3,} für mindestens 3
Außerhalb von eckigen Klammern bzw. in Kombinationen mit Charakterklassen muss man den Metazeichen einen Backslash voranstellen, wenn man explizit nach ihnen sucht,
z.B. \+, \?, \* etc.
Match-Parameter für die Oracle-REGEXP-Funktionen:
i (case insensitive) Groß- und Kleinschreibung wird nicht berücksichtigt c (case sensitive) Groß- und Kleinschreibung wird berücksichtigt n (newline) Der Punkt kann in diesem Fall auch für einen Zeilenumbruch stehen. m (multiline) Die Zeichenkette wird als mehrzeilige Eingabe betrachtet.^und $ können dann auf jede Zeile angewandt werden und nicht nur für Anfang und Ende des Strings.
Mit dem folgenden SQL-Code können Sie sich die Übungstabelle selbst erstellen:
DROP TABLE firma; CREATE TABLE firma( name VARCHAR2(20), telefon VARCHAR2(20), email VARCHAR2(30), plz VARCHAR2(10), strasse VARCHAR2(50)); INSERT INTO firma VALUES ('Maier','+49/012-345678-012','johann.maier@chaos.de','00000','24 a,Langstr.'); INSERT INTO firma VALUES ('meyer','012-345678-013','peter.meyer@chaos.de','a0674','1a,Langstr. '); INSERT INTO firma VALUES ('Mayerhöfer','012-345678-014','frank.mayerhoefer@chaos.de','00345','10-11,Langstr.'); INSERT INTO firma VALUES ('MEYR','012-345678-015','tomas.meyr@chaos.de','19387','16b,Waldgasse'); INSERT INTO firma VALUES ('Mayer','012-345678016','richard.mayer@chaos.de','11028',' 22,Richterstr.'); INSERT INTO firma VALUES ('Müller','012-345678-017','martin.mueller@chaos.de','-','235b,Langstr.'); INSERT INTO firma VALUES ('Mair','012-345678-018','wilhelmine.mair@chaos.de','12345',' 35a,Langstr.'); INSERT INTO firma VALUES ('meir','0049/012-345678-019','sabine.meir@chaos.de',' 12345','101,Langstr.'); INSERT INTO firma VALUES ('myers','0049-12-345678-029 ','jeff.myers@chaos.de','03927','10,Schlossallee'); INSERT INTO firma VALUES ('müller','D012-345678-021','kurt.müller2@chaos.de','12098 ','10 c,Langstr.'); INSERT INTO firma VALUES ('Hintermeier',' 012-345678-022','hansi.hintermeier@chaos.de',' 11937','180,Holzweg '); INSERT INTO firma VALUES ('MEIER','#012-345678023','eva.meier@chaos.de','12346','301,Langstr.'); INSERT INTO firma VALUES ('mAYR','?49-12-345678-024','klaus.mayr@chaos.de',' 19207','5d,Gärtnerplatz ');
Hinweis: In der Standard Edition können Policies nicht erstellt werden!
Oracle bietet ab Version 8i die Möglichkeit, aufgrund von bestimmten Sessionkriterien den Zugriff auf bestimmte Daten einzuschränken (Application Context). Dadurch können viele statische Views eingespart werden. Für die Einschränkung des Datenzugriffs benötigt man das DBMS_RLS Package.
Dieses Package implementiert das Feature "Virtual Private Database", das auch unter den Begriffen "Fine Grained Access Control" und "Row Level Security" bekannt ist. Es ist in der Enterprise und Personal Edition verfügbar, nicht aber in der Standard Edition.
Das DBMS_RLS Package ermöglicht die Verknüpfung von Tabellen oder Views mit Funktionen, die in Abhängigkeit von bestimmten Faktoren (Attribute und deren Attributwerte) nur die Sicht oder die Möglichkeit von Datenänderungen auf Teile des Objekts freigibt. Die Einschränkung wird erzielt, indem die Funktion eine dynamische WHERE Klausel (Prädikat) an den abgesetzten Select- oder DML-Befehl anhängt.
Die Verknüpfung zwischen der Funktion und der entsprechenden Tabelle erfolgt über eine Policy. Die Einschränkungen auf eine Tabelle können auch mit mehreren Policies gesteuert werden, wobei die einzelnen einschränkenden Bedingungen in der WHERE Klausel mit AND verbunden werden.
In der Datenbank gibt es einen Standardkontext 'USERENV' aus dem vordefinierte Kontextattribute der derzeitigen Session wie Benutzer, Session_id, Hostname, Servername usw. ermittelt werden können. Diese Attributwerte können zwar ausgelesen, jedoch nicht geändert werden. Die jeweiligen Attributwerte können mit der folgenden Funktion ausgelesen werden:
Werden darüber hinaus eigene Attribute benötigt, muss ein eigener Kontext erstellt werden.
Im folgenden Beispiel soll das einschränkende Prädikat anhand des Attributs ‚JOB' im eigenen Kontext ‚emp_restriction' festgelegt werden.
Anhand des Jobs soll in der Tabelle emp der Zugriff wie folgt gesteuert werden:
PRESIDENT: Sieht alle Mitarbeiter und kann diese ändern
MANAGER: Sieht nur alle Mitarbeiter seiner Abteilung und kann nur die Mitarbeiter seiner Abteilung ändern
ANALYST: Sieht nur seine eigenen Daten und kann nur diese ändern
Der Kontext soll im Schema von Scott erstellt werden.
Hierzu müssen ihm die erforderlichen Rollen und Rechte erteilt werden:
Anmeldung als SYS AS SYSDBA
Nachdem Scott nun die benötigten Rechte besitzt, sollen alle Mitarbeiter Zugriff auf die Tabelle emp mit Hilfe eines Synonyms erhalten:
Zunächst wird das Kontextobjekt erstellt:
Der Kontext verweist auf das Package, welches die Funktionen der Sicherheitsrichtlinien beinhaltet. Das Package braucht noch nicht erstellt zu sein.
Danach wird das Package mit den benötigten Funktionen erstellt.
Im Package befinden sich 2 Prozeduren und eine Funktion:
Die Funktion, welche die Einschränkungen festlegt, benötigt immer 2 VARCHAR2 Parameter, auch wenn diese Parameter in der eigentlichen Funktion nicht benötigt werden. Diese Funktion wird in der Policy mit der Tabelle EMP verknüpft.
Zunächst jedoch der Zugriff auf das Package für alle User mit Hilfe des Synonyms:
Da das Package mit DEFINER RIGHTS erstellt wurde, benötigen die Anwender keine weiteren Rechte oder Rollen um die Prozeduren des Packages S_Pkg aufzurufen.
Jetzt kann die Policy erstellt werden, welche die einzuschränkende Tabelle mit der Einschränkfunktion verknüpft:
Wobei:
object_schema: Schema in dem sich das Objekt (z.B.Tabelle/View) befindet, auf dem die Sicherheitsrichtlinie angewendet werden soll
object_name: Objekt, auf dem die Sicherheitsrichtlinie angewendet werden soll
policy_name: Name der Sicherheitsrichtlinie
function_schema: Schema in dem sich die Funktion befindet, mit dem die Sicherheitsrichtlinie angewendet werden soll
policy_function: Packagefunktion mit der Sicherheitsrichtlinie ausgeführt wird
statement_types: Statements, bei denen die Policy Funktion greift. Möglich: SELECT, INSERT, UPDATE, DELETE oder Kombinationen davon
update_check: TRUE/FALSE. TRUE: Überprüfung der Policy nach INSERT oder UPDATE Statement mit den neuen Werten
Der sessionabhängige Attributwert des Attributs ‚JOB' wird in LOGON Trigger im SYS Schema festgelegt.
In dem Trigger wird ein letzter "freier" Zugriff auf die Tabelle emp ermöglicht, um die Information des Jobs und der Abteilung des Users zu ermitteln. Danach wird der Attributwert gesetzt. Ab dem jetzigen Zeitpunkt hat jeder User in Abhängigkeit von seinem Job nur noch Einblick und Manipulationsmöglichkeiten auf "seine" Daten. Dies ist völlig unabhängig von dem Medium, mit dem sich der Anwender auf die Datenbank anmeldet.
Informationen über erstellte Kontexte und Policies im eigenen Schema erhält man über die Data Dictionary Views
Informationen über alle erstellte Kontexte und Policies erhält man über die Data Dictionary Views
Hinweise:
Für SYS gelten die Policies nicht, er kann die Tabelle uneingeschränkt sehen
Es können unabhängige Filter auf ein Objekt für SELECT, INSERT, UPDATE, INDEX und DELETE gesetzt werden
Bei mehreren Filtern auf ein Objekt werden die Filterbedingungen mit AND verknüpft
Das Recht EXEMPT ACCESS POLICY erlaubt die Policy zu umgehen
Löschen, ein- und ausschalten der Policy ist mit folgenden Prozeduren möglich:
object_chemsa: Schema in dem sich das Objekt (z.B.Tabelle/View) befindet, auf dem die Sicherheitsrichtlinie angewendet werden soll
Es kommt immer wieder vor, dass bei einem DML-Trigger für Überprüfungen oder Protokollierungen Daten aus der zu ändernden Tabelle benötigt werden. Ein normaler Row Trigger scheidet dann in der Regel aus, weil man sonst in das Problem mit den Mutating Tables läuft.
Ein kleines Beispiel dazu: Stellen Sie sich vor, eine Geschäftsregel besagt, dass niemand innerhalb einer Berufsgruppe mehr als 50% über dem Durchschnittsgehalt verdienen darf. Der einfachste Ansatz dazu sähe folgendermaßen aus:
Das geht aber leider schief, sobald Sie ein Update ausführen:
Das Problem mit den Mutating Tables hat zugeschlagen! Ein Workaround dazu könnte so aussehen, dass Sie über einen BEFORE STATEMENT Trigger zuerst den Durchschnitt aller Berufsgruppen einlesen und beispielsweise in einem Package oder in einer Tabelle zwischenspeichern. Der Row Trigger kann dann auf diese Werte zugreifen, ohne die zu ändernde Tabelle lesen zu müssen. Das ist machbar, aber aufwendig.
Ab Version 11g kann die Lösung viel einfacher aussehen: Compound Trigger haben Abschnitte für bisher unterschiedliche Triggertypen und bieten die Möglichkeit, Werte für die Dauer der Durchführung des DML-Befehls zu speichern. Obiges Beispiel könnte mit einem Compound Trigger so aussehen:
Erlaubter Update:
Verbotener Update:
Viele Applikationen verwenden die Möglichkeiten zur Mailversendung aus Oracle heraus, sei es mit UTL_SMTP, sei es mit UTL_MAIL. Bis Version 10g reichte es, wenn der Eigentümer der Sende-Prozedur das EXECUTE-Recht auf das jeweilige Package hatte. Soll auf 11g migriert werden, so erfordert dies einen zusätzlichen administrativen Aufwand.
Mit Version 11g wurde nämlich das Sicherheitskonzept dahingehend erweitert, dass Netzwerkzugriffe über Access Control Lists (ACL's) freigegeben werden müssen. Das betrifft neben UTL_SMTP und UTL_MAIL auch die Packages UTL_TCP, UTL_HTTP und UTL_INADDR. Wenn ein entsprechender Aufruf erfolgt, ohne dass der Host bzw. Port explizit freigegeben wurde, so erfolgt nun die Fehlermeldung:
Darüber hinaus wurden die angegebenen Packages auf Invoker Rights umgestellt, und auch entsprechende Prozeduren, die mit diesen Packages arbeiten, sollten mit AUTHID CURRENT_USER erstellt werden, um ein Unterlaufen der ACL's zu verhindern.
Am einfachsten kann der DBA ACL's über das Package DBMS_NETWORK_ACL_ADMIN verwalten. Zunächst muss eine ACL erstellt werden, wie in den folgenden Beispielen gezeigt:
Wir hatten häufig Probleme damit die ACL's wieder zu löschen. Es scheint so zu sein, dass wenn auf einem Rechner eine Freigabe auf eine spezielle Portrange und eine Freigabe ohne Portbeschränkung (lower_port und upper_port = NULL) existiert, diese ACL's nicht mehr zu löschen ist.Aber wir haben eine Alternative entwickelt:Zuerst schauen wir mal nach, welche ACL´s vergeben sind:
Der folgende Block nimmt eine IP Adresse entgegen (Zeile 2: 172.30.30.20) und erstellt ein Skript zum Löschen aller ACL's auf diese IP Adresse.Natürlich könnten Sie den Filter abwandeln z.B. alle ACL´s eines Benutzers löschen.Sie können auch die ACL's direkt löschen, wenn sie v_debug auf FALSE setzen. Sie sollten sich vorher jedoch erst mal ansehen, was gelöscht werden würde !Der Packageaufruf DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL ist eigentlich desupported, es gab jedoch für uns keine andere Alternative,die wirklich beim Löschen funktioniert hat. Wir können nur keine Gewähr für die Funktionalität übernehmen.
Für die Version 11g musste noch eine andere Syntax für das Erstellen der ACL's verwendet werden:
Die ACL wird dann einem Host zugewiesen, wobei auf einen Port bzw. auf einen Bereich eingeschränkt werden kann.
In diesem Beispiel wäre nur der Port 25 auf dem Host mailserver freigegeben. Wird zusätzlich ein upper_port mit angegeben, so ist der Bereich zwischen den beiden Werten freigegeben.Einem Host bzw. einem Port-Range kann immer nur eine ACL zugewiesen sein. Bei einer erneuten Zuweisung wird die alte Zuweisung entfernt. Statt Namen können auch IP-Adressen angegeben werden, und es kann mit dem Stern (*) als Wildcard gearbeitet werden, um ganze Bereiche einer Domäne bzw. ganze Subnetze zu verwalten.
SCOTT kann nun im obigen Beispiel über den Port 25 Mails versenden. Wenn er eine entsprechende Prozedur mit Invoker Rights schreibt und das EXECUTE-Recht an einen User DUMMY vergibt, so braucht dieser zusätzlich die entsprechende Freigabe:
In diesem Tipp des Monats schauen wir uns mal die Speicherbelegung der Tabellen und Indizes an.Es gibt zwar seit Version 10g einen Advisor, der einem die Speicherbelegung der Segmente anzeigt; wir wollen hier aber eine Ausgabe in Tabellenform für mehrere Objekte erzeugen.Wie heißt es so schön: Es gibt kaum eine Information, die man nicht aus einer Oracle-DB herausholen kann, man muss nur die richtige (Öl)-Quelle anzapfen.Dieser Trick beruht auf dem Package DBMS_SPACE, das die Zahl der Blöcke anzeigen kann, die jeweils bis
<= 25%, <= 50%, <= 75% oder <=100% gefüllt sind.
Diese Zahlen werden für jedes Segment zusammengezählt und vom Package zurückgegeben.Die Aufgabe meiner Funktion ist nur, das Ergebnis in Tabellenform darzustellen.
Der Benutzer des Package benötigt zwei Rechte DIREKT!
In 9i ist die RETURN-Klausel am Ende der Funktion zwingend erforderlich, in 10g kann sie auch weggelassen werden.
Beispiel für alle Objekte aus einem Schema:
Beispiel für ein Objekt aus einem Schema:
Beispiel für alle Objekte aus einem Tablespace:
Wenn Sie möchten, legen Sie doch eine View über den Select, dann sieht es richtig professionell aus:
Die Ausgabe könnte dann so aussehen:
In SQL*Plus kann man die Spalten-Längen noch formatieren.Beispiel:
In der Ausgabe sehen Sie in der letzten Spalte den ungenutzten Platz des Segments in Prozent. Auf Basis dieser Zahl kann man entscheiden, ob eine Reorganisation des Segments nötig ist.
Zu Risikien und Nebenwirkungen:
Die Information über den Füllpegel kann nicht von Objekten aus dem SYSTEM-Tablespace oder einem Tablespace ohne SEGMENT SPACE MANAGEMENT AUTO Option erzeugt werden.
Bei einem großen Schema kann die Ausgabe etwas dauern und einige Ressourcen verbrauchen.
Wenn Sie durch dieses Beispiel Lust auf mehr bekommen haben, besuchen Sie doch einen unserer DB Monitoring oder DB Tuning Kurse. Da machen wir noch ganz andere Sachen :-)
Wer sich für die Pipelined Row interessiert, ist in unserem PL/SQL gut aufgehoben.
Viel Spaß beim Segment-Beobachten...
Sie wollten schon häufiger Datenbankdateien von einem Server A auf den Server B kopieren und waren aber gerade nicht in der Lage sich lokal mit einem der Server zu verbinden und anschließend den anderen zu mounten? Oder es war Ihnen schlichtweg zu umständlich? Dann haben wir hier vielleicht eine ganz praktische Lösung für Sie.
Mit Version 10g stellt Oracle ein neues Package zur Verfügung, mit dessen Hilfe sich sehr einfach und komfortabel Binär-Dateien zwischen Datenbank-Servern kopieren lassen. DBMS_FILE_TRANSFER als API unterstützt die folgenden drei Prozeduren:
Nützliches rund um DBMS_FILE_TRANSFER
Für einen erfolgreichen Kopiervorgang mittels DBMS_FILE_TRANSFER müssen Sie sich folgende Punkte bewusst machen:
Sowohl die Quell-Datenbank, als auch die Ziel-Datenbank müssen sich in der OPEN-Phase befinden.Der Oracle Benutzer benötigt das Leserecht auf das Quellverzeichnis und das Schreibrecht auf das Zielverzeichnis.Die Größe der zu kopierenden Dateien muss einem Vielfachen von 512 Bytes entsprechen und darf höchstens zwei Terabytes betragen.
Kopiervorgänge großer Dateien können in der View V$SESSION_LONGOPS überwacht werden.
Die zu verwendenden Directories für Quell- und Zielverzeichnis müssen bereits existieren, während die Datei im Zielverzeichnis noch nicht bestehen darf.
COPY_FILE
Mit der COPY_FILE-Prozedur kopieren Sie eine Binär-Datei innerhalb desselben Servers. Dabei kann sich sowohl das Quellverzeichnis wie auch das Zielverzeichnis entweder im lokalen Dateisystem oder auf einer ASM Disk Group befinden. Soll eine Datei innerhalb des lokalen Dateisystems kopiert werden, kann es sich um eine beliebige Binär-Datei handeln. Stellt eines der Verzeichnisse eine ASM Disk Group dar, können nur Datenbank relevante Dateien kopiert werden, wie z.B. Daten-, Control- oder Logdateien.
Die Datendatei USERS01.DBF soll kopiert werden. Dazu ist es empfehlenswert, den Tablespace OFFLINE oder READ ONLY zu setzen, damit während des Kopiervorgangs keinerlei Änderungen an der Datei vorgenommen werden können.
GET_FILE
Die GET_FILE-Prozedur ermöglicht es Ihnen, eine Binär-Datei von einem remote Server ins lokale Dateisystem oder auf eine ASM Disk Group zu kopieren. Dazu muss ein Database Link zur remote (Quell-)Datenbank vorhanden sein bzw. erzeugt werden (an den Eintrag in der TNSNAMES.ORA denken, falls Sie nicht mit Easy Connect arbeiten).
Die Exportdatei EXPDAT.DMP soll von einem remote Rechner ins lokale Dateisystem kopiert werden. In der remote Datenbank wird zunächst das Directory für das Quellverzeichnis erzeugt.
PUT_FILE
Als Gegenstück zu GET_FILE können Sie mit der PUT_FILE-Prozedur eine Binär-Datei vom lokalen Server (aus dem Dateisystem oder aus ASM) zu einem remote Server ins dortige Dateisystem kopieren. Dazu muss wieder ein Database Link zur remote (Ziel-) Datenbank vorhanden sein bzw. erzeugt werden (an den Eintrag in der TNSNAMES.ORA denken, falls Sie nicht mit Easy Connect arbeiten).
Die über ASM verwaltete SPFILE-Datei soll vom lokalen Rechner ins remote Dateisystem kopiert werden. In der remote Datenbank wird – wie gehabt - das Directory für das Zielverzeichnis erzeugt.
Abschlussbemerkung
Natürlich müssen Sie einiges an Vorarbeit leisten, damit DBMS_FILE_TRANSFER genutzt werden kann. Haben Sie sich aber erst einmal die Directories und Database Links erzeugt sowie die notwendigen Berechtigungen vergeben, ist der Aufruf eine erfreulich unkomplizierte Geschichte. Besonders die Möglichkeit auch ASM Disk Groups sowohl als Quell- wie auch als Zielverzeichnis ansprechen zu können, macht das Package sehr nützlich.
Bedenken Sie, dass DBMS_FILE_TRANSFER keine Umlaute in Dateinamen unterstützt und nur für Datenbanken, die sich in der OPEN-Phase befinden nutzbar ist.
Das Package sollte nicht zum Erzeugen von Online Backups genutzt werden, da während des Kopiervorgangs Inkonsistenzen entstehen können. Diese können zwar in Verbindung mit den archivierten Redo-Log-Dateien wieder bereinigt werden, jedoch wird empfohlen, die Dateien vor dem Kopieren OFFLINE oder wenigstens auf READ ONLY zu setzen.
Nachdem das Thema REST immer mehr an Schwung gewinnt, wird es Zeit darüber einen Tipp zu schreiben.Wir haben aus eigener Erfahrung lange im Internet gesucht und nur selten etwas gefunden.Deswegen haben wir ein paar Beispiele gesammelt:Der Einfachheit halber, wird mit nicht mit SSL Verschlüsselung gearbeitet, ansonsten muss noch ein Wallet eingerichtet werden1. CLOB Übertragen (upload)
l_clob:=APEX_WEB_SERVICE.MAKE_REST_REQUEST( p_url=>'http://www.muniqsoft-training.de/ords/my_rest/muso/putCLOB', p_http_method =>'PUT', p_transfer_timeout =>720, p_username =>'rest_user', p_password =>'rest_password' p_body =>text_lob, --CLOB Datentyp übertragen );
Auf dem Zielserver dann einen Rest-Service anlegen (Typ PUT):
BEGIN my_proc(:body_text);END;
Die Procedure my_proc könnte dann wie folgt aussehen:
CREATE OR REPLACE PROCEDURE my_proc (p_clob IN CLOB)IS...BEGIN...END;
2. BLOB übertragen (upload)
l_clob:=APEX_WEB_SERVICE.MAKE_REST_REQUEST( p_url=>'http://www.muniqsoft-training.de/ords/my_rest/muso/putBLOB', p_http_method =>'PUT', p_transfer_timeout =>720, p_username =>'rest_user', p_password =>'rest_password' p_body_blob =>pdf_lob, --BLOB Datentyp übertragen );
BEGINmy_proc(:body);END;
CREATE OR REPLACE PROCEDURE my_proc (p_blob IN BLOB)IS...BEGIN...END;
3. Zwei Parameter übertragen (Upload)
DECLAREl_clob CLOB;BEGIN apex_web_service.g_request_headers.delete(); apex_web_service.g_request_headers(1).name := 'Content-Type'; apex_web_service.g_request_headers(1).value := 'application/octet-stream'; --'application/x-www-form-urlencoded'; apex_web_service.g_request_headers(2).name := 'PARAMETER_A'; apex_web_service.g_request_headers(2).value := '12345'; apex_web_service.g_request_headers(3).name := 'PARAMETER_B'; apex_web_service.g_request_headers(3).value := to_char(sysdate,'DD.MM.YYYY');l_clob:=APEX_WEB_SERVICE.MAKE_REST_REQUEST( p_url=>'http://www.muniqsoft-training.de/ords/my_rest/muso/putDATA', p_http_method =>'PUT', p_transfer_timeout =>180, p_username =>'rest_user', p_password =>'rest_passwort', p_body =>my_clob_text); if apex_web_service.g_status_code = 200 then dbms_output.put_line('Übertragung erfolgreich abgeschlossen.'); else dbms_output.put_line('Fehler aufgetreten: ' || apex_web_service.g_status_code); end if;END;
Auf dem Zielserver dann wieder ein REST Service anlegen (TYP: PUT)
BEGIN:status:=my_func(p_parameter1=>:PARAMETER_A,p_parameter1=>:PARAMETER_A,p_clob=>:BODY_TEXT);END;
REST Parameter:
Die Funktion könnte dann wie folgt aussehen:
CREATE OR REPLACE FUNCTION my_func(p_parameter1 IN VARCHAR2,p_parameter2 IN VARCHAR2,p_clob IN CLOB) RETURN VARCHAR2ISBEGIN...RETURN 200;EXCEPTION WHEN OTHERS THEN RETURN 500;END;
Jeder hat schon mal von den LOB-Datentypen gehört, die für die Speicherung von großen Textdateien bzw. binären Dateien geeignet sind. In der Oracle Datenbankversion 10g können sie Daten bis zu 128 Terabyte pro Feld aufnehmen ((4GB-1) * DB-Blockgröße).Das Einfügen von Bildern in Tabellen ist allerdings nicht über einfachen Insert möglich, sondern nur über PL/SQL-Prozeduren und das Package DBMS_LOB.Will man ein Bild in der DB nicht nur speichern, sondern auch seine Attribute (Höhe, Breite, Farbtiefe etc.) auslesen oder diverse Manipulationen durchführen wie z.B. Vergrößern, Rotieren etc., bietet sich der Datentyp ORDimage an (den man allerdings nicht in der Oracle Express-Edition verwenden kann, da die Intermedia-Komponente hier fehlt).Dieser Monatstipp soll eine kurze Einführung geben, wie man einzelne Bilder bzw. alle Bilder eines externen Verzeichnisses in Datenbanktabellen laden kann.
Zur Vorbereitung muss man erstmal ein Verzeichnis auf dem Server erstellen und dem Benutzer die nötigen Schreib- und Leserechte erteilen (Das Schreibrecht wird nur für die Erstellung der externen Tabelle benötigt.):
Als Beispiel dient die beliebte Tabelle scott.emp. Wir hängen zunächst eine Spalte "Bild" mit dem Datentyp BLOB an die Tabelle an:
Dann erzeugen wir eine Prozedur zum Einfügen einzelner Bilder. Übergeben werden der Primärschlüssel des Datensatzes und der Name des Bildes. Die Bilder müssen im Ordner c:\temp liegen und die Bildernamen sollten keine Umlaute enthalten:
Zuerst schieben wir die Photos in das Bilder-Directory c:\temp und erzeugen dann über die Eingabeaufforderung mit dir /B eine Liste der Filenamen, die in das Bilderverzeichnis zurück kopiert wird:
Diese Liste kann man in das Bilder-Verzeichnis als externe Tabelle einbinden:
Danach lässt sich der Lade-Vorgang über eine Schleife abwickeln. Im Beispiel wurde zusätzlich eine Geschwindigkeitsmessung eingebaut:
Oracle interMedia (ab 11g Oracle Multimedia genannt) ermöglicht neben Speichern und Abrufen auch die Manipulation von Bildern, Audiofiles und Videos in der Datenbank. Diese Komponente wird außer in der XE-Edition per default mitinstalliert.Der Datentyp ORDimage hat gegenüber den einfachen Blobs den Vorteil der direkten Integration in die Entwicklungstools von Oracle (z. B. JDeveloper, Oracle Content Management SDK, Oracle Application Server Portal, etc.). Darüber hinaus werden Bildinformationen wie Höhe, Breite, Format, Mime-Typ etc. beim Hochladen automatisch bestimmt und gespeichert.
Zur Nutzung der Intermedia-Optionen muss man das oben beschriebene Prozedere ein bisschen abwandeln. Die Returning-Klausel kann hier nicht verwendet werden, insofern wird die Initialisierung der Bildspalte ausgelagert:
Der Prozess dauert zwar ungefähr doppelt solange wie mit der BLOB-Methode, aber dafür werden die Bildeigenschaften gleich mit gespeichert, und man kann sie auch per Select auslesen:
Alternativ kann man die Metadaten als XML auslesen:
Der Import von Bildern erscheint zwar zunächst ein bisschen umständlich. Wenn man sich aber erst einen Satz geeigneter Prozeduren erstellt hat, steht der sicheren Speicherung großer Bildmengen in der Datenbank nichts mehr im Weg. Die Prozeduren kann man im Fall der BLOBs leicht abgewandelt natürlich auch für den Import anderer binärer Files (wie z. B. PDF- und Word-Dokumente) nutzen.Mehr über den Import, Export und die Bearbeitung von LOBs erfahren Sie in unserer Packages-Schulung.
Haben wir uns nicht alle schon mal darüber geärgert, dass auf NULL Spalten nur mit der folgenden Klausel abgefragt werden kann:
Seit Version 10g gibt es eine undokumentierte Funktion (und damit ist sie
leider auf Produktionsdatenbanken nur auf eigene Gefahr einsetzbar).
Die Funktion sys_op_map_nonnull kann zwei NULL Spalten vergleichen.
Beispiel: Alle Zeilen sollen ausgegeben werden, in denen die Spalte
mgr=comm= NULL ist:
Oder, alle Zeilen ausgeben, deren Spalte COMM = NULL ist:
Zum Vergleich: Die offizielle Klausel lautet:
In unseren Schulungen klagen die Teilnehmer häufig über das Mutating Table Problem.Man möchte ja nur mal in der Tabelle etwas nachsehen, auf der der Trigger liegt.
Nur leider quittiert der Trigger einen DML Aufruf mit der folgenden Fehlermeldung:
Lassen Sie uns als Beispiel folgende Problemstellung lösen:
In der klassischen EMP Tabelle wird eine Spalte hinzugefügt mit dem Namen job_chef. In die soll, wenn die Spalte mgr gefüllt wurde, auch automatisch der Beruf des Vorgesetzten eingetragen werden. (Für alle, die die Tabelle nicht so gut kennen, sei gesagt, dass in der Spalte mgr die Nummer des Vorgesetzten steht)Man müsste nun eigentlich nur mal kurz nachsehen, was für einen Beruf der Chef hat, aber da kommt uns das Mutating Table Problem in die Quere.
Man kann das Problem jetzt über 2 Lösungsansätze angehen.
Variante 1:
Wir legen uns erstmal eine Spalte mit Namen job_chef an:
Dann erstellen wir uns ein Package, dass die komplette! Tabelle in ein Array schreibt. (Vorsicht, bei grossen Tabellen, kann der Hauptspeicher zur neige gehen)
Dann brauchen wir einen Statement Trigger, denn der hat das Mutating Table Problem nicht. Der lädt die Daten in eine Collection in den Speicher.
Und nun kommt der eigentliche Trigger ins Spiel. Der braucht jetzt nicht die eigene Tabelle befragen, sondern holt sich die Daten aus dem Speicher.
Variante 2:
Das ist jetzt ein bisschen gefährlich, denn wir tricksen das Mutating Table Problem, mit dem Compiler Hinweiß:PRAGMA AUTONOMOUS_TRANSACTION aus. Hier wird für den Trigger eine eigene Session geöffnet, die dann dasMutating Table Problem nicht hat. Wenn sich aber die Jobbezeichnung des Chefs während des Updates auch ändert, erwischt man u.U. den falschen Wert.
Weitere Tipps und Tricks erhalten Sie z.B. in unserem PL/SQL Kurs oder PL/SQL II Kurs
In Unix gibt es eine schöne Funktion mit Namen eval. Mit der kann man dynamische Befehle ausführen. So etwas wäre doch auch unter Oracle ganz praktisch. Es gibt ja auch schon drei dynamische Konzepte:
Wir lösen das Ganze mit EXECUTE IMMEDIATE und packen eine kleine Funktion darum.
REM Löschen aller Tabellen des Benutzers SCOTT die mit Tab beginnen
oder alle Tabellen im gleichen Tablespace reorgansieren
Alle Indizes neu kompileren, die ungültig sind:
Dynamisches PL/SQL hat auch diverse Nachteile:
1. Es ist langsamer als normales PL/SQL
2. Es ist anfällig für SQL Injection. (Aber das wird in einem anderen Tipp besprochen :-) )
Weitere Tipps erhalten Sie in einem unserer schönen PL/SQL Kurse (PL/SQL I, PL/SQL II, PL/SQL Kompakt, PL/SQL Tuning, PL/SQL Packages)
Der Result Cache ist ein - leider auf die Enterprise Edition beschränktes - Feature, das mit Version 11g eingeführt wurde. Er ist sowohl in SQL für Select-Befehle als auch in PL/SQL für Funktionsaufrufe nutzbar. Die Idee dahinter ist in beiden Fällen, dass das Ergebnis bei der ersten Ausführung gespeichert wird und bei weiteren Aufrufen der Befehl selber nicht mehr ausgeführt, sondern das Ergebnis aus dem Cache abgerufen wird. Um Tom Kyte zu zitieren: "Everyone knows the fastest way to do something is – to not do it".
Der dafür genutzte Speicherbereich - eben der Result Cache - ist Bestandteil der SGA. Um ihn nutzen zu können, muss nur seine Größe (RESULT_CACHE_MAX_SIZE ) auf einen Wert > 0 eingestellt sein.
Parameter (serverseitig)
RESULT_CACHE_MAX_SIZEGröße des Result Cache in Bytes.Der Default hängt von den Einstellungen für SHARED_POOL_SIZE, SGA_TARGET und MEMORY_TARGET ab
RESULT_CACHE_MAX_RESULTAnteil am Result Cache in Prozent, der maximal für einen einzigen Befehl genutzt werden darf.Default: 5
In einem RAC-Umfeld muss darauf geachtet werden, dass entweder auf ALLEN Instanzen RESULT_CACHE_MAX_SIZE auf einem Wert > 0 steht, oder auf keiner; sonst kann es laut Doku zu falschen Ergebnissen kommen.
In SQL gibt es zwei Wege, den Result Cache zu nutzen, entweder über den (auch auf Session-Ebene einstellbaren) Parameter RESULT_CACHE_MODE oder über den Hint RESULT_CACHE:
Steht RESULT_CACHE_MODE auf MANUAL (dem Default), dann muss die Verwendung des Result Cache explizit im Select über den Hint RESULT_CACHE angefordert werden. Steht RESULT_CACHE_MODE dagegen auf FORCE, so wird bei JEDEM Select versucht, den Result Cache zu nutzen, es sei denn, das wird explizt durch den Hint NO_RESULT_CACHE unterbunden. Zumindest eine systemweite Einstellung von RESULT_CACHE_MODE=FORCE dürfte eher nicht sinnvoll sein, da der Platz ja begrenzt ist und somit ein ständiger Turnover stattfinden würde.
Die Verwendung des Result Cache ist auch im Ausführungsplan sichtbar.
Interessant ist der Result Cache vor allem im PL/SQL-Umfeld. Es kommt immer wieder vor, dass man kleine Lookup-Tabellen hat, aus denen häufig ein einzelner Wert - gekapselt in eine Funktion - ausgelesen wird. Oder man verwendet ständig die ein oder andere kleinere Hilfsfunktion (z.B. zur Formatierung oder Berechnung von Werten) für immer wieder die gleichen Werte. Musste man vor Version 11g die Performance steigern, so blieben nur Hilfskonstrukte, wie beispielsweise das Einlesen von Werten in globale Package-Variablen oder -Arrays. Der Nachteil dieses Vorgehens liegt auf der Hand: Package-Variablen behalten ihren Wert nur innerhalb der Session, und spätestens bei Web-Applikationen ist dieser Ansatz daher obsolet.
In Version 11g kann nun das Ergebnis einer Funktion im Result Cache abgelegt und wieder abgerufen werden, egal, ob es sich um eine Package-Funktion oder um eine Standalone Funktion handelt. Es muss nur in der Funktionsdeklaration angegeben werden (bei Packages sowohl im Header als auch im Body). Und da der Result Cache Bestandteil der SGA ist, nicht der PGA, ist er auch Session-übergreifend nutzbar!
Das Package DBMS_RESULT_CACHE bietet einige Verwaltungsmöglichkeiten an, wie z.B. Statusabfrage, vorübergehendes Ausschalten (s.o.), explizite Invalidierung oder, wie unten gezeigt, Erzeugung eines Reports zum Speicherverbrauch:
SET SERVEROUTPUT ONEXEC DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => TRUE)
Im obigen Beispiel wird innerhalb der Funktion auf eine Tabelle zugegriffen. Was passiert, wenn diese Tabelle nun von einer anderen Session geändert wird? In diesem Fall werden beim Commit alle davon abhängigen Ergebnisse automatisch invalidiert und bei Bedarf neu ermittelt, so dass die Funktion nie falsche Ergebnisse liefert. Wird die Tabelle innerhalb der EIGENEN Session geändert, so wird für abhängige Funktionen der Result Cache grundsätzlich so lange nicht mehr verwendet, bis die Transaktion beendet ist. Die Lesekonsistenz ist also immer gewährleistet.
In Version 11.1 musste man mit der Klausel RELIES_ON noch explizit angeben, von welchen Tabellen der Rückgabewert der Funktion abhängig ist. Hat man die Klausel vergessen, so führte das nicht zu einem Kompilierungsfehler, sondern ggf. zu falschen Resultaten. In Version 11.2 werden solche Abhängigkeiten automatisch erkannt.
Natürlich gibt es auch ein paar Einschränkungen bei der Verwendung, die aber normalerweise schon der Compiler nicht zulässt. Dazu gehören
Beispiel für Fehlermeldungen:
PLS-00999: Implementierungseinschränkung (kann temporär sein) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules
Ab Version 12c fällt die letztgenannte Einschränkung weg; dann kann der Result Cache auch bei Invoker Rights genutzt werden.
Eine genaue Auflistung aller Einschränkungen finden Sie in der PL/SQL Language Reference.
V$-VIEWS ZUM RESULT CACHE:
Viele weitere interessante Informationen hierzu erfahren Sie in unseren Schulungen.
Der Vergleich und die Synchronisation von Tabellen waren schon einmal Gegenstand eines Monatstipps. Damals beschränkte sich der Vergleich jedoch auf bis zu 4 Spalten. Die hier vorgestellten Packages für Oracle ermöglichen den Vergleich aller Spalten.
Das Package vergleich_11g ist ein Wrapper für das in der Oracle Version 11g eingeführte DBMS_COMPARISON, das den Umgang mit diesem optionsreichen Tool etwas einfacher gestalten soll, das Package Vergleich_10g ist auch in der Oracle-Version 10g lauffähig und darüberhinaus schneller als DBMS_COMPARISON.
Voraussetzung:
Die beiden Packages wurden auf einer 10gR2-Datenbank und einer 11gR2-Datenbank auf Windows7 64bit getestet mit je 2 unterschiedlichen Tabellen im gleichen Schema, in verschiedenen Schemata und verschiedenen Datenbanken getestet.
Beschreibung der Test-Skripte:
Die Parameter p_hash, p_diff und p_sync der zentralen Prozedur vergleichen ermöglichen verschiedene Durchführungsoptionen, je nachdem, ob man
Die Ausführungsbeispiele hier beziehen sich nur auf den Vergleich von Tabellen über einen DB-Link:
Das Vergleichspackage für die Version 11g bietet ähnliche Optionen wie vergleich_10g. Für die Befüllung der Differenzentabelle und die Synchronisation gibt es hier aber eigene Prozeduren, da DBMS_COMPARISON Vergleichstemplate erstellt und man die einzelnen Vergleiche über ihren Namen ansprechen kann.
Bereits seit der Version 10.1 kann man mit einer (undokumentierten) Funktion den Inhalt von Betriebssystemordnern auslesen. Diese Prozedur wird vom RMAN bei folgendem Befehl verwendet:
Hier werden alle Dateien des Ordners c:\temp und auch der Unterordner gelesen und geprüft, ob es sich um eine Oracle-Datei handelt.
Die folgende PL/SQL-Prozedur liest nur den Inhalt des Ordners c:\temp aus:
Ergibt z. B. die Ausgabe:
Leider darf nur der Benutzer SYS dieses Package ausführen. Wenn auch ein anderer Benutzer in den Genuss dieser Prozedur kommen soll, stehen zwei Varianten zur Verfügung:
Der Nachteil dieser Variante ist, dass dieses Package zwar viele interessante aber auch gefährliche Nutzungsmöglichkeiten bietet.
Danach muss (im Schema SYS!) eine View auf die fixed table x$krbmsft erstellt werden (Selectrechte an Fixed Tables können nicht direkt vergeben werden):
Der User SYSTEM bekommt das Select-Recht auf diese View:
Jetzt kann man sich als SYSTEM anmelden und schreibt die Prozedur etwas um:
Dann vergeben wir nur ein Ausführungsrecht an der Prozedur (nicht mehr am kompletten Package!):
Diese Prozedur bietet viele Einsatzmöglichkeiten, z. B.:
Diese und weitere Beispiele lernen Sie u. a. in unserem PL/SQL II oder Reorg und Wartungskurs.Besuchen Sie uns doch mal, wir freuen uns auf Sie !
Seit Jahren stellt sich mir immer wieder das gleiche Problem:Wie kann ich eine Aktion x-Mal in der Datenbank ausführen?
z.B. Wie kann ich 100 Indizes in einen anderen Tablespace verschieben?Bisher war meine Lösung immer:
Dann bekommt man ein paar Fehler durch die Formatierung ausgegeben (kann man durch geschickte SET Kommandos ausblenden), trotzdem funktioniert es.
Was macht man jedoch, wenn man kein Laufwerk hat, wo man die Spool Datei ablegen darf/kann? Oder wenn kein SQL*Plus zur Verfügung steht? Hier hilft ein kleines simples PL/SQL Skript, dass die Funktionalität nachbaut. Ich habe es eval genannt als Hommage an das Unix Kommando eval(uate).
Warnung: Die Funktion führt den Befehl sofort aus! Da gibt es keine Rückfrage mehr, wie SIND SIE SICHER?? :-)Eigentlich dürfen/sollen Funktionen solche Sachen gar nicht machen :-), aber wir sind ja Advanced User :-)
Anwendungsbeispiele:
Löschen aller Tabellen des Benutzers SCOTT, die mit Tab beginnen:
Alle Tabellen im gleichen Tablespace reorganisieren:
Alle Indizes neu kompilieren, die ungültig sind:
Wie immer gilt:Wollen Sie mehr aus Ihrer Datenbank machen? Kommen Sie in eine unserer Schulungen.
Die häufigsten Einsatzfälle eines Function Based Index dürften sein:
An dieser Stelle sollen zwei besondere Einsatzmöglichkeiten solcher Indices vorgestellt werden.
Wir hatten schon öfter in Applikationen folgendes Szenario: In Tabellen soll nicht physikalisch gelöscht werden, sondern nur logisch durch Setzen eines Flags. Gleichzeitig soll ein bestimmtes Merkmal (z.B. ein Name) nur innerhalb der aktiven Einträge eindeutig sein, um anhand dieses - in einer Maske angezeigten - Kriteriums suchen zu können. Ein Beispiel zur Demonstration des Problems:
Ein "normaler" UNIQUE Index hilft hier nicht weiter. Kein Name ist Unique, und auch die Kombination aus Status und Name ist es nicht:
Was tun? Ein Ansatz wäre, vor neuen Einträgen zunächst die Anzahl aktiver Datensätze mit diesem Wert zu zählen, und in dem Fall, dass die Anzahl > 0 ist, den Wert abzulehnen. Sinnvoller wäre es aber, wenn die Datenbank selber solche Anforderungen erzwingen würde. Dann können sie in keinem Fall umgangen werden. Hier kann ein Funktionsbasierter Index weiterhelfen. Der Trick dabei ist, dass NULL-Werte nicht indiziert werden:
Ziel erreicht. Die Eindeutigkeit für aktive Datensätze - und nur für aktive Datensätze - wird durch die Datenbank erzwungen.
Eine andere Fragestellung: Sie haben in Ihrem Workflow eine Tabelle mit zu erledigenden Aufträgen. Es werden laufend neue Einträge mit dem Status 'WAITING' eingetragen. Ein Job arbeitet die Aufträge ab und setzt dabei den Status z.B. zuerst auf 'IN PROGRESS' und am Ende auf 'DONE'. Sie werden sehr bald sehr viele Einträge haben, wobei in der Spalte "Status" nur drei (oder vier: ERROR im Fehlerfall) Werte stehen. Nur ein sehr geringer Prozentsatz davon wird WAITING sein. Aber die sollen effektiv gefunden werden.
Vielleicht denken Sie jetzt: "Wenige unterschiedliche Werte? Aha - Bitmap Index". Nun, das wäre das schlechteste, was Sie tun können, da die Tabelle laufend geändert wird.
Alternative 2 wäre ein normaler B*Tree-Index mit Histogrammen. Das funktionert, solange Sie beim Status nicht mit Bind-Variablen arbeiten und ausgerechnet bei der ersten Ausführung nach einem anderen Status gesucht haben. Allerdings wird der Index unnnötig groß.
Alternative 3: Sie gehen genauso vor wie im ersten Beispiel beschrieben:
Selbstverständlich können Sie hier auch mit DECODE arbeiten, und es ist letztlich unerheblich, welcher Wert GENAU im Index steht. Es könnte genauso gut 'WAITING' selber sein.
Hier ist die Zielsetzung eine andere als oben: Der Zugriff soll beschleunigt werden. Entsprechend muss nun beim SELECT-Befehl darauf geachtet werden, dass der Index auch wirklich verwendet wird. Das heisst, die Funktion muss auch in der WHERE-Klausel stehen:
Anmerkung: Aus Platzgründen wurden Leerzeichen aus dem Plan entfernt
Wenn Sie nicht - oder nicht mehr - wissen, wie ein funktionsbasierter Index angelegt wurde, schauen Sie in DBA_IND_EXPRESSIONS nach.
Funktionsbasierte Indices sind erfreulicherweise seit Version 9.2 auch in der Standard Editon von Oracle enthalten; davor waren sie auf die Enterprise Edition beschränkt.
Quelle:Thomas Kyte, Expert Oracle Database Architecture. 9i and 10g Programming Techniques and Solutions, Apress, 2005
In einem früheren Beitrag wurde das Tracing einer Datenbank-Session bereits generell beschrieben, daher soll hier nicht näher auf die Grundlagen eingegangen werden. Neben den dort vorgestellten Methoden gibt es seit Version 10g noch das Package DBMS_MONITOR, das diverse Möglichkeiten zum Tracing bietet.
Analog zu den bekannten Möglichkeiten bietet auch DBMS_MONITOR eine Prozedur an, um anhand von SID und SERIAL# (aus v$session) für eine bestimmte Session Tracing zu aktivieren: SESSION_TRACE_ENABLE. Die ersten beiden Parameter (session_id und serial_num) identifizieren die Session, für die Tracing aktiviert werden soll. Werden sie weggelassen (bzw. wird NULL für sie übergeben), so ist die eigene Session gemeint. Der dritte (waits) und vierte (binds) Parameter legt fest, ob Informationen über Wartezustände bzw. Bind-Variablen gesammelt werden sollen (TRUE) oder nicht (FALSE).
Ob Tracing eingeschaltet ist, und wenn ja, wofür, ist in v$session in den Spalten sql_trace, sql_trace_waits und sql_trace_binds ersichtlich (manchmal aber erst, wenn diese Session aktiv wurde):
Ausgeschaltet wird diese Art des Tracing mit SESSION_TRACE_DISABLE:
So weit, so gut. Aber dafür braucht man doch kein neues Package? Richtig, aber DBMS_MONITOR bietet auch Möglichkeiten, die NICHT an die SID einer Session gebunden sind. Das Package ist vor allem dafür gedacht, 3-Tier-Applikationen zu tracen, bei denen keine durchgehende Session zwischen Endbenutzer und Datenbank besteht.
Um dieses Feature nutzen zu können, muss ein Client Identifier gesetzt sein. Hier sind die Applikationsentwickler gefordert, einen entsprechenden Aufruf einzubauen, wenn eine Verbindung zu der Datenbank hergestellt wird:
Dieser Identifier ist auch sichtbar in v$session. Die Spalte heisst client_identifier.
Anstelle der SID dient nun dieser Identifier als Identifikationsmerkmal, was mitprotokolliert werden soll. Tracing wird in diesem Fall eingeschaltet mit:
Diese Art von Tracing verhält sich in einigen Aspekten komplett anders als klassisches Session-Tracing:
Ausgeschaltet wird es durch:
Die dabei erzeugten Tracefiles können in bekannter Art mit TKPROF ausgewertet werden. Da es hier jedoch zu einem Identifier in der Regel mehrere Tracefiles gibt, ist eine Einzelauswertung mühsam. Vorteilhafter ist es, alle zugehörigen Tracefiles erst zusammenzuführen mit trcsess, einer ebenfalls mit 10g neu eingeführten Utility, und dann das Output-File zu analysieren:
Der "*" bedeutet, es sollen alle Tracefiles einbezogen werden. trcsess unterstützt auch Tracefiles aus klassischem Session-Tracing. Eine Übersicht der Syntax erhalten Sie, wenn Sie trcsess ohne Parameter aufrufen.
DBMS_MONITOR bietet als dritte Alternative die Möglichkeit, anhand von Service-Name, Module-Name und Action-Name Tracing einzuschalten. Wird kein Modulname mitgegeben, dann wird standardmäßig für alle Module Tracing aktiviert. Sofern nicht explizit eine bestimmte Instanz angegegben wird, gilt das Tracing für alle Instanzen, die über den angegbenen Service-Namen angesprochen werden. Auch diese Art von Tracing bleibt bei einem Neustart bestehen und muss explizit beendet werden.
Sowohl Module-Name als auch Action-Name können in einer Session über einen DBMS_APPLICATION_INFO-Aufruf gesetzt werden. Das Package ist dafür gedacht, innerhalb einer Applikation Informationen darüber zu liefern, was gerade passiert, z. B. mit welchem Formular (module) der Anwender gerade welche Aktivität (action) ausführt:
Beides ist sowohl in v$session als auch in v$sqlarea sichtbar, die Spalten heissen module und action.
Wird beim Aufruf nur ein Service-Name mitgegeben, dann ist das Tracing für ALLE Module aktiviert (Default), wird für module_name NULL mitgegeben, dann gilt das Tracing für alle Sessions, bei denen kein module_name gesetzt wurde. Der Aufruf ist streng hierarchisch, das heisst, action_name wird nur im Zusammenhang mit module_name wirksam. Es wird nur protokolliert, wenn alle drei Eigenschaften übereinstimmen.
Das Tracing wird wieder ausgeschaltet durch die entprechende DISABLE-Methode:
Auch hier können Tracefiles über trcsess zusammengefasst werden. Die entscheidenden Parameter lauten in diesem Fall service, module und action:
Seit der Version 7 bietet Oracle die Möglichkeit der Volltextsuche. Seit Version 9i ist Oracle Text jedoch fester Bestandteil der Datenbank, auch in der Express Edition.Das kann man leicht nachprüfen, indem man nach dem User ctxsys sucht:
Die Basisfunktionalitäten kann man ohne zusätzliche Rechte nutzen, für die Anpassung der Sucheinstellungen braucht man jedoch mindestens das Execute-Recht an dem wichtigsten Package des Schemas ctxsys: ctx_ddl oder die Rolle ctxapp.
WIE FUNKTIONIERT ORACLE TEXT ?Die Dokumente werden in einen so genannten Datastore eingelesen. Die zu indizierenden Texte können dabei entweder in CLOB-, VARCHAR2- oder XMLTYPE-Spalten einer Tabelle in der Datenbank liegen (direct_datastore), im Filesystem des Datenbankservers (file_datastore) oder im Inter- bzw. Intranet (url_datastore). Es besteht sogar die Möglichkeit, die Texte über eine selbst definierte Prozedur direkt vor der Indizierung zusammenzustellen (user_datastore).Im 2. Schritt werden die Objekte im Bedarfsfall gefiltert. Das ist nur dann nötig, wenn es sich um binäre Files, wie Word-Dokumente oder PDF-Dateien handelt. Text-, HTML- und XML-Dateien müssen nicht gefiltert werden. Oracle erkennt über 150 Formate automatisch.Der Sectioner kann HTML- oder XML-Dokumente anhand von Tags (z.B. <H1> ...</H1> in HTML oder <Produktbeschreibung> .... </Produktbeschreibung> in XML) in einzelne Abschnitte aufteilen.Der Lexer extrahiert alle relevanten Wörter aus dem Text. Interpunktions- und Sonderzeichen werden entfernt. Bei diesem Schritt kann man u.a. einstellen,was als Trennzeichen gewertet bzw. ignoriert werden soll (Leerzeichen, Unterstriche etc.)ob Groß- und Kleinschreibung beibehalten werden soll,ob zusammengesetzte Worte in ihre Einzelteile zerlegt werden sollen, etc.Beim Indizierungsprozeß wird aus den gesammelten Wörtern ein invertierter Index erzeugt. Jedem Wort wird dabei eine Liste seiner Fundstellen zugeordnet.Artikel, Konjunktionen, Präpositionen und Hilfsverben etc., bei Oracle Text Stopwörter genannt, werden nicht indiziert.Die Einstellungen der sog. wordlist legen fest, welche grammatikalischen Regeln verwendet werden sollen, damit bei der Textsuche auch Beugungsformen des gesuchten Verbs oder Wörter mit ähnlichem Stamm erkannt werden.In diesem Monatstipp sollen zunächst die grundlegenden Funktionen der Volltextsuche am Beispiel eines Context-Indexes auf einer Text-Spalte erklärt werden.Die Beispiel-Tabelle, eine Sammlung von Sprichwörtern (mit diversen Schreibfehlern und kleinen Abwandlungen) können Sie (Initiates file download) hier als SQL-File herunterladen.
VOLLTEXTSUCHE IN TEXTSPALTENWir erstellen die Tabelle und erzeugen erstmal einen Index ohne zusätzliche Parameter:
Die Suche in einem Context-Index wird über das Schlüsselwort CONTAINS durchgeführt:
Die wichtigsten Suchmöglichkeiten:
1. EINFACHE SUCHE NACH WÖRTERN Z.B:
2. SUCHE NACH WORT-KOMBINATIONEN ODER -ALTERNATIVEN MIT DEN BOOLESCHEN OPERATOREN "AND" UND "OR":
3. SUCHE NACH ÄHNLICH GESCHRIEBENEN WÖRTERN:Wenn man den Operator "?" vor das gesuchte Wort stellt, kann man auch Wörter mit Rechtschreibfehlern oder Buchstabendrehern finden:
Dann bekommt man aber leicht unerwünschte Ergebnisse - wie hier Messer statt Meister.
Um die Möglichkeiten einzugrenzen, kann man das Schlüsselwort Fuzzy einsetzen, z.B.:
Der 1. Parameter bestimmt die Ähnlichkeit (minimal = 1, identisch = 80, default = 60). Daraus resultiert eine Liste ähnlicher Worte (hier Meister, Meitser und Messer). Mit diesen Wörtern wird dann eine oder-Suche durchgeführt.Der 2. Parameter bestimmt die maximale Anzahl der Wörter dieser Liste (1- 5000, default = 100).Der 3. Parameter wirkt sich nur auf die Relevanzgewichtung (s.u. Score) aus.
4. SUCHE MIT WILDCARDS: "%" FÜR KEIN ODER BELIEBIG VIELE ZEICHEN UND "_" FÜR GENAU 1 ZEICHEN:
Eigentlich erwartet man hier auch Ergebnisse, die das Wort "selbst" enthalten. Das wird aber als Stopwort behandelt und nicht indiziert.
5. SUCHE NACH ÄHNLICH KLINGENDEN WÖRTERN (SOUNDEX-FUNKTION) MIT DEM OPERATOR "!":Da die Soundex-Funktion auf englische Phonetik ausgerichtet ist, kann man dabei aber durchaus Überraschungen erleben (wie die zum Hasen mutierte Katze im Beispiel).
6. SUCHE NACH AUSDRÜCKEN, DIE DENSELBEN WORTSTAMM HABEN WIE DAS SUCHWORT ODER MIT DEM SUCHWORT ZUSAMMENGESETZTE WORTE BILDEN, MIT DEM OPERATOR "$":
oder
Enterprise-Edition:
Erklärungen:
STEMMER: legt die Grammatik für Stamm- und Beugungsformen fest. Die Voreinstellung GERMAN führt dazu, dass z.B. die Suche nach $laufen auch Formen wie lief, gelaufen, läuft, etc. liefert.FUZZY_MATCH: bestimmt, nach welcher Routine ähnlich geschriebene Wörter gesucht werden (hier sind allerdings nicht allzu viele Unterschiede zwischen GERMAN und GENERIC festzustellen)Die Einstellung COMPOSITE = GERMAN im deutschen Lexer ermöglicht die Zerlegung eines Wortes im Index in seine Bestandteile (Neben Porzellankiste wird im Index auch Porzellan und Kiste gespeichert).MIXED_CASE = YES bedeutet, dass Groß- und Kleinschreibung unterschieden wird.ALTERNATE_SPELLING = GERMAN bewirkt, dass Wörter in alternativen Schreibweisen im Index gespeichert werden, also z.B. Töchter als Toechter und bisschen als bißchen.
Ein weiterer Unterschied zeigt sich bei den oben erwähnten Stopwörtern, die von Oracle Text nicht indiziert werden, weil sie in jedem Text vorkommen, wie z.B. Artikel, Konjunktionen und Präpositionen. Diese Listen kann man einsehen:
Die darin enthaltenen Wörter erhält man über:
NACHRÜSTEN DER SPRACHSPEZIFISCHEN EINSTELLUNGEN IN DER EXPRESS-EDITIONBei der Installation der Express-Edition wird Oracle Text mit den amerikanischen Einstellungen vorkonfiguriert. Die Konfiguration kann man jedoch leicht über das Skript drdefd.sql im Ordner <Oracle-Home>\ctx\admin\defaults ändern (ein Blick in dieses Skript lohnt sich).Wenn die Einstellungen für alle User gelten sollen, nimmt man diese Änderungen am besten direkt im ctxsys-Schema vor. Dies muss man zuerst freischalten:
Die alten Einstellungen werden über Bord geworfen:
und die neuen eingespielt:
Jetzt muss man noch den Index an die neuen Einstellungen anpassen. Das geht entweder über einen Alter Index Befehl:
oder über Löschen und Neuerstellen des Indexes:
Die neuen Einstellungen kann man über die Views CTX_STOPLISTS und CTX_PREFERENCE_VALUES überprüfen.
ANPASSUNGEN DER SPRACH-EINSTELLUNGENDie Einstellungen von wordlist, stoplist und lexer kann man nachträglich noch anpassen. Wenn man z.B. die Case-Sensitivity abschalten will, kann man sich über die Prozedur CTX_DDL.CREATE_PREFERENCE auf der Basis des default-Lexers einen eigenen Lexer erzeugen, dessen Attribute man selber setzen kann. Leider genügt es nicht, hier nur diesen einen Parameter anzupassen, dann gehen nämlich die deutschen Einstellungen für ALTERNATE_SPELLING und COMPOSITE verloren.Wenn man einen Text indizieren will, der Wörter mit Sonderzeichen enthält, möchte man nicht, dass diese Sonderzeichen als Trennzeichen gewertet werden. Dies kann man mit dem Attribut PRINTJOINS erreichen. Im Beispiel unten werden Unterstrich, Dollarzeichen, Raute und das Apostroph (das wie üblich mit einem zusätzlichen Apostroph maskiert werden muss) als Bestandteile von Wörtern festgelegt.Zudem kann man über das Attribut NEW_GERMAN_SPELLING bewirken, dass sowohl die alten und die neuen Formen (z.B. rauh / rau, Stengel / Stängel) im Index abgespeichert und bei der Suche gefunden werden.
Anpassen des Indexes:
VORSICHT, FALLE!Da im neuen Lexer Bindestriche als Bestandteile von Wörtern gewertet werden, sollte die folgende Abfrage eigentlich ein Ergebnis zurückliefern:
Folgende Operationen zum Reorganisieren von Tabellen sind möglich:
Für Besitzer der Enterprise und Express Edition kann eine Tabelle auch online reorganisiert werden, wobei auch Schreibzugriffe während derReorganisation hier möglich sind.
Mit dem neuen Package DBMS_REDEFINITION können Tabellen mit einer minimalen Sperrzeit online reorganisiert werden.
Folgende Änderungen sollen an einer Tabelle emp durchgeführt werden:
Spalte ENAME soll umbenannt werden in NAME,
Spalte SAL soll umbenannt werden in VERDIENST und um den Faktor 1,5 erhöht,
die Spalten MGR, HIREDATE, COMM sollen gelöscht werden.
Tabelle EMP2
Überprüfen, ob EMP zu reorganisieren ist:
Erstellen Sie eine vorläufige Tabelle mit den gewünschten Änderungen
Start der Reorganisation:
Erstellen Sie den Primärschlüssel auf empno:
Erstellen Sie einen Fremdschlüssel auf der Spalte deptno der auf die Spalte deptno der Tabelle DEPT verweist:
Der FK muss ausgeschaltet werden. Dieser wird dann am Ende der Reorganisation automatisch aktiviert.
Wenn nötig, synchronisieren Sie die Tabellen zwischendurch:
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
Beenden Sie die Redefinition:
Löschen der alten Tabelle:
Der Redefinitions Vorgang kann abgebrochen werden mit:
Neuerungen ab Version 10.2:
Bevor die Reorg abgeschlossen ist, können noch diverse Objekte (wie Cosntraints, Trigger, Indizes, u.w.) mit übernommen werden.
Neuerungen ab Version 11.x:Nun können auch abhängige Materialized Views beim Reorg-Prozess mit übernommen werden.
Weitere Informationen zum Thema Reorganisation erhalten Sie in unseren Tuning und Monitoring Kursen.
Heute widmen wir uns dem wichtigen Thema "Schutz vor dem unbeabsichtigten Löschen von wichtigen Datenbank-Objekten".Fast jeder hat schon einmal aus Versehen das falsche Objekt gelöscht (ooops ich auch). Wenn es sich dabei um eine wichtige Applikationstabelle handelt,ist der Reparaturaufwand oft sehr erheblich. Wenn Sie natürlich FLASHBACK DATABASE (leider nur in der Enterprise Edition erhältlich) zur Verfügung haben, geht es etwas schneller :-)Wir erstellen uns eine Liste mit schützenswerten Objekten in einer eigenen Tabelle:
Dann erzeugen wir eine Applikationstabelle, die gegen DROP und TRUNCATE geschützt werden soll:
Diese Tabelle gehalts_tab wird in die Liste der geschützten Objekte aufgenommen:
Ein DDL Trigger kümmert sich um die Prüfung, welche DROP/TRUNCATE denn abgebrochen werden:
Wir testen den DROP und siehe da ...
Fall 2: Tablespace gegen Löschen schützen:
Wenn man ein Objekt nun doch löschen oder leeren möchte, nimmt man es einfach von der Liste in der Tabelle:
Beispiel für eine Tabelle:
Sie können natürlich auch das ganze umdrehen und alle Objekte Schützen, bis auf die, die dann in der Tabelle stehen. Das vereinfacht die Sache bei vielen Objekten im Schema.Sie sehen, auch ein DBA braucht hin und wieder mal PL/SQL-Kenntnisse, die er sich gerne in einem unserer fünf verschiedenen PL/SQL-Kurse aneignen kann. :-)
Häufig möchte man einen Trigger kurz einmal ausschalten, eine Aktion ausführen und ihn dann wieder einschalten.
Nur dann vergisst man das Einschalten wieder.
Das muss nicht sein:-)
Die folgende Routine schaltet einen gewünschten Trigger im aktuellen Schema zwar auf DISABLE, setzt ihn aber nach einer frei wählbaren Zeitspanne (in Minuten) wieder auf ENABLE.
Solche und weitere tolle Tricks lernt man in einem unserer PL/SQL Kurse.
Wenn Sie in PL/SQL Prozeduren, Funktionen oder Packages schreiben möchten und auf Objekte von anderen Schematas zugreifen, dann müssen Sie die dazugehörigen Rechte direkt bekommen haben und nicht über eine Rolle.
Nur stellt sich die Frage, welche Rechte habe ich direkt und welche über eine Rolle bekommen?
Das Problem löst der folgende SELECT:
Die Ausgabe könnte dann z.B. so aussehen:
Oder für Objektrechte:
Das ist nur ein Ausschnitt unserer vielfältigen Beispiele zu Oracle Datenbanken.
Kommen Sie in einen unserer PL/SQL Kurse und Sie bekommen da eine ganze Menge mehr zu sehen ...
Das JSON-Format (Java-Script Object Notation) erfreut sich zunehmender Beliebtheit, da es im Vergleich zu XML wesentlich weniger Overhead hat. Ausserdem ist es einfacher; es kennt im wesentlichen nur Wertepaare und Arrays. Diese können hierarchisch aufgebaut sein und entsprechen der JS-Notation.
Ein Beispiel:
Der "Wert" zu "sessions" ist hier ein Array von 2 Objekten. Jedes dieser Objekte hat die skalaren Werte sessionId, subject, startTime und endTime sowie das Array attendees. organizer wiederum stellt ein untergeordnetes Objekt dar.
Seit Version 12.1.0.2 unterstützt nun auch Oracle dieses Format nativ mit speziellen Operatoren und Funktionen. In früheren Versionen gibt es zumindest die Möglichkeit, das Package APEX_JSON zu nutzen, das Bestandteil von APEX 5 ist.
Für die Speicherung in der Datenbank gibt es keinen eigenen Datentyp, je nach Größe bieten sich VARCHAR2 oder CLOB an, aber mit Hilfe des Operators IS JSON kann die Einhaltung des Formats über einen Constraint erzwungen werden:
Das Gegenstück zu IS JSON ist IS NOT JSON. Beide Operatoren kann man z.B. auch in der WHERE-Klausel nutzen. Das macht natürlich nur dann Sinn, wenn eine Spalte auch normale Strings enthalten kann. (Auf bestimmte Pfade kann man übrigens über json_exists abprüfen.)
JSON_VALUE UND JSON_QUERYUm JSON auszuwerten, muss man in aller Regel mit Pfadangaben arbeiten, ähnlich wie bei XML. Für skalare Werte verwendet man JSON_VALUE, für nicht skalare JSON_QUERY. Dabei durchwandert man die Hierarchie über Punktnotation. Ausgangspunkt ist immer das JSON-Dokument selber, das durch $ gekennzeichnet wird. Standardmäßig wird der Wert als String zurückgegeben; über RETURNIN kann man aber NUMBER als Datentyp angeben:
Das Standardverhalten von JSON ist sehr fehlertolerant; deshalb kann ein leerer Wert auch bedeuten, dass die Abfrage fehlerhaft ist. Will man bei falschen Pfadangaben z. B. einen Fehler zurückbekommen, muss man das explizit mit ERROR ON ERROR anfordern (der Default ist NULL ON ERROR):
Eine Alternative zu JSON_VALUE und JSON_QUERY ist noch die Punktnotation:
Die funktioniert aber nur, wenn die Tabelle wie oben mit einem Check-Constraint angelegt wurde, der JSON erzwingt. Wenn nicht, bekommt man die Fehlermeldung:
JSON_TABLEJSON_TABLE ist für die Verarbeitung von JSON die umfassendste und interessanteste Funktion. Analog zu XMLTABLE für XML ermöglicht JSON_TABLE die relationale Darstellung der Inhalte:
Übergeben werden dabei
Auch auf Inhalte eines Arrays kann zugegriffen werden, indem man den Index mitgibt. Die Zählung beginnt bei 0. Ein nicht vorhandener Index führt auch hier standardmäßig nicht zu einem Fehler:
Auch hier kann nach der Pfadangabe ergänzt werden ERROR ON ERROR. Dann wird die zweite Abfrage mit einem Fehler quittiert.
JSON IN PL/SQLWesentlich interessanter als in Tabellen ist JSON jedoch als Austauschformat, z. B. bei Webservices (application/json). Auch hier kann man sehr gut mit JSON_TABLE arbeiten.
Angenommen, Sie rufen einen entsprechenden Webservice über PL/SQL auf, der die Daten nicht in Form einzelner Datensätze liefert sondern im folgenden Format und Sie wollen die Datensätze in normale relationale Tabellen eintragen:
Zunächst einmal können Sie den http-Response in eine VARCHAR2- oder CLOB-Variable einlesen (normalerweise werden mehr als zwei Datensätze übertragen), die dann Ihr JSON-Dokument darstellt. Dann brauchen Sie nur noch den richtigen Ausgangspunkt für den Pfad, um zunächst einmal die Daten der Sessions auszulesen. Da Sie alle Sessions haben wollen, ist das $.sessions[*] . Der Stern (*) bedeutet "alle".
Und natürlich wollen Sie auch die Teilnehmerdaten auslesen. Hier kommt dann nested pathOracle Dokumentationsübersicht von Version 11.2 bis Oracle 21cBereich:DBA:Monitoring:PL/SQL:SQL, Version: ab RDBMS 11.2:RDBMS 12.x:RDBMS 18.1:RDBMS 18.3:RDBMS 19.1:RDBMS 19.3:RDBMS 20.1, Letzte Überarbeitung: 05.07.2023Keywords:Oracle Doku, Oracle DocHaben Sie auch schon einmal verzweifelt nach der richtigen Oracle Dokumentation Ihrer Oracle Datenbank gesucht.Leider hat Oracle im Gegensatz zu Postgres keinen einheitlichen Doc Server, sondern legt die Dokumenation immer auf einen neuen Server...Die folgende Matrix soll Ihnen die Suche nach der richtigen Version für Ihre Oracle Datenbank helfen 11.212.112.218c19c21c23cOracle HauptseiteDokuDokuDokuDokuDokuDokuDokuSQL DocDokuDokuDokuDokuDokuDokuDokuPL/SQL DocDokuDokuDokuDokuDokuDokuDokuPL/SQL PackagesDokuDokuDokuDokuDokuDokuDokuOracle Datenbank AdministrationDokuDokuDokuDokuDokuDokuDokuOracle Upgrade GuideDokuDokuDokuDokuDokuDokuDokuOracle ReferenceDokuDokuDokuDokuDokuDokuDokuOracle ConceptsDokuDokuDokuDokuDokuDokuDokuOracle Database UtilitiesDokuDokuDokuDokuDokuDokuDokuOracle Backup & RecoveryDokuoracleDokuDokuDokuDokuDokuOracle Doc ServerUnd für alle Oracle APEX Fans:APEX Release18.219.119.220.120.221.222.122.223.123.2Oracle APEX HauptseiteDokuDokuDokuDokuDokuDokuDokuDokuDoku APEX Install GuideDokuDokuDokuDokuDokuDokuDokuDokuDoku APEX App Builder User GuideDokuDokuDokuDokuDokuDokuDokuDokuDoku APEX API ReferenceDokuDokuDokuDokuDokuDokuDokuDokuDoku Nun kommen Sie bei Ihrer Doku-Suche schneller zum Ziel. Wer die Dokumentation nicht so gerne liest, kann natürlich auch gerne in eine von unseren Schulungen kommen.Weitere Interessente Artikel zum Thema:Oracle APEX 19.2 installieren für Oracle XE 18c20 CREATE TABLE Beispiele für Oracle (Version 10.2 bis 21c)Export von allen APEX KomponentenEmpfohlene Schulungen zum Thema:APEXAPEX IIAPEX KompaktDB Tuning IIDBADBA IIData Warehouse PL_SQLPL_SQL IIPL_SQL KompReorg + WartungSQL ISQL IISecurity ISecurity II
Haben Sie auch schon einmal verzweifelt nach der richtigen Oracle Dokumentation Ihrer Oracle Datenbank gesucht.Leider hat Oracle im Gegensatz zu Postgres keinen einheitlichen Doc Server, sondern legt die Dokumenation immer auf einen neuen Server...Die folgende Matrix soll Ihnen die Suche nach der richtigen Version für Ihre Oracle Datenbank helfen
Und für alle Oracle APEX Fans:
Nun kommen Sie bei Ihrer Doku-Suche schneller zum Ziel. Wer die Dokumentation nicht so gerne liest, kann natürlich auch gerne in eine von unseren Schulungen kommen.
In den letzten Jahren wird von vielen Entwicklern verstärkt das Tool Visual Studio Code bei der Entwicklung verwendet.Wir haben das Tool in Verbindung mit Oracle SQL und PL/SQL mal näher angesehen und beschreiben in diesem Tipp die EinrichtungNetterweise gab es zu dem Thema schon einen Blog von Morten Braten, der aber noch die Version 0.1.0 verwendet, aktuelle Version ist 2.0.0 (Stand Juni 2019). Wir haben ein paar Sachen ergänzt und korrigiert, damit es unter der aktuellen Visual Studio Code Version läuftSie können Visual Studio Code hier herunterladen.Zusätzlich sollten Sie folgende Extensions installieren:
Extensions werden installiert durch Klick auf das zweite Icon von unten, links im Hauptfenster (Screenshot (1)). Danach im Bereich "Search..." den gesuchten Namen eintragen, Suche starten und bei der gewünschten Extension auf "Install" klicken.Danach legen Sie bitte einen Ordner im Betriebssystem an (z.B. c:\temp\VC)Gehen Sie nun auf File/Open Folder und wählen Sie den Ordner aus. Der Inhalt des Ordners wird Ihnen links (Screenshot (2)) angezeigt.Wenn Sie auf eine Datei (links) klicken, wird der Inhalt im Fenster (Screenshot (3)) angezeigt.Nun müssen wir einige Konfigdateien anlegen, die Visual Studo Code sagen, was zu tun ist.
Datei 1: _run_sqlplus.bat
Datei 2: _show_errors.sql
und die wichtigste, Datei 3: task.json im Unterordner .vscode
Passen Sie bitte in dieser Datei in der Zeile "args" ihre Daten bzgl. Benutzername/Passwort und Datenbank Daten an.Nun können wir loslegen:Wir öffnen den Ordner (File/Open Folder), bei uns war das der Ordner c:\temp\VCIn diesen Ordner legen Sie eine bereits existierende SQL bzw. PL/SQL Datei (in unserem Screenshot(2)) z.B: die Datei vc_test.sqlWenn Sie eine neue Datei erstellen möchten, klicken Sie mit der rechten Maustaste in den Dateibereich, Menue: New File. Geben Sie einen Namen an, dann wird die Datei im Ordner angelegt.Klicken Sie also auf die gewünschte Datei, die dann im rechten Fenster angezeigt wird.Ändern Sie ggf noch etwas ab und speichern Sie Datei (z.B. durch STRG-S)Drücken Sie nun STRG-Shift-B (für Build) und ein kleines Fenster öffnet sich oben.Wählen Sie dort ./_run_sqlplus.bat aus und die Datei wird in Oracle SQL*Plus ausgeführt.Die Ausgabe wird im Reiter "TERMINAL" unten ausgegeben (Screenshot(4)). Sollte in der Select Liste dort nicht _run_sqlplus stehen, dann bitte ändern.Mit dem Mülleimer Symbol (Screenshot(5)) kann das Fenster wieder geleert werden.Mit Klick auf das Dateiensymbol (Screenshot(6)) werden die aktuellen Dateien im Ordner angezeigt, ein nochmaliger Klick öffnet den Code als Vollbild.Wer es etwas kürzer mag (aber unflexibler), kann als tasks.json Datei auch die folgende verwenden:
Dann kann man sich auch die Dateien _run_sqlplus.bat und _show_errors.sql sparen hat dann aber
Weitere tolle Tipps erfahren Sie in einem unserer vielen Entwicklungskurse...
Wer kennt es nicht, immer wenn man mal listagg benötigt, reciht der Speicherplatz nicht, weil listagg maximal 32767 Bytes zurückgeben kann.Mit einem kleinen Trick klappt es auch mit CLOB als Rückggaabetyp. Dazu verwenden wir die XML Funktion XMLELEMENT.Keine Angst, sie müssen kein XML dazu beherschen:-)Wir haben das ganze in eine WITH Funktion gekappselt, für den Fall, dass Sie kein CREATE PROCEDURE RECHT haben.Die Funktion hat bis zu 3 Parameter,column_name ist der Name der Spalte, die zusammengefasst werden solltable_name ist der Name der Tabelle, in der die Spalte enthalten istwhere_cond ist Optional und beinhaltet eine Filterklausel Default 1=1 also alles)order_by Optional kann auch ein Sortierreihenfolge bei der Ausgabe definiert werden (Default: Sortiert nach der Ausgabespalte)delimiter ist auch Optional und gibt das Trennzeichen zwischen der Ausgabe der Spaltenwerte an (Default ',' )
Wenn Sie das Ganze als eigenständige Funktion anlegen möchten:
Testcases:
Damit können Sie nun (fast) unbegrenzt die Werte einer Spalte zusammenfassen und weiterverarbeiten.
Wenn man in seiner Oracle Datenbank Trigger zu Audit-Zwecken verwendet, ist man erstaunt, das als Apex Benutzer nicht der Anmeldebenutzer sondern ANONYMOUS oder APEX_PUBLIC_USER erscheint.Das Problem kann man natürlich mit ein paar PL/SQL Objekten lösen.:-)
Etwas unbekannt ist das Oracle Objekt Context, mit dem die Funktion sys_context erweitert werden kann.Die Funktion sys_context besteht aus zwei Parametern:Parameter 1: userenvParameter 2: ca 60 Stück u.a. OS_OSER, SESSION_USER, ...Wir können die Funktionen nun erweitern, indem wir den ersten Parameter ändern auf apex_env
Die Context Funktion benötigt zum Auffruf immer ein Package, das wir hiermit anlegen:
Führen wir einen Testcase durch und setzen den Context manuell:
Nun können wir den Context wieder auslesen:
Jetzt erstellen wir uns eine Audit-Tabelle, in der stehen soll, welcher APEX Benutzer auf welcher Seite, in welcher APP mit welcher Session IDdie Änderung durchgeführt hat.
und der passende Trigger dazu:
Jetzt brauchen wir nur noch den kleinen Testblock in unsere APEX-App einbauen:Gehen Sie dazu auf: Edit Application Properties / Security und dort auf Database Session.Im Bereich "Initialization PL/SQL Code" geben Sie dort ein:
Weitere Tipps und Tricks erfahren Sie in einem unserer APEX oder PL/SQL Kurse (auch als Video-Streaming)
In unserem heutigen Beitrag wollen wir uns mit dem Thema beschäftigen, wie man aus einem JSON String wieder die Daten extrahieren kann.Diesen Anwendungsfall hatte ich die letzten 24 Monat sehr häufig, deswegen wurde es mal wieder Zeit sich mit dem Thema JSON in Oracle zu beschäftigen.Wir fangen mit einer kleinen Test-Tabelle basiernd auf den Emp und Dept Daten des Benutzers SCOTT an:Die Besonderheit / Schwierigkeit besteht darin, dass die Mitarbeiter in einem Array eingetragen werden (["CLARK","KING","MILLER"]
Als Bonustrack legen wir einen Oracle Text-Index auf die JSON Spalte:
Wir können dann mit der Funktion JSON_TEXTCONTAINS im JSON String suchen.
Das Ergebnis sieht dann so aus:
Wenn man die Daten der JSON Tabelle wieder extrahieren (und weiterverarbeiten) möchte:
Wenn man mal einen unbekannten JSON String auswerten möchte stellt Oracle dafür auch ein Package zur Verfügung:
Die Ausgabe sieht dann so aus:
}Und nun viel Spaß mit der Auswertung von Daten aus JSON-Ausdrücken. Bei uns wird das Thema ausführlich behandelt in den Kursen Oracle PL/SQL II, Oracle REST und Oracle Dev New Features 12c - 21c.
In vielen unserer Kurse ist JSON ein Thema (z.B. im APEX oder ORDS oder PL/SQL II Kurs). Da die Syntax gewöhnungsbedürftig ist, haben wir mal ein paar Beispiele generiert um die syntax besser zu verstehen.Ausgangslage ist ein JSON String, der in ein relationales Modell überführt werden soll.Übersicht der Beispiele:Beispiel 1, mit 3 WerteBeispiel 2, Verschiedene Datentypen in JSONBeispiel 3, mit Geschachtelten ArraysBeispiel 4, mit 3 Zeilen mit 2 FeldernBeispiel 5, mit 3 Zeilen mit 2 Feldern und einem Root Eintrag (var)Beispiel 6, mit 2 Feldern und 3 ZeilenBeispiel 7, mit Sub-Sub FeldernBeispiel 8, nur einen Wert extrahierenBeispiel 9, Wieviele Elemente hat das Array?Beispiel 10, Sub-Arrays (Darstellungsvariante 1)Beispiel 11, Sub-Arrays (Darstellungsvariante 2) Beispiel 12, Tabelle die von Oracle mit Auto-Rest zurückkommt zerlegenBeispiel 13: JSON String gefiltertBeispiel 1, mit 3 Werte
Ergebnis:
Beispiel 2, Verschiedene Datentypen in JSON:
Hinweis: Die Stunden / Minuten / Sekunden verschwinden hier. Alternative: Datum als Text parsen und mittels to_date in Datum umwandeln.Beispiel 3, mit Geschachtelten Arrays
Beispiel 4, mit 3 Zeilen mit 2 Feldern
Ergebnis
Beispiel 5, mit 3 Zeilen mit 2 Feldern und einem Root Eintrag (var)
Beispiel 6, mit 2 Feldern und 3 Zeilen
Beispiel 7, mit Sub-Sub Feldern
Beispiel 8, nur einen Wert extrahieren
Beispiel 9, Wieviele Elemente hat das Array?
=>4oder
=>[1,3,1]Beispiel 10: Sub-Arrays (Darstellungsvariante 1)
Beispiel 11: Sub-Arrays (Darstellungsvariante 2
Beispiel 12, Tabelle die von Oracle mit Auto-Rest zurückkommt zerlegen
oder ausführlicher (aber nur für eine Ergebnis-Zeile zur Vereinfachung):