Oracle Database

Notizen vom Kurs "Oracle Administration" 21.-25. Juni 2010

Oracle Universal Installer

• wird zum Installieren, Aktualisieren oder Deinstallieren von Softwarekomponenten und zum Erstellen einer Datenbank verwendet
• basiert auf einer Java-Engine

Oracle Database Configuration Assistant

• Datenbank erstellen
• Datenbankoptionen konfigurieren
• Datenbanken löschen
• Templates verwalten

SQL *Plus

• Aufruf über
  • Administration > Zugehörige Links > iSQL*Plus (Anmelden als Benutzer "SYSTEM" mit Passwort "miro")
  • Start > Programme > Oracle > Anwendungsentwicklung > SQL Plus
  • über Befehlszeile: "sqlplus system/miro" (Benutzername/Passwort)
• Oracle-Werkzeug, das folgendes ermöglicht:
  • Dialog und Manipulation von Datenbanken (z. B. "select sysdate from dual;", Skript laden, Skript speichern, Historie)
  • Hoch- und Herunterfahren von Datenbanken, Erstellung von Abfragen, Hinzufügen von Zeilen usw.
• Ist eine Teilmenge der SQL-Standardsprache mit speziellen Ergänzungen

Oracle Enterprise Manager (Database Control)

• http://<Rechnername>:1158/em
• vordefinierter Benutzer "SYS" (Passwort "miro", anmelden als "SYSDBA") bzw. benutzer "SYSTEM" (Passwort "miro")
• dient als zentralisierte Systemverwaltung für Systemverwalter
• Werkzeug für Verwaltung, Diagnose und Optimierung mehrerer Datenbanken
• Werkzeug zum Verwalten mehrerer Netzwerkknoten und -dienste von verschiedenen Standorten aus

Oracle Server

• ein Datenbank-Management-Syste, das einen offenen, umfassenden und integrierten Ansatz für die Informationsverwaltung bereitstellt
• besteht aus einer Oracle-Instanz und einer Oracle-Datenbank

Oracle-Instanz

• dient dazu, um auf eine Oracle-Datenbank zuzugreifen
• öffnet immer genau eine Datenbank. Es können auch mehrere Instanzen auf dieselbe Datenbank zugreifen, eine Instanz kann aber nie auf mehr als eine Datenbank zugreifen.
• besteht aus Speicher- und Hintergrundprozess-Strukturen

Oracle-Datenbank

• ist eine Zusammenstellung von Daten, die als Einheit betrachtet werden
• besteht aus drei Dateitypen:
  • Datendateien
  • Kontrolldateien
  • Redo Log-Dateien (für Transaktionen)
• weitere Dateistrukturen:
  • Parameterdatei (enthält die Eigenschaften der Oracle-Instanz)
  • archivierte Redo Log-Dateien (Kopien der Redo Log-Dateien, sofern die Datenbank im Archiver-Modus läuft)
  • Passwortdatei (enthält die Benutzer, welche die Oracle-Instanz hoch- und herunterfahren können)

...

Dynamische Performance-Tabellen

• V$CONTROLFILE
• V$DATAFILE
• V$PARAMETER
• V$SGA
• V$TABLESPACE

Kontrolldatei

• Kleine binäre Datei
• Definiert den aktuellen Status der aktuellen Datenbank
• Erforderlich im Status "MOUNT" beim Hochfahren der Datenbank
• Ein Verlust kann "Recovery" notwendig machen
• Ist mit einer ganz bestimmten Datenbank verknüpft
• Die Kontrolldatei sollte redundant gesichert werden über "Administration > Datenbankverwaltung > Speicherung > Kontrolldateien" gesichert werden.

Inhalt

• Datenbankname
• Zeitstempel der Datenbankerstellung
• Namen und Speicherorte von Datendateien und Redo Log-Dateien
• Aktuelle Sequenznummer der Redo Log-Datei
• Checkpoint-Informationen
• Backup-Informationen (z. B. des Recovery Manager)

Spiegeln der Kontrolldatei bei Verwendung von SPFILE

• Änderung von SPFILE (unter Angabe der bestehenden sowie der neuen Kontrolldatei)

ALTER SYSTEM SET control_files=... SCOPE = SPFILE;

• Herunterfahren der Datenbank

shutdown immediate

• Erstellung zusätzlicher Kontrolldateien durch Kopieren der Kontrolldatei
• Hochfahren der Datenbank

startup;

Spiegeln der Kontrolldatei bei Verwendung von PFILE

• Herunterfahren der Datenbank

shutdown immediate

• Erstellung zusätzlicher Kontrolldateien durch Kopieren der Kontrolldatei
• Hinzufügen der Kontrolldateinamen zu PFILE durch Bearbeiten des Parameters "CONTROL_FILES"

CONTROL_FILES=...;

• Hochfahren der Datenbank

startup pfile='c:\oracle\init.ora';

Abrufen der Kontrolldatei-Informationen

• grafische Oberfläche unter "Administration > Datenbankverwaltung > Speicherung > Kontrolldateien"
• Auflisten von Namen und Status für alle Kontrolldateien der Instanz auf

select * from v$controlfile;

• Auflisten von Namen, Status und Speicherort der Kontrolldateien

show parameter control_files;

Verwendung von Redo Log-Dateien

Redo Log-Dateien

• erfassen alle an den Daten vorgenommenen Änderungen
• stellen einen Recovery-Modus bereit
• können in Gruppen organisiert werden, wobei mindestens zwei Gruppen benötigt werden, unter "Administration > Datenbankverwaltung > Speicherung > Redo Log-Gruppen"

Funktionsweise

• Redo Log-Dateien werden in zyklischer Weise verwendet
• Ist eine Redo Log-Datei voll, so wechselt LGWR zur nächsten Redo Log-Gruppe
  • Dies wird als Log-Switch bezeichnet
  • Checkpoint-Vorgang tritt ebenfalls ein
  • Informationen werden in die Kontrolldatei geschrieben

Mögliche Statusangaben

• Current: In diese wird gerade hineingeschrieben
• Active: enthalten Logeinträge, die noch im DB-Buffer enthalten sind (würde also für ein Instance Recovery benötigt)
• Inactive:

Log-Switch erzwingen

ALTER SYSTEM SWITCH LOGFILE;

Checkpoint erzwingen (z. B. in SQL-Skripten; auf der Konsole macht es wenig Sinn)

ALTER SYSTEM CHECKPOINT;

Hinzufügen von Redo Log-Gruppen mit dem Befehl "ALTER DATABASE" unter Angabe der "Members". Die Redo Log-Dateien sollten nicht allzu klein sein. Beispiel:

ALTER DATABASE
  ADD LOGFILE GROUP 3 ('c:\verzeichnis\u01\log3a.rdo', 'd:\ORADATA\u01\log3b.rdo) SIZE 50M;

Abruf von Informationen über Gruppen und Member über folgende Views:

• V$LOG
• V$LOGFILE

Archivierte Redo Log-Dateien

• Gefüllte Online Redo Log-Dateien können archiviert werden
• Datenbank muss sich im Modus "ARCHIVELOG" befinden
• Zweck: Wiederherstellung der Datenbank bei Ausfall einer Festplatte
• Archivierung erfolgt automatisch durch ARCn
• Bei erfolgreicher Archivierung:
  • wird ein Eintrag in die Kontrolldatei geschrieben
  • werden der Name der archivierten Log-Datei, die Log-Sequenznummer sowie die erste und letzte SCN erfasst

Tablespace und Datendateien

Oracle speichert Daten logisch in Tablespaces und physisch in Datendateien.

Tablespaces

• gehören immer nur zu einer Datenbank
• bestehen aus einer oder mehreren Datendateien
• werden weiter unterteilt in logische Einheiten

Datendateien

• gehören immer nur zu einem Tablespace
• sind ein Repositoiry für Schemaobjektdaten

...Bild logische und physische Sicht...

Arten von Tablespaces:

SYSTEM-Tablespace (Löschen ist nicht möglich)

• wird mit einer Datenbank erstellt
• enthält das Data Dictionary
• enthält das SYSTEM-Undo-Segment

Non-System-Tablespace

• ermöglicht die Speicherung verschiedener Segmente
• erleichtert die Speicherverwaltung
• steuert die einem Benutzer zugeordnete Speichermenge (Quota)

Erstellen über Administration > Datenbankverwaltung: Speicherung > Tablespaces > Erstellen"

Befehl "CREATE TABLESPACE"

CREATE TABLESPACE tablespace
  [DATAFILE clause]
  [MINIMUM EXTEND integer[K|M]]
  [BLOCKSIZE integer [K]]
  [LOGGING|NOLOGGING]
  [DEFAULT storage_clause]
  [ONLINE|OFFLINE]
  [PERMANENT|TEMPORARY]
  [extent_management_clause]
  [segment_managment_clause]
  

Erstellen eines Tablespace mit dem Befehl "CREATE TABLESPACE", wobei der Pfad zur angegebenen Datendatei bereits vorhanden sein muss. Üblicherweise liegen die Dateien unter "c:\oracle\product\10.2.0\oradata\<Rechnername>". Beispiel:

CREATE TABLESPACE userdata
  DATAFILE 'c:\u01\oradata\data01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

Einem Tablespace können weitere Datendateien hinzugefügt werden. Beispiel:

ALTER TABLESPACE tb1
  ADD DATAFILE 'c:\u01\oradata\data02.dbf' SIZE 200M;

Löschen eines Tablespace, dabei wird die zugehörige Datendatei allerdings nicht gelöscht:

DROP TABLESPACE userdata;

Ansehen der vorhandenen Tablespaces unter "Administration > Datenbankverwaltung > Speicherung > Tablespaces". Ansicht der physischen Sicht unter "Datendateien".

Speicherverwaltung in Tablespaces:

• Lokal verwalteter Tablespace
  • freie Extents werden in Tablespace verwaltet
  • Bitmap wird zur Erfassung freier Extents verwendet
• Vom Dictionary verwalteter Tablespace
  • freie Extents werden durch das Data Dictionary verwaltet

EXTENT MANAGEMENT-Klausel

[EXTENT MANAGEMENT [DICTIONARY|LOCAL [AUTOALLOCATEUNIFORM [SIZE integer [K|M]]]]]

• AUTOALLOCATE: Tablespace wird vom System verwaltet, Benutzer kann Extent-Grösse nicht selber festlegen
• UNIFORM: Tablespace wird mit einheitlichen Extents der Grösse "SIZE" Byte verwaltet

Lokal verwaltete Tablespaces (neu)

• Verringerung der Konflikte beim Zugriff auf Data Dictionary-Dateien
• Keine Zusammenführung von freiem Speicherplatz erforderlich
• Beispiel:

CREATE TABLESPACE userdata
  DATAFILE 'd:\prog\oracle\oradata\ora01\data01.dbf' SIZE 100M
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 128K;

Vom Data Dictionary verwaltete Tablespaces (alt bis Oracle 8, sollte nicht verwendet werden!)

• Extents werden im Data Dictionary verwaltet
• Jedes Segment kann über eine unterschiedliche STORAGE-Klausel verfügen
• Beispiel:

CREATE TABLESPACE userdata
  DATAFILE 'd:\prog\oracle\oradata\ora01\data01.dbf' SIZE 100M
  EXTENT MANAGEMENT DICTIONARY
  DEFAULT STORAGE
  (initial 1M NEXT 1M PCTINCREASE 0);

Undo-Tablespace

• dient zur Speicherung von Undo-Segmenten, die gerade bearbeitete Tabelleninhalte speichern
• kann keine anderen Objekte enthalten
• Extents werden lokal verwaltet
• Beispiel:

CREATE UNDO TABLESPACE userdata
  DATAFILE 'd:\prog\oracle\oradata\ora01\data01.dbf' SIZE 100M;

Temporäre Tablespaces

• werden für Sortiervorgänge verwendet (DISTINCT, ORDER BY, GROUP BY, UNION...)
• dürfen keine permanenten Objekte enthalten
• es wird empfohlen, Extents lokal zu verwalten
• Beispiel:

CREATE TEMPORARY TABLESPACE temp
  DATAFILE 'd:\prog\oracle\oradata\ora01\data01.dbf' SIZE 100M
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 4M;

Temporärer Tablespace als Default

• gibt für die gesamte Datenbank gültigen temporären Tablespace als Default an
• verhindert, dass der SYSTEM-Tablespace zum Speichern temporärer Daten verwendet wird, was die Performance verringern würde
• kann nur gelöscht werden, wenn zuvor ein neuer Default-Tablespace verfügbar gemacht wurde
• kann nicht offline gesetzt werden
• kann nicht in einen permanenten Tablespace geändert werden

ALTER DATABASE
  DEFAULT TEMPORARY TABLESPACE temp;

Tablespaces können in den Modus "READ ONLY" gesetzt werden.

• Löst einen Checkpoint aus
• Daten sind nur für Lesevorgänge verfügbar
• Objekte können aus Tablespace gelöscht werden
• Beispiel:

ALTER TABLESPACE userdata READ ONLY;

Tablespace offline setzen

• nicht verfügbar für den Datenzugriff
• Folgende Tablespaces können nicht offline gesetzt werden:
  • SYSTEM-Tablespace
  • Tablespaces mit aktiven Undo-Segmenten

ALTER TABLESPACE tablespace
  [ONLINE|OFFLINE [NORMAL|IMMEDIATE|TEMPORARY]]

• NORMAL: leert alle Blöcke aus der SGA, kein Media-Recovery (die Standardoption)
• IMMEDIATE: kein Checkpoint, gegebenenfalls ist ein Media Recovery erforderlich
• TEMPORARY: lert alle Blöcke für Dateien, die online sind, gegebenenfalls ist ein Media Recovery erforderlich

Beispiel:

ALTER TABLESPACE userdata OFFLINE;

Tablespace online setzen:

ALTER TABLESPACE userdata ONLINE;

Änderung der Grösse eines Tablespace ist auf zwei Arten möglich:

• durch Änderung der Grösse der Datendatei
  • automatisch mit der Klausel "AUTOEXTEND ON", sodass nur eine Datendatei benötigt wird. Beispiel:

CREATE TEMPORARY userdata
  DATAFILE 'd:\prog\oracle\oradata\ora01\data01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

• manuelles Hinzufügen einer Datendatei mit ALTER TABLESPACE, wobei die Datendatei vergrösser oder verkleinert werden kann
  • Nicht belegter Speicherplatz wird in der Datenbank freigegeben
  • Beispiel:

ALTER DATABASE
  DATAFILE 'd:\prog\oracle\oradata\ora01\userdata02.dbf'
  RESIZE 200M;

Hinzufügen einer Datendatei zu Tablespace

• vergrössert den Tablespace durch Hinzufügen weiterer Datendateien (dadurch wird der Eintrag in der Kontrolldatei verändert)
• Hinzufügen der Datendatei mit der KLausel "ADD DATAFILE"
• Beispiel:

ALTER TABLESPACE
  ADD DATAFILE 'd:\prog\oracle\oradata\ora01\userdata02.dbf' SIZE 200M;

Methode mit ALTER TABLESPACE

• Tablespace muss offline sein (ALTER TABLESPACE userdata OFFLINE;)
• Zieldatendateien müssen vorhanden sein
• Datendatei wird mit Betriebssystembefehl kopiert
• Änderung

ALTER TABLESPACE userdata RENAME
  DATAFILE 'c:\u01\oradata\data01.dbf'
  TO 'c:\u02\oradata\data01.dbf';

• anschliessend Tablespace online setzen (ALTER TABLESPACE userdata online)

Methode mit ALTER DATABASE (z. B. wenn SYSTEM-Tablespace verschoben werden muss)

• Datenbank herunterfahren (shutdown immediate;)
• Datendatei wird mit Betriebssystembefehl kopiert
• Datenbank in Modus MOUNT setzen
• Ausführen des Befehls "ALTER DATABASE RENAME FILE"

ALTER DATABASE RENAME
  FILE 'd:\prog\oracle\oradata\ora01\u01.dbf'
  TO 'c:\prog\oracle\oradata\ora02\u01.dbf'

• Datenbank öffnen

Löschen von Tablespaces

• erfolgt mit Befehl "DROP TABLESPACE tablespace"
• Tablespace SYSTEM kann nicht gelöscht werden
• Löschen von Segmenten

DROP TABLESPACE userdata
  INCLUDING CONTENTS;

• Löschen von Datendateien (nur aus Sicht von Oracle - physisch sind sie noch da!)

DROP TABLESPACE userdata
  INCLUDING CONTENTS AND DATAFILES;

• Löschen aller referentiellen Integritäts-Constraints mit zusätzlichem "CASCADE CONSTRAINTS"

Anzeige von Informationen über Tablespaces

• DBA_TABLESPACES (Data Dictionary)
• V$TABLESPACE (select * from v$tablespace)

Anzeige von Informationen über Datendateien

• DBA_DATA_FILES (Data Dictionary)
• V$DATAFILE (select * from v$datafile)

Segmente

• sind die Objekte, die einem Tablespace zugewiesen werden können
• beispielsweise Tabellen, Indizes, Views, Undo-Daten usw.

Storage-Klausel (wichtig nur bis Oracle Database 8)

• Auf Segmentebene kann in einer Storage-Klausel angegeben werden, wie Extents einem Segment zugeordnet werden
• Alle auf Segmentebene festgelegten Speicherparameter setzen die auf Tablespace-Ebene eingestellten Optionen ausser Kraft (ausser MINIMUM EXTENT und UNIFORM SIZE)

Extents

• Speicherbereiche bestimmter Grösse, die einem Segment zugewisen werden
• ein Segment besteht aus einem oder mehreren Extents

Extent-Zuweisung und -Freigabe

• ein Extent ist ein von einem Segment belegter Speicherplatzbereich
• Zuweisung bei Erstellung, Erweiterung oder Änderung des Segments
• Freigabe bei Löschung, Änderung oder Leeren eines Segments

Datenblock

• kleinste Ein- bzw. Ausgabeeinheit
• besteht aus einem oder mehreren Betriebssystemblöcken und wird durch den Parameter "DB_BLOCK_SIZE" bestimmt
• jedes Extent besteht aus Datenbankblöcken von einer Grösse von 2, 4, 8, 16 oder 32 KB (anstatt einer grösseren Blockgrösse wäre zur Verbesserung der Performance auch ein zweiter DB-Buffer in Betracht zu ziehen)
• eine Datenbank kann mit einer Standardblockgrösse und bis zu vier Nicht-Standardblockgrössen erstellt werden

Inhalt eines Datenbankblocks

• der Block-Header enthält Datenbankblockadresse, Tabellenverzeichnis, Zeilenverzeichnis und Transaktionseinträge
  • Der Datentyp varchar2() ist vorsichtig zu verwenden!
• im Datenspeicherplatz werden Zeilen von unten nach oben in den Block eingefügt
• freier Speicherplatz befindet sich in der Mitte des Blocks

Für Daten- und Indexsegmente kann die Speicherplatznutzung über Parameter für die Blocknutzung (auf Tabellenebene) gesteuert werden:

• INITTRANS: anfängliche Anzahl der Transaktionseinträge
• MAXTRANS: maximale Anzahl der Transaktionseinträge
• PCTFREE: Grösse des für das Wachstum bestehender Zeilen reservierten Speicherplatzes (von oben her; Standard 10%)
• PCTUSED: anzustrebender Mindestfüllgrad (von unten her; Standard 40%)

Ansehen der aktuellen Werte über "Administration > Schema: Datenbankobjekte > Tabellen > Erstellen > Speicherung".

analyze table scott.dept compute statistics;
select * from dba_tables where owner='SCOTT';

Nach Berechnung aller Statistiken sollte keine Spalte grösser 0 sein.

select * from dba_tables where chain_cnt > 0;

Standardblockgrösse

• wird beim Erstellen der Datenbank mit dem Parameter "DB_BLOCK_SIZE" festgelegt
• kann nicht geändert werden, ohne die Datenbank neu zu erstellen
• wird für den SYSTEM-Tablespace und die TEMPORARY-Tablespaces verwendet
• Der Parameter DB_CACHE_SIZE gibt die Grösse für die Standard-Blockgrösse an:
  • die Mindestgrösse ist ein Granulat (4 MB - oder 16 MB, falls SGA grösser als 100 MB ist)
  • Standardwert ist 48 MB

Nicht-Standardblockgrösse

• Zusätzliche Caches können mit folgenden dynamischen Parametern erstellt werden:
  • DB_2K_CACHE_SIZE für 2 KB-Blöcke
  • DB_4K_CACHE_SIZE für 4 KB-Blöcke
  • DB_8K_CACHE_SIZE für 8 KB-Blöcke
  • DB_16K_CACHE_SIZE für 16 KB-Blöcke
  • DB_32K_CACHE_SIZE für 32 KB-Blöcke
• DB_nK_CACHE ist nicht zulässig, wenn nK die Standardblockgrösse ist
• die Mindestgrösse für jeden Cache ist ein Granulat

Erstellung von Tablespaces mit Nicht-Standardblockgrösse

• Verwendung der BLOCKSIZE-Klausel zum Festlegen einer Nicht-Standardblockgrösse

CREATE TABLESPACE tbs_1
  DATAFILE 'c:\oracle\oradata\tbs_1.dbf'
  SIZE 10M BLOCKSIZE 4K;

Anzeige von Speicherinformationen über folgende Views

• DBA_EXTENTS
• DBA_SEGMENTS (desc dba_extents;)
• DBA_DATA_FILES

Undo-Segmente

Zweck

• Transaktionsrollback (während normalem Datenbankbetrieb)
• Transaktionsrecovery (während Recovery-Vorgang)
• Lesekonsistenz

Arten (seit Oracle Database 9):

• SYSTEM: Verwendung für Objekte im SYSTEM-Tablespace
• Non-System:
  • automatischer Modus: erfordert einen UNDO-Tablespace (empfohlen ab Oracle Database 9)
  • manuell: erfordert mindestens ein UNDO-Segment (alt, nicht empfohlen)

Automatisches UNDO-Management

• Konfiguration der folgenden beiden Parameter in der Initialisierungsdatei
  • UNDO_MANAGEMENT
  • UNDO_TABLESPACE
• Erstellung von mindestens einem UNDO-Tablespace

UNDO-Tablespace wechseln

• Es kann von einem UNDO-Tablespace zu einem anderen gewechselt werden
• Einer Datenbank kann immer nur ein UNDO-Tablespace zugeordnet werden
• Eine Oracle-Instanz kann mehrere UNDO-Tablespace enthalten, aber nur einer kann aktiv sein
• Wechseln des UNDO-Tablespace

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

Tipps über "Administration > Datenbankverwaltung: Datenbankkonfiguration > Undo-Management > Undo-Advisor". (u. a. Häufigkeit der Undo-Erhaltung)

Zum Festlegen der Grösse des UNDO-Tablespace werden drei Informationen benötigt:

• (UR) UNDO_RETENTION in Sekunden
• (UPS) Anzahl der pro Sekunde erzeugten UNDO-Datenblöcke
• Blockgrösse (DB_BLOCK_SIZE)

Datentypen

Die häufigsten Datentypen sind:

• VARCHAR2(size): Zeichenketten variabler Länge
• CHAR(size): Zeichenketten fester Länge
• NUMBER(p,s): Numerische Daten variabler Länge mit Angabe der Gesamt- bzw. Nachkommastellenzahl
• DATE: Datumswerte (zurück bis etwa 5000 v. u. Z.)
• LONG: Zeichendaten variabler Länge bis 4 GB
• BLOB: Binärdaten bis zu 4 GB

Datentyp ROWID

• ROWID ist ein eindeutiger Indikator für jede Zeile in einer Datenbank
• ROWID ist die schnellste Möglichkeit des Zugriffs auf eine Tabellenzeile
• ROWID werden in Indizes gespeichert, um bestimmte Zeilen einer Tabelle zu finden

Eine ROWID benötigt 10 Byte Plattenspeicher und wird mit 18 Zeichen dargestellt:

• Data Object Number: Jedes Datenbankobjekt bekommt eine eindeutige Objektnummer zugewiesen (5)
• Relative File Number: Jede Datei erhält eine eindeutige Dateinummer (5)
• Block Number: Angabe der Position des Blocks in der Datei (5)
• Row Number: Zeilennummer identifiziert die Position im Block (3)

select * from scott.dept
select rowid from scott.dept

Datetime-Unterstützung

• In Oracle können Zeitzonen in Datumsdaten einbezogen werden
• Zu DATE wurden drei neue Datentypen hinzugefügt:
  • TIMESTAMP
  • TIMESTAMP WITH TIMEZONE (TSTZ)
  • TIMESTAMP WITH LOCAL TIMEZONE (TSLTZ)

Benennungsregeln für Bezeichner

• müssen mit einem Buchstaben beginnen
• dürfen 1 bis 30 Zeichen enthalten
• dürfen nur die folgenden Zeichen enthalten: A-Z, a-z, _, $, #, 0-9
• dürfen nicht den Namen eines anderen Objektes duplizieren, das dem gleichen Benutzer gehört
• dürfen keinem Schlüsselwort entsprechen

Anweisung CREATE TABLE

CREATE TABLE [schema.]table
  (column datatype[DEFAULT exp][,...]);

Folgendes ist anzugeben:

• Tabellenname
• Spaltenname und Datentyp der Spalte

Beispiel:

CREATE TABLE dept
  (deptno NUMBER(6),
   dname  VARCHAR2(14),
   loc    VARCHAR2(13));

Richtlinien zum Erstellen von Tabellen

• Tabellen in separaten Tablespaces speichern
• Verwendung lokal verwalteter Tablespaces, um Fragmentierung zu vermeiden
• wenige Standard-Extent-Grössen verwenden

Einstellen von PCTFREE

Ein höherer Wert sollte eingestellt werden, wenn die Tabelle folgendes enthält:

• Spalten, die anfänglich auf NULL eingestellt sind und erst später durch einen Wert aktualisiert werden
• Spalten, deren Grösse wahrscheinlich als Folge der Aktualisierung wachsen wird

Ein höherer Wert von PCTFREE führt zu einer geringeren Blockdichte.

Zeilenmigration

• Wird PCTFREE auf einen niedrigen Wert eingestellt, so enthält der Block nicht genügend Speicherplatz um eine Zeile aufzunehmen, die als Folge einer Aktualisierung wächst
• wird eine vollständige Zeile wird in einen anderen Block verschoben, so verschlechtert sich die Performance

Zeilenverkettung

• erfolgt, wenn eine Zeile zu gross ist, um in einen beliebigen Block geschrieben zu werden
• in diesem Fall werden Zeilen in Chunks aufgeteilt und in unterschiedlichen Blöcken gespeichert (also grössere Blockgrösse wählen!)

Tabellen leeren

• Beim Leeren einer Tabelle werden alle Zeilen einer Tabelle gelöscht und belegter Speicherplatz freigegeben.
• Dazugehörige Indizes werden geleert

Beispiel:

TRUNCATE TABLE hr.employees;

Index

• ist ein Schema-Objekt
• wird vom Oracle Server verwendet, um den Abruf von Zeilen zu beschleunigen
• reduziert Datenträgerzugriffspfade
• ist unabhängig von der indizierten Tabelle
• wird vom Oracle Server automatisch verwendet und verwaltet

Indizes-Klassifizierung

Logisch

• einspaltige oder verknüpfte Indizes
• eindeutige oder nicht eindeutige Indizes
• funktionsbasierte Indizes

Physisch

• partitionierte oder nicht partitionierte Indizes
• B*-Baum-Indizes
• Bitmap-Indizes

B*-Baum-Index

• speichert eine Liste von ROWIDs für jeden Schlüssel
• es gibt keinen Indexeintrag für eine Zeile, in der eine Schlüsselspalte den Wert "NULL" enthält
• DML-Befehle führen zu einem Umbau des Index

Index erstellen

• automatisch: wenn PRIMARY KEY oder UNIQUE-Constraint verwendet wird

CREATE INDEX index_name ON table (column[,column ...]);

Beispiel:

CREATE INDEX emp_last_name ON employees (last_name);

Richtlinien:

• Abfrage- und DML-Anforderungen ausgewogen berücksichtigen
• in separatem Tablespace speichern
• NOLOGGING für grosse Indizes berücksichtigen

Beispiel:

CREATE INDEX hr.employees_last_name_idx ON hr.employees (last_name);

CREATE BITMAP INDEX orders_region_id_idx ON orders (region_id);

Index neu erstellen

Indizes löschen

Indexdaten abrufen

Bitmap-Indizes

• ist ebenfalls als B*-Baum organisiert, anstatt ROWIDs wird ein Bitmap für jeden Schlüsselwert gespeichert
• gut geeignet für Tabellen mit sehr vielen Zeilen und geringer Kardinalität, Zeilen werden selten geändert

Vergleich

	B*-Baum-Indizes	Bitmap-Indizes
Eignung für	Spalten hoher Kardinalität	Spalten geringer Kardinalität
Aktualisierung von Schlüsselspalten	einfach	aufwendig
Für Abfragen mit OR-Vergleichen	ungeeignet	geeignet
Geeignet für	OLTP	Data Warehousing

Datenintegrität

Daten in einer Datenbank müssen den Integritätsregeln entsprechen. Dies wird erreicht durch:

• Anwendungscode
• Datenbanktrigger
• deklarative Integritäts-Constraints

Constraints (Einschränkungen)

• Constraints erzwingen Regeln auf Tabellenebene
• erzwingen Regeln für DML-Befehle (INSERT, UPDATE, DELETE)
• verhindern das Löschen einer Tabelle, wenn Abhängigkeiten vorhanden sind
• können während oder nach der Tabellenerstellung definiert werden

Constraint-Typen

• NOT NULL: Spalte darf keinen NULL-Wert enthalten ( ist nicht NULL!)
• UNIQUE: gibt eine Spalte oder eine Spaltenkombination an, deren Werte in allen Spalten eindeutig sein müssen
• PRIMARY KEY: identifiziert jede Zeile eindeutig
• FOREIGN KEY: richtet Fremdschlüsselbeziehung ein
• CHECK: gibt eine Bedingung ein, die erfültt werden muss

UNIQUE-Constraint

• jeder Wert einer Spalte oder Spaltengruppe muss eindeutig sein
• lässt die Eingabe von NULL-Werten zu
• Beispiel:

CREATE TABLE mitarbeiter
  (...
   email VARCHAR2(25)
   ...
   CONSTRAINT emp_email_uk UNIQUE (email),
  ...);

Primary Key-Constraint

• erstellt für jede Tabelle einen Primärschlüssel
• für jede Tabelle kann nur ein Primärschlüssel erstellt werden
• Beispiel

dept_id NUMBER(6),
  ...
CONSTRAINT dept_id_pk PRIMARY KEY (dep_id),
  ...

Foreign Key-Constraint

• bestimmt eine Spalte oder Spaltenkombination als Fremdschlüssel
• Fremdschlüssel muss einem vorhandenen Wert in der übergeordneten Tabelle entsprechen
• Beispiel:

...
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id)
  REFERENCES departments(dep_id),
...

CHECK-Constraint

• definiert eine Bedingung, die jede Zeile erfüllen muss
• Beispiel:

...
salary NUMBER(6), CONSTRAINT emp_salary_min CHECK (salary > 0),
...

Constraints hinzufügen mit ALTER TABLE: Die Anweisung ALTER TABLE ermöglicht

• Hinzufügen oder Löschen eines Constraints, ohne jedoch seine Struktur zu ändern
• Aktivierung oder Deaktivierung eines Constraints
• Beispiel:

ALTER TABLE managers
  ADD CONSTRAINT emp_manager_fk
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id);

Contraint löschen

ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;

Contraint aktivieren

ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk;

Integritäts-Constraint-Fehler

• Zeilen mit einem Primärschlüssel können nicht gelöscht werden, die in einer Tabelle als Fremdschlüssel dienen
• Dies wird als "referentielle Integrität" bezeichnet

Profile

• Ein Profil ist die Angabe von Passwort- und Ressourcengrenzen
• Profile werden von den Benutzern mit dem Befehl CREATE USER oder ALTER USER zugewiesen
• Profile können aktiviert oder deaktiviert werden
• Profile können sich auf das DEFAULT-Profil beziehen

Profile-Inhalt

• Passwortalterung und Gültigkeitsablauf des Passworts
• Passworthistorie
• Prüfen der Passwortkomplexität
• Account-Sperren
• CPU-Zeit
• Verbindungsdauer
• Speicherplatz
• gleichzeitige Sitzungen usw.

DEFAULT-Profil

• wird automatisch bei der Datenbankerstellung angelegt
• für die Benutzer, denen nicht ausdrücklich ein bestimmtes Profil zugewiesen wurde, gelten die Grenzen des DEFAULT-Profils
• alle Grenzen des DEFAULT-Profils sind zunächst unbegrenzt