DDL and Data Dictionary – define and manage structures

Subject: Information Technology – Database Systems School level: 11th grade – HTL Informatik Requirements: DQL, DML, joins, subqueries, SCOTT schema Author: HTL Pinkafeld – IF/IT

1. Overview DDL

1.1 What is DDL?

DDL (Data Definition Language) includes all SQL statements that define, modify or delete database structures:

DDL-Anweisungen:
├── CREATE   → Objekte erstellen (Tabellen, Views, Sequences, Indizes, ...)
├── ALTER    → Objekte ändern
├── DROP     → Objekte löschen
├── RENAME   → Objekte umbenennen
└── TRUNCATE → Empty table (DDL, since no ROLLBACK is possible!)

1.2 DDL vs. DML

Important: Every DDL statement executes an implicit COMMIT - all open DML transactions are saved irrevocably!

1.3 Oracle database objects

Schema objects (owned by a user):
├── TABLE → Base object for data storage
├── VIEW → Virtual Table (Saved Query)
├── SEQUENCE → Number sequence for primary key
├── INDEX      → Beschleunigt Datenzugriff
├── SYNONYM → Alias ​​for an object
├── PROCEDURE  → Gespeicherte PL/SQL-Prozedur
├── FUNCTION   → Gespeicherte PL/SQL-Funktion
├── TRIGGER → Automatically executed procedure
└── PACKAGE    → Sammlung von Prozeduren/Funktionen

2. CREATE TABLE – create tables

2.1 Basic syntax

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

2.2 Simple example

-- Simple table without constraints
CREATE TABLE ABTEILUNG (
    ABTNR   NUMBER(4),
    ABTNAME VARCHAR2(50),
    LEITER  NUMBER(4),
    ORT     VARCHAR2(30)
);

2.3 Table with 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)

A table can be created directly from a query - structure and data are adopted, constraints are not:

-- Table with structure AND data from EMP
CREATE TABLE EMP_KOPIE AS
SELECT * FROM EMP;

-- Only structure, no data (WHERE 1=2 is always wrong)
CREATE TABLE EMP_LEER AS
SELECT * FROM EMP WHERE 1 = 2;

-- Only specific columns and calculated values
CREATE TABLE EMP_UEBERSICHT AS
SELECT EMPNO,
       ENAME,
       SAL * 12 AS JAHRESGEHALT,
       DEPTNO
FROM   EMP
WHERE  DEPTNO IN (10, 20);

3. Data Types in Oracle

3.1 Numeric data types

-- Examples of 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 String data types

-- Examples
ENAME      VARCHAR2(10)     -- variable text up to 10 characters
LAND_KZ    CHAR(2)          -- always 2 characters: 'AT', 'DE', 'US'
BESCHR     VARCHAR2(4000)   -- longer text
INHALT     CLOB             -- very long text (articles, etc.)

-- CHAR pitfall: 'AT' = 'AT ' is true for CHAR!
-- VARCHAR2: 'AT' <> 'AT ' (different length)

3.3 Date and time data types

-- DATE: always contains date AND time!
HIREDATE   DATE              -- 17.12.1980 00:00:00
ERSTELLT   DATE DEFAULT SYSDATE

-- TIMESTAMP: for accurate timestamps
ZEITSTEMPEL  TIMESTAMP       -- 17.12.1980 10:30:45.123456

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

3.4 Other data types

4. Constraints – integrity conditions

4.1 Constraint types

4.2 Column 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 Table constraints (table level)

Table constraints appear after all columns and enable composite keys:

CREATE TABLE BESTELLPOSITION (
    BESTNR    NUMBER(8)    NOT NULL,
    PRODNR    NUMBER(6)    NOT NULL,
    MENGE     NUMBER(6)    NOT NULL,
    EINZELPR  NUMBER(8,2)  NOT NULL,
    -- Composite primary key (only possible as a table constraint!)
    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

-- Simple primary key (column level)
EMPNO  NUMBER(4)  CONSTRAINT PK_EMP PRIMARY KEY

-- Composite primary key (table level)
CONSTRAINT PK_EINSCHR PRIMARY KEY (SCHNR, KURSNR)

-- Eigenschaften:
-- ✅ Eindeutig (UNIQUE)
-- ✅ Not empty (NOT ZERO) – automatically!
-- ✅ Only ONE possible per table
-- ✅ Oracle erstellt automatisch einen Index

4.5 FOREIGN KEY and referential actions

CREATE TABLE BESTELLUNG (
    BESTNR    NUMBER(8)   PRIMARY KEY,
    KDNR      NUMBER(6)   NOT NULL,
    BESTDAT   DATE        DEFAULT SYSDATE,
    -- ON DELETE CASCADE: Orders are deleted with customers
    CONSTRAINT FK_BEST_KD FOREIGN KEY (KDNR)
        REFERENCES KUNDE(KDNR)
        ON DELETE CASCADE,
    -- ON DELETE SET NULL: FK is set to NULL when parent is deleted
    -- ON DELETE NO ACTION (default): Deletion prevented if children exist
);

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

-- Uniqueness without a primary key
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 values: UNIQUE allows multiple NULLs (NULL <> NULL in Oracle)

4.8 Constraint status: ENABLE / DISABLE

-- Temporarily deactivate the constraint (e.g. for mass import)
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 with NOVALIDATE: activate without checking existing data
ALTER TABLE EMP ENABLE NOVALIDATE CONSTRAINT FK_EMP_DEPT;

5. ALTER TABLE – Change tables

5.1 Add columns

-- Add new column (at the end of the table)
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'))
);

-- Multiple columns at the same time
ALTER TABLE SCHUELER ADD (
    NATIONALIT VARCHAR2(30),
    BEMERKUNG  VARCHAR2(500)
);

5.2 Change columns

-- Change data type/size
-- (only possible if all existing values ​​match!)
ALTER TABLE EMP MODIFY ENAME VARCHAR2(20);    -- Enlarge: always possible
ALTER TABLE EMP MODIFY SAL   NUMBER(9,2);     -- Enlarge: always possible

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

-- Add NOT NULL (only if there are no NULL values!)
ALTER TABLE EMP MODIFY EMAIL NOT NULL;

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

5.3 Delete columns

-- Delete a column
ALTER TABLE EMP DROP COLUMN EMAIL;

-- Multiple columns at the same time
ALTER TABLE EMP DROP (TELEFON, BEMERKUNG);

-- SET UNUSED: Make column "invisible" immediately, physically delete later
-- (faster for large tables, no full table scan)
ALTER TABLE EMP SET UNUSED COLUMN NATIONALIT;
ALTER TABLE EMP DROP UNUSED COLUMNS;   -- Physically remove later

5.4 Add and remove constraints

-- Add primary key later
ALTER TABLE ABTEILUNG ADD CONSTRAINT PK_ABT PRIMARY KEY (ABTNR);

-- Add foreign key
ALTER TABLE ABTEILUNG ADD CONSTRAINT FK_ABT_LEITER
    FOREIGN KEY (LEITER) REFERENCES EMP(EMPNO);

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

-- Add CHECK constraint
ALTER TABLE EMP ADD CONSTRAINT CHK_SAL CHECK (SAL > 0);

-- Delete constraint
ALTER TABLE EMP DROP CONSTRAINT CHK_SAL;

-- Delete primary key (only if no FK refers to it!)
ALTER TABLE DEPT DROP PRIMARY KEY;
-- With CASCADE: also deletes all FKs that refer to this PK
ALTER TABLE DEPT DROP PRIMARY KEY CASCADE;

5.5 Rename table

-- Rename table
ALTER TABLE EMP_KOPIE RENAME TO EMP_ARCHIV;

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

6. DROP and RENAME

6.1 DROP TABLE

-- Permanently delete the table (with all data and indexes!)
DROP TABLE EMP_BACKUP;

-- With PURGE: remove immediately from Recyclebin (no FLASHBACK possible)
DROP TABLE EMP_BACKUP PURGE;

-- With CASCADE CONSTRAINTS: FK constraints that point to this table
-- are also deleted
DROP TABLE DEPT CASCADE CONSTRAINTS;

-- Restore table from Recyclebin (Oracle 10g+)
FLASHBACK TABLE EMP_BACKUP TO BEFORE DROP;

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

6.2 RENAME

-- Rename table
RENAME EMP_ARCHIV TO EMP_ALT;

-- Entspricht:
ALTER TABLE EMP_ARCHIV RENAME TO EMP_ALT;

6.3 DROP additional objects

-- Delete view
DROP VIEW V_EMP_DEPT;

-- Delete sequence
DROP SEQUENCE SEQ_EMPNO;

-- Delete index
DROP INDEX IDX_EMP_ENAME;

-- Delete synonym
DROP SYNONYM S_EMP;

7. SEQUENCE – Automatic number assignment

7.1 What is a sequence?

A Sequence is a database object that automatically generates unique sequences of numbers - ideal for primary keys:

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

-- With cache (faster, but gaps possible after crash):
CREATE SEQUENCE SEQ_BESTNR
    START WITH   1
    INCREMENT BY 1
    NOCYCLE
    CACHE 20;               -- 20 Werte vorausberechnen

7.2 Use Sequence

-- Get next value (NEXTVAL)
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;   -- returns 8000, next call 8001

-- Retrieve current value (CURRVAL – can only be called after NEXTVAL!)
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);

-- Multiple inserts in a row
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 Change and delete sequence

-- Change sequence (START WITH cannot be changed!)
ALTER SEQUENCE SEQ_EMPNO
    INCREMENT BY 2
    MAXVALUE 99999
    CACHE 10;

-- Delete sequence
DROP SEQUENCE SEQ_EMPNO;

8. VIEW – Virtual Tables

8.1 What is a view?

A View is a stored SELECT query that can be used like a table. The data is not saved - the query is executed every time it is accessed:

Vorteile von Views:
├── Komplexe Abfragen vereinfachen (einmal definieren, mehrfach nutzen)
├── Data protection: only share certain columns/rows
├── Independence: Protect applications against schema changes
└── Consistency: everyone uses the same query logic

8.2 Create VIEW

-- Simple view: Employees with department name
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;

-- Use View like a table
SELECT * FROM V_EMP_DEPT WHERE LOC = 'DALLAS';
SELECT ENAME, SAL FROM V_EMP_DEPT WHERE JOB = 'MANAGER';

8.3 View with WHERE (Row-Level Security)

-- View that only shows employees from department 20
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 via View must not change DEPTNO

-- View that hides sensitive columns (SAL, COMM).
CREATE VIEW V_EMP_PUBLIC AS
SELECT EMPNO, ENAME, JOB, HIREDATE, DEPTNO
FROM   EMP;

8.4 Replace View

-- Create or replace view (no DROP necessary)
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 Refreshable Views

-- Views can be used for DML under certain conditions:
-- ✅ 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 via the 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 – Speed ​​up queries

9.1 What is an index?

An index is an additional data structure that allows quick access to rows in a table - similar to the index of a book:

Without index: Full table scan → every line is read → slow for large tables
With index: Direct access via tree structure (B-Tree) → very fast

Oracle automatically creates indexes for PRIMARY KEY and UNIQUE constraints.

9.2 Create index

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

-- Composite index (multiple columns)
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 (for searching with functions)
CREATE INDEX IDX_EMP_UPPER_ENAME ON EMP(UPPER(ENAME));
-- Allows quick search: WHERE UPPER(ENAME) = 'SMITH'

9.3 When indices make sense

Well suited for indexes:
  ✅ Spalten die häufig in WHERE verwendet werden
✅ Columns used for joins (FK columns)
  ✅ Spalten die häufig sortiert werden (ORDER BY)
✅ Columns with high selectivity (many different values)

Not suitable:
❌ Columns with a few different values ​​(e.g. CHAR(1) gender)
❌ Small tables (full scan is often faster)
  ❌ Spalten die selten in WHERE vorkommen
  ❌ Zu viele Indizes verlangsamen INSERT/UPDATE/DELETE!

10. The Data Dictionary

10.1 What is the Data Dictionary?

The Data Dictionary (data catalog) is a collection of system tables and views that store all of the database's metadata:

Data Dictionary contains information about:
├── Tables, columns, data types
├── Constraints (PK, FK, CHECK, UNIQUE, NOT NULL)
├── Indexes and their columns
├── Views und ihre Definitionen
├── Sequences
├── Benutzer und ihre Rechte
├── Gespeicherte Prozeduren und Funktionen
└── Speichernutzung und Performance-Statistiken

Note according to rule 4 from Codd: The schema itself is stored as a relation and can be queried with SQL!

10.2 Prefix system of the Data Dictionary Views

Oracle organizes the data dictionary into three levels:

-- Example: Show tables
SELECT TABLE_NAME FROM USER_TABLES;   -- just my tables
SELECT TABLE_NAME FROM ALL_TABLES;    -- all accessible tables
SELECT TABLE_NAME FROM DBA_TABLES;    -- all (only as DBA)

11. Important Data Dictionary Views

11.1 Tables and Columns

-- Show your own tables
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM   USER_TABLES
ORDER BY TABLE_NAME;

-- Columns of a table (like DESCRIBE, but with more 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;

-- All columns of all custom tables
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, NULLABLE
FROM   USER_TAB_COLUMNS
ORDER BY TABLE_NAME, COLUMN_ID;

11.2 Constraints

-- All constraints of your own tables
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;

-- Decoding constraint types
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;

-- Show columns of a constraint
SELECT CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM   USER_CONS_COLUMNS
WHERE  TABLE_NAME = 'EMP'
ORDER BY CONSTRAINT_NAME, POSITION;

-- FK relationships: which table references which?
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, Indexes and Sequences

-- Show your own views
SELECT VIEW_NAME, TEXT_LENGTH
FROM   USER_VIEWS
ORDER BY VIEW_NAME;

-- Show view definition
SELECT TEXT
FROM   USER_VIEWS
WHERE  VIEW_NAME = 'V_EMP_DEPT';

-- Show indices
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;

-- Show index columns
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, DESCEND
FROM   USER_IND_COLUMNS
WHERE  TABLE_NAME = 'EMP'
ORDER BY INDEX_NAME, COLUMN_POSITION;

-- Show sequences
SELECT SEQUENCE_NAME,
       MIN_VALUE,
       MAX_VALUE,
       INCREMENT_BY,
       CYCLE_FLAG,
       CACHE_SIZE,
       LAST_NUMBER     -- next value to be output
FROM   USER_SEQUENCES
ORDER BY SEQUENCE_NAME;

11.4 Objects and Source Code

-- All your own objects at a glance
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;

-- Invalid objects (e.g. after renaming a referenced table)
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 Users and Rights

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

-- All users of the database (DBA only)
SELECT USERNAME, ACCOUNT_STATUS, CREATED
FROM   DBA_USERS
ORDER BY USERNAME;

11.6 Practical Data Dictionary Queries

-- Which tables do not have a primary key?
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;

-- All FK columns that do not have an index (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;

-- Largest tables by number of rows
SELECT TABLE_NAME, NUM_ROWS
FROM   USER_TABLES
WHERE  NUM_ROWS IS NOT NULL
ORDER BY NUM_ROWS DESC
FETCH FIRST 10 ROWS ONLY;

-- Dependencies: Which objects use a specific table?
SELECT NAME, TYPE
FROM   USER_DEPENDENCIES
WHERE  REFERENCED_NAME = 'EMP'
  AND  REFERENCED_TYPE = 'TABLE'
ORDER BY TYPE, NAME;

12. Summary and outlook

Overview DDL

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

-- Change table
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;

-- Delete table
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  -- get next value
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 – Most Important Views

Checklist

CREATE TABLE mit Datentypen und Constraints erstellen
Difference between column and table constraints
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 explain
INDEX: when useful, when not
Data Dictionary Prefix System: USER_ / ALL_ / DBA_
USER_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS abfragen
Determine FK relationships via data dictionary

Typical exam tasks

-- 1. Create table COURSE with all constraints
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 for primary key
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. Show all constraints of the EMP table
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. Add the EMAIL column later
ALTER TABLE EMP ADD EMAIL VARCHAR2(100);
ALTER TABLE EMP ADD CONSTRAINT UQ_EMP_MAIL UNIQUE (EMAIL);

-- 5. View for salary overview per department
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;

Outlook: Next topics

• DCL: GRANT, REVOKE – Grant and revoke rights
• PL/SQL: Procedural extension, variables, IF/LOOP
• PL/SQL procedures and functions
• PL/SQL Trigger: Automatically respond to DML events
• PL/SQL Cursor: Row-by-row processing of result sets

HTL Pinkafeld – IF/IT | Oracle SQL | 11th grade