DDL und Data Dictionary – Strukturen definieren und verwalten

Fach: Informationstechnologie – Datenbanksysteme
Schulstufe: 11. Schulstufe – HTL Informatik
Voraussetzungen: DQL, DML, Joins, Subqueries, SCOTT-Schema
Autor: HTL Pinkafeld – IF/IT

1. Überblick DDL

1.1 Was ist DDL?

DDL (Data Definition Language) umfasst alle SQL-Anweisungen, die Datenbankstrukturen definieren, ändern oder löschen:

DDL-Anweisungen:
├── CREATE   → Objekte erstellen (Tabellen, Views, Sequences, Indizes, ...)
├── ALTER    → Objekte ändern
├── DROP     → Objekte löschen
├── RENAME   → Objekte umbenennen
└── TRUNCATE → Tabelle leeren (DDL, da kein ROLLBACK möglich!)

1.2 DDL vs. DML

Wichtig: Jede DDL-Anweisung führt ein implizites COMMIT aus – alle offenen DML-Transaktionen werden dabei unwiderruflich gespeichert!

1.3 Oracle-Datenbankobjekte

Schema-Objekte (gehören einem Benutzer):
├── TABLE      → Basisobjekt für Datenspeicherung
├── VIEW       → Virtuelle Tabelle (gespeicherte Abfrage)
├── SEQUENCE   → Nummernfolge für Primärschlüssel
├── INDEX      → Beschleunigt Datenzugriff
├── SYNONYM    → Alias für ein Objekt
├── PROCEDURE  → Gespeicherte PL/SQL-Prozedur
├── FUNCTION   → Gespeicherte PL/SQL-Funktion
├── TRIGGER    → Automatisch ausgeführte Prozedur
└── PACKAGE    → Sammlung von Prozeduren/Funktionen

2. CREATE TABLE – Tabellen erstellen

2.1 Grundlegende Syntax

CREATE TABLE tabellenname (
    spalte1  datentyp  [constraint],
    spalte2  datentyp  [constraint],
    ...
    [tabellen-constraints]
);

2.2 Einfaches Beispiel

-- Einfache Tabelle ohne Constraints
CREATE TABLE ABTEILUNG (
    ABTNR   NUMBER(4),
    ABTNAME VARCHAR2(50),
    LEITER  NUMBER(4),
    ORT     VARCHAR2(30)
);

2.3 Tabelle mit Constraints

CREATE TABLE SCHUELER (
    SCHNR     NUMBER(6)      NOT NULL,
    VORNAME   VARCHAR2(30)   NOT NULL,
    NACHNAME  VARCHAR2(30)   NOT NULL,
    GEBDAT    DATE           NOT NULL,
    EMAIL     VARCHAR2(100),
    KLASSE    VARCHAR2(10)   NOT NULL,
    EINSCHR   DATE           DEFAULT SYSDATE,
    CONSTRAINT PK_SCHUELER PRIMARY KEY (SCHNR)
);

2.4 CREATE TABLE AS SELECT (CTAS)

Eine Tabelle kann direkt aus einer Abfrage erstellt werden – Struktur und Daten werden übernommen, Constraints nicht:

-- Tabelle mit Struktur UND Daten aus EMP
CREATE TABLE EMP_KOPIE AS
SELECT * FROM EMP;

-- Nur Struktur, keine Daten (WHERE 1=2 ist immer falsch)
CREATE TABLE EMP_LEER AS
SELECT * FROM EMP WHERE 1 = 2;

-- Nur bestimmte Spalten und berechnete Werte
CREATE TABLE EMP_UEBERSICHT AS
SELECT EMPNO,
       ENAME,
       SAL * 12 AS JAHRESGEHALT,
       DEPTNO
FROM   EMP
WHERE  DEPTNO IN (10, 20);

3. Datentypen in Oracle

3.1 Numerische Datentypen

-- Beispiele für NUMBER
EMPNO    NUMBER(4)      -- Ganzzahl: 0 bis 9999
SAL      NUMBER(7,2)    -- 5 Vorkommastellen, 2 Nachkommastellen
PROZENT  NUMBER(5,2)    -- z.B. 99.99
GROESSE  NUMBER(3,1)    -- z.B. 178.5

3.2 Zeichenketten-Datentypen

-- Beispiele
ENAME      VARCHAR2(10)     -- variabler Text bis 10 Zeichen
LAND_KZ    CHAR(2)          -- immer 2 Zeichen: 'AT', 'DE', 'US'
BESCHR     VARCHAR2(4000)   -- längerer Text
INHALT     CLOB             -- sehr langer Text (Artikel, etc.)

-- CHAR-Falle: 'AT' = 'AT ' ist wahr bei CHAR!
-- VARCHAR2: 'AT' <> 'AT ' (unterschiedliche Länge)

3.3 Datums- und Zeitdatentypen

-- DATE: enthält immer Datum UND Uhrzeit!
HIREDATE   DATE              -- 17.12.1980 00:00:00
ERSTELLT   DATE DEFAULT SYSDATE

-- TIMESTAMP: für genaue Zeitstempel
ZEITSTEMPEL  TIMESTAMP       -- 17.12.1980 10:30:45.123456

-- INTERVAL
VERTRAGSLAENGE  INTERVAL YEAR TO MONTH   -- z.B. 2 Jahre, 6 Monate

3.4 Sonstige Datentypen

4. Constraints – Integritätsbedingungen

4.1 Constraint-Typen

4.2 Spalten-Constraints (Column-Level)

CREATE TABLE PRODUKT (
    PRODNR    NUMBER(6)      CONSTRAINT PK_PRODUKT PRIMARY KEY,
    BEZEICHN  VARCHAR2(100)  CONSTRAINT NN_PROD_BEZ NOT NULL,
    PREIS     NUMBER(8,2)    CONSTRAINT CHK_PREIS CHECK (PREIS > 0),
    LAGERORT  VARCHAR2(20)   CONSTRAINT UQ_LAGERORT UNIQUE,
    KATEGNR   NUMBER(4)      CONSTRAINT FK_PROD_KAT
                             REFERENCES KATEGORIE(KATEGNR)
);

4.3 Tabellen-Constraints (Table-Level)

Tabellen-Constraints stehen nach allen Spalten und ermöglichen zusammengesetzte Schlüssel:

CREATE TABLE BESTELLPOSITION (
    BESTNR    NUMBER(8)    NOT NULL,
    PRODNR    NUMBER(6)    NOT NULL,
    MENGE     NUMBER(6)    NOT NULL,
    EINZELPR  NUMBER(8,2)  NOT NULL,
    -- Zusammengesetzter Primärschlüssel (nur als Tabellen-Constraint möglich!)
    CONSTRAINT PK_BESTPOS  PRIMARY KEY (BESTNR, PRODNR),
    CONSTRAINT FK_BP_BEST  FOREIGN KEY (BESTNR)  REFERENCES BESTELLUNG(BESTNR),
    CONSTRAINT FK_BP_PROD  FOREIGN KEY (PRODNR)  REFERENCES PRODUKT(PRODNR),
    CONSTRAINT CHK_MENGE   CHECK (MENGE > 0),
    CONSTRAINT CHK_EINZELPR CHECK (EINZELPR >= 0)
);

4.4 PRIMARY KEY

-- Einfacher Primärschlüssel (Spalten-Level)
EMPNO  NUMBER(4)  CONSTRAINT PK_EMP PRIMARY KEY

-- Zusammengesetzter Primärschlüssel (Tabellen-Level)
CONSTRAINT PK_EINSCHR PRIMARY KEY (SCHNR, KURSNR)

-- Eigenschaften:
-- ✅ Eindeutig (UNIQUE)
-- ✅ Nicht leer (NOT NULL) – automatisch!
-- ✅ Pro Tabelle nur EINER möglich
-- ✅ Oracle erstellt automatisch einen Index

4.5 FOREIGN KEY und referentielle Aktionen

CREATE TABLE BESTELLUNG (
    BESTNR    NUMBER(8)   PRIMARY KEY,
    KDNR      NUMBER(6)   NOT NULL,
    BESTDAT   DATE        DEFAULT SYSDATE,
    -- ON DELETE CASCADE: Bestellungen werden mit Kunden gelöscht
    CONSTRAINT FK_BEST_KD FOREIGN KEY (KDNR)
        REFERENCES KUNDE(KDNR)
        ON DELETE CASCADE,
    -- ON DELETE SET NULL: FK wird auf NULL gesetzt wenn Parent gelöscht
    -- ON DELETE NO ACTION (Standard): Löschen verhindert wenn Kinder existieren
);

4.6 CHECK Constraint

CREATE TABLE MITARBEITER (
    MATNR     NUMBER(6)    PRIMARY KEY,
    VORNAME   VARCHAR2(30) NOT NULL,
    NACHNAME  VARCHAR2(30) NOT NULL,
    GEHALT    NUMBER(8,2),
    GESCHL    CHAR(1),
    EINTRITT  DATE,
    AUSTRITT  DATE,
    -- CHECK-Constraints
    CONSTRAINT CHK_GEHALT   CHECK (GEHALT > 0),
    CONSTRAINT CHK_GESCHL   CHECK (GESCHL IN ('M', 'W', 'D')),
    CONSTRAINT CHK_DATUM    CHECK (AUSTRITT IS NULL OR AUSTRITT > EINTRITT)
);

4.7 UNIQUE Constraint

-- Eindeutigkeit ohne Primärschlüssel
CREATE TABLE BENUTZER (
    USERID    NUMBER(8)    PRIMARY KEY,
    USERNAME  VARCHAR2(30) CONSTRAINT UQ_USERNAME UNIQUE,
    EMAIL     VARCHAR2(100) CONSTRAINT UQ_EMAIL UNIQUE,
    -- Zusammengesetztes UNIQUE
    VORNAME   VARCHAR2(30),
    NACHNAME  VARCHAR2(30),
    GEBDAT    DATE,
    CONSTRAINT UQ_PERSON UNIQUE (VORNAME, NACHNAME, GEBDAT)
);
-- NULL-Werte: UNIQUE erlaubt mehrere NULLs (NULL <> NULL in Oracle)

4.8 Constraint-Status: ENABLE / DISABLE

-- Constraint temporär deaktivieren (z.B. für Massenimport)
ALTER TABLE EMP DISABLE CONSTRAINT FK_EMP_DEPT;

-- Daten laden...
INSERT INTO EMP ...;

-- Constraint wieder aktivieren
ALTER TABLE EMP ENABLE CONSTRAINT FK_EMP_DEPT;

-- Constraint mit NOVALIDATE: aktivieren ohne bestehende Daten zu prüfen
ALTER TABLE EMP ENABLE NOVALIDATE CONSTRAINT FK_EMP_DEPT;

5. ALTER TABLE – Tabellen ändern

5.1 Spalten hinzufügen

-- Neue Spalte hinzufügen (am Ende der Tabelle)
ALTER TABLE EMP ADD EMAIL VARCHAR2(100);

-- Neue Spalte mit Constraint und Default
ALTER TABLE EMP ADD (
    TELEFON    VARCHAR2(20),
    AKTIV      CHAR(1)  DEFAULT 'J' NOT NULL
                        CONSTRAINT CHK_AKTIV CHECK (AKTIV IN ('J', 'N'))
);

-- Mehrere Spalten gleichzeitig
ALTER TABLE SCHUELER ADD (
    NATIONALIT VARCHAR2(30),
    BEMERKUNG  VARCHAR2(500)
);

5.2 Spalten ändern

-- Datentyp / Größe ändern
-- (nur möglich wenn alle vorhandenen Werte passen!)
ALTER TABLE EMP MODIFY ENAME VARCHAR2(20);    -- Vergrößern: immer möglich
ALTER TABLE EMP MODIFY SAL   NUMBER(9,2);     -- Vergrößern: immer möglich

-- Default-Wert setzen
ALTER TABLE EMP MODIFY HIREDATE DEFAULT SYSDATE;

-- NOT NULL hinzufügen (nur wenn keine NULL-Werte vorhanden!)
ALTER TABLE EMP MODIFY EMAIL NOT NULL;

-- NOT NULL entfernen
ALTER TABLE EMP MODIFY COMM NULL;

5.3 Spalten löschen

-- Eine Spalte löschen
ALTER TABLE EMP DROP COLUMN EMAIL;

-- Mehrere Spalten gleichzeitig
ALTER TABLE EMP DROP (TELEFON, BEMERKUNG);

-- SET UNUSED: Spalte sofort "unsichtbar" machen, später physisch löschen
-- (schneller bei großen Tabellen, kein Full-Table-Scan)
ALTER TABLE EMP SET UNUSED COLUMN NATIONALIT;
ALTER TABLE EMP DROP UNUSED COLUMNS;   -- Später physisch entfernen

5.4 Constraints hinzufügen und entfernen

-- Primärschlüssel nachträglich hinzufügen
ALTER TABLE ABTEILUNG ADD CONSTRAINT PK_ABT PRIMARY KEY (ABTNR);

-- Fremdschlüssel hinzufügen
ALTER TABLE ABTEILUNG ADD CONSTRAINT FK_ABT_LEITER
    FOREIGN KEY (LEITER) REFERENCES EMP(EMPNO);

-- NOT NULL Constraint
ALTER TABLE ABTEILUNG MODIFY ABTNAME NOT NULL;

-- CHECK Constraint hinzufügen
ALTER TABLE EMP ADD CONSTRAINT CHK_SAL CHECK (SAL > 0);

-- Constraint löschen
ALTER TABLE EMP DROP CONSTRAINT CHK_SAL;

-- Primary Key löschen (erst wenn kein FK darauf verweist!)
ALTER TABLE DEPT DROP PRIMARY KEY;
-- Mit CASCADE: löscht auch alle FK die auf diesen PK verweisen
ALTER TABLE DEPT DROP PRIMARY KEY CASCADE;

5.5 Tabelle umbenennen

-- Tabelle umbenennen
ALTER TABLE EMP_KOPIE RENAME TO EMP_ARCHIV;

-- Spalte umbenennen (Oracle 9i+)
ALTER TABLE EMP RENAME COLUMN ENAME TO MITARBEITERNAME;

6. DROP und RENAME

6.1 DROP TABLE

-- Tabelle endgültig löschen (mit allen Daten und Indizes!)
DROP TABLE EMP_BACKUP;

-- Mit PURGE: sofort aus Recyclebin entfernen (kein FLASHBACK möglich)
DROP TABLE EMP_BACKUP PURGE;

-- Mit CASCADE CONSTRAINTS: FK-Constraints die auf diese Tabelle zeigen
-- werden ebenfalls gelöscht
DROP TABLE DEPT CASCADE CONSTRAINTS;

-- Tabelle aus Recyclebin wiederherstellen (Oracle 10g+)
FLASHBACK TABLE EMP_BACKUP TO BEFORE DROP;

-- Recyclebin anzeigen
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME FROM RECYCLEBIN;

6.2 RENAME

-- Tabelle umbenennen
RENAME EMP_ARCHIV TO EMP_ALT;

-- Entspricht:
ALTER TABLE EMP_ARCHIV RENAME TO EMP_ALT;

6.3 DROP weiterer Objekte

-- View löschen
DROP VIEW V_EMP_DEPT;

-- Sequence löschen
DROP SEQUENCE SEQ_EMPNO;

-- Index löschen
DROP INDEX IDX_EMP_ENAME;

-- Synonym löschen
DROP SYNONYM S_EMP;

7. SEQUENCE – Automatische Nummernvergabe

7.1 Was ist eine Sequence?

Eine Sequence ist ein Datenbankobjekt, das automatisch eindeutige Nummernfolgen erzeugt – ideal für Primärschlüssel:

-- Sequence erstellen
CREATE SEQUENCE SEQ_EMPNO
    START WITH   8000        -- Startwert
    INCREMENT BY 1           -- Schrittweite
    MAXVALUE     9999        -- Maximalwert
    NOCYCLE                  -- kein Neustart nach MAXVALUE
    NOCACHE;                 -- kein Vorausberechnen (sicherer, aber langsamer)

-- Mit Cache (schneller, aber Lücken möglich nach Absturz):
CREATE SEQUENCE SEQ_BESTNR
    START WITH   1
    INCREMENT BY 1
    NOCYCLE
    CACHE 20;               -- 20 Werte vorausberechnen

7.2 Sequence verwenden

-- Nächsten Wert abrufen (NEXTVAL)
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;   -- gibt 8000, beim nächsten Aufruf 8001

-- Aktuellen Wert abrufen (CURRVAL – nur nach NEXTVAL aufrufbar!)
SELECT SEQ_EMPNO.CURRVAL FROM DUAL;

-- In INSERT verwenden
INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES (SEQ_EMPNO.NEXTVAL, 'BAUER', 'CLERK', 1500, 10);

-- Mehrere Inserts hintereinander
INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES (SEQ_EMPNO.NEXTVAL, 'MAIER', 'ANALYST', 3000, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES (SEQ_EMPNO.NEXTVAL, 'HUBER', 'CLERK', 1200, 30);

7.3 Sequence ändern und löschen

-- Sequence ändern (START WITH kann nicht geändert werden!)
ALTER SEQUENCE SEQ_EMPNO
    INCREMENT BY 2
    MAXVALUE 99999
    CACHE 10;

-- Sequence löschen
DROP SEQUENCE SEQ_EMPNO;

8. VIEW – Virtuelle Tabellen

8.1 Was ist ein View?

Ein View ist eine gespeicherte SELECT-Abfrage, die wie eine Tabelle verwendet werden kann. Die Daten werden nicht gespeichert – bei jedem Zugriff wird die Abfrage ausgeführt:

Vorteile von Views:
├── Komplexe Abfragen vereinfachen (einmal definieren, mehrfach nutzen)
├── Datenschutz: nur bestimmte Spalten/Zeilen freigeben
├── Unabhängigkeit: Anwendungen gegen Schema-Änderungen schützen
└── Konsistenz: alle nutzen dieselbe Abfragelogik

8.2 VIEW erstellen

-- Einfacher View: Mitarbeiter mit Abteilungsname
CREATE VIEW V_EMP_DEPT AS
SELECT e.EMPNO,
       e.ENAME,
       e.JOB,
       e.SAL,
       d.DNAME,
       d.LOC
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

-- View verwenden wie eine Tabelle
SELECT * FROM V_EMP_DEPT WHERE LOC = 'DALLAS';
SELECT ENAME, SAL FROM V_EMP_DEPT WHERE JOB = 'MANAGER';

8.3 View mit WHERE (Row-Level Security)

-- View der nur Mitarbeiter der Abteilung 20 zeigt
CREATE VIEW V_EMP_ABT20 AS
SELECT EMPNO, ENAME, JOB, SAL
FROM   EMP
WHERE  DEPTNO = 20
WITH CHECK OPTION CONSTRAINT CHK_ABT20;
-- WITH CHECK OPTION: INSERT/UPDATE über View darf DEPTNO nicht ändern

-- View der sensible Spalten (SAL, COMM) ausblendet
CREATE VIEW V_EMP_PUBLIC AS
SELECT EMPNO, ENAME, JOB, HIREDATE, DEPTNO
FROM   EMP;

8.4 View ersetzen

-- View neu erstellen oder ersetzen (kein DROP nötig)
CREATE OR REPLACE VIEW V_EMP_DEPT AS
SELECT e.EMPNO,
       e.ENAME,
       e.JOB,
       e.SAL,
       e.COMM,
       d.DNAME,
       d.LOC,
       d.DEPTNO
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

8.5 Aktualisierbare Views

-- Views können unter bestimmten Bedingungen für DML verwendet werden:
-- ✅ Kein DISTINCT, GROUP BY, HAVING, ROWNUM
-- ✅ Keine Aggregatfunktionen
-- ✅ Keine Mengenoperationen (UNION, MINUS, INTERSECT)
-- ✅ Keine Joins (in der Regel)

-- Dieser View ist aktualisierbar:
CREATE VIEW V_EMP_CLERK AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM   EMP
WHERE  JOB = 'CLERK';

-- DML über den View:
UPDATE V_EMP_CLERK SET SAL = 1000 WHERE EMPNO = 7369;  -- funktioniert
INSERT INTO V_EMP_CLERK VALUES (9999, 'TEST', 1500, 10); -- JOB fehlt → NULL

9. INDEX – Abfragen beschleunigen

9.1 Was ist ein Index?

Ein Index ist eine zusätzliche Datenstruktur, die den schnellen Zugriff auf Zeilen einer Tabelle ermöglicht – ähnlich dem Stichwortverzeichnis eines Buchs:

Ohne Index:    Full Table Scan → jede Zeile wird gelesen → langsam bei großen Tabellen
Mit Index:     Direkter Zugriff über Baumstruktur (B-Tree) → sehr schnell

Oracle erstellt automatisch Indizes für PRIMARY KEY und UNIQUE Constraints.

9.2 Index erstellen

-- Einfacher Index auf eine Spalte
CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);

-- Zusammengesetzter Index (mehrere Spalten)
CREATE INDEX IDX_EMP_JOB_DEPT ON EMP(JOB, DEPTNO);

-- Eindeutiger Index (Alternative zu UNIQUE Constraint)
CREATE UNIQUE INDEX IDX_EMP_EMAIL ON EMP(EMAIL);

-- Function-Based Index (für Suche mit Funktionen)
CREATE INDEX IDX_EMP_UPPER_ENAME ON EMP(UPPER(ENAME));
-- Ermöglicht schnelle Suche: WHERE UPPER(ENAME) = 'SMITH'

9.3 Wann Indizes sinnvoll sind

Gut geeignet für Indizes:
  ✅ Spalten die häufig in WHERE verwendet werden
  ✅ Spalten die für Joins verwendet werden (FK-Spalten)
  ✅ Spalten die häufig sortiert werden (ORDER BY)
  ✅ Spalten mit hoher Selektivität (viele verschiedene Werte)

Nicht geeignet:
  ❌ Spalten mit wenigen verschiedenen Werten (z.B. CHAR(1) Geschlecht)
  ❌ Kleine Tabellen (Full Scan ist oft schneller)
  ❌ Spalten die selten in WHERE vorkommen
  ❌ Zu viele Indizes verlangsamen INSERT/UPDATE/DELETE!

10. Das Data Dictionary

10.1 Was ist das Data Dictionary?

Das Data Dictionary (Datenkatalog) ist eine Sammlung von Systemtabellen und Views, die alle Metadaten der Datenbank speichern:

Data Dictionary enthält Informationen über:
├── Tabellen, Spalten, Datentypen
├── Constraints (PK, FK, CHECK, UNIQUE, NOT NULL)
├── Indizes und ihre Spalten
├── Views und ihre Definitionen
├── Sequences
├── Benutzer und ihre Rechte
├── Gespeicherte Prozeduren und Funktionen
└── Speichernutzung und Performance-Statistiken

Merke nach Regel 4 von Codd: Das Schema selbst wird als Relation gespeichert und ist mit SQL abfragbar!

10.2 Präfix-System der Data Dictionary Views

Oracle organisiert das Data Dictionary in drei Ebenen:

-- Beispiel: Tabellen anzeigen
SELECT TABLE_NAME FROM USER_TABLES;   -- nur meine Tabellen
SELECT TABLE_NAME FROM ALL_TABLES;    -- alle zugänglichen Tabellen
SELECT TABLE_NAME FROM DBA_TABLES;    -- alle (nur als DBA)

11. Wichtige Data Dictionary Views

11.1 Tabellen und Spalten

-- Eigene Tabellen anzeigen
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM   USER_TABLES
ORDER BY TABLE_NAME;

-- Spalten einer Tabelle (wie DESCRIBE, aber mit mehr Info)
SELECT COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       DATA_SCALE,
       NULLABLE,
       DATA_DEFAULT
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'EMP'
ORDER BY COLUMN_ID;

-- Alle Spalten aller eigenen Tabellen
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, NULLABLE
FROM   USER_TAB_COLUMNS
ORDER BY TABLE_NAME, COLUMN_ID;

11.2 Constraints

-- Alle Constraints der eigenen Tabellen
SELECT CONSTRAINT_NAME,
       CONSTRAINT_TYPE,    -- P=Primary Key, R=Foreign Key, U=Unique, C=Check/Not Null
       TABLE_NAME,
       STATUS,             -- ENABLED / DISABLED
       VALIDATED           -- VALIDATED / NOT VALIDATED
FROM   USER_CONSTRAINTS
WHERE  TABLE_NAME = 'EMP'
ORDER BY CONSTRAINT_TYPE;

-- Constraint-Typen entschlüsseln
SELECT CONSTRAINT_NAME,
       CASE CONSTRAINT_TYPE
           WHEN 'P' THEN 'Primary Key'
           WHEN 'R' THEN 'Foreign Key'
           WHEN 'U' THEN 'Unique'
           WHEN 'C' THEN 'Check / Not Null'
       END AS TYP,
       TABLE_NAME,
       STATUS
FROM   USER_CONSTRAINTS
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;

-- Spalten eines Constraints anzeigen
SELECT CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM   USER_CONS_COLUMNS
WHERE  TABLE_NAME = 'EMP'
ORDER BY CONSTRAINT_NAME, POSITION;

-- FK-Beziehungen: Welche Tabelle verweist auf welche?
SELECT uc.TABLE_NAME       AS kind_tabelle,
       uc.CONSTRAINT_NAME  AS fk_name,
       ucc.COLUMN_NAME     AS fk_spalte,
       uc2.TABLE_NAME      AS eltern_tabelle,
       ucc2.COLUMN_NAME    AS pk_spalte
FROM   USER_CONSTRAINTS  uc
       JOIN USER_CONS_COLUMNS ucc  ON uc.CONSTRAINT_NAME  = ucc.CONSTRAINT_NAME
       JOIN USER_CONSTRAINTS  uc2  ON uc.R_CONSTRAINT_NAME = uc2.CONSTRAINT_NAME
       JOIN USER_CONS_COLUMNS ucc2 ON uc2.CONSTRAINT_NAME = ucc2.CONSTRAINT_NAME
WHERE  uc.CONSTRAINT_TYPE = 'R'
ORDER BY uc.TABLE_NAME;

11.3 Views, Indizes und Sequences

-- Eigene Views anzeigen
SELECT VIEW_NAME, TEXT_LENGTH
FROM   USER_VIEWS
ORDER BY VIEW_NAME;

-- View-Definition anzeigen
SELECT TEXT
FROM   USER_VIEWS
WHERE  VIEW_NAME = 'V_EMP_DEPT';

-- Indizes anzeigen
SELECT INDEX_NAME,
       TABLE_NAME,
       INDEX_TYPE,     -- NORMAL, BITMAP, FUNCTION-BASED
       UNIQUENESS,     -- UNIQUE / NONUNIQUE
       STATUS          -- VALID / UNUSABLE
FROM   USER_INDEXES
ORDER BY TABLE_NAME, INDEX_NAME;

-- Index-Spalten anzeigen
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, DESCEND
FROM   USER_IND_COLUMNS
WHERE  TABLE_NAME = 'EMP'
ORDER BY INDEX_NAME, COLUMN_POSITION;

-- Sequences anzeigen
SELECT SEQUENCE_NAME,
       MIN_VALUE,
       MAX_VALUE,
       INCREMENT_BY,
       CYCLE_FLAG,
       CACHE_SIZE,
       LAST_NUMBER     -- nächster Wert der ausgegeben wird
FROM   USER_SEQUENCES
ORDER BY SEQUENCE_NAME;

11.4 Objekte und Quellcode

-- Alle eigenen Objekte im Überblick
SELECT OBJECT_NAME,
       OBJECT_TYPE,    -- TABLE, VIEW, INDEX, SEQUENCE, PROCEDURE, ...
       STATUS,         -- VALID / INVALID
       CREATED,
       LAST_DDL_TIME
FROM   USER_OBJECTS
ORDER BY OBJECT_TYPE, OBJECT_NAME;

-- Ungültige Objekte (z.B. nach Umbenennung einer referenzierten Tabelle)
SELECT OBJECT_NAME, OBJECT_TYPE
FROM   USER_OBJECTS
WHERE  STATUS = 'INVALID';

-- Quellcode von PL/SQL-Objekten
SELECT LINE, TEXT
FROM   USER_SOURCE
WHERE  NAME = 'MEINE_PROZEDUR'
  AND  TYPE = 'PROCEDURE'
ORDER BY LINE;

11.5 Benutzer und Rechte

-- Eigener Benutzername
SELECT USER FROM DUAL;

-- Eigene Rechte (Systemprivilegien)
SELECT PRIVILEGE FROM USER_SYS_PRIVS;

-- Eigene Rollen
SELECT GRANTED_ROLE FROM USER_ROLE_PRIVS;

-- Objektrechte die ich anderen gegeben habe
SELECT GRANTEE, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   USER_TAB_PRIVS_MADE
ORDER BY TABLE_NAME, GRANTEE;

-- Objektrechte die ich erhalten habe
SELECT GRANTOR, TABLE_NAME, PRIVILEGE
FROM   USER_TAB_PRIVS_RECD
ORDER BY TABLE_NAME;

-- Alle Benutzer der Datenbank (nur als DBA)
SELECT USERNAME, ACCOUNT_STATUS, CREATED
FROM   DBA_USERS
ORDER BY USERNAME;

11.6 Praktische Data Dictionary Abfragen

-- Welche Tabellen haben keinen Primärschlüssel?
SELECT TABLE_NAME
FROM   USER_TABLES
WHERE  TABLE_NAME NOT IN (SELECT TABLE_NAME
                          FROM   USER_CONSTRAINTS
                          WHERE  CONSTRAINT_TYPE = 'P')
ORDER BY TABLE_NAME;

-- Alle FK-Spalten die keinen Index haben (Performance-Problem!)
SELECT uc.TABLE_NAME, ucc.COLUMN_NAME
FROM   USER_CONSTRAINTS  uc
       JOIN USER_CONS_COLUMNS ucc ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME
WHERE  uc.CONSTRAINT_TYPE = 'R'
  AND  (uc.TABLE_NAME, ucc.COLUMN_NAME) NOT IN
       (SELECT TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS)
ORDER BY uc.TABLE_NAME;

-- Größte Tabellen nach Zeilenanzahl
SELECT TABLE_NAME, NUM_ROWS
FROM   USER_TABLES
WHERE  NUM_ROWS IS NOT NULL
ORDER BY NUM_ROWS DESC
FETCH FIRST 10 ROWS ONLY;

-- Abhängigkeiten: Welche Objekte verwenden eine bestimmte Tabelle?
SELECT NAME, TYPE
FROM   USER_DEPENDENCIES
WHERE  REFERENCED_NAME = 'EMP'
  AND  REFERENCED_TYPE = 'TABLE'
ORDER BY TYPE, NAME;

12. Zusammenfassung und Ausblick

Überblick DDL

-- Tabelle erstellen
CREATE TABLE name (col typ constraint, ...);
CREATE TABLE name AS SELECT ...;    -- mit Daten (keine Constraints!)

-- Tabelle ändern
ALTER TABLE name ADD spalte typ;
ALTER TABLE name MODIFY spalte typ;
ALTER TABLE name DROP COLUMN spalte;
ALTER TABLE name ADD CONSTRAINT name typ (...);
ALTER TABLE name DROP CONSTRAINT name;

-- Tabelle löschen
DROP TABLE name;
DROP TABLE name PURGE;
DROP TABLE name CASCADE CONSTRAINTS;
FLASHBACK TABLE name TO BEFORE DROP;

-- Sequence
CREATE SEQUENCE name START WITH n INCREMENT BY n;
seq.NEXTVAL  -- nächsten Wert abrufen
seq.CURRVAL  -- aktuellen Wert abrufen

-- View
CREATE [OR REPLACE] VIEW name AS SELECT ...;
DROP VIEW name;

-- Index
CREATE [UNIQUE] INDEX name ON tabelle(spalten);
DROP INDEX name;

Data Dictionary – Wichtigste Views

Checkliste

CREATE TABLE mit Datentypen und Constraints erstellen
Unterschied Spalten- vs. Tabellen-Constraint
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL anwenden
ALTER TABLE: Spalten/Constraints hinzufügen, ändern, löschen
ON DELETE CASCADE vs. ON DELETE SET NULL
DROP TABLE mit PURGE und FLASHBACK
SEQUENCE erstellen und mit NEXTVAL/CURRVAL verwenden
VIEW erstellen und OR REPLACE anwenden
WITH CHECK OPTION erklären
INDEX: wann sinnvoll, wann nicht
Data Dictionary Präfix-System: USER_ / ALL_ / DBA_
USER_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS abfragen
FK-Beziehungen über Data Dictionary ermitteln

Typische Prüfungsaufgaben

-- 1. Tabelle KURS mit allen Constraints erstellen
CREATE TABLE KURS (
    KURSNR    NUMBER(4)     CONSTRAINT PK_KURS PRIMARY KEY,
    BEZEICHN  VARCHAR2(100) NOT NULL,
    DAUER_H   NUMBER(3)     CONSTRAINT CHK_DAUER CHECK (DAUER_H BETWEEN 1 AND 500),
    PREIS     NUMBER(8,2)   CONSTRAINT CHK_PREIS CHECK (PREIS >= 0),
    LEITER    NUMBER(6)     CONSTRAINT FK_KURS_MA REFERENCES MITARBEITER(MATNR)
);

-- 2. Sequence für Primärschlüssel
CREATE SEQUENCE SEQ_KURSNR START WITH 100 INCREMENT BY 1 NOCYCLE NOCACHE;

INSERT INTO KURS (KURSNR, BEZEICHN, DAUER_H, PREIS)
VALUES (SEQ_KURSNR.NEXTVAL, 'Oracle SQL Grundlagen', 40, 1500);

-- 3. Alle Constraints der Tabelle EMP anzeigen
SELECT CONSTRAINT_NAME,
       CASE CONSTRAINT_TYPE
           WHEN 'P' THEN 'Primary Key'
           WHEN 'R' THEN 'Foreign Key'
           WHEN 'U' THEN 'Unique'
           WHEN 'C' THEN 'Check/Not Null'
       END AS TYP,
       STATUS
FROM   USER_CONSTRAINTS
WHERE  TABLE_NAME = 'EMP'
ORDER BY CONSTRAINT_TYPE;

-- 4. Spalte EMAIL nachträglich hinzufügen
ALTER TABLE EMP ADD EMAIL VARCHAR2(100);
ALTER TABLE EMP ADD CONSTRAINT UQ_EMP_MAIL UNIQUE (EMAIL);

-- 5. View für Gehaltsübersicht pro Abteilung
CREATE OR REPLACE VIEW V_GEHALT_ABT AS
SELECT d.DEPTNO,
       d.DNAME,
       COUNT(e.EMPNO)        AS anzahl,
       ROUND(AVG(e.SAL), 2)  AS avg_gehalt,
       MIN(e.SAL)            AS min_gehalt,
       MAX(e.SAL)            AS max_gehalt
FROM   DEPT d LEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO
GROUP BY d.DEPTNO, d.DNAME
ORDER BY d.DEPTNO;

SELECT * FROM V_GEHALT_ABT;

Ausblick: Nächste Themen

• DCL: GRANT, REVOKE – Rechte vergeben und entziehen
• PL/SQL: Prozedurale Erweiterung, Variablen, IF/LOOP
• PL/SQL Prozeduren und Funktionen
• PL/SQL Trigger: Automatisch auf DML-Ereignisse reagieren
• PL/SQL Cursor: Zeilenweise Verarbeitung von Ergebnismengen

HTL Pinkafeld – IF/IT | Oracle SQL | 11. Schulstufe