Copying sources and texts (also in parts) for publishing without our permission is NOT ALLOWED. We are fed up with finding our work on other sites (like stackoverflow).
Das Kopieren von Quellcode und Texten (auch in Auszügen) ist nicht erlaubt. Wir haben es gründlich satt, unsere Arbeit auf anderen Webseiten zu finden.

Copying for non-public usage is allowed.           Das private Kopieren und Benutzen ist natürlich erlaubt und erwünscht.

Kategorie "Oracle"

22. Jul 2010
Teaser

Oracle: Dynamischen SQL Befehl in einer LOOP

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 < (select TO_NUMBER(TO_CHAR(SYSDATE,'IW')) as week from dual);

    -- Stati des Kunden kopieren
    insert into customer_invoices (customer,status,week)
      select cust_id, state_id, week
      from state_tbl
      WHERE status > 0
  end loop;
  close the_cursor;
END;
16. Jul 2010
Teaser

Oracle: SQL Statements optimieren

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.

08. Aug 2009
Teaser

Oracle: Datenbank interne Views (Details zur Datenbank)

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.

03. Aug 2009
Teaser

Oracle: Text, Befehl (string) in Stored Procedures suchen

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

23. Jul 2009
Teaser

Oracle: “Delete if exists” & “Drop if exists” Befehl

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
15. Jul 2009
Teaser

PHP & Oracle: PHP Datum & Oracle Datum vergleichen / umrechnen

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.

09. Jul 2009
Teaser

Oracle: LOBSEGMENT Speicherplatz freigeben / bereinigen

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
07. Jul 2009
Teaser

Oracle: Größe von Tabellen berechnen (in GB, MB, KB oder Byte)

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.

07. Jul 2009
Teaser

Oracle: Stored Procedures mit dynamischen SQL Befehlen

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
07. Jul 2009
Teaser

Oracle: Tabellen einer Datenbank mit Anzahl der Datensätze auflisten

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;