Oracle Database

Aus Mikiwiki
Zur Navigation springen Zur Suche springen

Oracle Database (auch: Oracle Database Server, Oracle RDBMS) ist ein relationales Datenbankmanagementsystem der Firma Oracle. Damit können sowohl relationale Daten, als auch objektrelationale Daten gespeichert werden.

Funktionsweise

Oracle Database besteht aus zwei Hauptbestandteilen:

  • das Datenbankmanagementsystem besteht im laufenden und aktiven Zustand aus folgenden Teilen:
    • einer oder mehrere Oracle Server-Prozessen (Schattenprozess, engl. shadow process), welche Datenbankabfragen (engl. queries) oder Datenveränderungsanweisungen (DML) in der Sprache SQL von den Datenbank-Clients entgegennehmen und diese ausführen bzw. Ergebnisdaten zurückliefern. Dabei arbeiten sie teilweise direkt auf den Datenbankdateien, teilweise übertragen sie aber auch Aktivitäten auf die Hintergrundprozesse der Oracle-Instanz.
    • eine oder mehrere Oracle-Instanzen, die gemeinsamen Arbeitsspeicher in Form von Shared Memory sowie zahlreiche darauf zugreifende Hintergrundprozesse bereitstellen. Die wichtigsten Hintergrundprozesse sind:
      • Der Database Writer / DBWR, der Änderungen an den Datenblöcken in die Datenbankdateien schreibt.
      • Der Log Writer / LGWR, der Redo-Informationen in die Redo Log-Dateien schreibt.
      • Der Archiver / ARCH, der Redo Log-Dateien archiviert, sofern sich die Datenbank im Archivelog-Modus befindet.
      • Der System Monitor / SMON, der Konsistenzinformationen in die Kontrolldateien sowie in die Header von Datenbankdateien und in Redo Log-Dateien schreibt. Beim Wiederanlauf einer Datenbank nach einem Crash prüft der System-Monitor diese Konsistenzinformationen in einem Quercheck über alle Kontrolldateien, Datenbankdateien und Redo Log-Dateien. Sollte der SMON Inkonsistenzen feststellen, so er eine Crash Recovery ein, bei der aus den Redo Log-Dateien solange fehlende Transaktionen in die Datenbankdateien übertragen werden, bis die Datenbank mit allen Datenbankdateien wieder in sich konsistent ist.
      • Der Prozess Monitor / PMON, der die Oracle-Prozesse überwacht.
    • einer oder mehrere Listener-Prozesse, die Verbindungswünsche bearbeiten und Oracle Server-Prozesse starten können
  • die Datenbankdateien bestehen meist aus Dateien in einem Dateisystem, es werden jedoch auch Raw Devices oder per ASM (Automatic Storage Management) verwaltete Diskgroups verwendet.
    • die Datendateien (engl. data files) entahlten die eigentlichen Dateninhalte.
    • die Redo Log-Dateien (engl. redo log files) sind sehr schnell schreibbare Dateien, die als Transaktionslogs dienen und die die Datenblockänderungen (Change-Vektoren) von Transaktionen aufnehmen. Diese gespeicherten Change-Vektoren dienen zur Wiederherstellung von Datenblöcken, falls ungeplant oder beabsichtigt das Datenbankmanagementsystem beendet werden muss. Noch nicht in die Datenbankdateien übertragene, festgeschriebene Änderungen können so wiederhergestellt und nachgefahren werden (roll forward). Anschliessend werden alle Änderungen nach dem letzten erfolgreichen Festschreibvorgang (check point) zurückgeschrieben (roll back).
    • die Kontrolldateien (engl. control files) enthalten unter anderem die Struktur- und Zustandsinformation der Datenbank. Hierzu zählen die System Change Number / SCN sowie die Pfade und Namen aller Datenbank- und Redo Log-Dateien.

Folgende Werkzeuge zur Entwicklung und Datenbankverwaltung werden von Oracle zur Verfügung gestellt:

  • SQL*Plus ist ein befehlszeilenorientiertes Verwaltungswerkzeug zur Verwaltung und Bedienung von Oracle-Datenbanken. Es steht auf jedem Rechner zur Verfügung, auf dem die Oracle Client- oder Serversoftware installiert ist. Damit lassen sich alle Verwaltungstätigkeiten sowie die Eingabe, Änderung, Abfrage und das Löschen der eigentlichen Dateninhalte durchführen. Der Aufruf von SQL*Plus erfolgt durch Eingabe von "sqlplus". Mit iSQL*Plus stellt Oracle auch eine Weboberfläche für SQL*Plus bereit.
  • Der Oracle SQL Developer (Project Raptor) ist Oracles kostenloses Werkzeug für den Datenbankentwickler. Es läuft als Java-Programm mit grafischer Benutzer­oberfläche und ermöglicht das Bearbeiten von Datenbankobjekten, das Erstellen und Testen von SQL-Statements und Skripten, das Erstellen und Debuggen von PL/SQL-Prozeduren und einfache Datenbankanalysen.
  • Der Oracle Enterprise Manager (Java-Konsole) ist eine grafische Bedienoberfläche zur Datenbankverwaltung auf Grundlage von Java.
  • Oracle Enterprise Manager Database Control ist eine webbasierende, grafische Oberfläche zur Verwaltung einer Datenbank.

Folgende Werkzeuge werden von anderen Herstellern zur Verfügung gestellt:

  • Tool for Oracle Application Developer / TOAD der Firma Quest Software ist ein grafisches Werkzeug zur Verwaltung und Entwicklung mit Oracle-Datenbanken.
  • Hora der Firma Keeptool ist ein grafisches Werkzeug zur Verwaltung und Entwicklung von Oracle Datenbanken.

Konzepte

Installation

Notizen vom Kurs "Oracle Administration" 21.-25. Juni 2010 in Köln

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Microsoft Windows XP

Falls die Oracle-Software bereits installiert ist:

Menü "Start > Programme > Oracle - OraDb10g_home1 > Konfigurations- und Migrations-Tools > Datenbank-Konfigurationsassistent"

  • Datenbank erstellen
  • Vorlage: Benutzerdefinierte Datenbank
    • Globaler Datenbankname: ora01.local
    • SID: ora01
  • Verwaltungsoptionen
    • Datenbank mit Enterprise Manager konfigurieren
    • Database Control zur Datenbankverwaltung verwenden (gemeint ist die Web-Administration)
  • Kennwort
    • Dasselbe Kennwort für alle Accounts verwenden
  • Speicherungsverfahren: Dateisystem
  • Speicherorte: Speicherot von Datenbankdateien aus Vorlage verwenden
  • Recovery-Optionen
    • Flash Recovery-Bereich angeben
  • Datenbankkomponenten
    • Enterprise Manager Repository
    • Standard-Datenbankkomponenten: alles abwählen
  • Schritt 10 von 12
    • Speicher
    • Skalierung
    • Zeichensätze: Standardwert; am verünftigsten ist jedoch "Unicode (AL32UTF8)"
      • Länderspezifischer Zeichensatz
      • Standardsprache: (entspricht NLS_LANGUAGE)
      • Standarddatumsformat: (entspricht NLS_TERRITORY)
    • Verbindungsmodus: Dedizierter Server-Modus
  • Schritt 11 von 12: Datenbankspeicherung zeigt Struktur der Datenbank
  • Schritt 12 von 12
    • Skripts für das Erstellen von Datenbanken generieren

Anschliessend sind unter "Dienste" sowohl der OracleService... und OracleDB... gestartet. Die Weboberfläche ist unter http://lunar61:5500/em aufrufbar.

D:\oracle\product\10.2.0\admin\ora01\scripts

Die Oracle-Instanz hat zwei Namen:

  • Der globale Datenbankname sollte ein weltweit eindeutiger Name sein, der die Oracle-Instanz bezeichnet. Der erste Teil sollte der SID entsprechen, daran anschliessend folgt der Domainname.
  • SID (Oracle System Identifier) bezeichnet den Namen der Oracle-Instanz. So heisst dann auch die Datenbank!

Client verbindet sich mit Hilfe der Datei "D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora" mit dem Oracle-Server (bzw. der Oracle-Instanz). 

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORA01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lunar61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora01.local)
    )
  )

LUNAR61 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lunar61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lunar61.gfu.net)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Verbindung über Konsole mit "sqlplus system/passwort@ora01". 

show parameter db_name;

Bearbeiten der Einstellungen manuell oder mit "Oracle Net-Konfigurationsassistent > Konfiguration von lokalem Net Service Name". Als "Service Name" muss dabei der globale Datenbankname angegeben werden. Der angegebene Rechner muss natürlich erreichbar sein (tnsping). 

D:\Dokumente und Einstellungen\user1>tnsping lunar57
...
Parameterdateien benutzt:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Adapter EZCONNECT zur Auflösung des Alias benutzt
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=lunar57))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.50.157)(PORT=1521)))
OK (10 ms)

Als Net-Service Name kann eine beliebige Bezeichnung gewählt werden (hier "ORANEU"). Damit wird folgender Eintrag in die Datei "tnsnames.ora" eingefügt: 

ORANEU =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lunar57)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora01.local)
    )
  )

Damit ist folgende Verbindung möglich: 

sqlplus system/passwort@oraneu


Auf Serverseite gibt es die Datei "listener.ora": 

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lunar61)(PORT = 1521))
    )
  )

Weitere Einträge können wie folgt hinzugefügt werden: 

    (SID_DESC =
      (SID_NAME = ORA01)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME=ora01.local)
    )
    (SID_DESC =
      (SID_NAME = LUNAR61)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME=lunar61.gfu.net)
    )
  • Test über Dienste und OracleOraDb10g_home1TNSListener neu starten.
  • Ausserdem auch die Instanzen OracleServiceOra01 und OracleServiceLunar61 neu starten.
sqlplus system/passwort@ora01 as sysdba
SQL> shutdown immediate;
SQL> startup;

Zusätzlichen Tablespace "index01" hinzufügen: 

CREATE TABLESPACE index01 
  DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA01\index01.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

Tablespace wurde angelegt.

Benutzer "scott" anlegen.


Der Oracle Server ist ein relationales Datenbankmanagementsystem, das einen offenen, umfassenden und integrierten Ansatz für die Informationsverwaltung bereitstellt. Er besteht aus

  • 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)

Oracle Universal Installer

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

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 Database Configuration Assistant

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

iSQL*Plus ist ein 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
  • wird aufgerufen ü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)

...

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 [AUTOALLOCATE UNIFORM [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

Jede Oracle-Datenbank hat ein Standard-Datumsformat: 

select sysdate from dual;

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;

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),
...

Beispiel: 

drop table auftrag;

drop table kunde;

create table kunde
  (kunden_id number(8),
   nachname varchar2(32),
   plz varchar2(8),
   ort varchar2(24),
   status char(2),
   constraint kunden_id_pk primary key (kunden_id)
  );

create table auftrag
  (auftrag_id number(8),
   kunden_nr number(8),
   auftrag_menge number(8),
   menge_gel number(8),
   erfuellt char(2),
   datum_ert date,
   datum_erf date,
   bemerkung varchar2(256),
   artikel_nr number(8),
   mitarbeiter_nr number(8),
   constraint auftrag_id_pk primary key (auftrag_id),
   constraint kunden_nr_fk foreign key (kunden_nr) references kunde (kunden_id)
  );

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);

Constraint löschen 

ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;

Constraint aktivieren 

ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk;

Constraint deaktivieren 

ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk;

Referentielle Integrität: Zeilen mit einem Primärschlüssel können nicht gelöscht werden, wenn sie in einer anderen Tabelle als Fremdschlüssel dienen.

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 - es kann aber auch sein, dass der eingebaute Optimizer stattdessen einen full table scan macht

Nachführen des Index kostet Zeit (pro Spalte etwa Faktor 3). Natürlich benötigt der Index auch Platz.

Index erstellen wenn

  • überhaupt nach dem Spalteninhalt gesucht wird
  • Selektivität (je mehr verschiedene Werte es gibt, desto sinnvoller ist ein Index; so macht es etwa wenig Sinn, einen Index über das Geschlecht zu erstellen; sinnvoll wird es unter 10-15% der Sätze, ansonsten macht der Optimzer eine full table scan)

Indizes-Klassifizierung

Logisch

  • einspaltige (z. B. Postleitzahl) oder verknüpfte Indizes (z. B. Postleitzahl und Ort)
  • eindeutige oder nicht eindeutige Indizes
  • funktionsbasierte Indizes (z. B. für ... WHERE upper(name)='MEYER')

Physisch

  • partitionierte oder nicht partitionierte Indizes
  • B*-Baum-Indizes (B-Tree)
    • 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 (Abfragen auf "IS NULL" ist also schlecht und greift nicht auf den Index zurück)
    • DML-Befehle (INSERT, UPDATE, DELETE) führen zu einem Umbau des Index
  • 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 (ab etwa einer Million) und geringer Kardinalität, Zeilen werden selten oder gar nicht geändert

Index erstellen

Richtlinien:

  • Abfrage- und DML-Anforderungen ausgewogen berücksichtigen (z. B. Indizes nur während der Zeiten zur Verfügung stellen, wenn sie wirklich benötigt werden)
  • Dateninhalte und Indizes in separaten Tablespaces auf unterschiedlichen Festplatten speichern
  • NOLOGGING für grosse Indizes berücksichtigen (standardmässig werden Indizes mitgeloggt, was aber nicht notwendig ist)

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);

Beispiel B*-Baum-Index: 

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

Beispiel Bitmap-Index: 

CREATE BITMAP INDEX orders_region_id_idx ON orders (region_id);

Index neu erstellen

Der Befehl ALTER INDEX eignet sich für folgende Aktionen:

  • Index in einen anderen Tablespace verschieben
  • Speichernutzung durch Entfernen gelöschter Einträge verbessern

Beispiel: 

ALTER INDEX orders_region_id_idx REBUILD TABLESPACE ind02;

Indizes löschen

  • Löschen der Indizes vor dem Laden grosser Datenmengen
  • selten verwendete Indizes erst erstellen, wenn sie benötigt werden

Indexdaten abrufen

Informationen über Indizes erhält man durch Abfrage folgender Views:

  • DBA_INDEXES: Informationen über die Indizes
  • DBA_IND_COLUMNS: Informationen über die indizierten Spalten

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

Benutzer

Erstellen von Benutzern

  • Tablespaces festlegen, in denen der Benutzer Objekte speichern soll
  • Quotas für jeden Tablespace festlegen
  • Default-Tablespace und temporären Tablespace festlegen
  • Privilegien und Rollen an Benutzer vergeben

Datenbankschema

  • ein Schema ist eine benannte Gruppe von Objekten
  • beim Erstellen eines Benutzers wird gleichzeitig ein entsprechendes Schema angelegt
  • ein Benutzer kann nur einem Schema zugeordnet werden

Schema-Objekte

  • Tabellen
  • Constraints
  • Trigger
  • Indizes
  • Views
  • gespeicherte Programmblöcke
  • Datenbanklinks usw.

Syntax CREATE USER: 

CREATE USER user
  IDENTIFIED {BY password|EXTERNALLY}
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [QUOTA {integer[K|M]|UNLIMITED} ON tablespace]...]
  [PASSWORD EXPIRE]
  [ACCOUNT LOCK {LOCK|UNLOCK}]
  [PROFILE {profile|DEFAULT}]

Beispiel (Benutzer "sys" und "system" dürfen das): 

CREATE USER aaron
  IDENTIFIED BY soccer
  DEFAULT TABLESPACE userdata
  TEMPORARY TABLESPACE temp
  QUOTA 15M ON userdata;

Ein so angelegter Benutzer kann sich allerdings noch nicht anmelden: 

D:>sqlplus aaron/soccer
...
ERROR:
ORA-01045: user AARON lacks CREATE SESSION privilege; logon denied
Benutzernamen eingeben:

Syntax ALTER USER: 

ALTER USER user
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [QUOTA {integer[K|M]|UNLIMITED} ON tablespace]...]

Benutzer-Quota ändern

Tablespace-Quotas können in folgenden Situationen geändert werden:

  • Tabellen wachsen unerwartet schnell an
  • Anwendung wird weiterentwickelt und benötigt zusätzliche Tabellen und Indizes

Objekte werden bei einer Änderung der Quota neu organisiert und in andere Tablespaces gestellt.

Benutzer löschen

  • Klausel CASCADE verwenden, um alle Objekte im Schema zu löschen
  • angemeldete Benutzer können nicht gelöscht werden

Beispiel (Löschen des Benutzers und seines Schemas): 

DROP USER aaron CASCADE;

Benutzerinformationen abrufen

Abfrage folgender Views:

  • Weboberfläche (Administration > Schema: Benutzer & Berechtigungen: Benutzer
  • DBA_USERS
  • DBA_TS_QUOTAS

Privilegien

Ein Privileg ist die Berechtigung, eine bestimmte Art von SQL-Befehl auszuführen. Beispiele:

  • Anmelden an die Datenbank
  • Erstellung einer Tabelle
  • Zeilen aus der Tabelle eines anderen Benutzers abfragen

Benutzerprivilegien

  • System: ermöglicht Benutzern, bestimmte allgemeine Aktionen auf der Datenbank auszuführen
  • Objekte: ermöglicht Benutzern, auf ein bestimmtes Objekt zuzugreifen und etwas damit zu tun

...Bild S. 170...

Objektprivilegien definieren die zulässigen Operationen eines Benutzers auf folgende Datenbankobjekte:

  • Tabellen
  • Views
  • PL/SQL-Programme
  • Sequenzen
  • Schnappschüsse usw.

...Tabelle S. 172...

Systemprivilegien definieren Operationen, die innerhalb des Datenbankservers durchgeführt werden dürfen:

  • das Erstellen einer Tabelle (CREATE TABLE)
  • das Ändern eines Tablespace (ALTER TABLESPACE)
  • das Anlegen eines Rollback-Segments (CREATE ROLLBACK SEGMENT)

Wird ein Benutzer mit CREATE USER angelegt, so besitzt er vorerst keine Privilegien und kann keine Aktionen auf der Datenbank durchführen.

  • Es gibt über 100 verschiedene Systemprivilegien.
  • das Schlüsselwort ANY bedeutet, dass Benutzer diese Berechtigung in jedem Schema besitzen
  • der Befehl GRANT vergibt ein Privileg an einen Benutzer oder eine Gruppe von Benutzern
  • der Befehl REVOKE löscht die vergebenen Privilegien

Syntax GRANT: 

GRANT {system_privilege|role}[, {system_privilege|role}]...
  TO {user|role|PUBLIC}[, {user|role|PUBLIC}]...
  [WITH ADMIN OPTION]

Beispiel: 

GRANT CREATE SESSION TO aaron;
GRANT CREATE SESSION TO aaron WITH ADMIN OPTION;

Systemprivilegien entziehen

  • Befehl REVOKE verwenden, um Benutzern ein Systemprivileg zu entziehen
  • Nur Benutzer mit der ADMIN OPTION für Systemprivilegien können Systemprivilegien entziehn
  • Beim Entziehen von Systemprivilegien gibt es keine kaskadierenden Wirkungen

Syntax REVOKE: 

REVOKE {system_privilege|role}[, {system_privilege|role}]...
  FROM {user|role|PUBLIC}[, {user|role|PUBLIC}]...

Beispiel: 

REVOKE CREATE TABLE FROM aaron;

Objektprivilegien vergeben

Mit dem Befehl "GRANT" werden Objektprivilegien vergeben. Das Privileg muss sich im Schema des Privilegienverleihers befinden oder der Privilegienverleiher muss über die Option "WITH GRANT OPTION" verfügen.

Objektprivilegien entziehen

  • Befehl "REVOKE" verwenden, um Objektprivilegien zu entziehen.
  • Mit jedem Aufruf kann jeweils immer nur ein Objekt entzogen werden (?)
  • Objektprivilegien können nur vom Benutzer entzogen werden, der das Privileg ursprünglich vergeben hat.
  • Der Entzug von Objektprivilegien hat kaskadierende Wirkungen

Beispiel: 

REVOKE SELECTION ON emi.orders FROM jeff;

Informationen über Privilegien abrufen

Folgende Views:

  • DBA_SYS_PRIVS
  • DBA_TAB_PRIVS
  • DBA_COL_PRIVS
select * from dba_sys_privs where grantee='AARON';

Rollenkonzept

Beliebige Objekt- und Systemprivilegien können in einer Rollendefinition zusammengefasst werden.

  • Eine Rolle ist dabei ein Datenbankobjekt, das von einem Benutzer mit CREATE ROLE-Privileg erstellt werden kann.
  • Eine solche Rolle kann einem anderen Benutzer oder einer anderen Rolle zugewiesen werden.
  • Rollen haben keinen Eigentümer
  • Rollenbeschreibungen werden im Data Dictionary gespeichert
  • Eine Rolle kann aktiviert oder deaktiviert werden
  • Zum Aktivieren einer Rolle muss eventuell ein Passwort angegeben werden

Syntax: 

...S. 185-194...

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
Parameter Beschreibung
FAILED_LOGIN_ATTEMPTS Anzahl von Fehlversuchen bei der Anmeldung
PASSWORD_LOCK_TIME Anzahl von Tagen, die ein Benutzerkonto gesperrt bleibt
PASSWORD_LIFE_TIME Lebensdauer des Passworts in Tagen
PASSWORD_GRACE_TIME Nachfrist in Tagen für die Änderung des Passworts
PASSWORD_REUSE_TIME Zeitspanne in Tagen, die vergehen muss, bevor ein Passwort erneut verwendet werden darf
PASSWORD_REUSE_MAX Maximale Anzahl der zulässigen Wiederverwendungen eines Passworts
PASSWORD_VERIFY_FUNCTION PL/SQL-Funktion, die eine Komplexitätsprüfung vornimmt, bevor das Passwort zugewisen wird

....S. 153-158... 

create profile kuhn limit sessions_per_user 2;

alter profile kuhn limit sessions_per_user 3;

Globalisierung

  • Sprachenunterstützung
  • Länderunterstützung
  • Zeichensatzunterstützung
  • Linguistisches Sortieren
  • Datums- und Zeitformate
  • Numerische Formate
  • Währungsformate

Zeichensätze

  • Einzel-Byte-Zeichensätze (7-Bit, 8-Bit)
  • Mehr-Byte-Zeichensätze variabler Breite
  • Mehr-Byte-Zeichensätze fester Breite
  • Unicode (physikalisch: AL32UTF8, AL16UTF16, UTF8)

Datenbankzeichensätze und länderspezifische Zeichensätze

Datenbank-Zeichensätze Länderspezifische Zeichensätze
werden zur Zeit der Erstellung definiert
können nur durch Neuerstellung geändert werden
speichern Datentypen vom Typ CHAR, VARCHAR2, CLOB, LONG
können Zeichensätze variabler Länge speichern können Unicode mit AL16UTF16 oder UTF8 speichern

Richtlinien für die Auswahl eines länderspezifischen Oracle-Zeichensatzes (wird üblicherweise nicht benötigt)

...S. 203-208...

Sprachabhängiges Verhalten für die Sitzung angeben

Umgebungsvariable 

 NLS_LANG=<language>_<territory>.<charset>

Jede Komponente steuert dabei eine Untermenge der NLS-Funktionen. Beispiel: 

NLS_LANG=French_France.UTF8

Zusätzleiche Umgebungsvariablen: Für die Initialisierungsparameter stehen die entsprechenden Umgebungsvariablen zur Verfügung.

Linguistisches Sortieren

  • Binäre Sortierung: Hierbei erfolgt die Sortierung entsprechend den Binärwerten der kodierten Zeichen
  • Einsprachiges Sortieren
  • Multilinguales Sortieren: Basiert auf dem neuen Standard ISO 14651

NLS-Parameter in SQL-Funktionen sortieren

Einige SQL-Funktionen erforden die ausdrückliche Angabe von NLS-Parametern als Teil ihrer Parameterliste. Beispiel: 

SELECT TO_CHAR (hire_date, 'DD.Mon.YYYY','NLS_DATE_LANGUAGE=FRENCH') 
  FROM employees;

Auf SQL-Ebene in der Funktion "to_char": 

select sysdate from dual;
select to_char(sysdate, 'ddd') from dual;
select to_char(sysdate, 'ww') from dual;
select to_char(sysdate, 'month') from dual;
select to_char(sysdate, 'year') from dual;
select to_char(sysdate, 'month dd, yyyy') from dual;
select to_char(sysdate, 'hh24:mi') from dual;
select to_char(sysdate, 'month', 'NLS_DATE_LANGUAGE=FRENCH') from dual;

Informationen über Zeichensätze und NLS-Einstellungen

  • NLS_DATABASE_PARAMETERS
  • NLS_INSTANCE_PARAMETERS
  • NLS_SESSION_PARAMETERS

Backup / Recovery

Physisches Backup (Dateien bzw. Datenblöcke im Dateisystem kopieren)

  • kaltes Backup (offline)
    • Datenbank wird heruntergefahren und gesichert
    • Datenbank kann im Archivelog- oder Noarchivelog-Modus laufen
    • Vollständige Sicherung der Datenbank (alle Datendateien, Redo Log-Dateien, Kontrolldateien)
  • heisses Backup (online)
    • im laufenden Betrieb, womöglich ergeben sich dabei Performanceverluste
    • Datenbank läuft dabei zwingend im Archivelog-Modus
    • Sicherung erfolgt auf Ebene der Tablespaces

Durchführung der physischen Backups:

  • Befehle des Betriebssystems
    • hoher Verwaltungsaufwand bzw. hohe Fehlerquote
  • Oracle Recovery Manager / RMAN
    • prüft zuvor alle Datenblöcke
    • inkrementelle Sicherungen möglich
    • Recovery-Katalog
    • betriebssystemunabhängige Befehle

Logisches Backup (SQL-Dump): Sichert keine Transaktionen, also erfolgt vermutlich Datenverlust; eignet sich für Datenaustausch oder Updatevorgänge

  • mit Export-Tool (Client-Programm)
  • Oracle Data Pump (läuft auf Oracle-Server; seit Oracle 10)

Logische Sicherung mit exp

exp -help

Datenbank-Modus: Export sämtlicher Objekte der Datenbank 

exp system/miro@instanzname full=y file=c:\temp\full1.dmp

Schema-Modus 

exp system/miro@instanzname owner=jeff,scott file=c:\temp\full2.dmp

Tabellen-Modus 

exp system/miro@instanzname tables=scott.kunde file=c:\temp\full3.dmp

Logische Wiederherstellung mit imp

imp -help

Einlesen der Schemas von Benutzer "scott" und "jeff" und Zuweisung an die vorhandenen Benutzer "ann" und "cathy" in der Zieldatenbank. 

imp system/miro@instanzname fromuser=scott,jeff touser=ann,cathy file=c:\temp\full2.dmp

Falls für den Tablespace "users" keine Berechtigung vorhanden ist, muss dem Benutzer die entsprechende Quota angepasst werden.

Logische Sicherung mit expdp

Data Pump 

expdp -help

Kalte Sicherung mit Betriebssystembefehl

sqlplus sys/miro@lunar61 as sysdba

SQL> desc dba_data_files
Name                                      Null?    Typ
----------------------------------------- -------- ----------------------------

FILE_NAME                                          VARCHAR2(513)
FILE_ID                                            NUMBER
TABLESPACE_NAME                                    VARCHAR2(30)
BYTES                                              NUMBER
BLOCKS                                             NUMBER
STATUS                                             VARCHAR2(9)
RELATIVE_FNO                                       NUMBER
AUTOEXTENSIBLE                                     VARCHAR2(3)
MAXBYTES                                           NUMBER
MAXBLOCKS                                          NUMBER
INCREMENT_BY                                       NUMBER
USER_BYTES                                         NUMBER
USER_BLOCKS                                        NUMBER
ONLINE_STATUS                                      VARCHAR2(7)



SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\LUNAR61\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\LUNAR61\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\LUNAR61\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\LUNAR61\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\LUNAR61\EXAMPLE01.DBF
C:\U03\ORADATA\DATA01.DBF

6 Zeilen ausgewõhlt.

shutdown immediate

Danach werden mit Betriebssystembefehlen alle Dateien gesichert.

Beim Wiederherstellen müssten mit ALTER DATABASE die Pfade der gesicherten Datenbank angepasst werden.

Heisses Backup mit Betriebssystembefehlen

Ansehen der Grundeinstellungen: 

desc v$database

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

Für diese Art Sicherung muss die Datenbank im Modus ARCHIVELOG laufen.

1. Datenbank herunterfahren 

shutdown immediate;

2. In den MOUNT-Modus hochfahren 

startup mount;

3. Archivelog-Modus einschalten 

alter database archivelog;

4. Datenbank hochfahren 

alter database open;

5. Überprüfen 

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Wechsel der Protokolldatei. 

alter system switch logfile;

Nun sollte einmalig eine vollständige Sicherung durchgeführt werden!

Heisses Backup

Die Sicherung wird bei geöffneter Datenbank durchgeführt.

Backup eines Online-Tablespace

ALTER TABLESPACE ... BEGIN BACKUP

Sichern der Datendateien des Tablespace mit Betriebssystembefehlen. 

ALTER TABLESPACE ... END BACKUP


1. Anlegen Verzeichnis "d:\backup-heiss"

2. 

sqlplus system/miro@lunar61

3. Tablespace "users" in den Backup-Modus versetzen. 

alter tablespace users begin backup;

4. 

SQL> desc v$backup;
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------

 FILE#                                              NUMBER
 STATUS                                             VARCHAR2(18)
 CHANGE#                                            NUMBER
 TIME                                               DATE

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- --------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 ACTIVE                 800417 25.06.10
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0

5. Kopieren des Tablespace "users" auf Betriebssystemebene

6. Backup-Modus für Tablespace "users" beenden. 

SQL> alter tablespace users end backup;
SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- --------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE             800417 25.06.10
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0

7. Für eine Wiederherstellung müssen alle gesicherten Tablespaces zurückkopiert und ausserdem die zugehörigen Redo Log-Dateien nachgeschoben werden.



Zeile in Tabelle einfügen: 

INSERT INTO kunde VALUES(1,'Siemens','8000','München',1);
INSERT INTO auftrag VALUES
 (1,1,22.5,0,1,to_date('23.06.2010','dd.mm.yyyy'),null,'Eine Bemerkung',1,1
 );
commit;

select * from kunde;
KUNDEN_ID              NACHNAME                         PLZ      ORT                      STATUS 
---------------------- -------------------------------- -------- ------------------------ ------ 
1                      Siemens                          8000     München                  1      

1 rows selected

FAQ

Datenbank entladen

login: sisis
$ . /opt/lib/sisis/etc/ora.rc
$ ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1
$ nohup $ORACLE_HOME/bin/exp sisis/sisis FILE=/tmp/sisis.exp \
    OWNER=sisis LOG=log.txt &
Export: Release 10.2.0.1.0 - Production on Mi Sep 21 16:20:44 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Angemeldet bei: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
Exportieren in WE8ISO8859P1-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef�hrt

Spezifizierte Benutzer werden gleich exportiert ...
. Prozedurale Pre-Schema-Objekte und -Aktionen werden exportiert
. Fremdfunktions-Bibliotheksnamen für Benutzer SISIS werden exportiert 
. Synonyme vom Typ PUBLIC werden exportiert
. Synonyme vom Typ PRIVATE werden exportiert
. Objekttypdefinitionen für Benutzer SISIS  werden exportiert 
SISIS Objekte werden gleich exportiert ...
. Datenbank-Links werden exportiert
. Sequenzzahlen werden exportiert
. Clusterdefinitionen werden exportiert
. Tabellen von SISIS werden gleich exportiert ... über 'Conventional Path'
. . Export der Tabelle                     ABOAUFTRAG          0 Zeilen exportiert
...
. . Export der Tabelle                    Z39_ATTRSET          2 Zeilen exportiert
. Synonyme werden exportiert
. Views werden exportiert
. Gespeicherte Prozeduren werden exportiert
. Operatoren werden exportiert
. Referentielle Integritäts-Constraints werden exportiert
. Trigger werden exportiert
. Indextypen werden exportiert
. Bitmap-, funktionale und erweiterbare Indizes werden exportiert
. Exportieren von Posttable-Aktionen
. Materialized Views werden exportiert
. Snapshot-Log werden exportiert
. Exportieren von Job-Queues
. Exportieren von Aktualisierengruppen und untergeordneten Gruppen
. Dimensionen werden exportiert
. Prozedurale Post-Schema-Objekte und -Aktionen werden exportiert
. Statistiken werden exportiert
Export erfolgreich ohne Warnungen beendet.

Datenbank laden

login: sisis
$ . /opt/lib/sisis/etc/ora.rc
$ ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1
$ nohup $ORACLE_HOME/bin/imp sisis/sisis FILE=/backup/OLDHOST/EXPORTsisis/sisis.exp \
    FROMUSER=sisis TOUSER=sisis LOG=log.txt RECALCULATE_STATISTICS=Y &

Tabelle entladen

login: sisis
$ . /opt/lib/sisis/etc/ora.rc
$ ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1
$ nohup $ORACLE_HOME/bin/exp sisis/sisis FILE=d02ben.exp \
    TABLES='\"D02BEN\"' DIRECT=Y LOG=explog.txt &
Export: Release 10.2.0.1.0 - Production on Di Sep 29 12:10:04 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Angemeldet bei: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
Exportieren in WE8ISO8859P9-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef
Server verwendet Zeichensatz AL32UTF8 (Zeichensatzkonvertierung möglich)

Angegebene Tabellen werden gleich exportiert über 'Direct Path'
. . Export der Tabelle                         D02BEN      34395 Zeilen exportiert
EXP-00091: Fragliche Statistiken werden exportiert.
EXP-00091: Fragliche Statistiken werden exportiert.
EXP-00091: Fragliche Statistiken werden exportiert.
Der Exportvorgang endete erfolgreich, aber mit einigen Warnungen.

Tabelle laden

login: sisis
$ . /opt/lib/sisis/etc/ora.rc
$ ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1
$ nohup $ORACLE_HOME/bin/imp sisis/sisis FILE=d02ben.exp \
    TABLES='\"D02BEN\"' IGNORE=Y LOG=implog.txt &

Umbenennung Rechner

Bei Umbenennung des Rechners müssen auch folgende für Oracle wichtige Dateien angepasst werden, da diese den Rechnernamen enthalten:

  • ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1
  • $ORACLE_HOME/network/admin/listener.ora
  • $ORACLE_HOME/network/admin/tnsnames.ora

Weblinks

Herausgeber Sprache Webseitentitel Anmerkungen
Wikipedia ger Oracle (Datenbanksystem)wbm Enzyklopädischer Artikel
Von «https://mikiwiki.org/mikiwiki/index.php?title=Oracle_Database&oldid=47843»