Kategorie: Oracle

In Oracle können Sie auch dynamische SQL Statements als Datenquelle für SQL Abfragen nutzen. Aber nicht nur diese Technik wird im folgenden Beispiel erklärt. Zusätzlich können Sie die Daten des dynamischen SQL Statements auch in einer Schleife (Loop) abarbeiten lassen und dadurch auch mehrere Befehl für jeden Datensatz (Record) verarbeiten. Sie haben dadurch die Möglichkeit, komplexe Verarbeitungen (Befehle) in mehrere einzelne Befehle auszuteilen (zu splitten): create or replace PROCEDURE TEST (in_Filter IN number) IS Ssql varchar2(2000); TYPE c1 IS REF CURSOR; the_cursor c1; BEGIN — SQL Basis (Basisstatement) Ssql := ’select distinct kunde.id as kunde_id FROM customer kunde WHERE kunde.status = 1′; — Filter and das Statement anhängen (wenn ein Filter vorhanden) if (in_Filter is not null) then Ssql := Ssql || ‚ AND kunde.code = ‚ || in_Filter; end if; — Daten auslesen OPEN the_cursor FOR Ssql; — Für jeden gefundenen Kunden Aktionen verarbeiten LOOP fetch the_cursor into tmp_kunde_id; EXIT WHEN the_cursor%NOTFOUND; — Historie löschen delete from customer_state where customer = tmp_kunde_id and week 0 end loop; close the_cursor; END;

Mit der Hilfe des folgenden Oracle SQL Befehles können Sie für alle SQL Befehle im SQL Cache herausfinden, wie häufig diese ausgeführt wurden und wieviele Blockzugriffe für die Verarbeitung notwenig waren. select to_char(executions,’999G999G990′) „executions“, to_char(buffer_gets,’999G999G990′) „gets“, to_char(buffer_gets/greatest(nvl(executions,1),1),’999G999G990′) „gets je exec“, to_char(round(100*(1-(disk_reads/greatest(nvl(buffer_gets,1),1))),2),’990D00′) Trefferquote, sql_text from v$sql where buffer_gets > 1000 order by buffer_gets desc; Zusätzlich wird Ihnen die „Trefferquote“ des jeweiligen Befehls im Datencache angezeigt. Durch diese Abfrage können Sie sehr schnell schlecht optimierte SQL-Befehle herausfinden. Eine Trefferquote < 70% deutet meist darauf hin, dass in dem Fall eine Tabelle komplett ohne Index-Zugriffe gelesen wurde. Zur Optimierung sollten Sie dann einen weiteren (oder überhaupt einen) Index hinzuzufügen. Der Befehl kann danach die Daten schneller lesen. Diese Statistik zeigt nur die Befehle, welche sich aktuell im SQL-Cache befinden. Um ein umfassendes Bild der auffälligen SQL Befehle zu erhalten sollten Sie den o.a. SQL Befehl also mehrfach täglich ausführen. Das Intervall hängt dabei stark von der Nutzung Ihrer Oracle Datenbank ab. Auch ein Aufruf zu unterschiedlichen Zeiten gibt aufschlußreiche Informationen an Sie weiter. Die Einschränkung „buffer_gets > 1000“ hilft, die Ergebnismenge zu reduzieren. Durch diese Auswahl werden nur die relevanten Befehle angezeigt. Natürlich können Sie auch diesen Parameter frei anpassen.

Eine gute Übersicht über Tabellen und Views zur Struktur (Tabellen, Felder, Indizies, Primary keys, Foreign keys, Read only, etc.) finden Sie unter: http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/toc.htm Der Aufbau der Views ist kurz und klar dokumentiert. Ideal als Nachschlagewerk für tiefere Datenabfragen.

Um in Oracle Datenbanken die Stored Procedures (gespeicherten Prozeduren) nach Schlüsselworten, Befehlen oder allg. Text zu durchsuchen, nutzen Sie folgenden SQL Befehl: select * from USER_SOURCE where lower(text) like ‚%suchtext%‘ Wichtig ist, dass Sie den Suchtext in Kleinschreibweise eingeben. Das Textfeld wird bei der Suche in Kleinschreibung umgewandelt. Auf diese Weise finden Sie durch den o.a. Befehl auch folgende Daten: select * from USER_SOURCE where lower(text) like ‚%drop table%‘ DROP TABLE Drop Table drop table dROp TAbLe

Um auf einfache Weise in Oracle eine Tabelle, Prozedure, View, Funktion usw. zu löschen, nutzen Sie folgenden Befehl. Dabei wird die Exception (Fehlermeldung / Ausnahmefehler) unterdrückt, sodaß Ihr Script oder Programm fehlerfrei weiter laufen kann: begin execute immediate ‚DROP TABLE NAME_DER_TABELLE‘; exception when others then null; end Oder für eine Prozedur (Stored Procedure): begin execute immediate ‚DROP PROCEDURE NAME_DER_PROZEDUR‘; exception when others then null; end

Unter PHP haben Sie z.Bsp. mit folgendem Code ein Zeitstempel erzeugt: <?php startdatum = time(); ?> In einer Oracle Datenbank wurde dieser Zeitstempel in einem Feld vom Typ „number“ abgespeichert. Nun möchten Sie diese Werte vergleichen. Nutzen Sie dafür folgendes SQL Statement: select startdatum, to_date(‚01.06.2009‘, ‚dd.mm.yyyy‘), to_char(TO_DATE(‚19700101000000‘, ‚YYYYMMDDHH24MISS‘) + NUMTODSINTERVAL(startdatum, ‚SECOND‘), ‚dd.mm.yyyy hh24:mi‘) from Tabellenname Dieses SQL Beispiel zeigt Ihnen die unterschiedlichen Daten und Methoden in einem SQL Query. Passen Sie die Daten einfach an Ihre Bedürfnisse an.

Um in Oracle den Speicherplatz von LOBSEGMENTEN (Speicherbereich für Datenelemente) wieder freizugeben oder dem aktuellen Bedarf anzupassen, nutzen Sie folgende Befehle: ALTER TABLE [Tabellenname] DEALLOCATE UNUSED Oder alternativ: ALTER TABLE [Tabellenname] DEALLOCATE UNUSED KEEP 0M

Damit Sie die Größe (das Volumen) Ihrer Tabellen ausrechnen können, nutzen Sie folgenden SQL Befehl: SELECT TableName, TABLESPACE_NAME, sum(mb) FROM ( SELECT nvl2(l.segment_name, l.table_name, s.segment_name) TableName, s.TABLESPACE_NAME, bytes / 1024 / 1024 mb FROM user_segments s, user_lobs l WHERE s.segment_name = l.segment_name(+) AND ROUND(bytes / 1024 / 1024) > 0 ORDER BY 1, 2) GROUP BY TableName, TABLESPACE_NAME ORDER BY 1; Ihnen werden alle Tabellen mit einer Größe von mehr als 0 MB angezeigt. Leere Tabellen werden nicht angezeigt. Um die Berechnung in GB, KB oder Bytes zu erhalten, ändern Sie bitte die Division im zweiten SELECT Statement entsprechend ab.

Um innerhalb einer „Stored Procedure“ oder „Function“ in Oracle Parameter in eine SQL Abfrage einzubauen, können Sie folgendes Beispiel als Vorlage nutzen: CREATE OR REPLACE PROCEDURE SP_TESTS (in_condition IN VARCHAR2) IS ret_value number; Ssql varchar2(4000); BEGIN DBMS_OUTPUT.PUT_LINE(‚Start …‘); — Prüfen, ob eine Bedingung übergeben wurde — Fall nicht, where mit „1=1“ ergänzen lassen if (in_condition is null OR LENGTH(in_condition) = 0) then condition := ‚ 1=1 ‚; else condition := in_condition; end if; — SQL Query zusammensetzen Ssql := ‚SELECT id FROM demo WHERE ‚ || condition || ‚ ORDER by 1‘; — SQL Query anzeigen DBMS_OUTPUT.PUT_LINE(‚SQL Query: ‚ || Ssql); — Den zusammengebauten SQL Befehl ausführen lassen execute immediate Ssql into ret_value; — Ergebnis anzeigen DBMS_OUTPUT.PUT_LINE(‚Ergebnis: ‚ || ret_value|| ‚%‘); END; Das Script erkennt nach dem Start automatisch, ob eine WHERE Bedingung als Parameter übergeben wurde. Fehlt diese, würde der fehlerhaft SQL so aussehen: SELECT id FROM demo WHERE ORDER by 1 Dadurch würde ein Fehler ausgelöst werden. Durch die Ergänzung mit „1 = 1“ wird der SQL Befehl wieder gültig und kann fehlerfrei ausgefürt werden: SELECT id FROM demo WHERE 1 = 1 ORDER by 1

Mit dieser Funktion erhalten Sie alle Tabellen der Datenbank inkl. der aktuellen Anzahl an gespeicherten Datensätzen. Zusätzlich werden die Summen (Tabellenanzahl, Datensätze) angezeigt: declare cnt number; total number := 0; tabs number := 0; begin for x in ( select owner || ‚.‘ || table_name tab from dba_tables) loop execute immediate ’select count(*) from ‚ || x.tab into cnt; total := total + cnt; tabs := tabs + 1; dbms_output.put_line(‚Table: ‚ || x.tab || ‚, Record count: ‚ || TO_CHAR(cnt)); end loop; dbms_output.put_line(‚Total ‚ || TO_CHAR(total) || ‚ rows in ‚ || TO_CHAR(tabs) || ‚ tables‘); end;

In Oracle kann aus einer Kalenderwoche (KW) einfach das Startdatum und Enddatum berechnet werden: SELECT TRUNC(TO_DATE(‚25112009‘, ‚DDMMYYYY‘), ‚IYYY‘) + (26 – 1) * 7 AS date_from, TRUNC(TO_DATE(‚25112009‘, ‚DDMMYYYY‘), ‚IYYY‘) + (26 – 1) * 7 + 6 AS date_to FROM sys.dual; Das Beispiel gilt für die Woche 26/2009. Für andere Kalenderwochen ändern Sie bitte beide Werte „26“ im SQL Statement in Ihre gewünschte Woche um. Das Jahr (im Beispiel „2009“) ggf. ebenfalls angepasst werden. Das folgende Beispiel zeigt Ihnen den Aufbau der Query: SELECT TRUNC(TO_DATE(‚2511[Jahr]‘, ‚DDMMYYYY‘), ‚IYYY‘) + ([KW] – 1) * 7 AS date_from, TRUNC(TO_DATE(‚2511[Jahr]‘, ‚DDMMYYYY‘), ‚IYYY‘) + ([KW] – 1) * 7 + 6 AS date_to FROM sys.dual; Dabei ist das Datum im SQL Befehl (hier der 25.11.) unwichtig.

Mit dem folgenden SQL Script können Sie alle in der Datenbank gespeicherten Stored Procedures (SP) auflisten: select * from user_procedures

Mit dem Befehl desc Muster wird Ihnen der Aufbau der Tabelle „Muster“ angezeigt. Folgende Daten werden als Liste angezeigt: Table Column Datentyp Länge Gesamtstellenzahl Anzahl Nachkommastellen Primärschlüssel Auf Null setzbar Standard Kommentar

Wenn Sie eine Zahl mit führenden Nullen auffüllen möchten, nutzen Sie folgenden Befehl: select to_char(zahlfeld, ‚0000000‘) Würde als Beispiel im Feld „Zahlfeld“ eine 2 stehen, erhalten Sie 000002.

Wenn Sie unter Oracle das SqlPlus Programm als sysdba (Datenbank-Administrator) starten möchten, dann nutzen Sie folgende Befehle: Bei Oracle 8.0 (Clientversion): sqlplus ’sys/oracle@datenbank as sysdba‘ Bei Oracle 8i: (Meist geht auch die Eingabe wie unter Oracle 8.0) sqlplus \“sys/oracle@datenbank as sysdba\“ Bei Oracle 9i: sqlplus „sys/oracle@datenbank as sysdba“

Mit dem folgenden SQL Befehl können Sie auf Ihrer Oracle Datenbank prüfen, wie der „In-Memory Sort Ratio“ auf Ihrem Datenbankserver ist: SELECT a.value/(b.value + a.value) „Sort Ratio“ FROM v$sysstat a, v$sysstat b WHERE a.name = ’sorts (memory)‘ AND b.name = ’sorts (disk)‘ Ihnen wird als Ergebnis eine Zahl angezeigt. Bei gut optimierten Datenbanken liegt der „In-Memory Sort Ratio“ über 0,95 (also 95%). Im Beispiel würde es bedeuten, dass mehr als 95 % sämtlicher Sortieraktionen im Hauptspeicher stattfinden.

Um Daten automatisch entweder in die Tabelle einzufügen oder upzudaten, können Sie den MERGE Befehl von Oracle nutzen. Im folgenden PHP-Beispiel wird die Nutzung verdeutlicht. Dabei ist zusätzlich eine automatische Löschung des Datensatzes eingebaut, wenn kein Benutzername übergeben wurde (Erklärung s.u.): foreach ($users as $id => $username) { if (empty($username)) $sql = „DELETE FROM users WHERE id = $id“; else { $sql = „MERGE INTO users U USING dual ON (dual.dummy is not null and U.id = $id) WHEN MATCHED THEN UPDATE SET name = $username WHEN NOT MATCHED THEN INSERT (id, name) VALUES ($id, $username)“; } } Als SQL Beispiel: MERGE INTO users U USING dual ON (dual.dummy is not null and U.id = 10) WHEN MATCHED THEN UPDATE SET name = ‚Max Muster‘ WHEN NOT MATCHED THEN INSERT (id, name) VALUES (10, ‚Max Muster‘) Das Script sucht nach dem Benutzer mit der ID „10“. Wird er gefunden, dann wird der Name per UPDATE geändert. Wird der Eintrag nicht gefunden, dann wird per INSERT ein entsprechender Datensatz angelegt. Bei Fragen oder Anmerkung schreiben Sie uns gerne einen Kommentar. Wir freuen uns auch über Einträge, wenn Ihnen dieser Artikel half.

Die folgenden Oracle Befehle lösen (je nach Umfang aufwändigere) Sortierungen aus: ORDER BY GROUP BY SELECT DISTINCT CREATE INDEX ANALYZE UNION INTERSECT MINUS JOIN Nur Joins von Tabellen mit nicht indexierten Spalten Bei großen Datenmengen können diese Befehle / Optionen so aufwändig werden, dass Sie mehrere Sekunden bis Minuten nur wegen der Sortierung warten. Kombinieren oder kapseln Sie diese Befehle noch, vervielfacht sich der Rechenaufwand für den Datenbankserver und die performance sinkt dementsprechent stark. Um die Performance zu verbessern, können Sie folgende Maßnahmen umsetzen: Sortiervorgänge vermeiden Das Vermeiden der Sortiervorgänge ist die erste (und beste !) Maßnahme. Die folgenden Punkte geben einige Hinweise, wie Sie Sortiervorgänge vermeiden können: Benutzen Sie UNION ALL anstelle von UNION (nicht immer möglich) Indizieren Sie alle Tabellen (Felder), die Sie in einer Join-Operation verwenden Spalten, die Sie in ORDER BY und GROUP BY Funktionen verwenden, sollten mit einem Index versehen werden Benutzen Sie den COMPUTE Befehl für die Analyse von Tabellen und Indizes Vergrößerung der Sort Area Seit Oracle9i ist der Initialisierungsparameter „PGA_AGGREGATE_TARGET“ verfügbar. Dieser legt fest, wie viel Hauptspeicher in der PGA für alle Serverprozesse der Instanz zur Verfügung gestellt wird. Oracle versucht dabei, dass die Größe des Private Memorys nicht über diese Größe

Um aus einem Datum den Monat zu erhalten, hilft der folgende Oracel SQL-Befehl: to_number(to_char(Datumfeld, ’n‘)) Beispiel: Möchte man wissen, ob das Datum im Feld „Startdatum“ im August liegt: to_number(to_char(Startdatum, ’n‘)) = 8

Um Tablespace auf einem Oracle System anzulegen, nutzen Sie folgenden Befehl: CREATE TABLESPACE user_ts DATAFILE ‚c:\oracle\oradata\ora\userts.dbf‘ SIZE 100M AUTOEXTEND ON NEXT 50M Dieses Beispiel erzeugt Tablespace … mit dem Namen „user_ts“ in der Datei „c:\oracle\oradata\ora\userts.dbf“ mit einer Anfangsgröße von 100 Megabyte (MB) einer automatischen Vergrößerung bei Bedarf um jeweils 50 MB

Um eine exportierte Datenbank wieder in Oracle zu laden, stellt Ihnen die Oracle Software den Befehl „imp“ zur Verfügung. Das folgende Beispiel lädt das Datenbankdump aus der Datei „c:\datensicherung.dmp“ in die Struktur (Benutzer) „Test“. Dabei wurde die Datenbank unter der Struktur (Benutzernamen) „Muster“ gesichert. Fehler werden übergangen und der Import wird in der Datei „full_import.log“ protokolliert. Die Grant-Rechte werden nicht gesetzt. Um sich an der Datenbank anzumelden, zu das Importscript den Benutzer „system“ mit dem Passwort „pwd“: imp userid=system/pwd file=C:\datensicherung.dmp log=full_import.log FROMUSER=Muster TOUSER=Test feedback=100 ignore=yes grants=no Der Befehl wird in einer Zeile eingegeben. Sichern Sie regelmäßig Ihre Daten und Datenbanken !