Einblick in die Oracle Datenbank

<Insert Picture Here>
Einblick in die Oracle Datenbank
Carsten Czarski
Business Unit Database
ORACLE Deutschland B.V. & Co KG
Einblick in die Oracle-Datenbank
Grundlagen
Architektur, Historie, SQL
Anwendungsentwicklung
Application Express
Benutzerfehler selbst – ohne Backup – korrigieren
Flashback Query
Relevante Informationen besser finden
Ähnlichkeitssuche, Oracle TEXT
Gängige Aufgaben schneller erledigen
Kreuztabellen, reguläre Ausdrücke, XML erzeugen, ...
2
So fing alles an...
Ed Oates
Bruce Scott
Bob Miner
Larry Ellison
3
Oracle Corporation
Headquarter in Redwood Shores in Kalifornien
4
Oracle Corporation
Gegründet 1977
85 000 Angestellte
20 000 Entwickler
7 500 Support-Techniker
13 000 Consulting Experten
20 000 Partner
320 000 Kunden davon …
280 000 Datenbank
80 000 Fusion Middleware
43 500 Applications
5
Historie im Überblick
2009
2007
2005
Oracle 11g Release 2
Oracle 11g
Oracle 10g Release 2
2003
2002
2001
1999
1997
Oracle 9i Release 2
Oracle 9i
Oracle 8i
Oracle 8
...
1979
Oracle Version 2
6
Die Datenbank
Verfügbar auf allen gängigen Plattformen:
Unix , Linux, Windows etc
Erhältlich in verschiedenen Editionen
Standard-, Enterprise-, Express- Edition
Die Standard-Komponenten sind:
Prozesse:
User
Hintergrundprozesse
Speicher
Dateien
Memory
Monitoren, Konfigurieren, Entwickeln über Werkzeuge
7
Oracle Datenbank Architektur
Ein Auschnitt
Oracle Instance
System Global Area
Dispatcher
Processes
Buffer Cache
Shared SQL Log Buffer
Dedicated
Server
RECO
PMON
Shared
Servers
DBWR
Query
Servers
LGWR
SMON
Kontrolldateien
Datenbank-Dateien
Redolog-Dateien
8
Enterprise Manager- Homepage
9
Datentypen in der Datenbank
Beispiele
Oracle
Datentypen
„Built-In“
Datentypen
„Supplied“
Datentypen
„User-Defined“
Datentypen
Beispiele für strukturierte Datentypen:
XML Type
Object Type
CHAR(n), VARCHAR2(n),
Media Type
Varying Array
NUMBER, BINARY_FLOAT,
Spatial Type
Nested Table
DATE, TIMESTAMP
Any Type
ROWID,
Beispiele für unstrukturierte Datentypen:
BLOB, CLOB, BFILE, SECUREFILE
10
Wie kommen die Daten in die Datenbank?
SQL, PL/SQL, Java ...
Spezielle Lade-Werkzeuge wie
SQL*Loader
Data Pump
Konstrukte ausserhalb der Datenbank wie
External Tabelle
Über Mount eines Filesystems wie
Datenbank-Filesystem
11
INSERT Statement
Effizienz erhöhen mit:
Parallelisierung
Direct LOAD
Wenige Tabellen Scans
Beispiel : Ein Multi Table INSERT Statement
fügt Daten gleichzeitig in mehrere Ziel-Tabellen ein
erspart mehrfaches Scannen der Originaldaten
Syntax-Ausschnitt:
INSERT FIRST|ALL [condition]
INTO[tablename]...
INTO[tablename]
SELECT ...
12
External Tables
Flat Files ausserhalb der Datenbank
Flat Files beschreibbar mit SQL Loader Syntax
Können zusätzlich aufbereitet sein (Präprozessor)
Als Tabelle in der Datenbank verfügbar
Read Only Zugriff (SELECT) – kein DML
keine Indizes
Zugriff über logisches Directory
Externe Tabelle in der
Datenbank
Präprozessor
(gunzip, zcat)
Datei im
Originalzustand
(file.txt.gz)
13
Database Filesystem
Ein Dateisystem in der Datenbank
Zugriff wie auf ein Dateisystem
Betriebssystem-Kommandos, Standard-Bibliotheken
Alle Dateien liegen in Datenbanktabellen
SQL und PL/SQL Zugriff parallel möglich
14
Die Dateisystem-Sicht für eine Tabelle
Tabelle mit
BLOB-Spalten
Implementierungen
DBMS_DBFS_CONTENT
DBMS_DBFS_CONTENT_SPI
Abstrakte Hierarchie von Dateien und Ordnern
Mount auf Linux-Systemen möglich (mod_fuse)
JDBC
PL/SQL
Linux Mount
(mod_fuse)
Gemeinsame
Schnittstelle
File / Folder
Zugriffe
Hierarchie
15
Beispiel: Database Filesystem
Dateisystem MYFS erstellen
16
Beispiel: Database Filesystem
Linux-Mount des Dateisystems MYFS
17
Beispiel: Database Filesystem
Linux-Mountpoint: /oracle/dbfs
18
Beispiel: Database Filesystem
Datentabelle zu Beginn
19
Beispiel: Database Filesystem
Dateien per Linux Filecopy kopieren ...
20
Beispiel: Database Filesystem
Datentabelle nach dem Linux-Filecopy
21
Einblick in die Oracle-Datenbank
ANWENDUNGEN ENTWICKELN
22
Erwartungen an die Anwendungsentwicklung
Budget einhalten
Kurze Lernkurve
Intuitive Oberfläche
Skalierbarkeit
Rechtzeitige Fertigstellung
Moderne Anwendung
23
Erwartungen an die Anwendungsentwicklung
Bedarf nach unabhängigen Lösungen
Bedarf nach schnellen Lösungen
Bedarf nach einfachen Lösungen
Bedarf nach günstigen Lösungen
24
Lösung: Oracle Application Express
Die Vorteile einer Einzelplatzdatenbank ….
Einfache Bedienung
Schnelle Ergebnisse
Flexibilität
Kombiniert mit einer zentralen Datenbank
Sicherheitskonzepte
Datenintegrität
Skalierbarkeit
Verfügbarkeit
... durch Web-Technologie überall verfügbar!
25
APEX Workspaces
Unabhängige, private Arbeitsbereiche
Unabhängige Entwicklung und Administration
Eigene Applikationsverwaltung
Eigene Userverwaltung
Zentrale Datenbank
Zentrale Wartung
Zentrale Datenhaltung
26
Daten ins Web bringen – schneller geht es nicht!
27
Einblick in die Oracle-Datenbank
BENUTZERFEHLER OHNE
BACKUP KORRIGIEREN
28
Flashback Query
Abfrage mit AS OF-Klausel oder DBMS_FLASHBACK
Alte Zustände wieder sichtbar
Über Transaktionsgrenzen hinweg
select * from emp
as of timestamp (systimestamp – interval '5' minute)
select * from emp
as of scn (871876)
Merkmale
Informationen werden aus UNDO-Tablespace geholt
Steuerung durch Parameter UNDO_RETENTION
Tabellendefinition darf sich nicht ändern
29
Flashback Query: Package DBMS_FLASHBACK
Flashback "aktivieren"
dbms_flashback.enable_at_time(
query_scn => (systimestamp – interval '5' minute)
);
dbms_flashback.enable_at_system_change_number(
query_time => 871876
);
Abfragen durchführen
select * from emp
as of timestamp (systimestamp – interval '5' minute)
Flashback deaktivieren
dbms_flashback.disable;
30
Flashback Query
Wie weit kommt man "in die Vergangenheit" ...?
Abhängig von
Der Größe des UNDO-Tablespace
Der Transaktionslast auf der Datenbank
Parameter UNDO_RETENTION
Angabe in Sekunden: Default ist 900 = 15 Minuten
"Ziel" der Datenbank für Flashback Query
UNDO-Tablespace wächst entsprechend, wenn möglich
31
Einblick in die Oracle-Datenbank
RELEVANTE INFORMATIONEN
BESSER FINDEN
32
Unscharfe Namenssuche
Oracle 11.2.0.2
33
Oracle Text Name Search Feature
Namenssuche
mit Rücksicht auf verschiedene Schreibweisen
in unterschiedlichen Kulturen
Anwendung eines Regelwerks
Basis ist Oracle TEXT
Volltextindex nötig – User Data Store für relationale Daten
SQL-Funktion CONTAINS mit NDATA-Operator
select * from names
where contains(name, 'NDATA(name,Tsaarski)') > 0
ID
NAME
---------- ---------------------------------------4 Carsten Czarski
34
Exkurs: Was ist Oracle TEXT?
Spezieller Volltextindex (CTXSYS.CONTEXT)
SQL-Funktionen zur Recherche (CONTAINS)
Linguistische Abfragefunktionen
Relevanz-Ranking
Ergebnis-Aufbereitung: Highlight, Markup, Snippet
Volltextindex zwingend nötig
select score(1), dokument
from dokument_tab
where CONTAINS(dokument, 'Software AND Oracle') > 0
35
Oracle TEXT: Indizierung
Unterstützung aller gängigen Datentypen
XMLTYPE, VARCHAR2, CLOB, BLOB
Filter bei Binärdaten
Achtung: Textindex ist asynchron
Mehrere Indizes pro Tabelle möglich
create index idx_textindex
on dokument_tab (dokument)
indextype is CTXSYS.CONTEXT
parameters ('{index-parameters}')
36
Oracle TEXT: Einige Abfragemöglichkeiten
Exakte Wort/Phrasensuche
... where contains(text, ‘Hund’)>0
Logische Kombinationen
... where contains(text, ‘Hund AND Katze’) >0
Wildcard-Suche
... where contains(text, ‘Hu%d AND Kat_e’) >0
Einfaches Fuzzy matching
... where contains(text, ‘?Hunt’) >0
Namenssuche
... where contains(text, ‘NDATA(name, Hunt)’) >0
Multilinguale Stammsuche
... where contains(text, ‘$läuft’ ) >0
37
Einblick in die Oracle-Datenbank
GÄNGIGE AUFGABEN
SCHNELLER ERLEDIGEN
38
Reguläre Ausdrücke in SQL und PL/SQL
REGEXP_LIKE
REGEXP_INSTR
REGEXP_SUBSTR
REGEXP_REPLACE
REGEXP_COUNT
Existiert das Muster?
Position des Musters?
Muster extrahieren
Muster ersetzen
Wie oft existiert das Muster?
select * from message_table
where REGEXP_LIKE(message, 'ORA-.[0-9]{5}')
select REGEXP_SUBSTR(
'Max Mustermann, 80992 München',
'[[:digit:]]{5}')
from dual
39
Reguläre Ausdrücke: Weitere Beispiele
Reduziert komplexe Logik auf eine Zeichenkette
Email-Adresse:
^[a-z0-9\-_]?[a-z0-9.\-_]+[a-z0-9\-_]?@[a-z.-]+\.[a-z]{2,}$
URL:
^http[s]?://[-a-zA-Z0-9_.:]+[-a-zA-Z0-9_:@&?=+,.!/~*'%$]*$
KFZ-Kennzeichen
^[A-Z]{1,3}-[A-Z]{1,2} [0-9]{1,4}$
Uhrzeitformat (24h)
^([01]?[0-9]|2[0123])[:]?([0-5][0-9])$
40
XML erzeugen: SQL/XML-Funktionen
XMLElement()
XMLForest()
XMLAgg()
XMLComment()
XMLCDATA()
XMLPI()
XMLRoot()
XMLSerialize()
41
XML erzeugen: Ein Beispiel
SQL-Abfrage (kann auch als View erstellt werden)
select
XMLElement("employee",
XMLElement("ename",
XMLAttributes(empno as "id", hiredate as "hire-date"
),
XMLForest(
ename as "name",
job as "job",
sal as "salary",
comm as "commission"
)
) as xml from emp
42
XML erzeugen: Ein Beispiel
Ergebnis der Abfrage:
<employee>
<ename id="7369" hire-date="1980-12-17">
<name>SMITH</name>
<job>CLERK</job>
<salary>800</salary>
</ename>
</employee>
<employee>
<ename id="7499" hire-date="1981-02-20">
<name>ALLEN</name>
<job>SALESMAN</job>
<salary>1600</salary>
<commission>300</commission>
</ename>
</employee>
43
Weitere Informationen
APEX Community
http://www.oracle.com/global/de/community
DBA Community
http://www.oracle.com/global/de/community/dbadmin
Blog: SQL und PL/SQL auf Deutsch
http://sql-plsql-de.blogspot.com
Blog: Oracle TEXT auf Deutsch
http://oracle-text-de.blogspot.com
44
Fragen & Antworten
45