# Fichier: python_cheats/cheatsheets/oracle.txt

```
# Cheatsheet Oracle Database - Guide Complet pour Débutants


[OK] QU'EST-CE QU'ORACLE DATABASE ?

Oracle Database est un système de gestion de base de données relationnelle (SGBDR)
développé par Oracle Corporation. C'est l'une des bases de données les plus puissantes
et les plus utilisées au monde, particulièrement dans les grandes entreprises.

# POURQUOI Oracle Database ?
[OK] Robustesse et fiabilité extrêmes
[OK] Scalabilité - Gère des téraoctets de données
[OK] Sécurité avancée - Cryptage, audit, contrôle d'accès
[OK] Haute disponibilité - Réplication, clustering (RAC)
[OK] Performance - Optimisation automatique
[OK] Support entreprise - Documentation, formations, support technique
[OK] Conformité - Standards SQL, ACID
[OK] Fonctionnalités avancées - PL/SQL, partitionnement, compression

# QUAND utiliser Oracle ?
[OK] Applications critiques d'entreprise (ERP, CRM, finance)
[OK] Gros volumes de données (> 100 GB)
[OK] Besoins de haute disponibilité (99.99% uptime)
[OK] Transactions complexes avec intégrité garantie
[OK] Réglementations strictes (banque, santé, gouvernement)
[OK] Besoin de support technique professionnel

# QUAND NE PAS utiliser Oracle ?
[X] Petits projets ou prototypes (coût élevé)
[X] Startups avec budget limité
[X] Applications simples (PostgreSQL/MySQL suffisent)
[X] Projets open-source nécessitant gratuité totale


[OK] ARCHITECTURE D'ORACLE DATABASE

# === COMPOSANTS PRINCIPAUX ===

┌─────────────────────────────────────────────────────────────────┐
│                    ARCHITECTURE ORACLE                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │              INSTANCE (Mémoire + Processus)              │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │           SYSTEM GLOBAL AREA (SGA)                 │ │  │
│  │  │  ┌──────────────┬──────────────┬─────────────┐    │ │  │
│  │  │  │ Shared Pool  │ Database     │ Redo Log    │    │ │  │
│  │  │  │ (SQL, PL/SQL)│ Buffer Cache │ Buffer      │    │ │  │
│  │  │  └──────────────┴──────────────┴─────────────┘    │ │  │
│  │  │  ┌──────────────┬──────────────┐                  │ │  │
│  │  │  │ Large Pool   │ Java Pool    │                  │ │  │
│  │  │  └──────────────┴──────────────┘                  │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │         PROGRAM GLOBAL AREA (PGA)                  │ │  │
│  │  │  (Mémoire privée de chaque session)                │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │              BACKGROUND PROCESSES                   │ │  │
│  │  │  • PMON (Process Monitor)                          │ │  │
│  │  │  • SMON (System Monitor)                           │ │  │
│  │  │  • DBWn (Database Writer)                          │ │  │
│  │  │  • LGWR (Log Writer)                               │ │  │
│  │  │  • CKPT (Checkpoint)                               │ │  │
│  │  │  • ARCn (Archiver)                                 │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  └──────────────────────────────────────────────────────────┘  │
│                              ^v                                  │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │              DATABASE (Fichiers physiques)               │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │          DATA FILES (.dbf)                         │ │  │
│  │  │  • Tablespaces                                     │ │  │
│  │  │  • Tables, Index, Données utilisateur              │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │          CONTROL FILES (.ctl)                      │ │  │
│  │  │  • Métadonnées de la base                          │ │  │
│  │  │  • Informations structurelles                      │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │          REDO LOG FILES (.log)                     │ │  │
│  │  │  • Journalisation des transactions                 │ │  │
│  │  │  • Recovery en cas de crash                        │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │          ARCHIVE LOG FILES (.arc)                  │ │  │
│  │  │  • Backup des redo logs                            │ │  │
│  │  │  • Point-in-time recovery                          │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  │                                                          │  │
│  │  ┌────────────────────────────────────────────────────┐ │  │
│  │  │          PARAMETER FILE (init.ora / spfile)        │ │  │
│  │  │  • Configuration de l'instance                     │ │  │
│  │  └────────────────────────────────────────────────────┘ │  │
│  └──────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────┘


# EXPLICATION DES COMPOSANTS

1. INSTANCE
───────────
Une instance Oracle est composée de:
- Structures mémoire (SGA + PGA)
- Processus background

POURQUOI? Séparer instance et database permet:
[OK] Plusieurs instances peuvent accéder à la même database (RAC)
[OK] Redémarrage instance sans affecter les données physiques
[OK] Optimisation mémoire indépendante du stockage

2. SYSTEM GLOBAL AREA (SGA)
────────────────────────────
Mémoire partagée entre tous les utilisateurs

• Shared Pool: Cache des requêtes SQL et code PL/SQL
• Database Buffer Cache: Cache des blocs de données lus depuis disque
• Redo Log Buffer: Buffer temporaire des modifications avant écriture
• Large Pool: Opérations volumineuses (backup, recovery)
• Java Pool: Exécution code Java dans la base

POURQUOI? Performance - Évite accès disque coûteux

3. PROGRAM GLOBAL AREA (PGA)
─────────────────────────────
Mémoire privée pour chaque session utilisateur

Contient:
- Variables de session
- Tri des données
- Curseurs

POURQUOI? Isolation - Chaque utilisateur a son propre espace mémoire

4. BACKGROUND PROCESSES
────────────────────────
Processus système qui maintiennent la base

• PMON (Process Monitor): Nettoie les processus morts
• SMON (System Monitor): Recovery automatique au démarrage
• DBWn (Database Writer): Écrit buffer cache -> data files
• LGWR (Log Writer): Écrit redo buffer -> redo log files
• CKPT (Checkpoint): Synchronise data files avec buffer cache
• ARCn (Archiver): Archive les redo logs pleins

POURQUOI? Automatisation - Garantit cohérence et récupérabilité

5. DATA FILES
──────────────
Fichiers physiques contenant les données

Organisés en TABLESPACES (espaces logiques)

POURQUOI? Séparation logique/physique - Facilite gestion et backup

6. CONTROL FILES
─────────────────
Fichiers binaires contenant métadonnées critiques:
- Nom de la base
- Localisation des data files et redo logs
- Timestamp de création
- Informations de backup

POURQUOI? Point central - Oracle sait où trouver tout

7. REDO LOG FILES
──────────────────
Journaux des transactions pour recovery

Mode circulaire: Log1 -> Log2 -> Log3 -> Log1

POURQUOI? Recovery - Rejouer transactions après crash

8. ARCHIVE LOG FILES
─────────────────────
Copies des redo logs pleins (si ARCHIVELOG mode activé)

POURQUOI? Point-in-time recovery - Restaurer à n'importe quel moment


[OK] INSTALLATION D'ORACLE DATABASE

# === ÉDITIONS ORACLE ===

1. Oracle Database Express Edition (XE) - GRATUIT
   - Limité à 12 GB de données
   - 2 GB RAM max
   - 2 CPU cores max
   - Idéal pour: Développement, apprentissage, petites apps

2. Oracle Database Standard Edition (SE)
   - Fonctionnalités de base
   - Limité à 4 sockets CPU
   - Support partitionnement limité

3. Oracle Database Enterprise Edition (EE)
   - Toutes les fonctionnalités
   - Pas de limites matérielles
   - Options avancées (RAC, partitionnement, compression, etc.)

4. Oracle Cloud Database
   - Database as a Service
   - Autonomous Database (gestion automatique)


# === INSTALLATION ORACLE XE (LINUX) ===

# PRÉREQUIS
───────────
OS: Oracle Linux, Red Hat, CentOS, Ubuntu
RAM: Minimum 2 GB (recommandé 4 GB+)
Disk: Minimum 10 GB libre
Swap: Minimum 2 GB

# ÉTAPES D'INSTALLATION
────────────────────────

# 1. Télécharger Oracle XE
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

# 2. Installer le package
sudo yum -y localinstall oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

# OU sur Ubuntu/Debian
sudo alien -i oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

# 3. Configurer la base de données
sudo /etc/init.d/oracle-xe-21c configure

# Vous serez invité à:
# - Définir le port listener (défaut: 1521)
# - Définir le port EM Express (défaut: 5500)
# - Définir le mot de passe SYS et SYSTEM

# 4. Configurer l'environnement
echo "source /opt/oracle/product/21c/dbhomeXE/bin/oraenv" >> ~/.bashrc
source ~/.bashrc

# Entrer ORACLE_SID: XE

# 5. Vérifier installation
sqlplus / as sysdba

# Vous devriez voir:
# SQL*Plus: Release 21.0.0.0.0
# Connected to:
# Oracle Database 21c Express Edition Release 21.0.0.0.0


# === INSTALLATION ORACLE XE (WINDOWS) ===

# 1. Télécharger
https://www.oracle.com/database/technologies/xe-downloads.html

# 2. Exécuter setup.exe

# 3. Suivre l'assistant d'installation
# - Choisir répertoire d'installation
# - Définir mot de passe SYS et SYSTEM
# - Définir ports (listener: 1521, EM Express: 5500)

# 4. Après installation, configurer variables d'environnement
ORACLE_HOME=C:\app\oracle\product\21c\dbhomeXE
ORACLE_SID=XE
PATH=%PATH%;%ORACLE_HOME%\bin

# 5. Tester connexion
sqlplus / as sysdba


# === INSTALLATION AVEC DOCKER (RECOMMANDÉ POUR DÉVELOPPEMENT) ===

# POURQUOI Docker ?
[OK] Installation rapide (minutes vs heures)
[OK] Pas de pollution système
[OK] Environnements multiples isolés
[OK] Facile à supprimer/recréer

# 1. Télécharger image officielle
docker pull gvenzl/oracle-xe:21-slim

# 2. Lancer container
docker run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PASSWORD=MyStrongPassword123 \
  -e ORACLE_DATABASE=MYDB \
  -v oracle-data:/opt/oracle/oradata \
  gvenzl/oracle-xe:21-slim

# Explications:
# -d: Mode détaché (background)
# --name: Nom du container
# -p 1521:1521: Port listener SQL*Net
# -p 5500:5500: Port Enterprise Manager Express
# -e ORACLE_PASSWORD: Mot de passe SYS et SYSTEM
# -e ORACLE_DATABASE: Nom de la PDB (pluggable database)
# -v oracle-data: Volume persistant pour données

# 3. Vérifier que le container démarre
docker logs -f oracle-xe

# Attendre message: "DATABASE IS READY TO USE!"

# 4. Se connecter
docker exec -it oracle-xe sqlplus system/MyStrongPassword123@//localhost:1521/XEPDB1

# 5. Commandes utiles Docker
docker stop oracle-xe           # Arrêter
docker start oracle-xe          # Démarrer
docker restart oracle-xe        # Redémarrer
docker rm -f oracle-xe          # Supprimer
docker exec -it oracle-xe bash  # Shell dans container


# === CONFIGURATION POST-INSTALLATION ===

# 1. Se connecter en tant que SYS
sqlplus / as sysdba

# 2. Vérifier statut de la base
SELECT instance_name, status, database_status FROM v$instance;

# 3. Voir les tablespaces
SELECT tablespace_name, status FROM dba_tablespaces;

# 4. Créer un utilisateur de développement
CREATE USER dev_user IDENTIFIED BY DevPassword123;
GRANT CONNECT, RESOURCE TO dev_user;
GRANT UNLIMITED TABLESPACE TO dev_user;

# 5. Autoriser connexions distantes (si nécessaire)
# Éditer listener.ora et tnsnames.ora
# Localisation:
# Linux: $ORACLE_HOME/network/admin/
# Windows: %ORACLE_HOME%\network\admin\

# 6. Redémarrer le listener
lsnrctl stop
lsnrctl start


# === OUTILS D'ADMINISTRATION ===

1. SQL*Plus (Ligne de commande)
   - Installé par défaut
   - Interface texte
   - Scripts automatisables

2. SQL Developer (GUI gratuit)
   - Télécharger: https://www.oracle.com/tools/downloads/sqldev-downloads.html
   - Interface graphique moderne
   - Développement SQL, PL/SQL, export/import

3. Oracle Enterprise Manager (EM) Express
   - Interface web
   - URL: https://localhost:5500/em
   - Monitoring, administration

4. DBeaver (Tiers, gratuit)
   - Multi-bases de données
   - Interface moderne
   - Diagrammes ER

5. Toad for Oracle (Commercial)
   - Fonctionnalités avancées
   - Optimisation, debugging


[OK] SE CONNECTER À ORACLE

# === MÉTHODES DE CONNEXION ===

# 1. CONNEXION LOCALE (SQL*Plus)
────────────────────────────────

# En tant que SYSDBA (administrateur système)
sqlplus / as sysdba

# En tant qu'utilisateur normal
sqlplus username/password

# En tant qu'utilisateur avec SID spécifique
sqlplus username/password@SID

# Exemple
sqlplus dev_user/DevPassword123@XE


# 2. CONNEXION DISTANTE (Chaîne de connexion)
──────────────────────────────────────────────

# Format Easy Connect
sqlplus username/password@hostname:port/service_name

# Exemple
sqlplus dev_user/DevPassword123@192.168.1.100:1521/XEPDB1

# Format TNS (nécessite tnsnames.ora configuré)
sqlplus dev_user/DevPassword123@PROD_DB


# 3. CHAÎNE DE CONNEXION COMPLÈTE
──────────────────────────────────

sqlplus username/password@"(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=service_name))
)"

# Exemple
sqlplus system/MyPassword@"(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=XEPDB1))
)"


# 4. CONNEXION AVEC SQL DEVELOPER
──────────────────────────────────

1. Lancer SQL Developer
2. Créer nouvelle connexion (icône +)
3. Remplir:
   - Name: Ma Connexion
   - Username: dev_user
   - Password: DevPassword123
   - Hostname: localhost
   - Port: 1521
   - SID ou Service Name: XEPDB1
4. Test -> Connect


# === COMPRENDRE CDB vs PDB ===

À partir d'Oracle 12c, architecture multi-tenant:

CDB (Container Database)
└── PDB1 (Pluggable Database 1)
└── PDB2 (Pluggable Database 2)
└── PDB3 (Pluggable Database 3)

POURQUOI?
[OK] Consolidation - Plusieurs bases dans une instance
[OK] Économie ressources - Processus partagés
[OK] Facilité de déploiement - Clone PDB rapidement
[OK] Isolation - Chaque PDB indépendante

# Se connecter à la CDB root
sqlplus sys/password@localhost:1521/XE as sysdba

# Se connecter à une PDB
sqlplus dev_user/password@localhost:1521/XEPDB1

# Lister les PDBs
SELECT name, open_mode FROM v$pdbs;

# Ouvrir une PDB
ALTER PLUGGABLE DATABASE XEPDB1 OPEN;

# Basculer vers une PDB
ALTER SESSION SET CONTAINER = XEPDB1;


# === COMMANDES SQL*PLUS UTILES ===

# Afficher utilisateur actuel
SHOW USER;

# Afficher base de données connectée
SELECT sys_context('USERENV', 'DB_NAME') FROM dual;

# Formater sortie
SET LINESIZE 200
SET PAGESIZE 100
COLUMN column_name FORMAT A30

# Spool (sauvegarder sortie)
SPOOL output.txt
SELECT * FROM employees;
SPOOL OFF

# Exécuter script
@script.sql
START script.sql

# Décrire structure table
DESCRIBE employees;
DESC employees;

# Aide
HELP command;

# Quitter
EXIT;
QUIT;


[OK] GESTION DES UTILISATEURS ET PRIVILÈGES

# === CRÉER UN UTILISATEUR ===

# SYNTAXE DE BASE
CREATE USER username IDENTIFIED BY password;

# Exemple simple
CREATE USER john_doe IDENTIFIED BY SecurePass123;

# Création avec options
CREATE USER john_doe 
IDENTIFIED BY SecurePass123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
ACCOUNT UNLOCK
PASSWORD EXPIRE;

# Explications:
# - DEFAULT TABLESPACE: Où ses objets seront stockés
# - TEMPORARY TABLESPACE: Pour tris et opérations temporaires
# - QUOTA: Limite d'espace sur tablespace
# - ACCOUNT UNLOCK: Compte actif immédiatement
# - PASSWORD EXPIRE: Forcer changement mot de passe à première connexion


# === MODIFIER UN UTILISATEUR ===

# Changer mot de passe
ALTER USER john_doe IDENTIFIED BY NewPassword456;

# Changer tablespace par défaut
ALTER USER john_doe DEFAULT TABLESPACE data_ts;

# Modifier quota
ALTER USER john_doe QUOTA 500M ON users;
ALTER USER john_doe QUOTA UNLIMITED ON users;

# Verrouiller/déverrouiller compte
ALTER USER john_doe ACCOUNT LOCK;
ALTER USER john_doe ACCOUNT UNLOCK;

# Expirer mot de passe
ALTER USER john_doe PASSWORD EXPIRE;


# === SUPPRIMER UN UTILISATEUR ===

# Suppression simple
DROP USER john_doe;

# Suppression avec tous ses objets
DROP USER john_doe CASCADE;

# ATTENTION: CASCADE supprime TOUTES les tables, vues, etc. de l'utilisateur!


# === PRIVILÈGES SYSTÈME ===

Les privilèges système permettent d'effectuer des opérations spécifiques

# TYPES DE PRIVILÈGES SYSTÈME

1. Connexion
   - CREATE SESSION: Se connecter à la base

2. Création d'objets
   - CREATE TABLE: Créer tables
   - CREATE VIEW: Créer vues
   - CREATE SEQUENCE: Créer séquences
   - CREATE PROCEDURE: Créer procédures stockées
   - CREATE TRIGGER: Créer triggers
   - CREATE SYNONYM: Créer synonymes

3. Administration
   - CREATE USER: Créer utilisateurs
   - ALTER USER: Modifier utilisateurs
   - DROP USER: Supprimer utilisateurs
   - CREATE TABLESPACE: Créer tablespaces


# ACCORDER PRIVILÈGES SYSTÈME

# Accorder privilège unique
GRANT CREATE SESSION TO john_doe;

# Accorder plusieurs privilèges
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO john_doe;

# Accorder avec option de transmission
GRANT CREATE SESSION TO john_doe WITH ADMIN OPTION;
-- john_doe peut maintenant accorder CREATE SESSION à d'autres


# RÉVOQUER PRIVILÈGES SYSTÈME

REVOKE CREATE TABLE FROM john_doe;
REVOKE CREATE SESSION, CREATE VIEW FROM john_doe;


# === PRIVILÈGES OBJET ===

Les privilèges objet contrôlent l'accès aux objets spécifiques (tables, vues, etc.)

# TYPES DE PRIVILÈGES OBJET

Sur les TABLES:
- SELECT: Lire données
- INSERT: Insérer lignes
- UPDATE: Modifier données
- DELETE: Supprimer lignes
- ALTER: Modifier structure
- INDEX: Créer index
- REFERENCES: Créer clés étrangères

Sur les VUES:
- SELECT, INSERT, UPDATE, DELETE

Sur les SEQUENCES:
- SELECT: Utiliser NEXTVAL et CURRVAL
- ALTER: Modifier séquence

Sur les PROCEDURES/FUNCTIONS:
- EXECUTE: Exécuter


# ACCORDER PRIVILÈGES OBJET

# Syntaxe
GRANT privilege ON object_name TO user;

# Exemples
GRANT SELECT ON employees TO john_doe;
GRANT SELECT, INSERT, UPDATE ON employees TO john_doe;
GRANT ALL ON employees TO john_doe;

# Avec option de transmission
GRANT SELECT ON employees TO john_doe WITH GRANT OPTION;
-- john_doe peut accorder SELECT sur employees à d'autres

# Sur toutes les colonnes
GRANT SELECT ON employees TO john_doe;

# Sur colonnes spécifiques
GRANT UPDATE (salary, commission) ON employees TO john_doe;


# RÉVOQUER PRIVILÈGES OBJET

REVOKE SELECT ON employees FROM john_doe;
REVOKE ALL ON employees FROM john_doe;

# En cascade (révoque aussi pour ceux à qui john_doe a transmis)
REVOKE SELECT ON employees FROM john_doe CASCADE CONSTRAINTS;


# === RÔLES ===

Un rôle est un groupe de privilèges nommé

POURQUOI LES RÔLES?
[OK] Gestion simplifiée - Accorder un rôle au lieu de 50 privilèges
[OK] Maintenance facile - Modifier rôle affecte tous les utilisateurs
[OK] Standardisation - Rôles prédéfinis pour fonctions communes

# RÔLES PRÉDÉFINIS D'ORACLE

1. CONNECT
   - CREATE SESSION
   - CREATE TABLE
   - CREATE VIEW
   - CREATE SEQUENCE
   - CREATE SYNONYM
   - Idéal pour développeurs

2. RESOURCE
   - CREATE TABLE
   - CREATE SEQUENCE
   - CREATE TRIGGER
   - CREATE PROCEDURE
   - CREATE TYPE
   - Idéal pour développeurs avancés

3. DBA
   - TOUS les privilèges système WITH ADMIN OPTION
   - Idéal pour administrateurs

# CRÉER UN RÔLE PERSONNALISÉ

CREATE ROLE app_developer;

# Accorder privilèges au rôle
GRANT CREATE SESSION TO app_developer;
GRANT CREATE TABLE TO app_developer;
GRANT CREATE VIEW TO app_developer;
GRANT SELECT ON hr.employees TO app_developer;

# Accorder rôle à utilisateur
GRANT app_developer TO john_doe;

# Accorder plusieurs rôles
GRANT CONNECT, RESOURCE TO john_doe;


# ACTIVER/DÉSACTIVER RÔLES

# Par défaut, rôles activés à la connexion
# Désactiver temporairement
SET ROLE NONE;

# Activer rôle spécifique
SET ROLE app_developer;

# Activer tous les rôles
SET ROLE ALL;

# Voir rôles actifs
SELECT * FROM session_roles;


# SUPPRIMER UN RÔLE

DROP ROLE app_developer;


# === PROFILS (GESTION DES RESSOURCES) ===

Les profils contrôlent ressources et politiques de mot de passe

# CRÉER UN PROFIL

CREATE PROFILE developer_profile LIMIT
  -- Limites de sessions
  SESSIONS_PER_USER 3
  CPU_PER_SESSION UNLIMITED
  CONNECT_TIME 480  -- Minutes (8 heures)
  IDLE_TIME 30      -- Minutes
  
  -- Limites de ressources
  LOGICAL_READS_PER_SESSION UNLIMITED
  PRIVATE_SGA 50M
  
  -- Politique de mot de passe
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME 90        -- Jours
  PASSWORD_REUSE_TIME 365      -- Jours
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1         -- Jours
  PASSWORD_GRACE_TIME 7;       -- Jours

# Assigner profil à utilisateur
ALTER USER john_doe PROFILE developer_profile;

# Profil par défaut
ALTER USER john_doe PROFILE DEFAULT;

# Voir profil d'un utilisateur
SELECT username, profile FROM dba_users WHERE username = 'JOHN_DOE';

# Voir limites d'un profil
SELECT * FROM dba_profiles WHERE profile = 'DEVELOPER_PROFILE';


# === AUDIT DES UTILISATEURS ===

# Activer audit
AUDIT CREATE SESSION BY john_doe;
AUDIT SELECT TABLE BY john_doe;

# Voir résultats audit
SELECT username, action_name, timestamp 
FROM dba_audit_trail 
WHERE username = 'JOHN_DOE';

# Désactiver audit
NOAUDIT CREATE SESSION BY john_doe;


# === BONNES PRATIQUES SÉCURITÉ ===

1. Principe du moindre privilège
   [OK] Accorder seulement privilèges nécessaires
   [X] Ne pas accorder DBA à tout le monde

2. Utiliser des rôles
   [OK] Créer rôles par fonction (developer, analyst, admin)
   [OK] Accorder rôles aux utilisateurs

3. Mots de passe forts
   [OK] Minimum 8 caractères
   [OK] Majuscules, minuscules, chiffres, symboles
   [OK] Expiration régulière (90 jours)

4. Audit
   [OK] Activer audit pour opérations sensibles
   [OK] Réviser logs régulièrement

5. Séparation des environnements
   [OK] Dev, Test, Prod avec utilisateurs différents
   [OK] Ne jamais utiliser SYS/SYSTEM en production

6. Quotas
   [OK] Limiter espace disque par utilisateur
   [OK] Éviter UNLIMITED TABLESPACE sauf si nécessaire


# === EXEMPLES PRATIQUES ===

# SCÉNARIO 1: Nouvel employé développeur
──────────────────────────────────────────

-- 1. Créer utilisateur
CREATE USER alice IDENTIFIED BY SecurePass123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

-- 2. Accorder rôles standards
GRANT CONNECT, RESOURCE TO alice;

-- 3. Accorder accès aux tables de dev
GRANT SELECT, INSERT, UPDATE, DELETE ON dev_schema.customers TO alice;
GRANT SELECT ON dev_schema.products TO alice;

-- 4. Assigner profil
ALTER USER alice PROFILE developer_profile;


# SCÉNARIO 2: Analyste de données (lecture seule)
──────────────────────────────────────────────────

-- 1. Créer rôle analyst
CREATE ROLE data_analyst;

-- 2. Accorder privilèges au rôle
GRANT CREATE SESSION TO data_analyst;
GRANT SELECT ON sales.orders TO data_analyst;
GRANT SELECT ON sales.customers TO data_analyst;
GRANT SELECT ON sales.products TO data_analyst;

-- 3. Créer utilisateur
CREATE USER bob IDENTIFIED BY AnalystPass456;

-- 4. Accorder rôle
GRANT data_analyst TO bob;


# SCÉNARIO 3: Application web
─────────────────────────────

-- 1. Créer utilisateur pour l'application
CREATE USER webapp_user IDENTIFIED BY WebAppPass789
DEFAULT TABLESPACE app_data
QUOTA UNLIMITED ON app_data;

-- 2. Accorder privilèges minimums
GRANT CREATE SESSION TO webapp_user;

-- 3. Accorder accès tables spécifiques
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.users TO webapp_user;
GRANT SELECT, INSERT ON app_schema.logs TO webapp_user;
GRANT EXECUTE ON app_schema.authenticate_user TO webapp_user;

-- 4. Créer synonymes pour simplifier
CREATE SYNONYM webapp_user.users FOR app_schema.users;
CREATE SYNONYM webapp_user.logs FOR app_schema.logs;


# === DIAGNOSTIC ET DÉPANNAGE ===

# Lister tous les utilisateurs
SELECT username, account_status, created, profile 
FROM dba_users 
ORDER BY created DESC;

# Voir privilèges d'un utilisateur
SELECT * FROM dba_sys_privs WHERE grantee = 'JOHN_DOE';
SELECT * FROM dba_tab_privs WHERE grantee = 'JOHN_DOE';

# Voir rôles d'un utilisateur
SELECT * FROM dba_role_privs WHERE grantee = 'JOHN_DOE';

# Voir contenu d'un rôle
SELECT * FROM role_sys_privs WHERE role = 'APP_DEVELOPER';
SELECT * FROM role_tab_privs WHERE role = 'APP_DEVELOPER';

# Voir sessions actives
SELECT username, status, osuser, machine, program
FROM v$session
WHERE username IS NOT NULL;

# Déconnecter utilisateur
ALTER SYSTEM KILL SESSION 'sid,serial#';

# Trouver objets d'un utilisateur
SELECT object_type, object_name 
FROM dba_objects 
WHERE owner = 'JOHN_DOE'
ORDER BY object_type, object_name;

# Voir espace utilisé par utilisateur
SELECT owner, tablespace_name, SUM(bytes)/1024/1024 AS mb_used
FROM dba_segments
WHERE owner = 'JOHN_DOE'
GROUP BY owner, tablespace_name;


[OK] CRÉATION ET GESTION DES TABLES

# === TYPES DE DONNÉES ORACLE ===

# 1. TYPES NUMÉRIQUES
─────────────────────

NUMBER(p, s)
  p = précision (nombre total de chiffres), max 38
  s = échelle (nombre de décimales)
  
  Exemples:
  NUMBER(5)      -> 12345 (entier jusqu'à 5 chiffres)
  NUMBER(10,2)   -> 12345678.90 (8 entiers, 2 décimales)
  NUMBER         -> Précision maximale (38 chiffres)

INTEGER / INT
  Synonyme de NUMBER sans décimales
  
FLOAT(p)
  Nombre à virgule flottante
  p = précision binaire (max 126)

BINARY_FLOAT
  32-bit floating point (comme float en C)
  
BINARY_DOUBLE
  64-bit floating point (comme double en C)


# 2. TYPES CARACTÈRES
─────────────────────

CHAR(n)
  Chaîne de longueur FIXE (paddée avec espaces)
  n = longueur (1-2000 bytes)
  Exemple: CHAR(10) stocke toujours 10 bytes
  
VARCHAR2(n)
  Chaîne de longueur VARIABLE
  n = longueur maximum (1-4000 bytes)
  Exemple: VARCHAR2(100) stocke jusqu'à 100 bytes
  
  POURQUOI VARCHAR2?
  [OK] Plus efficace en espace que CHAR
  [OK] Utilisé pour 90% des colonnes texte

NCHAR(n) / NVARCHAR2(n)
  Versions Unicode (National Character Set)
  Support caractères internationaux

CLOB (Character Large Object)
  Texte très long (jusqu'à 4 GB)
  Exemple: Articles de blog, descriptions longues

NCLOB
  Version Unicode de CLOB


# 3. TYPES DATE ET HEURE
────────────────────────

DATE
  Date + Heure (précision: seconde)
  Format: JJ-MOI-AAAA HH24:MI:SS
  Plage: 01-JAN-4712 BC à 31-DEC-9999
  
TIMESTAMP
  Date + Heure avec fractions de secondes
  Précision: jusqu'à 9 décimales
  Format: JJ-MOI-AAAA HH24:MI:SS.FF
  
TIMESTAMP WITH TIME ZONE
  TIMESTAMP + fuseau horaire
  Exemple: 15-JAN-2024 10:30:00.000000 +02:00
  
TIMESTAMP WITH LOCAL TIME ZONE
  Stocké en UTC, affiché en fuseau local de la session

INTERVAL YEAR TO MONTH
  Période en années et mois
  Exemple: INTERVAL '2-6' YEAR TO MONTH = 2 ans 6 mois
  
INTERVAL DAY TO SECOND
  Période en jours, heures, minutes, secondes
  Exemple: INTERVAL '4 10:30:00' DAY TO SECOND = 4 jours 10h30


# 4. TYPES BINAIRES
───────────────────

RAW(n)
  Données binaires brutes (1-2000 bytes)
  
LONG RAW
  Données binaires longues (jusqu'à 2 GB)
  DÉPRÉCIÉ: Utiliser BLOB à la place

BLOB (Binary Large Object)
  Données binaires volumineuses (jusqu'à 4 GB)
  Exemple: Images, PDFs, fichiers

BFILE
  Pointeur vers fichier externe sur disque


# 5. TYPES SPÉCIAUX
───────────────────

ROWID
  Adresse physique d'une ligne dans la base
  Format: AAA BBB CCC DDD
  
UROWID
  Universal ROWID (pour tables IOT et partitionnées)

XMLType
  Documents XML
  
JSON
  Documents JSON (Oracle 21c+)


# === CRÉER UNE TABLE ===

# SYNTAXE DE BASE
─────────────────

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
);

# EXEMPLE SIMPLE
────────────────

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    salary NUMBER(8,2)
);


# EXEMPLE AVEC CONTRAINTES
──────────────────────────

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8,2) CHECK (salary > 0),
    commission_pct NUMBER(2,2) CHECK (commission_pct >= 0 AND commission_pct <= 0.99),
    manager_id NUMBER(6),
    department_id NUMBER(4),
    
    -- Contraintes au niveau table
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id),
    CONSTRAINT fk_department FOREIGN KEY (department_id) 
        REFERENCES departments(department_id) ON DELETE CASCADE
);


# === CONTRAINTES D'INTÉGRITÉ ===

# 1. PRIMARY KEY (Clé primaire)
────────────────────────────────

Identifie de façon unique chaque ligne
- Une seule par table
- Valeurs uniques et non NULL

# Contrainte de colonne
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

# Contrainte de table (clé composite)
CREATE TABLE order_items (
    order_id NUMBER,
    product_id NUMBER,
    quantity NUMBER,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

# Nommer explicitement
CREATE TABLE departments (
    department_id NUMBER CONSTRAINT pk_departments PRIMARY KEY,
    department_name VARCHAR2(50)
);


# 2. FOREIGN KEY (Clé étrangère)
────────────────────────────────

Maintient intégrité référentielle entre tables

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    department_id NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

# Avec actions référentielles
FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE CASCADE        -- Supprimer employee si department supprimé
    ON DELETE SET NULL       -- Mettre NULL si department supprimé
    -- ON DELETE RESTRICT (défaut) - Empêcher suppression si employees existent


# 3. UNIQUE (Unicité)
────────────────────

Garantit valeurs uniques (NULL autorisé)

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    email VARCHAR2(100) UNIQUE,
    phone_number VARCHAR2(20) CONSTRAINT uk_phone UNIQUE
);

# UNIQUE composite
CREATE TABLE table_name (
    col1 NUMBER,
    col2 VARCHAR2(50),
    CONSTRAINT uk_composite UNIQUE (col1, col2)
);


# 4. NOT NULL (Non NULL)
───────────────────────

Interdit valeurs NULL

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) NOT NULL
);


# 5. CHECK (Validation)
──────────────────────

Valide valeurs selon condition

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    salary NUMBER(8,2) CHECK (salary > 0),
    age NUMBER CHECK (age >= 18 AND age <= 65),
    status VARCHAR2(10) CHECK (status IN ('ACTIVE', 'INACTIVE', 'RETIRED'))
);

# CHECK avec nom
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    price NUMBER(10,2),
    discount_pct NUMBER(3,2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount_pct BETWEEN 0 AND 1)
);


# 6. DEFAULT (Valeur par défaut)
────────────────────────────────

Valeur automatique si non spécifiée

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    hire_date DATE DEFAULT SYSDATE,
    status VARCHAR2(10) DEFAULT 'ACTIVE',
    salary NUMBER DEFAULT 0
);

# Avec expressions
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_date DATE DEFAULT SYSDATE,
    order_number VARCHAR2(20) DEFAULT 'ORD-' || TO_CHAR(SYSDATE, 'YYYYMMDD')
);


# === CRÉER TABLE DEPUIS UNE AUTRE (CTAS) ===

# Copier structure + données
CREATE TABLE employees_backup AS
SELECT * FROM employees;

# Copier seulement structure
CREATE TABLE employees_template AS
SELECT * FROM employees
WHERE 1=0;  -- Condition toujours fausse

# Copier colonnes spécifiques
CREATE TABLE employee_names AS
SELECT employee_id, first_name, last_name
FROM employees;

# Avec transformation
CREATE TABLE high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 100000;


# === MODIFIER UNE TABLE ===

# AJOUTER COLONNE
─────────────────

ALTER TABLE employees ADD (
    middle_name VARCHAR2(50),
    birth_date DATE
);

# Avec valeur par défaut
ALTER TABLE employees ADD (
    status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL
);


# MODIFIER COLONNE
──────────────────

# Changer type de données
ALTER TABLE employees MODIFY (
    email VARCHAR2(150)
);

# Ajouter/modifier contrainte NOT NULL
ALTER TABLE employees MODIFY (
    phone_number VARCHAR2(20) NOT NULL
);

# Retirer NOT NULL
ALTER TABLE employees MODIFY (
    phone_number VARCHAR2(20) NULL
);

# Changer valeur par défaut
ALTER TABLE employees MODIFY (
    status DEFAULT 'PENDING'
);


# RENOMMER COLONNE
──────────────────

ALTER TABLE employees RENAME COLUMN phone_number TO mobile_number;


# SUPPRIMER COLONNE
───────────────────

# Suppression immédiate
ALTER TABLE employees DROP COLUMN middle_name;

# Suppression différée (plus rapide sur grosses tables)
ALTER TABLE employees SET UNUSED (middle_name);
-- Plus tard:
ALTER TABLE employees DROP UNUSED COLUMNS;

# Supprimer plusieurs colonnes
ALTER TABLE employees DROP (middle_name, birth_date);


# AJOUTER/SUPPRIMER CONTRAINTES
────────────────────────────────

# Ajouter PRIMARY KEY
ALTER TABLE departments ADD CONSTRAINT pk_dept PRIMARY KEY (department_id);

# Ajouter FOREIGN KEY
ALTER TABLE employees ADD CONSTRAINT fk_dept 
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

# Ajouter UNIQUE
ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email);

# Ajouter CHECK
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

# Supprimer contrainte
ALTER TABLE employees DROP CONSTRAINT fk_dept;
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees DROP UNIQUE (email);

# Désactiver/Activer contrainte
ALTER TABLE employees DISABLE CONSTRAINT fk_dept;
ALTER TABLE employees ENABLE CONSTRAINT fk_dept;

# Valider contrainte désactivée
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT fk_dept;
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT fk_dept;


# RENOMMER TABLE
────────────────

ALTER TABLE employees RENAME TO staff;

# Ou
RENAME employees TO staff;


# === SUPPRIMER UNE TABLE ===

# Suppression simple
DROP TABLE employees;

# Avec suppression cascade des contraintes
DROP TABLE departments CASCADE CONSTRAINTS;

# Purger définitivement (ne va pas dans corbeille)
DROP TABLE employees PURGE;


# === TRONQUER UNE TABLE ===

Vide table rapidement (plus rapide que DELETE)

TRUNCATE TABLE employees;

# DIFFÉRENCES DELETE vs TRUNCATE:

DELETE:
[OK] Peut avoir WHERE clause
[OK] Peut être annulé (ROLLBACK)
[OK] Déclenche triggers
[X] Plus lent

TRUNCATE:
[X] Pas de WHERE clause (supprime tout)
[X] Ne peut pas être annulé
[X] Ne déclenche pas triggers
[OK] Très rapide
[OK] Réinitialise séquences


# === TABLES TEMPORAIRES ===

# Table temporaire globale
CREATE GLOBAL TEMPORARY TABLE temp_employees (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    salary NUMBER
) ON COMMIT DELETE ROWS;  -- Vide à chaque COMMIT

# OU

ON COMMIT PRESERVE ROWS;  -- Conserve jusqu'à fin de session

# POURQUOI tables temporaires?
[OK] Performance - Données en mémoire
[OK] Isolement - Chaque session voit ses propres données
[OK] Pas de redo logs - Plus rapide
[OK] Auto-nettoyage


# === TABLES EXTERNES ===

Accéder à fichiers plats comme tables

CREATE TABLE ext_employees (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
    )
    LOCATION ('employees.csv')
);

# POURQUOI tables externes?
[OK] Importer données sans chargement préalable
[OK] Requêter fichiers directement
[OK] ETL simplifié


# === PARTITIONNEMENT ===

Diviser table en morceaux plus petits

# Partitionnement par plage (RANGE)
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2024', 'DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2024', 'DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2024', 'DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY'))
);

# Partitionnement par liste (LIST)
CREATE TABLE customers (
    customer_id NUMBER,
    country VARCHAR2(50)
)
PARTITION BY LIST (country) (
    PARTITION cust_us VALUES ('USA'),
    PARTITION cust_uk VALUES ('UK'),
    PARTITION cust_fr VALUES ('FRANCE'),
    PARTITION cust_others VALUES (DEFAULT)
);

# Partitionnement par hash (HASH)
CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;

# POURQUOI partitionner?
[OK] Performance - Requêtes scannent moins de données
[OK] Maintenance - Backup/archivage par partition
[OK] Disponibilité - Une partition down, autres accessibles
[OK] Scalabilité - Tables de milliards de lignes


# === COMPRESSION ===

# Compression basique
CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR2(50)
) COMPRESS;

# Compression avancée (Enterprise Edition)
CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR2(50)
) COMPRESS FOR OLTP;  -- Online Transaction Processing

CREATE TABLE archive_data (
    ...
) COMPRESS FOR QUERY HIGH;  -- Data Warehouse

# POURQUOI compresser?
[OK] Économie d'espace (50-90% réduction)
[OK] Performance I/O - Moins de blocs à lire
[X] CPU - Compression/décompression


# === INDEX-ORGANIZED TABLES (IOT) ===

Table stockée comme index

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    price NUMBER
) ORGANIZATION INDEX;

# POURQUOI IOT?
[OK] Performance - Pas de ROWID lookup
[OK] Espace - Pas de stockage séparé index + table
[OK] Idéal pour tables souvent accédées par clé primaire


# === VOIR INFORMATIONS SUR LES TABLES ===

# Lister toutes les tables
SELECT table_name FROM user_tables ORDER BY table_name;

# Structure d'une table
DESC employees;
DESCRIBE employees;

# Colonnes d'une table
SELECT column_name, data_type, data_length, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

# Contraintes d'une table
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

# Contraintes type:
# P = PRIMARY KEY
# R = FOREIGN KEY
# U = UNIQUE
# C = CHECK

# Détail des contraintes
SELECT 
    uc.constraint_name,
    uc.constraint_type,
    ucc.column_name,
    uc.search_condition
FROM user_constraints uc
JOIN user_cons_columns ucc ON uc.constraint_name = ucc.constraint_name
WHERE uc.table_name = 'EMPLOYEES'
ORDER BY uc.constraint_type, uc.constraint_name;

# Index d'une table
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

# Espace occupé par table
SELECT segment_name, bytes/1024/1024 AS mb_size
FROM user_segments
WHERE segment_name = 'EMPLOYEES';

# Nombre de lignes (estimation)
SELECT num_rows, blocks, avg_row_len
FROM user_tables
WHERE table_name = 'EMPLOYEES';

# Statistiques actuelles
SELECT COUNT(*) FROM employees;

# Mettre à jour statistiques
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');


# === EXEMPLES PRATIQUES COMPLETS ===

# EXEMPLE 1: Système de gestion d'école
────────────────────────────────────────

-- Table étudiants
CREATE TABLE students (
    student_id NUMBER(8) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    phone VARCHAR2(20),
    date_of_birth DATE NOT NULL,
    enrollment_date DATE DEFAULT SYSDATE NOT NULL,
    status VARCHAR2(20) DEFAULT 'ACTIVE' 
        CHECK (status IN ('ACTIVE', 'INACTIVE', 'GRADUATED', 'SUSPENDED'))
);

-- Table cours
CREATE TABLE courses (
    course_id NUMBER(6) PRIMARY KEY,
    course_code VARCHAR2(10) UNIQUE NOT NULL,
    course_name VARCHAR2(100) NOT NULL,
    credits NUMBER(1) CHECK (credits BETWEEN 1 AND 6),
    department VARCHAR2(50) NOT NULL,
    max_students NUMBER(3) DEFAULT 30 CHECK (max_students > 0)
);

-- Table inscriptions (relation many-to-many)
CREATE TABLE enrollments (
    enrollment_id NUMBER PRIMARY KEY,
    student_id NUMBER(8) NOT NULL,
    course_id NUMBER(6) NOT NULL,
    enrollment_date DATE DEFAULT SYSDATE NOT NULL,
    grade VARCHAR2(2) CHECK (grade IN ('A', 'B', 'C', 'D', 'F', 'I', 'W')),
    status VARCHAR2(20) DEFAULT 'ENROLLED' 
        CHECK (status IN ('ENROLLED', 'COMPLETED', 'DROPPED', 'FAILED')),
    
    CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) 
        REFERENCES students(student_id) ON DELETE CASCADE,
    CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) 
        REFERENCES courses(course_id),
    CONSTRAINT uk_enrollment UNIQUE (student_id, course_id)
);

-- Créer séquences pour IDs auto-incrémentés
CREATE SEQUENCE student_id_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE course_id_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE enrollment_id_seq START WITH 1 INCREMENT BY 1;


# EXEMPLE 2: Système e-commerce
────────────────────────────────

-- Table clients
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    username VARCHAR2(50) UNIQUE NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    password_hash VARCHAR2(256) NOT NULL,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    phone VARCHAR2(20),
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    last_login TIMESTAMP,
    is_active CHAR(1) DEFAULT 'Y' CHECK (is_active IN ('Y', 'N'))
);

-- Table produits
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    sku VARCHAR2(50) UNIQUE NOT NULL,
    product_name VARCHAR2(200) NOT NULL,
    description CLOB,
    category VARCHAR2(50) NOT NULL,
    price NUMBER(10,2) NOT NULL CHECK (price >= 0),
    cost NUMBER(10,2) CHECK (cost >= 0),
    stock_quantity NUMBER DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active CHAR(1) DEFAULT 'Y' CHECK (is_active IN ('Y', 'N')),
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Table commandes
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    status VARCHAR2(20) DEFAULT 'PENDING' NOT NULL
        CHECK (status IN ('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED')),
    subtotal NUMBER(10,2) NOT NULL CHECK (subtotal >= 0),
    tax NUMBER(10,2) DEFAULT 0 CHECK (tax >= 0),
    shipping NUMBER(10,2) DEFAULT 0 CHECK (shipping >= 0),
    total NUMBER(10,2) NOT NULL CHECK (total >= 0),
    shipping_address VARCHAR2(500),
    
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

-- Table détails commandes
CREATE TABLE order_items (
    order_item_id NUMBER PRIMARY KEY,
    order_id NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    quantity NUMBER NOT NULL CHECK (quantity > 0),
    unit_price NUMBER(10,2) NOT NULL CHECK (unit_price >= 0),
    discount NUMBER(5,2) DEFAULT 0 CHECK (discount BETWEEN 0 AND 100),
    total NUMBER(10,2) NOT NULL CHECK (total >= 0),
    
    CONSTRAINT fk_item_order FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) ON DELETE CASCADE,
    CONSTRAINT fk_item_product FOREIGN KEY (product_id) 
        REFERENCES products(product_id)
);

-- Index pour performance
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_items_order ON order_items(order_id);
CREATE INDEX idx_products_category ON products(category);


# EXEMPLE 3: Système RH avec historique
────────────────────────────────────────

-- Table employés
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_number VARCHAR2(20) UNIQUE NOT NULL,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    phone VARCHAR2(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8,2) CHECK (salary > 0),
    commission_pct NUMBER(2,2) CHECK (commission_pct BETWEEN 0 AND 0.99),
    manager_id NUMBER,
    department_id NUMBER,
    
    CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id),
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

-- Table historique des postes
CREATE TABLE job_history (
    history_id NUMBER PRIMARY KEY,
    employee_id NUMBER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    job_id VARCHAR2(10) NOT NULL,
    department_id NUMBER,
    
    CONSTRAINT fk_jobhist_emp FOREIGN KEY (employee_id) 
        REFERENCES employees(employee_id) ON DELETE CASCADE,
    CONSTRAINT chk_dates CHECK (end_date IS NULL OR end_date > start_date)
);

-- Table congés
CREATE TABLE leave_requests (
    request_id NUMBER PRIMARY KEY,
    employee_id NUMBER NOT NULL,
    leave_type VARCHAR2(20) NOT NULL 
        CHECK (leave_type IN ('VACATION', 'SICK', 'PERSONAL', 'UNPAID')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days NUMBER GENERATED ALWAYS AS (end_date - start_date + 1),
    status VARCHAR2(20) DEFAULT 'PENDING'
        CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'CANCELLED')),
    requested_date TIMESTAMP DEFAULT SYSTIMESTAMP,
    approved_by NUMBER,
    approved_date TIMESTAMP,
    comments VARCHAR2(500),
    
    CONSTRAINT fk_leave_emp FOREIGN KEY (employee_id) 
        REFERENCES employees(employee_id),
    CONSTRAINT fk_leave_approver FOREIGN KEY (approved_by) 
        REFERENCES employees(employee_id),
    CONSTRAINT chk_leave_dates CHECK (end_date >= start_date)
);
```

```
[OK] REQUÊTES SQL - SELECT (LECTURE DE DONNÉES)

# === SYNTAXE DE BASE SELECT ===

SELECT colonne1, colonne2, ...
FROM table_name
WHERE condition
ORDER BY colonne;


# === SELECT SIMPLE ===

# Sélectionner toutes les colonnes
SELECT * FROM employees;

# ATTENTION: SELECT * en production
[X] Mauvaise performance si table volumineuse
[X] Réseau surchargé si beaucoup de colonnes
[OK] OK pour exploration/développement

# Sélectionner colonnes spécifiques
SELECT first_name, last_name, salary FROM employees;

# POURQUOI spécifier les colonnes?
[OK] Performance - Moins de données transférées
[OK] Clarté - Code plus lisible
[OK] Maintenance - Changements structure table n'affectent pas


# === ALIAS DE COLONNES ===

# Renommer colonne dans résultat
SELECT first_name AS prenom, last_name AS nom FROM employees;

# Sans AS (également valide)
SELECT first_name prenom, last_name nom FROM employees;

# Alias avec espaces (guillemets obligatoires)
SELECT salary AS "Salaire Annuel" FROM employees;

# Expressions avec alias
SELECT first_name, last_name, salary * 12 AS annual_salary FROM employees;


# === EXPRESSIONS ET CALCULS ===

# Opérations arithmétiques
SELECT 
    product_name,
    price,
    price * 0.8 AS discounted_price,          -- -20%
    price * 1.20 AS price_with_tax            -- +20% TVA
FROM products;

# Concaténation de chaînes
SELECT first_name || ' ' || last_name AS full_name FROM employees;

# Alternative: fonction CONCAT
SELECT CONCAT(first_name, CONCAT(' ', last_name)) AS full_name FROM employees;


# === CLAUSE WHERE (FILTRAGE) ===

# POURQUOI WHERE?
Filtrer les lignes selon conditions
[OK] Récupérer seulement données pertinentes
[OK] Performance - Moins de données traitées

# Égalité
SELECT * FROM employees WHERE department_id = 10;

# Différent
SELECT * FROM employees WHERE department_id != 10;
SELECT * FROM employees WHERE department_id <> 10;  -- Même chose

# Comparaisons numériques
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE salary >= 50000;
SELECT * FROM employees WHERE salary < 100000;
SELECT * FROM employees WHERE salary <= 100000;

# Comparaisons de chaînes
SELECT * FROM employees WHERE last_name = 'King';

# ATTENTION: Oracle est CASE-SENSITIVE pour les données
'King' ≠ 'king' ≠ 'KING'

# Pour recherche insensible à la casse
SELECT * FROM employees WHERE UPPER(last_name) = 'KING';
SELECT * FROM employees WHERE LOWER(last_name) = 'king';


# === OPÉRATEURS LOGIQUES ===

# AND (ET logique) - Toutes conditions doivent être vraies
SELECT * FROM employees 
WHERE department_id = 10 
AND salary > 50000;

# OR (OU logique) - Au moins une condition vraie
SELECT * FROM employees 
WHERE department_id = 10 
OR department_id = 20;

# NOT (Négation)
SELECT * FROM employees 
WHERE NOT department_id = 10;

# Combinaisons avec parenthèses
SELECT * FROM employees 
WHERE (department_id = 10 OR department_id = 20) 
AND salary > 50000;

# ATTENTION à l'ordre d'évaluation:
# AND a priorité sur OR
# Utiliser parenthèses pour clarifier


# === OPÉRATEUR BETWEEN ===

# Plage de valeurs (inclusif)
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;

# Équivalent à:
SELECT * FROM employees WHERE salary >= 50000 AND salary <= 100000;

# Dates
SELECT * FROM employees 
WHERE hire_date BETWEEN DATE '2020-01-01' AND DATE '2023-12-31';

# NOT BETWEEN
SELECT * FROM employees WHERE salary NOT BETWEEN 50000 AND 100000;


# === OPÉRATEUR IN ===

# Vérifier si valeur dans liste
SELECT * FROM employees WHERE department_id IN (10, 20, 30);

# Équivalent à:
SELECT * FROM employees 
WHERE department_id = 10 OR department_id = 20 OR department_id = 30;

# Avec chaînes
SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'SA_REP', 'ST_CLERK');

# NOT IN
SELECT * FROM employees WHERE department_id NOT IN (10, 20, 30);

# ATTENTION: NOT IN avec NULL
# Si la liste contient NULL, NOT IN retourne toujours FALSE
# Utiliser IS NOT NULL dans ce cas


# === OPÉRATEUR LIKE (RECHERCHE DE MOTIFS) ===

# CARACTÈRES SPÉCIAUX:
# % = n'importe quelle séquence de caractères (0 ou plus)
# _ = exactement un caractère

# Commence par 'A'
SELECT * FROM employees WHERE last_name LIKE 'A%';

# Termine par 'son'
SELECT * FROM employees WHERE last_name LIKE '%son';

# Contient 'and'
SELECT * FROM employees WHERE last_name LIKE '%and%';

# Deuxième lettre est 'a'
SELECT * FROM employees WHERE last_name LIKE '_a%';

# Exactement 5 caractères
SELECT * FROM employees WHERE last_name LIKE '_____';

# Recherche insensible à la casse
SELECT * FROM employees WHERE UPPER(last_name) LIKE 'A%';

# NOT LIKE
SELECT * FROM employees WHERE last_name NOT LIKE 'A%';

# Échapper caractères spéciaux
# Rechercher un % littéral
SELECT * FROM products WHERE description LIKE '%\%%' ESCAPE '\';


# === VALEURS NULL ===

# COMPRENDRE NULL:
# NULL ≠ 0
# NULL ≠ ''
# NULL = inconnu/absent

# Tester NULL
SELECT * FROM employees WHERE commission_pct IS NULL;

# Tester NOT NULL
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

# ERREURS FRÉQUENTES:
# [X] FAUX:
SELECT * FROM employees WHERE commission_pct = NULL;

# [OK] CORRECT:
SELECT * FROM employees WHERE commission_pct IS NULL;

# COALESCE - Remplacer NULL par valeur par défaut
SELECT 
    first_name,
    COALESCE(commission_pct, 0) AS commission
FROM employees;

# NVL - Fonction Oracle pour remplacer NULL
SELECT 
    first_name,
    NVL(commission_pct, 0) AS commission
FROM employees;

# NVL2 - Valeur différente si NULL ou NOT NULL
SELECT 
    first_name,
    NVL2(commission_pct, 'Has Commission', 'No Commission') AS status
FROM employees;


# === CLAUSE ORDER BY (TRI) ===

# Tri ascendant (défaut)
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary ASC;  -- Explicite

# Tri descendant
SELECT * FROM employees ORDER BY salary DESC;

# Tri sur plusieurs colonnes
SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

# Tri sur colonnes non affichées
SELECT first_name, last_name FROM employees ORDER BY salary DESC;

# Tri sur alias
SELECT first_name, salary * 12 AS annual_salary 
FROM employees 
ORDER BY annual_salary DESC;

# Tri sur position de colonne (déconseillé)
SELECT first_name, last_name, salary FROM employees ORDER BY 3 DESC;
-- 3 = troisième colonne (salary)

# NULLS FIRST / NULLS LAST
SELECT * FROM employees ORDER BY commission_pct NULLS FIRST;
SELECT * FROM employees ORDER BY commission_pct NULLS LAST;


# === CLAUSE DISTINCT (ÉLIMINER DOUBLONS) ===

# Valeurs uniques d'une colonne
SELECT DISTINCT department_id FROM employees;

# Combinaisons uniques de plusieurs colonnes
SELECT DISTINCT department_id, job_id FROM employees;

# COUNT avec DISTINCT
SELECT COUNT(DISTINCT department_id) FROM employees;


# === CLAUSE FETCH / OFFSET (PAGINATION) ===

# Premières N lignes (Oracle 12c+)
SELECT * FROM employees 
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

# Avec offset (skip N lignes)
SELECT * FROM employees 
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

# Pourcentage de lignes
SELECT * FROM employees 
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

# Avec TIES (inclure ex-aequo)
SELECT * FROM employees 
ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;

# Ancienne méthode (avant 12c) avec ROWNUM
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;


# === FONCTIONS D'AGRÉGATION ===

# POURQUOI les fonctions d'agrégation?
Résumer données - obtenir statistiques globales

# COUNT - Compter lignes
SELECT COUNT(*) FROM employees;                    -- Toutes les lignes
SELECT COUNT(commission_pct) FROM employees;       -- Seulement valeurs non-NULL
SELECT COUNT(DISTINCT department_id) FROM employees;  -- Valeurs uniques

# SUM - Somme
SELECT SUM(salary) FROM employees;
SELECT SUM(salary) AS total_payroll FROM employees;

# AVG - Moyenne
SELECT AVG(salary) FROM employees;
SELECT AVG(salary) AS average_salary FROM employees;

# ATTENTION: AVG ignore NULL
SELECT AVG(commission_pct) FROM employees;  -- Moyenne seulement des non-NULL

# MIN - Minimum
SELECT MIN(salary) FROM employees;
SELECT MIN(hire_date) FROM employees;  -- Date la plus ancienne

# MAX - Maximum
SELECT MAX(salary) FROM employees;
SELECT MAX(hire_date) FROM employees;  -- Date la plus récente

# Plusieurs agrégations
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;


# === CLAUSE GROUP BY (REGROUPEMENT) ===

# POURQUOI GROUP BY?
Calculer agrégations par groupe

# Syntaxe
SELECT colonne_groupe, fonction_agregation(colonne)
FROM table_name
GROUP BY colonne_groupe;

# Exemple: Nombre d'employés par département
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

# Plusieurs colonnes de regroupement
SELECT department_id, job_id, COUNT(*) AS count
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;

# Avec calculs
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

# RÈGLE IMPORTANTE:
# Toutes les colonnes dans SELECT (sauf agrégations) doivent être dans GROUP BY

# [X] ERREUR:
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id;  -- job_id manque dans GROUP BY

# [OK] CORRECT:
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;


# === CLAUSE HAVING (FILTRER GROUPES) ===

# DIFFÉRENCE WHERE vs HAVING:
# WHERE: Filtre AVANT regroupement (lignes individuelles)
# HAVING: Filtre APRÈS regroupement (groupes)

# Départements avec plus de 5 employés
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

# Départements avec salaire moyen > 50000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

# Combinaison WHERE et HAVING
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 10000              -- Filtre avant regroupement
GROUP BY department_id
HAVING AVG(salary) > 50000        -- Filtre après regroupement
ORDER BY avg_salary DESC;


# === ORDRE D'EXÉCUTION SQL ===

Comprendre l'ordre aide à éviter erreurs

1. FROM      - Identifier tables
2. WHERE     - Filtrer lignes
3. GROUP BY  - Regrouper
4. HAVING    - Filtrer groupes
5. SELECT    - Sélectionner colonnes
6. DISTINCT  - Éliminer doublons
7. ORDER BY  - Trier résultats
8. FETCH     - Limiter lignes

Exemple complet:
SELECT department_id, AVG(salary) AS avg_salary    -- 5
FROM employees                                      -- 1
WHERE salary > 5000                                 -- 2
GROUP BY department_id                              -- 3
HAVING AVG(salary) > 50000                         -- 4
ORDER BY avg_salary DESC                            -- 7
FETCH FIRST 5 ROWS ONLY;                           -- 8


# === FONCTIONS DE CHAÎNES ===

# UPPER - Convertir en majuscules
SELECT UPPER('hello') FROM dual;  -- HELLO
SELECT UPPER(first_name) FROM employees;

# LOWER - Convertir en minuscules
SELECT LOWER('HELLO') FROM dual;  -- hello

# INITCAP - Première lettre majuscule
SELECT INITCAP('hello world') FROM dual;  -- Hello World

# LENGTH - Longueur de chaîne
SELECT first_name, LENGTH(first_name) AS name_length FROM employees;

# SUBSTR - Extraire sous-chaîne
SUBSTR(string, start_position, length)

SELECT SUBSTR('Hello World', 1, 5) FROM dual;   -- Hello
SELECT SUBSTR('Hello World', 7) FROM dual;      -- World
SELECT SUBSTR('Hello World', -5) FROM dual;     -- World (depuis la fin)

# INSTR - Trouver position de sous-chaîne
SELECT INSTR('Hello World', 'World') FROM dual;  -- 7
SELECT INSTR('Hello World', 'o') FROM dual;      -- 5 (première occurrence)

# REPLACE - Remplacer texte
SELECT REPLACE('Hello World', 'World', 'Oracle') FROM dual;  -- Hello Oracle

# TRIM - Supprimer espaces
SELECT TRIM('  Hello  ') FROM dual;              -- 'Hello'
SELECT LTRIM('  Hello  ') FROM dual;             -- 'Hello  '
SELECT RTRIM('  Hello  ') FROM dual;             -- '  Hello'

# LPAD / RPAD - Padding
SELECT LPAD('42', 5, '0') FROM dual;             -- 00042
SELECT RPAD('42', 5, '0') FROM dual;             -- 42000

# Combinaisons pratiques
SELECT 
    UPPER(SUBSTR(first_name, 1, 1)) || LOWER(SUBSTR(first_name, 2)) AS proper_name
FROM employees;


# === FONCTIONS NUMÉRIQUES ===

# ROUND - Arrondir
SELECT ROUND(123.456, 2) FROM dual;     -- 123.46
SELECT ROUND(123.456, 0) FROM dual;     -- 123
SELECT ROUND(123.456, -1) FROM dual;    -- 120

# TRUNC - Tronquer
SELECT TRUNC(123.456, 2) FROM dual;     -- 123.45
SELECT TRUNC(123.456) FROM dual;        -- 123

# CEIL - Arrondir au supérieur
SELECT CEIL(123.01) FROM dual;          -- 124

# FLOOR - Arrondir à l'inférieur
SELECT FLOOR(123.99) FROM dual;         -- 123

# MOD - Modulo (reste division)
SELECT MOD(10, 3) FROM dual;            -- 1

# ABS - Valeur absolue
SELECT ABS(-15) FROM dual;              -- 15

# POWER - Puissance
SELECT POWER(2, 10) FROM dual;          -- 1024

# SQRT - Racine carrée
SELECT SQRT(16) FROM dual;              -- 4

# Exemple pratique: Calculer remise
SELECT 
    product_name,
    price,
    ROUND(price * 0.8, 2) AS discounted_price
FROM products;


# === FONCTIONS DE DATE ===

# SYSDATE - Date/heure actuelle
SELECT SYSDATE FROM dual;

# SYSTIMESTAMP - Timestamp actuel avec fuseau
SELECT SYSTIMESTAMP FROM dual;

# Arithmétique de dates
SELECT SYSDATE + 7 FROM dual;           -- Dans 7 jours
SELECT SYSDATE - 7 FROM dual;           -- Il y a 7 jours
SELECT SYSDATE + 1/24 FROM dual;        -- Dans 1 heure
SELECT SYSDATE + 1/1440 FROM dual;      -- Dans 1 minute

# Différence entre dates (en jours)
SELECT SYSDATE - hire_date AS days_employed FROM employees;

# MONTHS_BETWEEN - Différence en mois
SELECT 
    first_name,
    hire_date,
    MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed
FROM employees;

# ADD_MONTHS - Ajouter mois
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;  -- Dans 3 mois

# NEXT_DAY - Prochain jour spécifié
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;

# LAST_DAY - Dernier jour du mois
SELECT LAST_DAY(SYSDATE) FROM dual;

# EXTRACT - Extraire partie de date
SELECT 
    hire_date,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    EXTRACT(MONTH FROM hire_date) AS hire_month,
    EXTRACT(DAY FROM hire_date) AS hire_day
FROM employees;

# TO_CHAR - Formater date
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM dual;

# Formats courants:
# DD    = Jour (01-31)
# MM    = Mois numérique (01-12)
# MON   = Mois abrégé (JAN, FEB...)
# MONTH = Mois complet (JANUARY...)
# YY    = Année 2 chiffres
# YYYY  = Année 4 chiffres
# HH24  = Heure 24h (00-23)
# MI    = Minutes (00-59)
# SS    = Secondes (00-59)
# Day   = Jour de la semaine

# TO_DATE - Convertir chaîne en date
SELECT TO_DATE('25-12-2024', 'DD-MM-YYYY') FROM dual;
SELECT TO_DATE('2024-12-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

# TRUNC - Tronquer date
SELECT TRUNC(SYSDATE) FROM dual;                -- Minuit aujourd'hui
SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;       -- Premier du mois
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;        -- Premier janvier

# Exemple: Employés embauchés cette année
SELECT * FROM employees 
WHERE TRUNC(hire_date, 'YEAR') = TRUNC(SYSDATE, 'YEAR');


# === FONCTIONS DE CONVERSION ===

# TO_CHAR - Convertir en chaîne
SELECT TO_CHAR(12345.67, '999,999.99') FROM dual;  -- 12,345.67
SELECT TO_CHAR(12345.67, '$999,999.99') FROM dual; -- $12,345.67

# TO_NUMBER - Convertir en nombre
SELECT TO_NUMBER('12345.67') FROM dual;
SELECT TO_NUMBER('$12,345.67', '$999,999.99') FROM dual;

# TO_DATE - Déjà vu ci-dessus

# CAST - Conversion générique
SELECT CAST('123' AS NUMBER) FROM dual;
SELECT CAST(123 AS VARCHAR2(10)) FROM dual;
SELECT CAST(SYSDATE AS TIMESTAMP) FROM dual;


# === FONCTIONS CONDITIONNELLES ===

# CASE - Expression conditionnelle (SQL standard)
SELECT 
    first_name,
    salary,
    CASE 
        WHEN salary < 5000 THEN 'Low'
        WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
        WHEN salary > 10000 THEN 'High'
        ELSE 'Unknown'
    END AS salary_level
FROM employees;

# CASE avec égalité
SELECT 
    first_name,
    department_id,
    CASE department_id
        WHEN 10 THEN 'Administration'
        WHEN 20 THEN 'Marketing'
        WHEN 30 THEN 'Purchasing'
        ELSE 'Other'
    END AS department_name
FROM employees;

# DECODE - Fonction Oracle (équivalent CASE)
SELECT 
    first_name,
    department_id,
    DECODE(department_id,
        10, 'Administration',
        20, 'Marketing',
        30, 'Purchasing',
        'Other'
    ) AS department_name
FROM employees;

# NVL - Remplacer NULL (déjà vu)
SELECT first_name, NVL(commission_pct, 0) FROM employees;

# NVL2 - Valeur si NULL ou NOT NULL
SELECT 
    first_name,
    NVL2(commission_pct, commission_pct, 0) AS commission
FROM employees;

# NULLIF - Retourne NULL si deux valeurs égales
SELECT NULLIF(10, 10) FROM dual;  -- NULL
SELECT NULLIF(10, 20) FROM dual;  -- 10

# COALESCE - Première valeur non-NULL
SELECT COALESCE(NULL, NULL, 'Default') FROM dual;  -- Default
SELECT COALESCE(column1, column2, column3, 'N/A') FROM table_name;


# === JOINTURES (JOINS) ===

# POURQUOI LES JOINTURES?
Combiner données de plusieurs tables liées

# TYPES DE JOINTURES:
1. INNER JOIN - Seulement correspondances
2. LEFT OUTER JOIN - Toutes lignes table gauche
3. RIGHT OUTER JOIN - Toutes lignes table droite
4. FULL OUTER JOIN - Toutes lignes des deux tables
5. CROSS JOIN - Produit cartésien


# INNER JOIN (Jointure interne)
────────────────────────────────

# Syntaxe ANSI (recommandée)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

# Même chose sans le mot INNER (implicite)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

# Ancienne syntaxe Oracle (WHERE clause)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

# Jointure sur plusieurs colonnes
SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2;

# Exemple pratique: Employés avec leur département et manager
SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    d.department_name,
    m.first_name || ' ' || m.last_name AS manager
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employees m ON e.manager_id = m.employee_id;


# LEFT OUTER JOIN (Jointure externe gauche)
────────────────────────────────────────────

# Retourne TOUTES les lignes de la table gauche
# + correspondances de la table droite (NULL si pas de correspondance)

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

# Même chose
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

# Ancienne syntaxe Oracle avec (+)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

# Trouver employés SANS département
SELECT e.first_name, e.last_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;


# RIGHT OUTER JOIN (Jointure externe droite)
─────────────────────────────────────────────

# Retourne TOUTES les lignes de la table droite
# + correspondances de la table gauche

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

# Trouver départements SANS employés
SELECT d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;


# FULL OUTER JOIN (Jointure externe complète)
──────────────────────────────────────────────

# Retourne TOUTES les lignes des deux tables

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

# Ancienne syntaxe Oracle (simulation avec UNION)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;


# CROSS JOIN (Produit cartésien)
─────────────────────────────────

# Chaque ligne table1 × chaque ligne table2

SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

# Équivalent (ancienne syntaxe)
SELECT e.first_name, d.department_name
FROM employees e, departments d;

# ATTENTION: Résultat peut être ÉNORME!
# Si employees = 100 lignes et departments = 10 lignes
# -> Résultat = 100 × 10 = 1000 lignes


# SELF JOIN (Auto-jointure)
────────────────────────────

# Joindre table avec elle-même
# Utile pour hiérarchies (employé-manager)

SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;


# NATURAL JOIN (Jointure naturelle)
────────────────────────────────────

# Oracle joint automatiquement sur colonnes de même nom

SELECT first_name, last_name, department_name
FROM employees
NATURAL JOIN departments;

# ATTENTION: Peut être dangereux!
# Joint sur TOUTES colonnes de même nom
# Préférer JOIN ... ON explicite


# USING (Simplification)
─────────────────────────

# Quand colonnes ont même nom dans les deux tables

SELECT first_name, last_name, department_name
FROM employees e
JOIN departments d USING (department_id);

# Équivalent à:
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;


# === SOUS-REQUÊTES (SUBQUERIES) ===

# POURQUOI LES SOUS-REQUÊTES?
Requête imbriquée dans une autre requête
[OK] Filtrage complexe
[OK] Calculs intermédiaires
[OK] Comparaisons dynamiques


# SOUS-REQUÊTE SCALAIRE (retourne une seule valeur)
─────────────────────────────────────────────────────

# Employés avec salaire supérieur à la moyenne
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

# Avec alias pour clarté
SELECT first_name, last_name, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);


# SOUS-REQUÊTE AVEC IN
───────────────────────

# Employés dans départements localisés à 'Seattle'
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id IN (
        SELECT location_id 
        FROM locations 
        WHERE city = 'Seattle'
    )
);


# SOUS-REQUÊTE AVEC EXISTS
───────────────────────────

# EXISTS vérifie si sous-requête retourne au moins une ligne

# Départements ayant au moins un employé
SELECT department_name
FROM departments d
WHERE EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.department_id = d.department_id
);

# NOT EXISTS - Départements sans employés
SELECT department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.department_id = d.department_id
);

# PERFORMANCE: EXISTS vs IN
# EXISTS généralement plus rapide car s'arrête dès première correspondance


# SOUS-REQUÊTE AVEC ANY / ALL
──────────────────────────────

# ANY - Au moins une valeur satisfait condition
SELECT first_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);
-- Salaire > au moins un salaire du dept 10

# ALL - Toutes les valeurs satisfont condition
SELECT first_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);
-- Salaire > TOUS les salaires du dept 10


# SOUS-REQUÊTE DANS FROM (Table dérivée)
─────────────────────────────────────────

SELECT dept_stats.*
FROM (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_stats
WHERE dept_stats.avg_salary > 50000;


# SOUS-REQUÊTE CORRÉLÉE
────────────────────────

# Sous-requête référence table externe
# Exécutée pour chaque ligne de requête externe

# Employés gagnant plus que moyenne de leur département
SELECT e.first_name, e.last_name, e.salary, e.department_id
FROM employees eWHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);


# === OPÉRATEURS D'ENSEMBLE ===

# UNION - Combiner résultats (sans doublons)
──────────────────────────────────────────────

SELECT first_name, last_name FROM employees_us
UNION
SELECT first_name, last_name FROM employees_eu;

# UNION ALL - Combiner résultats (avec doublons)
───────────────────────────────────────────────────

SELECT first_name FROM employees_us
UNION ALL
SELECT first_name FROM employees_eu;

# PERFORMANCE: UNION ALL plus rapide car pas de dédoublonnage


# INTERSECT - Lignes communes
──────────────────────────────

SELECT first_name, last_name FROM employees_us
INTERSECT
SELECT first_name, last_name FROM employees_eu;


# MINUS - Différence ensembliste
─────────────────────────────────

# Lignes dans première requête mais pas dans deuxième
SELECT first_name, last_name FROM employees_us
MINUS
SELECT first_name, last_name FROM employees_eu;


# RÈGLES OPÉRATEURS D'ENSEMBLE:
1. Même nombre de colonnes
2. Types de données compatibles
3. ORDER BY seulement à la fin


# === REQUÊTES HIÉRARCHIQUES ===

# Oracle supporte requêtes récursives avec START WITH ... CONNECT BY

# Hiérarchie employé-manager
SELECT 
    LEVEL,
    employee_id,
    manager_id,
    LPAD(' ', 2 * (LEVEL - 1)) || first_name || ' ' || last_name AS employee
FROM employees
START WITH manager_id IS NULL  -- Racine (PDG)
CONNECT BY PRIOR employee_id = manager_id  -- Parent-enfant
ORDER SIBLINGS BY last_name;

# Explications:
# START WITH: Point de départ
# CONNECT BY PRIOR: Relation parent-enfant
# LEVEL: Profondeur dans hiérarchie (1 = racine)
# ORDER SIBLINGS BY: Trier frères/sœurs


# Chemin hiérarchique avec SYS_CONNECT_BY_PATH
SELECT 
    employee_id,
    SYS_CONNECT_BY_PATH(last_name, ' -> ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;


# === FONCTIONS ANALYTIQUES (WINDOW FUNCTIONS) ===

# POURQUOI fonctions analytiques?
Calculs sur "fenêtres" de données sans GROUP BY
[OK] Numérotation lignes
[OK] Classements
[OK] Totaux cumulés
[OK] Moyennes mobiles

# Syntaxe générale:
fonction() OVER (
    PARTITION BY colonne
    ORDER BY colonne
    ROWS/RANGE specification
)


# ROW_NUMBER - Numéroter lignes
────────────────────────────────

SELECT 
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

# Par partition (numérotation recommence dans chaque groupe)
SELECT 
    department_id,
    first_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;


# RANK - Classement (gaps pour ex-aequo)
──────────────────────────────────────────

SELECT 
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

# Résultat exemple:
# Name    Salary  Rank
# John    100000  1
# Jane    100000  1
# Bob     90000   3  <- Gap (pas 2)


# DENSE_RANK - Classement (sans gaps)
──────────────────────────────────────

SELECT 
    first_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

# Résultat exemple:
# Name    Salary  Rank
# John    100000  1
# Jane    100000  1
# Bob     90000   2  <- Pas de gap


# NTILE - Diviser en buckets
─────────────────────────────

# Diviser en quartiles
SELECT 
    first_name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;


# LAG - Valeur ligne précédente
────────────────────────────────

SELECT 
    first_name,
    salary,
    LAG(salary, 1) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;

# Avec valeur par défaut si pas de ligne précédente
SELECT 
    first_name,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;


# LEAD - Valeur ligne suivante
───────────────────────────────

SELECT 
    first_name,
    salary,
    LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;


# FIRST_VALUE / LAST_VALUE
───────────────────────────

SELECT 
    first_name,
    salary,
    FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS highest_salary,
    LAST_VALUE(salary) OVER (ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;


# Fonctions agrégation comme fonctions analytiques
───────────────────────────────────────────────────

# Total cumulé
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

# Moyenne mobile (3 dernières lignes)
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;


# === EXEMPLES PRATIQUES COMPLETS ===

# EXEMPLE 1: Rapport de ventes complet
─────────────────────────────────────────

SELECT 
    o.order_id,
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total,
    SUM(oi.quantity * oi.unit_price) OVER (PARTITION BY o.order_id) AS order_total,
    TO_CHAR(o.order_date, 'YYYY-MM') AS order_month,
    SUM(oi.quantity * oi.unit_price) OVER (
        PARTITION BY TO_CHAR(o.order_date, 'YYYY-MM')
    ) AS monthly_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -3)
ORDER BY o.order_date DESC, o.order_id, oi.order_item_id;


# EXEMPLE 2: Top 10 produits par catégorie
────────────────────────────────────────────

SELECT *
FROM (
    SELECT 
        p.category,
        p.product_name,
        SUM(oi.quantity) AS total_sold,
        RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) AS category_rank
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.category, p.product_name
)
WHERE category_rank <= 10;


# EXEMPLE 3: Analyse cohorte
──────────────────────────────

SELECT 
    TO_CHAR(c.created_at, 'YYYY-MM') AS cohort_month,
    COUNT(DISTINCT c.customer_id) AS total_customers,
    COUNT(DISTINCT CASE WHEN o.order_date IS NOT NULL THEN c.customer_id END) AS active_customers,
    ROUND(
        COUNT(DISTINCT CASE WHEN o.order_date IS NOT NULL THEN c.customer_id END) * 100.0 / 
        COUNT(DISTINCT c.customer_id),
        2
    ) AS retention_rate
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id 
    AND o.order_date BETWEEN c.created_at AND ADD_MONTHS(c.created_at, 1)
GROUP BY TO_CHAR(c.created_at, 'YYYY-MM')
ORDER BY cohort_month;


# EXEMPLE 4: Employés vs moyenne département
──────────────────────────────────────────────

SELECT 
    e.first_name,
    e.last_name,
    e.department_id,
    e.salary,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS dept_avg_salary,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg,
    CASE 
        WHEN e.salary > AVG(e.salary) OVER (PARTITION BY e.department_id) THEN 'Above Average'
        WHEN e.salary < AVG(e.salary) OVER (PARTITION BY e.department_id) THEN 'Below Average'
        ELSE 'Average'
    END AS salary_level
FROM employees e
ORDER BY e.department_id, e.salary DESC;


[OK] MANIPULATION DE DONNÉES (DML)

# === INSERT (INSÉRER DONNÉES) ===

# INSERT avec valeurs explicites
───────────────────────────────────

# Syntaxe basique
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

# Exemple
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (207, 'John', 'Doe', 'jdoe@company.com', SYSDATE, 'IT_PROG');

# Sans spécifier colonnes (TOUTES les colonnes, dans l'ordre)
INSERT INTO employees
VALUES (208, 'Jane', 'Smith', 'jsmith@company.com', '555-1234', 
        SYSDATE, 'SA_REP', 8000, 0.15, 145, 80);

# ATTENTION: Fragile! Si structure table change, requête casse


# INSERT plusieurs lignes
─────────────────────────

# Oracle 23c+ (INSERT ALL simplifié)
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES 
    (209, 'Alice', 'Brown', 'abrown@company.com', SYSDATE),
    (210, 'Bob', 'Wilson', 'bwilson@company.com', SYSDATE),
    (211, 'Carol', 'Davis', 'cdavis@company.com', SYSDATE);

# Versions antérieures - INSERT ALL
INSERT ALL
    INTO employees VALUES (209, 'Alice', 'Brown', ...)
    INTO employees VALUES (210, 'Bob', 'Wilson', ...)
    INTO employees VALUES (211, 'Carol', 'Davis', ...)
SELECT * FROM dual;


# INSERT depuis SELECT
───────────────────────

# Copier données depuis autre table
INSERT INTO employees_backup
SELECT * FROM employees
WHERE department_id = 10;

# Avec transformation
INSERT INTO employee_salaries (employee_id, annual_salary)
SELECT employee_id, salary * 12
FROM employees;


# INSERT avec valeurs par défaut
─────────────────────────────────

# Utiliser DEFAULT explicite
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, status)
VALUES (212, 'David', 'Miller', 'dmiller@company.com', DEFAULT, DEFAULT);

# Omettre colonnes (valeurs par défaut appliquées)
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (213, 'Emma', 'Garcia', 'egarcia@company.com');


# INSERT avec séquence
───────────────────────

# Méthode traditionnelle
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (employee_id_seq.NEXTVAL, 'Frank', 'Martinez', 'fmartinez@company.com');

# Oracle 12c+ - Colonne identité (auto-increment)
CREATE TABLE employees_new (
    employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50)
);

INSERT INTO employees_new (first_name) VALUES ('Grace');
-- employee_id généré automatiquement


# === UPDATE (MODIFIER DONNÉES) ===

# UPDATE simple
────────────────

# Syntaxe
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

# Exemple
UPDATE employees
SET salary = 85000
WHERE employee_id = 207;

# Modifier plusieurs colonnes
UPDATE employees
SET salary = salary * 1.10,
    commission_pct = 0.20
WHERE department_id = 80;


# UPDATE avec sous-requête
───────────────────────────

# Mettre salaire à la moyenne du département
UPDATE employees e
SET salary = (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
)
WHERE employee_id = 207;


# UPDATE avec CASE
──────────────────

UPDATE employees
SET salary = CASE
    WHEN salary < 5000 THEN salary * 1.20
    WHEN salary BETWEEN 5000 AND 10000 THEN salary * 1.10
    ELSE salary * 1.05
END
WHERE department_id = 50;


# UPDATE JOIN (mise à jour avec jointure)
──────────────────────────────────────────

# Oracle nécessite sous-requête corrélée
UPDATE employees e
SET salary = (
    SELECT d.avg_salary
    FROM (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    ) d
    WHERE d.department_id = e.department_id
)
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);


# UPDATE TOUS (ATTENTION!)
───────────────────────────

# Sans WHERE = modifier TOUTES les lignes
UPDATE employees
SET status = 'ACTIVE';

# TOUJOURS vérifier avec SELECT avant!
SELECT COUNT(*) FROM employees WHERE status != 'ACTIVE';


# === DELETE (SUPPRIMER DONNÉES) ===

# DELETE simple
────────────────

# Syntaxe
DELETE FROM table_name
WHERE condition;

# Exemple
DELETE FROM employees
WHERE employee_id = 207;


# DELETE avec sous-requête
───────────────────────────

# Supprimer employés de départements inactifs
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE status = 'INACTIVE'
);


# DELETE avec JOIN (via sous-requête corrélée)
────────────────────────────────────────────────

DELETE FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.status = 'CLOSED'
);


# DELETE TOUS (DANGER!)
────────────────────────

# Sans WHERE = supprimer TOUTES les lignes
DELETE FROM employees;

# TRUNCATE plus rapide pour supprimer tout
TRUNCATE TABLE employees;


# === MERGE (UPSERT - INSERT OU UPDATE) ===

# POURQUOI MERGE?
Insérer si n'existe pas, mettre à jour si existe
[OK] Synchronisation de données
[OK] Import de fichiers
[OK] Réplication

# Syntaxe
MERGE INTO target_table t
USING source_table s
ON (condition_correspondance)
WHEN MATCHED THEN
    UPDATE SET t.column = s.column
WHEN NOT MATCHED THEN
    INSERT (columns) VALUES (values);


# Exemple: Synchroniser employés depuis table temporaire
MERGE INTO employees e
USING temp_employees t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET
        e.first_name = t.first_name,
        e.last_name = t.last_name,
        e.salary = t.salary,
        e.updated_at = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary, created_at)
    VALUES (t.employee_id, t.first_name, t.last_name, t.salary, SYSDATE);


# MERGE avec condition DELETE
──────────────────────────────

MERGE INTO employees e
USING temp_employees t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = t.salary
    DELETE WHERE e.status = 'INACTIVE'  -- Supprimer après mise à jour
WHEN NOT MATCHED THEN
    INSERT VALUES (t.employee_id, t.first_name, t.last_name, t.salary);


# === TRANSACTIONS ===

# POURQUOI les transactions?
Garantir intégrité des données (ACID)
[OK] Atomicité - Tout ou rien
[OK] Cohérence - État valide
[OK] Isolation - Transactions indépendantes
[OK] Durabilité - Changements permanents


# COMMIT - Valider transaction
────────────────────────────────

# Rendre changements permanents
INSERT INTO employees VALUES (...);
UPDATE employees SET salary = 90000 WHERE employee_id = 207;
DELETE FROM employees WHERE employee_id = 208;
COMMIT;

# AUTO-COMMIT dans certains outils (SQL Developer, etc.)
# En SQL*Plus, COMMIT manuel obligatoire


# ROLLBACK - Annuler transaction
─────────────────────────────────

# Annuler tous changements depuis dernier COMMIT
UPDATE employees SET salary = 0;  -- Erreur!
ROLLBACK;  -- Annule l'UPDATE

# État restauré à avant l'UPDATE


# SAVEPOINT - Point de sauvegarde
──────────────────────────────────

INSERT INTO employees VALUES (...);
SAVEPOINT after_insert;

UPDATE employees SET salary = 90000 WHERE employee_id = 207;
SAVEPOINT after_update;

DELETE FROM employees WHERE department_id = 10;
-- Oups, erreur!

ROLLBACK TO after_update;  -- Annule seulement DELETE
COMMIT;  -- Valide INSERT et UPDATE


# Transaction explicite
────────────────────────

BEGIN
    INSERT INTO orders (order_id, customer_id, total) 
    VALUES (1001, 50, 1500.00);
    
    INSERT INTO order_items (order_id, product_id, quantity)
    VALUES (1001, 100, 5);
    
    UPDATE products SET stock = stock - 5 WHERE product_id = 100;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/


# Niveaux d'isolation
──────────────────────

# Oracle supporte:
# - READ COMMITTED (défaut)
# - SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transactions...
COMMIT;

# READ COMMITTED:
[OK] Lecture données validées seulement
[OK] Dirty reads impossibles
[X] Non-repeatable reads possibles

# SERIALIZABLE:
[OK] Transactions complètement isolées
[OK] Résultats reproductibles
[X] Performance réduite


# Verrouillage explicite
─────────────────────────

# SELECT FOR UPDATE - Verrouiller lignes pour mise à jour
SELECT * FROM employees
WHERE employee_id = 207
FOR UPDATE;

# Autres sessions ne peuvent pas modifier jusqu'à COMMIT/ROLLBACK

# NOWAIT - Ne pas attendre si verrouillé
SELECT * FROM employees
WHERE employee_id = 207
FOR UPDATE NOWAIT;

# WAIT n - Attendre n secondes
SELECT * FROM employees
WHERE employee_id = 207
FOR UPDATE WAIT 10;


# === GESTION DES ERREURS ===

# Vérifier nombre de lignes affectées
───────────────────────────────────────

# SQL%ROWCOUNT - Nombre lignes affectées par dernier DML
BEGIN
    UPDATE employees SET salary = salary * 1.10
    WHERE department_id = 50;
    
    DBMS_OUTPUT.PUT_LINE('Lignes mises à jour: ' || SQL%ROWCOUNT);
    
    IF SQL%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Aucun employé dans département 50');
    END IF;
    
    COMMIT;
END;
/


# RETURNING clause - Récupérer valeurs modifiées
──────────────────────────────────────────────────

DECLARE
    v_old_salary NUMBER;
    v_new_salary NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * 1.10
    WHERE employee_id = 207
    RETURNING salary INTO v_new_salary;
    
    DBMS_OUTPUT.PUT_LINE('Nouveau salaire: ' || v_new_salary);
END;
/


# === BONNES PRATIQUES DML ===

1. TOUJOURS utiliser WHERE avec UPDATE/DELETE
   [OK] UPDATE employees SET salary = 90000 WHERE employee_id = 207;
   [X] UPDATE employees SET salary = 90000;  -- DANGER!

2. Tester avec SELECT avant UPDATE/DELETE
   SELECT * FROM employees WHERE employee_id = 207;
   -- Vérifier résultat
   UPDATE employees SET salary = 90000 WHERE employee_id = 207;

3. Utiliser transactions pour opérations liées
   BEGIN
       INSERT INTO orders ...;
       INSERT INTO order_items ...;
       UPDATE inventory ...;
       COMMIT;
   END;

4. Ne pas oublier COMMIT
   En SQL*Plus, changements pas validés automatiquement

5. ROLLBACK en cas d'erreur
   Toujours gérer exceptions avec ROLLBACK

6. Utiliser MERGE pour synchronisation
   Plus efficace et atomique que UPDATE + INSERT séparés

7. Index pour performance UPDATE/DELETE
   WHERE clause utilise index = plus rapide

8. Batch processing pour gros volumes
   Découper en lots de 1000-10000 lignes


# === EXEMPLES PRATIQUES COMPLETS ===

# EXEMPLE 1: Import de données avec validation
──────────────────────────────────────────────────

BEGIN
    -- Créer table temporaire
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_import (
        employee_id NUMBER,
        first_name VARCHAR2(50),
        last_name VARCHAR2(50),
        email VARCHAR2(100),
        salary NUMBER
    ) ON COMMIT DELETE ROWS';
    
    -- Charger données (exemple fictif)
    INSERT INTO temp_import VALUES (300, 'Test', 'User', 'test@company.com', 50000);
    
    -- Valider données
    FOR rec IN (SELECT * FROM temp_import) LOOP
        IF rec.salary < 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salaire négatif pour ' || rec.email);
        END IF;
    END LOOP;
    
    -- Merge dans table principale
    MERGE INTO employees e
    USING temp_import t
    ON (e.employee_id = t.employee_id)
    WHEN MATCHED THEN
        UPDATE SET
            e.first_name = t.first_name,
            e.last_name = t.last_name,
            e.email = t.email,
            e.salary = t.salary
    WHEN NOT MATCHED THEN
        INSERT (employee_id, first_name, last_name, email, salary, hire_date)
        VALUES (t.employee_id, t.first_name, t.last_name, t.email, t.salary, SYSDATE);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Import réussi: ' || SQL%ROWCOUNT || ' lignes');
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLERRM);
        RAISE;
END;
/


# EXEMPLE 2: Traitement batch avec log
────────────────────────────────────────

DECLARE
    v_batch_size CONSTANT NUMBER := 1000;
    v_total_processed NUMBER := 0;
BEGIN
    LOOP
        UPDATE employees
        SET status = 'PROCESSED'
        WHERE status = 'PENDING'
        AND ROWNUM <= v_batch_size;
        
        v_total_processed := v_total_processed + SQL%ROWCOUNT;
        
        EXIT WHEN SQL%ROWCOUNT = 0;
        
        COMMIT;  -- Commit chaque batch
        
        -- Log progression
        INSERT INTO processing_log (timestamp, rows_processed)
        VALUES (SYSDATE, SQL%ROWCOUNT);
        COMMIT;
        
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total traité: ' || v_total_processed);
END;
/


# EXEMPLE 3: Cascade manual avec gestion erreurs
──────────────────────────────────────────────────

DECLARE
    v_dept_id NUMBER := 10;
BEGIN
    -- Vérifier si département a des employés
    FOR rec IN (SELECT employee_id FROM employees WHERE department_id = v_dept_id) LOOP
        -- Supprimer dépendances
        DELETE FROM job_history WHERE employee_id = rec.employee_id;
        DELETE FROM leave_requests WHERE employee_id = rec.employee_id;
        DELETE FROM employees WHERE employee_id = rec.employee_id;
    END LOOP;
    
    -- Supprimer département
    DELETE FROM departments WHERE department_id = v_dept_id;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Département ' || v_dept_id || ' supprimé avec succès');
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Erreur lors suppression: ' || SQLERRM);
        RAISE;
END;
/
```

```
[OK] PL/SQL (PROCEDURAL LANGUAGE/SQL)

# === QU'EST-CE QUE PL/SQL ? ===

PL/SQL = Extension procédurale de SQL par Oracle
[OK] Ajoute structures de programmation (IF, LOOP, variables, etc.)
[OK] Combine SQL et logique procédurale
[OK] Exécuté côté serveur pour performance
[OK] Réutilisable via procédures stockées et fonctions

# POURQUOI PL/SQL ?
[OK] Performance - Réduction trafic réseau (plusieurs requêtes en un appel)
[OK] Réutilisabilité - Code stocké dans base, utilisable par plusieurs apps
[OK] Sécurité - Encapsulation logique métier
[OK] Intégrité - Validation données côté serveur
[OK] Portabilité - Indépendant du langage client


# === STRUCTURE DE BASE D'UN BLOC PL/SQL ===

# Syntaxe complète
DECLARE
    -- Section déclarative (optionnelle)
    -- Variables, constantes, curseurs, exceptions
BEGIN
    -- Section exécutable (obligatoire)
    -- Instructions SQL et PL/SQL
EXCEPTION
    -- Section exceptions (optionnelle)
    -- Gestion des erreurs
END;
/

# ATTENTION: Le "/" sur ligne séparée exécute le bloc dans SQL*Plus


# Bloc anonyme simple (sans DECLARE)
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');
END;
/

# Activer sortie dans SQL*Plus
SET SERVEROUTPUT ON;


# === VARIABLES ET TYPES DE DONNÉES ===

# Déclaration de variables
DECLARE
    -- Type simple
    v_employee_id NUMBER;
    v_first_name VARCHAR2(50);
    v_salary NUMBER(8,2);
    v_hire_date DATE;
    v_is_active BOOLEAN;
    
    -- Avec valeur initiale
    v_tax_rate CONSTANT NUMBER := 0.20;
    v_count NUMBER := 0;
    v_message VARCHAR2(100) := 'Bienvenue';
    
    -- Type %TYPE (copie type d'une colonne)
    v_emp_salary employees.salary%TYPE;
    
    -- Type %ROWTYPE (copie structure ligne entière)
    v_employee employees%ROWTYPE;
    
BEGIN
    -- Affectation
    v_employee_id := 100;
    v_first_name := 'John';
    v_salary := 75000.50;
    v_hire_date := SYSDATE;
    v_is_active := TRUE;
    
    -- Affichage
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Nom: ' || v_first_name);
    DBMS_OUTPUT.PUT_LINE('Salaire: ' || v_salary);
END;
/


# %TYPE - Lier type à colonne table
───────────────────────────────────

DECLARE
    v_emp_salary employees.salary%TYPE;
    v_emp_name employees.first_name%TYPE;
BEGIN
    SELECT salary, first_name 
    INTO v_emp_salary, v_emp_name
    FROM employees 
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' gagne ' || v_emp_salary);
END;
/

# POURQUOI %TYPE ?
[OK] Si type colonne change, code reste compatible
[OK] Pas besoin de connaître type exact


# %ROWTYPE - Représenter ligne complète
────────────────────────────────────────

DECLARE
    v_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO v_employee
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('Nom: ' || v_employee.first_name);
    DBMS_OUTPUT.PUT_LINE('Nom famille: ' || v_employee.last_name);
    DBMS_OUTPUT.PUT_LINE('Salaire: ' || v_employee.salary);
END;
/


# RECORD personnalisé
─────────────────────

DECLARE
    TYPE employee_rec IS RECORD (
        emp_id NUMBER,
        full_name VARCHAR2(100),
        annual_salary NUMBER
    );
    
    v_emp employee_rec;
BEGIN
    SELECT employee_id, first_name || ' ' || last_name, salary * 12
    INTO v_emp.emp_id, v_emp.full_name, v_emp.annual_salary
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp.full_name || ': ' || v_emp.annual_salary);
END;
/


# === STRUCTURES DE CONTRÔLE ===

# IF-THEN-ELSE
──────────────

DECLARE
    v_salary NUMBER;
    v_bonus NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 100;
    
    IF v_salary < 5000 THEN
        v_bonus := v_salary * 0.20;
    ELSIF v_salary BETWEEN 5000 AND 10000 THEN
        v_bonus := v_salary * 0.15;
    ELSE
        v_bonus := v_salary * 0.10;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/


# CASE - Expression
───────────────────

DECLARE
    v_grade CHAR(1) := 'B';
    v_result VARCHAR2(20);
BEGIN
    v_result := CASE v_grade
        WHEN 'A' THEN 'Excellent'
        WHEN 'B' THEN 'Très bien'
        WHEN 'C' THEN 'Bien'
        WHEN 'D' THEN 'Passable'
        ELSE 'Échec'
    END;
    
    DBMS_OUTPUT.PUT_LINE('Résultat: ' || v_result);
END;
/


# CASE - Instruction
────────────────────

DECLARE
    v_dept_id NUMBER := 10;
BEGIN
    CASE v_dept_id
        WHEN 10 THEN
            DBMS_OUTPUT.PUT_LINE('Administration');
        WHEN 20 THEN
            DBMS_OUTPUT.PUT_LINE('Marketing');
        WHEN 30 THEN
            DBMS_OUTPUT.PUT_LINE('Purchasing');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Autre département');
    END CASE;
END;
/


# === BOUCLES ===

# LOOP basique (boucle infinie avec EXIT)
──────────────────────────────────────────

DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Itération: ' || v_counter);
        v_counter := v_counter + 1;
        
        EXIT WHEN v_counter > 5;  -- Sortie condition
    END LOOP;
END;
/


# WHILE LOOP
────────────

DECLARE
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Itération: ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;
/


# FOR LOOP (numérique)
───────────────────────

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Itération: ' || i);
    END LOOP;
END;
/

# FOR LOOP inversé
BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Itération: ' || i);
    END LOOP;
END;
/


# FOR LOOP sur curseur
───────────────────────

BEGIN
    FOR emp_rec IN (SELECT first_name, salary FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/


# CONTINUE (Oracle 11g+)
─────────────────────────

BEGIN
    FOR i IN 1..10 LOOP
        IF MOD(i, 2) = 0 THEN
            CONTINUE;  -- Passer nombres pairs
        END IF;
        DBMS_OUTPUT.PUT_LINE('Nombre impair: ' || i);
    END LOOP;
END;
/


# === CURSEURS ===

# POURQUOI les curseurs ?
Traiter résultats SELECT ligne par ligne
[OK] Contrôle fin sur traitement
[OK] Opérations complexes par ligne
[OK] Boucles sur résultats requêtes


# Curseur implicite (SELECT INTO)
──────────────────────────────────

DECLARE
    v_first_name VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    SELECT first_name, salary
    INTO v_first_name, v_salary
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_first_name || ': ' || v_salary);
END;
/

# ATTENTION: SELECT INTO échoue si:
# - Aucune ligne trouvée (NO_DATA_FOUND)
# - Plus d'une ligne (TOO_MANY_ROWS)


# Curseur explicite
────────────────────

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;
    
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_first_name || ' - ' || v_salary);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/


# Curseur avec %ROWTYPE
────────────────────────

DECLARE
    CURSOR emp_cursor IS
        SELECT * FROM employees WHERE department_id = 10;
    
    v_emp emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/


# Curseur avec FOR LOOP (plus simple)
──────────────────────────────────────

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
    -- OPEN, FETCH, CLOSE automatiques!
END;
/


# Curseur avec paramètres
──────────────────────────

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER, p_min_salary NUMBER) IS
        SELECT first_name, salary
        FROM employees
        WHERE department_id = p_dept_id
        AND salary > p_min_salary;
BEGIN
    FOR emp_rec IN emp_cursor(10, 5000) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/


# Attributs de curseur
───────────────────────

DECLARE
    CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
    -- %ISOPEN - Curseur ouvert ?
    IF NOT emp_cursor%ISOPEN THEN
        OPEN emp_cursor;
    END IF;
    
    -- %FOUND - Dernière FETCH a retourné ligne ?
    -- %NOTFOUND - Dernière FETCH n'a pas retourné ligne ?
    -- %ROWCOUNT - Nombre de lignes fetchées
    
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('Ligne ' || emp_cursor%ROWCOUNT || ': ' || emp_rec.first_name);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/


# Curseur FOR UPDATE (verrouillage)
────────────────────────────────────

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
        FOR UPDATE OF salary NOWAIT;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        UPDATE employees
        SET salary = salary * 1.10
        WHERE CURRENT OF emp_cursor;  -- Ligne actuelle du curseur
    END LOOP;
    
    COMMIT;
END;
/


# === GESTION DES EXCEPTIONS ===

# Exceptions prédéfinies Oracle
────────────────────────────────

DECLARE
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT first_name INTO v_emp_name
    FROM employees
    WHERE employee_id = 9999;  -- N'existe pas
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employé non trouvé');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Plusieurs employés trouvés');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLERRM);
END;
/


# Exceptions courantes
──────────────────────

# NO_DATA_FOUND - SELECT INTO sans résultat
# TOO_MANY_ROWS - SELECT INTO plusieurs résultats
# ZERO_DIVIDE - Division par zéro
# VALUE_ERROR - Erreur conversion/affectation
# DUP_VAL_ON_INDEX - Violation contrainte UNIQUE
# INVALID_NUMBER - Conversion chaîne->nombre invalide
# INVALID_CURSOR - Opération curseur invalide


# Exception personnalisée
─────────────────────────

DECLARE
    e_salary_too_low EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_salary_too_low, -20001);
    
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 100;
    
    IF v_salary < 5000 THEN
        RAISE e_salary_too_low;
    END IF;
    
EXCEPTION
    WHEN e_salary_too_low THEN
        DBMS_OUTPUT.PUT_LINE('Salaire trop bas!');
        RAISE;  -- Re-lever exception
END;
/


# RAISE_APPLICATION_ERROR
─────────────────────────

BEGIN
    DECLARE
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count
        FROM employees
        WHERE department_id = 99;
        
        IF v_count = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Département 99 sans employés');
        END IF;
    END;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
END;
/


# === PROCÉDURES STOCKÉES ===

# POURQUOI procédures stockées ?
[OK] Réutilisabilité - Code partagé entre applications
[OK] Performance - Compilé et optimisé
[OK] Sécurité - Contrôle accès granulaire
[OK] Maintenance - Modification centralisée


# Créer procédure simple
─────────────────────────

CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Bonjour, ' || p_name || '!');
END greet_user;
/

# Exécuter procédure
EXECUTE greet_user('Alice');
-- Ou
BEGIN
    greet_user('Bob');
END;
/


# Procédure avec paramètres IN, OUT, IN OUT
────────────────────────────────────────────

CREATE OR REPLACE PROCEDURE calculate_bonus (
    p_employee_id IN NUMBER,
    p_bonus OUT NUMBER,
    p_message OUT VARCHAR2
) IS
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    p_bonus := v_salary * 0.10;
    p_message := 'Bonus calculé avec succès';
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_bonus := 0;
        p_message := 'Employé non trouvé';
END calculate_bonus;
/

# Appeler procédure avec OUT
DECLARE
    v_bonus NUMBER;
    v_msg VARCHAR2(100);
BEGIN
    calculate_bonus(100, v_bonus, v_msg);
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
    DBMS_OUTPUT.PUT_LINE('Message: ' || v_msg);
END;
/


# Paramètre IN OUT (entrée ET sortie)
──────────────────────────────────────

CREATE OR REPLACE PROCEDURE double_value (
    p_value IN OUT NUMBER
) IS
BEGIN
    p_value := p_value * 2;
END double_value;
/

DECLARE
    v_num NUMBER := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Avant: ' || v_num);
    double_value(v_num);
    DBMS_OUTPUT.PUT_LINE('Après: ' || v_num);
END;
/


# Procédure complexe - Augmentation salaires
─────────────────────────────────────────────

CREATE OR REPLACE PROCEDURE raise_salaries (
    p_department_id IN NUMBER,
    p_percentage IN NUMBER,
    p_rows_updated OUT NUMBER
) IS
    v_max_salary NUMBER := 100000;
BEGIN
    UPDATE employees
    SET salary = CASE
        WHEN salary * (1 + p_percentage/100) > v_max_salary THEN v_max_salary
        ELSE salary * (1 + p_percentage/100)
    END
    WHERE department_id = p_department_id;
    
    p_rows_updated := SQL%ROWCOUNT;
    
    COMMIT;
    
    -- Log
    INSERT INTO salary_changes_log (department_id, change_date, rows_affected)
    VALUES (p_department_id, SYSDATE, p_rows_updated);
    
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END raise_salaries;
/


# === FONCTIONS ===

# DIFFÉRENCE Procédure vs Fonction:
# - Fonction RETOURNE une valeur (RETURN)
# - Fonction peut être utilisée dans SELECT
# - Procédure peut avoir plusieurs OUT


# Créer fonction simple
────────────────────────

CREATE OR REPLACE FUNCTION get_employee_name (
    p_employee_id IN NUMBER
) RETURN VARCHAR2 IS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM employees
    WHERE employee_id = p_employee_id;
    
    RETURN v_name;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Employé non trouvé';
END get_employee_name;
/

# Utiliser fonction
SELECT get_employee_name(100) FROM dual;

-- Dans PL/SQL
DECLARE
    v_name VARCHAR2(100);
BEGIN
    v_name := get_employee_name(100);
    DBMS_OUTPUT.PUT_LINE(v_name);
END;
/


# Fonction avec calcul
───────────────────────

CREATE OR REPLACE FUNCTION calculate_annual_salary (
    p_employee_id IN NUMBER
) RETURN NUMBER IS
    v_monthly_salary NUMBER;
    v_commission NUMBER;
    v_annual NUMBER;
BEGIN
    SELECT salary, NVL(commission_pct, 0)
    INTO v_monthly_salary, v_commission
    FROM employees
    WHERE employee_id = p_employee_id;
    
    v_annual := v_monthly_salary * 12 * (1 + v_commission);
    
    RETURN ROUND(v_annual, 2);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END calculate_annual_salary;
/

# Utiliser dans SELECT
SELECT 
    employee_id,
    first_name,
    salary,
    calculate_annual_salary(employee_id) AS annual_salary
FROM employees;


# Fonction déterministe (optimisation)
───────────────────────────────────────

CREATE OR REPLACE FUNCTION get_tax_rate
RETURN NUMBER
DETERMINISTIC  -- Toujours même résultat pour mêmes entrées
IS
BEGIN
    RETURN 0.20;
END get_tax_rate;
/


# === PACKAGES ===

# POURQUOI packages ?
[OK] Organisation - Grouper fonctions/procédures liées
[OK] Encapsulation - Séparer interface (spec) et implémentation (body)
[OK] Performance - Chargé une fois en mémoire
[OK] Surcharge - Plusieurs versions même nom


# Package specification (interface publique)
──────────────────────────────────────────────

CREATE OR REPLACE PACKAGE employee_pkg IS
    -- Constantes publiques
    c_max_salary CONSTANT NUMBER := 100000;
    
    -- Procédures publiques
    PROCEDURE hire_employee (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_job_id IN VARCHAR2,
        p_employee_id OUT NUMBER
    );
    
    PROCEDURE fire_employee (
        p_employee_id IN NUMBER
    );
    
    -- Fonctions publiques
    FUNCTION get_employee_count (
        p_department_id IN NUMBER DEFAULT NULL
    ) RETURN NUMBER;
    
    FUNCTION get_department_payroll (
        p_department_id IN NUMBER
    ) RETURN NUMBER;
    
END employee_pkg;
/


# Package body (implémentation)
────────────────────────────────

CREATE OR REPLACE PACKAGE BODY employee_pkg IS
    
    -- Fonction privée (non dans spec)
    FUNCTION validate_email (p_email VARCHAR2) RETURN BOOLEAN IS
    BEGIN
        RETURN REGEXP_LIKE(p_email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');
    END validate_email;
    
    -- Implémentation procédure hire_employee
    PROCEDURE hire_employee (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_job_id IN VARCHAR2,
        p_employee_id OUT NUMBER
    ) IS
    BEGIN
        -- Validation
        IF NOT validate_email(p_email) THEN
            RAISE_APPLICATION_ERROR(-20001, 'Email invalide');
        END IF;
        
        -- Générer ID
        SELECT employee_id_seq.NEXTVAL INTO p_employee_id FROM dual;
        
        -- Insérer
        INSERT INTO employees (
            employee_id, first_name, last_name, email, hire_date, job_id
        ) VALUES (
            p_employee_id, p_first_name, p_last_name, p_email, SYSDATE, p_job_id
        );
        
        COMMIT;
    END hire_employee;
    
    -- Implémentation procédure fire_employee
    PROCEDURE fire_employee (
        p_employee_id IN NUMBER
    ) IS
    BEGIN
        -- Archiver
        INSERT INTO employees_archive
        SELECT * FROM employees WHERE employee_id = p_employee_id;
        
        -- Supprimer
        DELETE FROM employees WHERE employee_id = p_employee_id;
        
        COMMIT;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END fire_employee;
    
    -- Implémentation fonction get_employee_count
    FUNCTION get_employee_count (
        p_department_id IN NUMBER DEFAULT NULL
    ) RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        IF p_department_id IS NULL THEN
            SELECT COUNT(*) INTO v_count FROM employees;
        ELSE
            SELECT COUNT(*) INTO v_count 
            FROM employees 
            WHERE department_id = p_department_id;
        END IF;
        
        RETURN v_count;
    END get_employee_count;
    
    -- Implémentation fonction get_department_payroll
    FUNCTION get_department_payroll (
        p_department_id IN NUMBER
    ) RETURN NUMBER IS
        v_total NUMBER;
    BEGIN
        SELECT NVL(SUM(salary), 0)
        INTO v_total
        FROM employees
        WHERE department_id = p_department_id;
        
        RETURN v_total;
    END get_department_payroll;
    
END employee_pkg;
/


# Utiliser package
──────────────────

-- Appeler procédure
DECLARE
    v_emp_id NUMBER;
BEGIN
    employee_pkg.hire_employee('John', 'Doe', 'jdoe@company.com', 'IT_PROG', v_emp_id);
    DBMS_OUTPUT.PUT_LINE('Nouvel employé ID: ' || v_emp_id);
END;
/

-- Utiliser fonction
SELECT employee_pkg.get_employee_count(10) FROM dual;

-- Utiliser constante
BEGIN
    DBMS_OUTPUT.PUT_LINE('Salaire max: ' || employee_pkg.c_max_salary);
END;
/


# === TRIGGERS ===

# POURQUOI triggers ?
[OK] Automatisation - Actions automatiques sur événements
[OK] Audit - Tracer modifications
[OK] Validation - Règles métier complexes
[OK] Cohérence - Maintenir intégrité référentielle


# Types de triggers
────────────────────

1. DML Triggers - INSERT, UPDATE, DELETE
   - BEFORE - Avant opération
   - AFTER - Après opération
   - FOR EACH ROW - Par ligne (row-level)
   - Statement - Une fois par instruction (statement-level)

2. DDL Triggers - CREATE, ALTER, DROP

3. System Triggers - LOGON, LOGOFF, STARTUP, SHUTDOWN


# Trigger BEFORE INSERT
────────────────────────

CREATE OR REPLACE TRIGGER employees_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Générer ID si pas fourni
    IF :NEW.employee_id IS NULL THEN
        :NEW.employee_id := employee_id_seq.NEXTVAL;
    END IF;
    
    -- Timestamps
    :NEW.created_at := SYSDATE;
    :NEW.updated_at := SYSDATE;
    
    -- Email en minuscules
    :NEW.email := LOWER(:NEW.email);
    
    -- Validation
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salaire ne peut pas être négatif');
    END IF;
END;
/


# Trigger AFTER UPDATE (audit)
───────────────────────────────

CREATE OR REPLACE TRIGGER employees_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Logger changements
    INSERT INTO employees_audit (
        employee_id,
        old_salary,
        new_salary,
        old_department_id,
        new_department_id,
        changed_by,
        changed_date
    ) VALUES (
        :NEW.employee_id,
        :OLD.salary,
        :NEW.salary,
        :OLD.department_id,
        :NEW.department_id,
        USER,
        SYSDATE
    );
END;
/


# Trigger avec conditions
──────────────────────────

CREATE OR REPLACE TRIGGER employees_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary)  -- Seulement si augmentation
BEGIN
    -- Vérifier augmentation raisonnable
    IF :NEW.salary > :OLD.salary * 1.50 THEN
        RAISE_APPLICATION_ERROR(-20002, 
            'Augmentation supérieure à 50% non autorisée');
    END IF;
    
    -- Logger
    INSERT INTO salary_changes (
        employee_id,
        old_salary,
        new_salary,
        change_date
    ) VALUES (
        :NEW.employee_id,
        :OLD.salary,
        :NEW.salary,
        SYSDATE
    );
END;
/


# Trigger INSTEAD OF (sur vues)
────────────────────────────────

-- Créer vue
CREATE VIEW employee_dept_view AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Trigger pour rendre vue modifiable
CREATE OR REPLACE TRIGGER employee_dept_view_insert
INSTEAD OF INSERT ON employee_dept_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, department_id)
    VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.department_id);
END;
/


# Trigger composé (Oracle 11g+)
────────────────────────────────

CREATE OR REPLACE TRIGGER employees_compound_trigger
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
    
    -- Variable globale au trigger
    TYPE emp_salary_table IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;
    old_salaries emp_salary_table;
    
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Début mise à jour salaires');
    END BEFORE STATEMENT;
    
    BEFORE EACH ROW IS
    BEGIN
        old_salaries(:NEW.employee_id) := :OLD.salary;
    END BEFORE EACH ROW;
    
    AFTER EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Emp ' || :NEW.employee_id || ': ' || 
            old_salaries(:NEW.employee_id) || ' -> ' || :NEW.salary);
    END AFTER EACH ROW;
    
    AFTER STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Fin mise à jour salaires');
    END AFTER STATEMENT;
    
END employees_compound_trigger;
/


# Désactiver/Activer trigger
─────────────────────────────

ALTER TRIGGER employees_before_insert DISABLE;
ALTER TRIGGER employees_before_insert ENABLE;

-- Tous les triggers d'une table
ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE employees ENABLE ALL TRIGGERS;


# Supprimer trigger
────────────────────

DROP TRIGGER employees_before_insert;


# === COLLECTIONS ===

# Types de collections PL/SQL:
1. Associative Arrays (INDEX BY)
2. Nested Tables
3. VARRAYs (tableaux à taille fix)


# Associative Array (INDEX BY)
────────────────────────────────

DECLARE
    TYPE salary_table_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    salaries salary_table_type;
BEGIN
    -- Ajouter éléments
    salaries(100) := 50000;
    salaries(101) := 60000;
    salaries(102) := 55000;
    
    -- Accéder
    DBMS_OUTPUT.PUT_LINE('Salaire 101: ' || salaries(101));
    
    -- Parcourir
    FOR i IN salaries.FIRST..salaries.LAST LOOP
        IF salaries.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE('Index ' || i || ': ' || salaries(i));
        END IF;
    END LOOP;
END;
/


# Associative Array avec chaînes comme index
──────────────────────────────────────────────

DECLARE
    TYPE country_capital_type IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
    capitals country_capital_type;
BEGIN
    capitals('France') := 'Paris';
    capitals('USA') := 'Washington';
    capitals('Japan') := 'Tokyo';
    
    DBMS_OUTPUT.PUT_LINE('Capitale France: ' || capitals('France'));
END;
/


# Nested Table
───────────────

DECLARE
    TYPE name_list_type IS TABLE OF VARCHAR2(50);
    names name_list_type;
BEGIN
    -- Initialiser
    names := name_list_type('Alice', 'Bob', 'Carol');
    
    -- Ajouter élément
    names.EXTEND;
    names(4) := 'David';
    
    -- Parcourir
    FOR i IN 1..names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
END;
/


# VARRAY (taille fixe)
───────────────────────

DECLARE
    TYPE day_list_type IS VARRAY(7) OF VARCHAR2(10);
    days day_list_type;
BEGIN
    days := day_list_type('Lundi', 'Mardi', 'Mercredi', 'Jeudi', 'Vendredi', 'Samedi', 'Dimanche');
    
    FOR i IN 1..days.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(days(i));
    END LOOP;
END;
/


# BULK COLLECT (performance)
─────────────────────────────

DECLARE
    TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
    emp_table emp_table_type;
BEGIN
    -- Charger toutes lignes d'un coup (BULK)
    SELECT * BULK COLLECT INTO emp_table
    FROM employees
    WHERE department_id = 10;
    
    -- Traiter
    FOR i IN 1..emp_table.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(emp_table(i).first_name);
    END LOOP;
END;
/


# FORALL (DML en masse)
────────────────────────

DECLARE
    TYPE emp_id_table IS TABLE OF NUMBER;
    emp_ids emp_id_table;
    
    TYPE salary_table IS TABLE OF NUMBER;
    new_salaries salary_table;
BEGIN
    -- Préparer données
    SELECT employee_id, salary * 1.10
    BULK COLLECT INTO emp_ids, new_salaries
    FROM employees
    WHERE department_id = 10;
    
    -- UPDATE en masse (beaucoup plus rapide que boucle)
    FORALL i IN 1..emp_ids.COUNT
        UPDATE employees
        SET salary = new_salaries(i)
        WHERE employee_id = emp_ids(i);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' lignes mises à jour');
END;
/


# === SQL DYNAMIQUE ===

# EXECUTE IMMEDIATE
───────────────────

DECLARE
    v_table_name VARCHAR2(30) := 'employees';
    v_sql VARCHAR2(1000);
    v_count NUMBER;
BEGIN
    -- Construction requête dynamique
    v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
    
    EXECUTE IMMEDIATE v_sql INTO v_count;
    
    DBMS_OUTPUT.PUT_LINE('Nombre de lignes: ' || v_count);
END;
/


# EXECUTE IMMEDIATE avec paramètres
────────────────────────────────────

DECLARE
    v_dept_id NUMBER := 10;
    v_count NUMBER;
BEGIN
    EXECUTE IMMEDIATE 
        'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id'
    INTO v_count
    USING v_dept_id;
    
    DBMS_OUTPUT.PUT_LINE('Employés dans dept ' || v_dept_id || ': ' || v_count);
END;
/


# DDL dynamique
────────────────

DECLARE
    v_table_name VARCHAR2(30) := 'temp_table_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_name || ' (
        id NUMBER PRIMARY KEY,
        data VARCHAR2(100)
    )';
    
    DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' créée');
END;
/


# === PACKAGES UTILITAIRES ORACLE ===

# DBMS_OUTPUT - Afficher messages
──────────────────────────────────

BEGIN
    DBMS_OUTPUT.PUT_LINE('Message texte');
    DBMS_OUTPUT.PUT('Plusieurs ');
    DBMS_OUTPUT.PUT('mots ');
    DBMS_OUTPUT.NEW_LINE;
END;
/


# DBMS_SCHEDULER - Planifier jobs
──────────────────────────────────

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'nightly_backup',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'backup_pkg.full_backup',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
        enabled         => TRUE,
        comments        => 'Backup quotidien à 2h'
    );
END;
/


# DBMS_RANDOM - Génération aléatoire
─────────────────────────────────────

BEGIN
    -- Nombre entre 0 et 1
    DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.VALUE);
    
    -- Nombre entre 1 et 100
    DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.VALUE(1, 100));
    
    -- Chaîne aléatoire
    DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.STRING('X', 10));
END;
/


# DBMS_LOB - Manipulation LOBs
────────────────────────────────

DECLARE
    v_clob CLOB;
    v_amount NUMBER := 32767;
    v_text VARCHAR2(32767) := 'Texte long...';
BEGIN
    -- Créer LOB temporaire
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    
    -- Écrire
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_text), v_text);
    
    -- Lire
    DBMS_OUTPUT.PUT_LINE('Taille: ' || DBMS_LOB.GETLENGTH(v_clob));
    
    -- Libérer
    DBMS_LOB.FREETEMPORARY(v_clob);
END;
/


# DBMS_CRYPTO - Cryptographie
──────────────────────────────

DECLARE
    v_input VARCHAR2(100) := 'Secret message';
    v_key RAW(128) := UTL_RAW.CAST_TO_RAW('my_secret_key_16');
    v_encrypted RAW(2000);
    v_decrypted VARCHAR2(2000);
BEGIN
    -- Crypter
    v_encrypted := DBMS_CRYPTO.ENCRYPT(
        src => UTL_RAW.CAST_TO_RAW(v_input),
        typ => DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
        key => v_key
    );
    
    -- Décrypter
    v_decrypted := UTL_RAW.CAST_TO_VARCHAR2(
        DBMS_CRYPTO.DECRYPT(
            src => v_encrypted,
            typ => DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
            key => v_key
        )
    );
    
    DBMS_OUTPUT.PUT_LINE('Original: ' || v_input);
    DBMS_OUTPUT.PUT_LINE('Décrypté: ' || v_decrypted);
END;
/


# UTL_MAIL - Envoyer emails
────────────────────────────

BEGIN
    UTL_MAIL.SEND(
        sender      => 'admin@company.com',
        recipients  => 'user@example.com',
        subject     => 'Notification Oracle',
        message     => 'Votre rapport est prêt.',
        mime_type   => 'text/plain; charset=us-ascii'
    );
END;
/


# === BONNES PRATIQUES PL/SQL ===

1. Nommage cohérent
   [OK] Variables: v_nom
   [OK] Paramètres: p_nom
   [OK] Constantes: c_nom
   [OK] Curseurs: nom_cursor
   [OK] Exceptions: e_nom

2. Gérer TOUTES les exceptions
   [OK] EXCEPTION WHEN OTHERS avec log

3. Utiliser %TYPE et %ROWTYPE
   [OK] Maintenance facilitée

4. BULK COLLECT pour performance
   [OK] Réduire context switches

5. Éviter SQL dynamique quand possible
   [OK] Moins performant
   [X] Risque injection SQL

6. Commenter code complexe
   [OK] Expliquer logique métier

7. Tester exhaustivement
   [OK] Cas normaux + cas limites

8. Packages pour organisation
   [OK] Regrouper fonctionnalités liées

9. Éviter triggers trop complexes
   [X] Difficile à déboguer

10. Toujours COMMIT/ROLLBACK
    [OK] Gérer transactions explicitement


[OK] INDEX ET OPTIMISATION

# === QU'EST-CE QU'UN INDEX ? ===

Index = Structure de données pour accélérer recherches
Comme index d'un livre - trouver information sans tout lire

# POURQUOI index ?
[OK] Performance SELECT - Recherche beaucoup plus rapide
[OK] Unicité - Garantir valeurs uniques
[OK] Tri - ORDER BY plus rapide
[OK] Jointures - Accélérer JOIN

# QUAND indexer ?
[OK] Colonnes dans WHERE clause fréquemment
[OK] Colonnes dans JOIN
[OK] Colonnes dans ORDER BY
[OK] Colonnes avec forte sélectivité (beaucoup de valeurs uniques)

# QUAND NE PAS indexer ?
[X] Petites tables (< 1000 lignes)
[X] Colonnes modifiées fréquemment
[X] Colonnes avec peu de valeurs distinctes (ex: genre M/F)
[X] Table avec beaucoup d'INSERT/UPDATE (overhead maintenance index)


# === TYPES D'INDEX ===

1. B-Tree Index (défaut, 90% des cas)
2. Bitmap Index (colonnes faible cardinalité)
3. Function-Based Index (index sur expression)
4. Unique Index (garantir unicité)
5. Composite Index (plusieurs colonnes)
6. Full-Text Index (recherche texte)


# === B-TREE INDEX (INDEX STANDARD) ===

# Créer index simple
──────────────────────

CREATE INDEX idx_employees_last_name ON employees(last_name);

# Index unique
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

# Index composite (plusieurs colonnes)
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

# IMPORTANT ORDRE COLONNES:
# Mettre colonne la plus sélective EN PREMIER
# Index utilisé si WHERE inclut première colonne


# Voir index d'une table
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;


# Supprimer index
DROP INDEX idx_employees_last_name;


# === BITMAP INDEX ===

# Pour colonnes avec peu de valeurs distinctes
# Ex: gender (M/F), status (ACTIVE/INACTIVE), boolean

CREATE BITMAP INDEX idx_employees_gender ON employees(gender);

# POURQUOI Bitmap ?
[OK] Très compact pour faible cardinalité
[OK] Excellent pour Data Warehouse
[OK] Opérations AND/OR très rapides

# QUAND NE PAS utiliser Bitmap ?
[X] Tables avec beaucoup d'UPDATE/INSERT (verrouillage)
[X] Colonnes haute cardinalité


# === FUNCTION-BASED INDEX ===

# Index sur expression/fonction

CREATE INDEX idx_employees_upper_last_name ON employees(UPPER(last_name));

# Maintenant cette requête utilise l'index:
SELECT * FROM employees WHERE UPPER(last_name) = 'KING';

# Index sur expression arithmétique
CREATE INDEX idx_employees_annual_salary ON employees(salary * 12);

SELECT * FROM employees WHERE salary * 12 > 100000;


# === INDEX COMPOSITE (PLUSIEURS COLONNES) ===

CREATE INDEX idx_emp_dept_job_salary ON employees(department_id, job_id, salary);

# Requêtes utilisant l'index:
[OK] WHERE department_id = 10
[OK] WHERE department_id = 10 AND job_id = 'IT_PROG'
[OK] WHERE department_id = 10 AND job_id = 'IT_PROG' AND salary > 5000

# Requêtes N'utilisant PAS l'index:
[X] WHERE job_id = 'IT_PROG'  (pas première colonne)
[X] WHERE salary > 5000       (pas première colonne)

# RÈGLE: Index utilisé si WHERE inclut premières colonnes de l'index (de gauche à droite)


# === INDEX INVISIBLE (Oracle 11g+) ===

# Tester impact d'un index sans le supprimer

CREATE INDEX idx_test ON employees(column) INVISIBLE;

-- Activer temporairement
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

-- Test requêtes...

-- Désactiver
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE;

-- Rendre visible si bénéfique
ALTER INDEX idx_test VISIBLE;


# === STATISTIQUES D'INDEX ===

# Oracle utilise statistiques pour optimiser requêtes

# Collecter statistiques table + index
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

# Collecter statistiques index uniquement
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'INDEX_NAME');

# Voir statistiques index
SELECT 
    index_name,
    num_rows,
    distinct_keys,
    leaf_blocks,
    clustering_factor
FROM user_indexes
WHERE table_name = 'EMPLOYEES';


# === REBUILD INDEX ===

# Reconstruire index (défragmentation)

ALTER INDEX idx_employees_last_name REBUILD;

# En ligne (table reste accessible)
ALTER INDEX idx_employees_last_name REBUILD ONLINE;

# QUAND rebuild ?
[OK] Après beaucoup de DELETE/UPDATE
[OK] Index fragmenté
[OK] Clustering factor élevé


# === EXPLAIN PLAN (ANALYSER REQUÊTES) ===

# Voir plan d'exécution d'une requête

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

-- Voir plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# Plan d'exécution montre:
- Opérations (TABLE ACCESS, INDEX SCAN, etc.)
- Ordre d'exécution
- Coût estimé
- Lignes estimées


# Types d'accès:
─────────────────

1. TABLE ACCESS FULL (Full Table Scan)
   - Lit toute la table
   - Lent sur grandes tables
   - OK si petite table ou besoin de beaucoup de lignes

2. INDEX UNIQUE SCAN
   - Accès direct via index unique
   - Très rapide (1 ligne)

3. INDEX RANGE SCAN
   - Parcours plage d'index
   - Rapide (plusieurs lignes)

4. INDEX FULL SCAN
   - Parcours tout l'index
   - Plus rapide que TABLE ACCESS FULL

5. INDEX FAST FULL SCAN
   - Parcours index sans ordre
   - Très rapide si toutes colonnes dans index


# === OPTIMISEUR ORACLE ===

# Oracle choisit automatiquement meilleur plan d'exécution

# Modes optimiseur:
1. ALL_ROWS (défaut) - Minimiser temps total
2. FIRST_ROWS - Minimiser temps première ligne

# Changer mode
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;


# Hints (forcer comportement optimiseur)
──────────────────────────────────────────

# Forcer utilisation index
SELECT /*+ INDEX(employees idx_employees_last_name) */
    * FROM employees
WHERE last_name = 'King';

# Forcer full table scan
SELECT /*+ FULL(employees) */
    * FROM employees
WHERE department_id = 10;

# Forcer ordre jointure
SELECT /*+ ORDERED */
    e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

# ATTENTION: Hints à utiliser avec parcimonie
[X] Peut devenir obsolète avec évolution données
[OK] Laisser optimiseur décider en général


# === PARTITIONNEMENT (TABLES VOLUMINEUSES) ===

# Diviser table en partitions pour performance

# Partitionnement par plage (RANGE)
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2023_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_2023_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_2023_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_2023_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

# POURQUOI partitionner ?
[OK] Partition Pruning - Oracle scanne seulement partitions pertinentes
[OK] Maintenance - Backup/archivage par partition
[OK] Performance - Parallélisation


# Index local (un index par partition)
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

# Index global (un index pour toute la table)
CREATE INDEX idx_sales_amount ON sales(amount) GLOBAL;


# === MATÉRIALISÉES VUES (MATERIALIZED VIEWS) ===

# Vue avec données physiquement stockées

CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT 
    e.department_id,
    d.department_name,
    COUNT(*) AS emp_count,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.department_id, d.department_name;

# POURQUOI Materialized View ?
[OK] Pré-calculer agrégations complexes
[OK] Query très rapide (données déjà calculées)
[OK] Idéal pour Data Warehouse

# Refresh modes:
- ON COMMIT: Refresh automatique après chaque COMMIT
- ON DEMAND: Refresh manuel
- FAST: Incremental (seulement changements)
- COMPLETE: Recalcul total


# Refresh manuel
EXEC DBMS_MVIEW.REFRESH('EMP_DEPT_MV');


# === ANALYSE PERFORMANCE AVEC AWR ===

# Automatic Workload Repository

# Créer snapshot
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

# Générer rapport AWR
@?/rdbms/admin/awrrpt.sql

# Rapport montre:
- Top SQL (requêtes plus coûteuses)
- Attentes système
- Statistiques I/O
- Utilisation mémoire


# === BONNES PRATIQUES PERFORMANCE ===

1. Index judicieusement
   [OK] Colonnes WHERE, JOIN, ORDER BY
   [X] Pas trop d'index (overhead INSERT/UPDATE)

2. Mettre à jour statistiques régulièrement
   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

3. Utiliser BULK COLLECT en PL/SQL
   [OK] Réduire context switches

4. Éviter SELECT *
   [OK] Sélectionner seulement colonnes nécessaires

5. Utiliser BIND VARIABLES
   [OK] Réutilisation plan d'exécution

6. Analyser plans d'exécution
   [OK] EXPLAIN PLAN pour requêtes lentes

7. Partitionner grandes tables
   [OK] > 100 GB

8. Utiliser Materialized Views pour agrégations
   [OK] Data Warehouse

9. Monitorer avec AWR
   [OK] Identifier goulots d'étranglement

10. Éviter fonctions dans WHERE
    [X] WHERE UPPER(name) = 'JOHN'
    [OK] CREATE FUNCTION-BASED INDEX


```
[OK] VUES (VIEWS)

# === QU'EST-CE QU'UNE VUE ? ===

Vue = Requête SELECT enregistrée comme objet
[OK] Table virtuelle (pas de stockage physique)
[OK] Simplifier requêtes complexes
[OK] Sécurité (masquer colonnes sensibles)
[OK] Abstraction (indépendance applications)

# POURQUOI utiliser des vues ?
[OK] Simplification - Encapsuler requêtes complexes
[OK] Sécurité - Contrôler accès aux données
[OK] Cohérence - Même logique pour tous utilisateurs
[OK] Compatibilité - Maintenir interface stable


# === CRÉER UNE VUE SIMPLE ===

CREATE VIEW employee_basic_info AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM employees;

# Utiliser la vue comme une table
SELECT * FROM employee_basic_info;
SELECT first_name, last_name FROM employee_basic_info WHERE employee_id = 100;


# === VUE AVEC JOINTURES ===

CREATE VIEW employee_department_view AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_id,
    d.department_name,
    l.city,
    l.country_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

# Utilisation
SELECT first_name, department_name, city
FROM employee_department_view
WHERE country_id = 'US';


# === VUE AVEC CALCULS ET AGRÉGATIONS ===

CREATE VIEW department_stats AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MIN(e.salary) AS min_salary,
    MAX(e.salary) AS max_salary,
    SUM(e.salary) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

# Utilisation
SELECT * FROM department_stats WHERE employee_count > 5;


# === VUE AVEC SOUS-REQUÊTES ===

CREATE VIEW high_earners AS
SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);


# === VUE AVEC CONDITIONS ===

CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, department_id
FROM employees
WHERE status = 'ACTIVE'
AND hire_date >= ADD_MONTHS(SYSDATE, -60);  -- Embauché dans 5 dernières années


# === OR REPLACE (MODIFIER VUE) ===

# Remplacer vue existante sans la supprimer d'abord
CREATE OR REPLACE VIEW employee_basic_info AS
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,  -- Colonne ajoutée
    hire_date
FROM employees;

# POURQUOI OR REPLACE ?
[OK] Conserve privilèges accordés sur la vue
[OK] Pas besoin de DROP puis CREATE


# === VUES AVEC CHECK OPTION ===

# Empêcher modifications violant condition WHERE de la vue

CREATE OR REPLACE VIEW it_employees AS
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE department_id = 60
WITH CHECK OPTION CONSTRAINT it_emp_ck;

# Cette UPDATE échoue car viole CHECK OPTION:
UPDATE it_employees SET department_id = 50 WHERE employee_id = 100;
-- ORA-01402: violation de la clause WHERE de la vue WITH CHECK OPTION

# Cette UPDATE réussit:
UPDATE it_employees SET salary = 8000 WHERE employee_id = 100;


# === VUES AVEC READ ONLY ===

# Vue non modifiable

CREATE OR REPLACE VIEW employee_summary AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WITH READ ONLY;

# Toute tentative INSERT/UPDATE/DELETE échoue:
UPDATE employee_summary SET salary = 9000 WHERE employee_id = 100;
-- ORA-42399: impossible de réaliser une opération DML sur une vue en lecture seule


# === VUES MODIFIABLES (DML SUR VUES) ===

# CONDITIONS pour vue modifiable:
1. Pas de fonctions d'agrégation (SUM, AVG, COUNT, etc.)
2. Pas de GROUP BY, DISTINCT, ROWNUM
3. Pas d'opérateurs ensemblistes (UNION, INTERSECT, MINUS)
4. Colonnes basées directement sur colonnes table (pas d'expressions)

# Vue modifiable
CREATE OR REPLACE VIEW emp_dept_10 AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 10;

# INSERT dans la vue
INSERT INTO emp_dept_10 (employee_id, first_name, last_name, salary, department_id)
VALUES (999, 'New', 'Employee', 5000, 10);

# UPDATE dans la vue
UPDATE emp_dept_10 SET salary = salary * 1.10 WHERE employee_id = 100;

# DELETE dans la vue
DELETE FROM emp_dept_10 WHERE employee_id = 999;


# === VUES INSTEAD OF TRIGGERS ===

# Rendre vue non-modifiable modifiable via trigger

CREATE OR REPLACE VIEW emp_dept_summary AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_id,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Cette vue n'est pas naturellement modifiable (jointure)

# Créer INSTEAD OF trigger
CREATE OR REPLACE TRIGGER emp_dept_summary_insert
INSTEAD OF INSERT ON emp_dept_summary
FOR EACH ROW
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, department_id)
    VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.department_id);
END;
/

# Maintenant INSERT fonctionne:
INSERT INTO emp_dept_summary (employee_id, first_name, last_name, department_id)
VALUES (1000, 'John', 'Doe', 10);


# === VUES INLINE (SOUS-REQUÊTE DANS FROM) ===

# Vue temporaire dans requête (pas stockée)

SELECT dept_stats.department_name, dept_stats.avg_salary
FROM (
    SELECT 
        d.department_name,
        AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY d.department_name
) dept_stats
WHERE dept_stats.avg_salary > 5000;


# === VUES MATÉRIALISÉES (MATERIALIZED VIEWS) ===

# Vue avec données physiquement stockées (déjà vu dans section Performance)

CREATE MATERIALIZED VIEW emp_dept_summary_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS emp_count,
    AVG(e.salary) AS avg_salary,
    SUM(e.salary) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

# Refresh manuel
EXEC DBMS_MVIEW.REFRESH('EMP_DEPT_SUMMARY_MV');

# DIFFÉRENCES Vue normale vs Matérialisée:
┌─────────────────────┬────────────────────┬─────────────────────┐
│                     │   VUE NORMALE      │  VUE MATÉRIALISÉE   │
├─────────────────────┼────────────────────┼─────────────────────┤
│ Stockage            │ Aucun (virtuelle)  │ Physique            │
│ Performance         │ Calcul à la volée  │ Très rapide         │
│ Actualisation       │ Toujours à jour    │ Refresh périodique  │
│ Espace disque       │ Minimal            │ Significatif        │
│ Usage idéal         │ Requêtes simples   │ Agrégations lourdes │
└─────────────────────┴────────────────────┴─────────────────────┘


# === VOIR INFORMATIONS SUR LES VUES ===

# Lister toutes les vues
SELECT view_name, text_length, read_only
FROM user_views
ORDER BY view_name;

# Voir définition d'une vue
SELECT text FROM user_views WHERE view_name = 'EMPLOYEE_BASIC_INFO';

# Colonnes d'une vue
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEE_BASIC_INFO';

# Dépendances (objets utilisés par la vue)
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'EMPLOYEE_BASIC_INFO';


# === SUPPRIMER UNE VUE ===

DROP VIEW employee_basic_info;

# ATTENTION: Supprimer vue n'affecte PAS les tables sous-jacentes


# === VUES SYSTÈME ORACLE ===

# Oracle fournit nombreuses vues dictionnaire données

# USER_* - Objets possédés par utilisateur actuel
SELECT * FROM user_tables;
SELECT * FROM user_indexes;
SELECT * FROM user_views;

# ALL_* - Objets accessibles par utilisateur actuel
SELECT * FROM all_tables;
SELECT * FROM all_views;

# DBA_* - Tous objets (nécessite privilèges DBA)
SELECT * FROM dba_tables;
SELECT * FROM dba_users;

# V$* - Vues de performance dynamiques
SELECT * FROM v$session;      -- Sessions actives
SELECT * FROM v$sql;           -- Requêtes SQL en cache
SELECT * FROM v$database;      -- Info base de données


# === EXEMPLES PRATIQUES VUES ===

# EXEMPLE 1: Vue de sécurité (masquer salaires)
────────────────────────────────────────────────

CREATE OR REPLACE VIEW employee_public_info AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    department_id
    -- Pas de colonne salary
FROM employees;

-- Accorder accès à tous employés
GRANT SELECT ON employee_public_info TO public_role;


# EXEMPLE 2: Vue pour reporting
────────────────────────────────

CREATE OR REPLACE VIEW monthly_sales_report AS
SELECT 
    TO_CHAR(order_date, 'YYYY-MM') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month DESC;


# EXEMPLE 3: Vue avec logique métier complexe
──────────────────────────────────────────────

CREATE OR REPLACE VIEW employee_performance AS
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.hire_date,
    MONTHS_BETWEEN(SYSDATE, e.hire_date) / 12 AS years_employed,
    e.salary,
    (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS dept_avg_salary,
    CASE 
        WHEN e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) * 1.2
            THEN 'Top Performer'
        WHEN e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id)
            THEN 'Above Average'
        WHEN e.salary >= (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) * 0.8
            THEN 'Average'
        ELSE 'Below Average'
    END AS performance_category,
    (SELECT COUNT(*) FROM projects p WHERE p.manager_id = e.employee_id) AS projects_managed
FROM employees e;


# EXEMPLE 4: Vue hiérarchique
──────────────────────────────

CREATE OR REPLACE VIEW employee_hierarchy AS
SELECT 
    LEVEL AS hierarchy_level,
    employee_id,
    first_name || ' ' || last_name AS employee_name,
    manager_id,
    SYS_CONNECT_BY_PATH(first_name || ' ' || last_name, ' -> ') AS hierarchy_path,
    LPAD(' ', 2 * (LEVEL - 1)) || first_name || ' ' || last_name AS indented_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;


# === BONNES PRATIQUES VUES ===

1. Nommer clairement
   [OK] Préfixe ou suffixe _view, _v, _vw
   [OK] Nom descriptif du contenu

2. Documenter vues complexes
   COMMENT ON TABLE employee_summary IS 'Vue résumant info employés avec calculs salaire annuel';

3. WITH CHECK OPTION pour intégrité
   [OK] Empêcher modifications violant condition WHERE

4. WITH READ ONLY pour protection
   [OK] Vues de reporting, analyse

5. Éviter SELECT * dans définition vue
   [OK] Spécifier colonnes explicitement
   [OK] Meilleure maintenance

6. Index sur tables sous-jacentes
   [OK] Pas sur vues elles-mêmes
   [OK] Améliore performance vues

7. Vues matérialisées pour agrégations
   [OK] Data Warehouse
   [OK] Rapports complexes

8. Tester performance
   [OK] EXPLAIN PLAN sur requêtes utilisant vues
   [OK] Comparer avec requêtes directes

9. Privilèges minimaux
   [OK] Accorder seulement ce qui est nécessaire

10. Maintenir vues à jour
    [OK] Recréer après modifications structure tables


[OK] COPIE DE DONNÉES (IMPORT/EXPORT)

# === MÉTHODES DE COPIE ===

1. SQL*Loader - Charger fichiers externes
2. Data Pump (expdp/impdp) - Export/Import Oracle
3. CREATE TABLE AS SELECT (CTAS)
4. INSERT INTO SELECT
5. COPY command (SQL*Plus)
6. External Tables - Accès fichiers comme tables
7. Database Links - Copie entre bases


# === CREATE TABLE AS SELECT (CTAS) ===

# Copier structure ET données
CREATE TABLE employees_backup AS
SELECT * FROM employees;

# Copier seulement structure (sans données)
CREATE TABLE employees_template AS
SELECT * FROM employees
WHERE 1=0;  -- Condition toujours fausse

# Copier avec filtrage
CREATE TABLE it_employees AS
SELECT * FROM employees
WHERE department_id = 60;

# Copier avec transformation
CREATE TABLE employee_summary AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary,
    TRUNC(hire_date) AS hire_date
FROM employees;

# Avec options stockage
CREATE TABLE employees_backup
TABLESPACE users
AS SELECT * FROM employees;


# === INSERT INTO SELECT ===

# Copier vers table existante
INSERT INTO employees_archive
SELECT * FROM employees
WHERE hire_date < DATE '2020-01-01';

# Avec transformation
INSERT INTO employee_salaries (employee_id, annual_salary)
SELECT employee_id, salary * 12
FROM employees;

# Copier depuis plusieurs tables
INSERT INTO all_contacts (id, name, email, type)
SELECT employee_id, first_name || ' ' || last_name, email, 'Employee'
FROM employees
UNION ALL
SELECT customer_id, customer_name, email, 'Customer'
FROM customers;


# === SQL*LOADER ===

# Charger données depuis fichiers CSV, texte, etc.

# ÉTAPE 1: Créer fichier contrôle (employees.ctl)
──────────────────────────────────────────────────

LOAD DATA
INFILE 'employees.csv'
BADFILE 'employees.bad'
DISCARDFILE 'employees.dsc'
INSERT INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    employee_id,
    first_name,
    last_name,
    email,
    hire_date DATE "YYYY-MM-DD",
    salary
)

# ÉTAPE 2: Fichier données (employees.csv)
───────────────────────────────────────────

100,"John","Doe","jdoe@company.com","2020-01-15",50000
101,"Jane","Smith","jsmith@company.com","2021-03-20",55000
102,"Bob","Wilson","bwilson@company.com","2019-07-10",60000

# ÉTAPE 3: Exécuter SQL*Loader
────────────────────────────────

sqlldr userid=username/password control=employees.ctl log=employees.log

# Options SQL*Loader:
- INSERT: Insère dans table vide
- APPEND: Ajoute à table existante
- REPLACE: Vide table puis insère
- TRUNCATE: TRUNCATE table puis insère


# === DATA PUMP (EXPORT) ===

# Export logique Oracle (remplace exp/imp)

# Export schéma complet
expdp username/password DIRECTORY=dump_dir DUMPFILE=schema_export.dmp LOGFILE=schema_export.log SCHEMAS=hr

# Export tables spécifiques
expdp username/password DIRECTORY=dump_dir DUMPFILE=tables_export.dmp TABLES=employees,departments

# Export avec clause WHERE
expdp username/password DIRECTORY=dump_dir DUMPFILE=filtered_export.dmp TABLES=employees QUERY=employees:\"WHERE department_id=10\"

# Export base complète (nécessite DBA)
expdp system/password DIRECTORY=dump_dir DUMPFILE=full_export.dmp FULL=Y

# Compression (Oracle 11g+)
expdp username/password DIRECTORY=dump_dir DUMPFILE=compressed.dmp COMPRESSION=ALL


# Créer répertoire (une fois)
CREATE DIRECTORY dump_dir AS '/u01/app/oracle/dump';
GRANT READ, WRITE ON DIRECTORY dump_dir TO username;


# === DATA PUMP (IMPORT) ===

# Import dump file
impdp username/password DIRECTORY=dump_dir DUMPFILE=schema_export.dmp LOGFILE=schema_import.log

# Import dans schéma différent
impdp username/password DIRECTORY=dump_dir DUMPFILE=schema_export.dmp REMAP_SCHEMA=old_schema:new_schema

# Import tables spécifiques
impdp username/password DIRECTORY=dump_dir DUMPFILE=full_export.dmp TABLES=employees,departments

# Import avec remap tablespace
impdp username/password DIRECTORY=dump_dir DUMPFILE=schema_export.dmp REMAP_TABLESPACE=old_ts:new_ts

# Import en mode TABLE_EXISTS_ACTION
impdp username/password DIRECTORY=dump_dir DUMPFILE=tables.dmp TABLE_EXISTS_ACTION=APPEND
# Options: SKIP, APPEND, TRUNCATE, REPLACE


# === EXTERNAL TABLES ===

# Accéder à fichiers externes comme si c'étaient des tables

# ÉTAPE 1: Créer répertoire
CREATE DIRECTORY ext_data_dir AS '/u01/data/external';
GRANT READ ON DIRECTORY ext_data_dir TO username;

# ÉTAPE 2: Créer external table
CREATE TABLE employees_ext (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    salary NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        (
            employee_id,
            first_name,
            last_name,
            email,
            hire_date DATE "YYYY-MM-DD",
            salary
        )
    )
    LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

# ÉTAPE 3: Utiliser comme table normale
SELECT * FROM employees_ext;

# Charger dans table Oracle
INSERT INTO employees
SELECT * FROM employees_ext;

# POURQUOI External Tables ?
[OK] Pas de chargement préalable
[OK] Requêtes SQL standards
[OK] Parallélisation automatique
[OK] Intégration facile ETL


# === DATABASE LINKS ===

# Copier données entre bases de données Oracle

# ÉTAPE 1: Créer database link
CREATE DATABASE LINK remote_db
CONNECT TO username IDENTIFIED BY password
USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=remote_host)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=remote_service))
)';

# ÉTAPE 2: Requêter base distante
SELECT * FROM employees@remote_db;

# ÉTAPE 3: Copier données
INSERT INTO local_employees
SELECT * FROM employees@remote_db;

# Ou avec CTAS
CREATE TABLE local_employees AS
SELECT * FROM employees@remote_db;


# === MERGE (UPSERT) POUR SYNCHRONISATION ===

# Synchroniser données entre deux tables

MERGE INTO target_employees t
USING source_employees s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET
        t.first_name = s.first_name,
        t.last_name = s.last_name,
        t.salary = s.salary,
        t.updated_at = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary, created_at)
    VALUES (s.employee_id, s.first_name, s.last_name, s.salary, SYSDATE);


# === COPY COMMAND (SQL*Plus) ===

# Copier données entre bases (ancien, moins utilisé)

COPY FROM username/password@source_db -
TO username/password@target_db -
INSERT target_table -
USING SELECT * FROM source_table;


# === EXPORT CSV DEPUIS SQL*Plus ===

SET COLSEP ','
SET PAGESIZE 0
SET TRIMSPOOL ON
SET HEADSEP OFF
SET LINESIZE 1000

SPOOL employees_export.csv

SELECT employee_id, first_name, last_name, email, hire_date, salary
FROM employees;

SPOOL OFF


# === BULK INSERT AVEC PL/SQL ===

# Pour performance maximale

DECLARE
    TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
    emp_data emp_table_type;
BEGIN
    -- Charger données source
    SELECT * BULK COLLECT INTO emp_data
    FROM employees@remote_db;
    
    -- Insert en masse
    FORALL i IN 1..emp_data.COUNT
        INSERT INTO employees VALUES emp_data(i);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' lignes insérées');
END;
/


# === EXPORT/IMPORT JSON ===

# Oracle 21c+ supporte JSON nativement

# Export JSON
SELECT JSON_OBJECT(
    'employee_id' VALUE employee_id,
    'first_name' VALUE first_name,
    'last_name' VALUE last_name,
    'salary' VALUE salary
) AS employee_json
FROM employees;

# Import depuis JSON
INSERT INTO employees (employee_id, first_name, last_name, salary)
SELECT 
    jt.employee_id,
    jt.first_name,
    jt.last_name,
    jt.salary
FROM json_table_data jd,
     JSON_TABLE(jd.json_column, '$[*]'
         COLUMNS (
             employee_id NUMBER PATH '$.employee_id',
             first_name VARCHAR2(50) PATH '$.first_name',
             last_name VARCHAR2(50) PATH '$.last_name',
             salary NUMBER PATH '$.salary'
         )
     ) jt;


# === BONNES PRATIQUES IMPORT/EXPORT ===

1. Toujours tester sur petit échantillon
   [OK] Valider format, types données

2. Vérifier espace disque
   [OK] Export peut être volumineux
   [OK] Prévoir 2-3x taille données

3. Utiliser compression
   [OK] Data Pump: COMPRESSION=ALL
   [OK] Économise espace, temps transfert

4. Parallélisme pour gros volumes
   expdp ... PARALLEL=4

5. Sauvegarder logs
   [OK] Tracer erreurs, statistiques

6. Valider données après import
   SELECT COUNT(*) FROM table_source;
   SELECT COUNT(*) FROM table_target;

7. Désactiver contraintes/triggers temporairement
   ALTER TABLE employees DISABLE CONSTRAINT fk_dept;
   -- Import
   ALTER TABLE employees ENABLE CONSTRAINT fk_dept;

8. Utiliser NOLOGGING pour gros volumes
   ALTER TABLE employees NOLOGGING;
   -- Import
   ALTER TABLE employees LOGGING;

9. Commit par lots
   [OK] Éviter rollback segment plein

10. Nettoyer après import
    [OK] Rebuild index: ALTER INDEX idx_name REBUILD;
    [OK] Statistiques: EXEC DBMS_STATS.GATHER_TABLE_STATS('schema', 'table');


# === EXEMPLES PRATIQUES COMPLETS ===

# EXEMPLE 1: Migration complète schéma vers nouveau serveur
──────────────────────────────────────────────────────────────

-- Sur serveur source
expdp hr/password DIRECTORY=dump_dir DUMPFILE=hr_full.dmp LOGFILE=hr_export.log SCHEMAS=hr COMPRESSION=ALL

-- Transférer fichier hr_full.dmp vers serveur cible

-- Sur serveur cible
impdp hr/password DIRECTORY=dump_dir DUMPFILE=hr_full.dmp LOGFILE=hr_import.log REMAP_TABLESPACE=old_ts:new_ts


# EXEMPLE 2: Archivage données anciennes
──────────────────────────────────────────

-- Créer table archive
CREATE TABLE orders_archive AS
SELECT * FROM orders WHERE 1=0;

-- Copier données anciennes
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < ADD_MONTHS(SYSDATE, -24);  -- Plus de 2 ans

COMMIT;

-- Vérifier
SELECT COUNT(*) FROM orders_archive;

-- Supprimer de table active
DELETE FROM orders
WHERE order_date < ADD_MONTHS(SYSDATE, -24);

COMMIT;


# EXEMPLE 3: Synchronisation quotidienne
──────────────────────────────────────────

CREATE OR REPLACE PROCEDURE sync_employee_data IS
BEGIN
    MERGE INTO local_employees l
    USING (SELECT * FROM employees@remote_db) r
    ON (l.employee_id = r.employee_id)
    WHEN MATCHED THEN
        UPDATE SET
            l.first_name = r.first_name,
            l.last_name = r.last_name,
            l.salary = r.salary,
            l.department_id = r.department_id,
            l.updated_at = SYSDATE
        WHERE l.first_name != r.first_name
           OR l.last_name != r.last_name
           OR l.salary != r.salary
           OR l.department_id != r.department_id
    WHEN NOT MATCHED THEN
        INSERT (employee_id, first_name, last_name, email, salary, department_id, created_at)
        VALUES (r.employee_id, r.first_name, r.last_name, r.email, r.salary, r.department_id, SYSDATE);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Synchronisation terminée: ' || SQL%ROWCOUNT || ' lignes affectées');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Planifier exécution quotidienne
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'daily_employee_sync',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'sync_employee_data',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
        enabled         => TRUE
    );
END;
/
```


[OK] SÉCURITÉ ORACLE

# === GESTION DES UTILISATEURS (APPROFONDIE) ===

# Créer utilisateur avec options complètes
──────────────────────────────────────────────

CREATE USER dev_user IDENTIFIED BY SecurePass123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
PROFILE developer_profile
PASSWORD EXPIRE
ACCOUNT UNLOCK;

# Explications détaillées:
# - IDENTIFIED BY: Mot de passe (ou IDENTIFIED EXTERNALLY, IDENTIFIED GLOBALLY)
# - DEFAULT TABLESPACE: Où ses objets seront stockés
# - TEMPORARY TABLESPACE: Pour opérations temporaires (tri, etc.)
# - QUOTA: Limite d'espace disque
# - PROFILE: Profil de ressources/sécurité
# - PASSWORD EXPIRE: Forcer changement à première connexion
# - ACCOUNT UNLOCK: Compte actif immédiatement


# Authentification externe (OS)
─────────────────────────────────

CREATE USER ops$user IDENTIFIED EXTERNALLY;

# Connexion sans mot de passe si authentifié OS
sqlplus /


# Authentification globale (LDAP/Active Directory)
────────────────────────────────────────────────────

CREATE USER global_user IDENTIFIED GLOBALLY AS 'CN=John Doe,OU=Users,DC=company,DC=com';


# === PROFILS DE SÉCURITÉ AVANCÉS ===

CREATE PROFILE secure_profile LIMIT
    -- Limite connexions
    SESSIONS_PER_USER 3
    CPU_PER_SESSION UNLIMITED
    CPU_PER_CALL 3000
    CONNECT_TIME 480  -- Minutes (8h)
    IDLE_TIME 15      -- Minutes inactivité
    
    -- Limites ressources
    LOGICAL_READS_PER_SESSION DEFAULT
    LOGICAL_READS_PER_CALL 1000
    PRIVATE_SGA 15M
    COMPOSITE_LIMIT 5000000
    
    -- Politique mot de passe
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 365
    PASSWORD_REUSE_MAX 10
    PASSWORD_VERIFY_FUNCTION verify_function_11G
    PASSWORD_LOCK_TIME 1/24    -- 1 heure
    PASSWORD_GRACE_TIME 7;

# Assigner profil
ALTER USER dev_user PROFILE secure_profile;


# Fonction de vérification mot de passe personnalisée
────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION verify_password_complexity (
    username VARCHAR2,
    password VARCHAR2,
    old_password VARCHAR2
) RETURN BOOLEAN IS
    n BOOLEAN;
    m INTEGER;
    differ INTEGER;
    isdigit BOOLEAN;
    ischar BOOLEAN;
    ispunct BOOLEAN;
    digitarray VARCHAR2(20);
    punctarray VARCHAR2(25);
    chararray VARCHAR2(52);
BEGIN
    digitarray := '0123456789';
    chararray := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    punctarray := '!"#$%&()``*+,-/:;<=>?_';
    
    -- Vérifier longueur minimum
    IF LENGTH(password) < 8 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Mot de passe doit contenir au moins 8 caractères');
    END IF;
    
    -- Vérifier complexité
    isdigit := FALSE;
    ischar := FALSE;
    ispunct := FALSE;
    
    FOR i IN 1..LENGTH(password) LOOP
        IF INSTR(digitarray, SUBSTR(password, i, 1)) > 0 THEN
            isdigit := TRUE;
        ELSIF INSTR(chararray, SUBSTR(password, i, 1)) > 0 THEN
            ischar := TRUE;
        ELSIF INSTR(punctarray, SUBSTR(password, i, 1)) > 0 THEN
            ispunct := TRUE;
        END IF;
    END LOOP;
    
    IF NOT (isdigit AND ischar AND ispunct) THEN
        RAISE_APPLICATION_ERROR(-20002, 'Mot de passe doit contenir lettres, chiffres et caractères spéciaux');
    END IF;
    
    -- Vérifier ne contient pas username
    IF INSTR(LOWER(password), LOWER(username)) > 0 THEN
        RAISE_APPLICATION_ERROR(-20003, 'Mot de passe ne peut pas contenir le nom d''utilisateur');
    END IF;
    
    -- Vérifier différence avec ancien mot de passe
    IF old_password IS NOT NULL THEN
        differ := LENGTH(old_password) - LENGTH(password);
        IF ABS(differ) < 3 THEN
            RAISE_APPLICATION_ERROR(-20004, 'Nouveau mot de passe doit différer significativement');
        END IF;
    END IF;
    
    RETURN TRUE;
END;
/


# === PRIVILÈGES SYSTÈME AVANCÉS ===

# Liste complète privilèges système importants
───────────────────────────────────────────────

-- Connexion et session
GRANT CREATE SESSION TO user;
GRANT ALTER SESSION TO user;

-- Objets
GRANT CREATE TABLE TO user;
GRANT CREATE VIEW TO user;
GRANT CREATE SEQUENCE TO user;
GRANT CREATE SYNONYM TO user;
GRANT CREATE PROCEDURE TO user;
GRANT CREATE TRIGGER TO user;
GRANT CREATE TYPE TO user;

-- Administration
GRANT CREATE USER TO user;
GRANT ALTER USER TO user;
GRANT DROP USER TO user;
GRANT CREATE ROLE TO user;
GRANT DROP ANY ROLE TO user;
GRANT GRANT ANY ROLE TO user;

-- Tablespaces
GRANT CREATE TABLESPACE TO user;
GRANT ALTER TABLESPACE TO user;
GRANT DROP TABLESPACE TO user;
GRANT UNLIMITED TABLESPACE TO user;

-- Audit
GRANT AUDIT SYSTEM TO user;
GRANT AUDIT ANY TO user;

-- Backup/Recovery
GRANT SYSDBA TO user;  -- Attention: privilège très puissant!
GRANT SYSOPER TO user;


# === PRIVILÈGES OBJET GRANULAIRES ===

# Privilèges sur colonnes spécifiques
───────────────────────────────────────

GRANT SELECT (employee_id, first_name, last_name) ON employees TO hr_analyst;
GRANT UPDATE (salary) ON employees TO hr_manager;


# Privilèges avec contraintes (VPD - Virtual Private Database)
─────────────────────────────────────────────────────────────────

-- Créer fonction de politique de sécurité
CREATE OR REPLACE FUNCTION department_security_policy (
    schema_name VARCHAR2,
    table_name VARCHAR2
) RETURN VARCHAR2 IS
    v_user VARCHAR2(100);
    v_dept_id NUMBER;
BEGIN
    v_user := SYS_CONTEXT('USERENV', 'SESSION_USER');
    
    -- Récupérer département de l'utilisateur
    SELECT department_id INTO v_dept_id
    FROM user_departments
    WHERE username = v_user;
    
    -- Retourner clause WHERE
    RETURN 'department_id = ' || v_dept_id;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '1=0';  -- Aucune ligne visible
END;
/

-- Appliquer politique
BEGIN
    DBMS_RLS.ADD_POLICY (
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'dept_security_policy',
        function_schema => 'HR',
        policy_function => 'department_security_policy',
        statement_types => 'SELECT,UPDATE,DELETE'
    );
END;
/

# RÉSULTAT: Chaque utilisateur voit seulement employés de son département


# === RÔLES AVANCÉS ===

# Créer hiérarchie de rôles
─────────────────────────────

CREATE ROLE app_user;
CREATE ROLE app_analyst;
CREATE ROLE app_manager;
CREATE ROLE app_admin;

-- Privilèges de base (tous)
GRANT CREATE SESSION TO app_user;
GRANT SELECT ON employees TO app_user;
GRANT SELECT ON departments TO app_user;

-- Privilèges analyste (hérite user + ajout)
GRANT app_user TO app_analyst;
GRANT SELECT ON salary_history TO app_analyst;

-- Privilèges manager (hérite analyst + ajout)
GRANT app_analyst TO app_manager;
GRANT INSERT, UPDATE ON employees TO app_manager;

-- Privilèges admin (hérite manager + ajout)
GRANT app_manager TO app_admin;
GRANT DELETE ON employees TO app_admin;
GRANT ALL ON employees TO app_admin;


# Rôles avec mot de passe
──────────────────────────

CREATE ROLE sensitive_role IDENTIFIED BY SecureRole123;
GRANT SELECT ON sensitive_data TO sensitive_role;

-- Utilisateur doit activer rôle explicitement
SET ROLE sensitive_role IDENTIFIED BY SecureRole123;


# Rôles par défaut
───────────────────

-- Définir rôles activés par défaut
ALTER USER john_doe DEFAULT ROLE app_analyst;

-- Tous sauf un
ALTER USER john_doe DEFAULT ROLE ALL EXCEPT sensitive_role;

-- Aucun (activation manuelle)
ALTER USER john_doe DEFAULT ROLE NONE;


# === CRYPTAGE DE DONNÉES ===

# Transparent Data Encryption (TDE)
─────────────────────────────────────

# Configurer wallet
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "WalletPassword123";

# Crypter tablespace
CREATE TABLESPACE secure_data
DATAFILE '/u01/app/oracle/oradata/ORCL/secure_data01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

# Crypter colonne existante
ALTER TABLE employees MODIFY (salary ENCRYPT USING 'AES256');

# Créer table avec colonnes cryptées
CREATE TABLE credit_cards (
    card_id NUMBER PRIMARY KEY,
    card_number VARCHAR2(16) ENCRYPT USING 'AES256',
    cvv VARCHAR2(3) ENCRYPT USING 'AES256',
    expiry_date DATE
);

# POURQUOI TDE?
[OK] Protection au repos (fichiers volés = illisibles)
[OK] Transparent (applications ne changent pas)
[OK] Performance minimale


# === AUDIT DE SÉCURITÉ ===

# Audit unifié (Oracle 12c+)
─────────────────────────────

# Activer audit unifié
ALTER SYSTEM SET AUDIT_TRAIL = DB, EXTENDED SCOPE=SPFILE;
-- Redémarrer base

# Auditer toutes connexions
AUDIT SESSION;

# Auditer actions spécifiques
AUDIT SELECT TABLE, UPDATE TABLE, DELETE TABLE BY ACCESS;

# Auditer utilisateur spécifique
AUDIT ALL BY dev_user;

# Auditer actions réussies ET échouées
AUDIT DELETE ON employees BY ACCESS WHENEVER SUCCESSFUL;
AUDIT DELETE ON employees BY ACCESS WHENEVER NOT SUCCESSFUL;

# Auditer privilèges
AUDIT CREATE TABLE BY ACCESS;
AUDIT DROP TABLE BY ACCESS;


# Créer politique d'audit personnalisée
────────────────────────────────────────

BEGIN
    DBMS_FGA.ADD_POLICY (
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'audit_salary_access',
        audit_condition => 'SALARY > 100000',
        audit_column    => 'SALARY',
        statement_types => 'SELECT,UPDATE'
    );
END;
/

# RÉSULTAT: Audit seulement accès aux salaires > 100000


# Consulter logs d'audit
─────────────────────────

-- Audit standard
SELECT 
    username,
    action_name,
    object_name,
    timestamp,
    returncode
FROM dba_audit_trail
WHERE username = 'DEV_USER'
ORDER BY timestamp DESC;

-- Fine-Grained Audit
SELECT 
    db_user,
    object_name,
    sql_text,
    timestamp
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;


# Purger logs audit
────────────────────

BEGIN
    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
        audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        use_last_arch_timestamp => FALSE
    );
END;
/


# === CONTRÔLE D'ACCÈS RÉSEAU ===

# Restreindre accès par adresse IP
────────────────────────────────────

-- Dans sqlnet.ora
tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.1.100, 192.168.1.101)
tcp.excluded_nodes = (10.0.0.*)

-- Ou utiliser ACL dans base
BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl         => 'company_network.xml',
        description => 'Access control for company network',
        principal   => 'DEV_USER',
        is_grant    => TRUE,
        privilege   => 'connect'
    );
    
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
        acl  => 'company_network.xml',
        host => '*.company.com',
        lower_port => 80,
        upper_port => 443
    );
END;
/


# === MASQUAGE DE DONNÉES (DATA REDACTION) ===

BEGIN
    DBMS_REDACT.ADD_POLICY (
        object_schema    => 'HR',
        object_name      => 'EMPLOYEES',
        column_name      => 'SALARY',
        policy_name      => 'redact_salary_policy',
        expression       => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_MANAGER''',
        policy_description => 'Mask salary for non-managers',
        function_type    => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVFVVVVFVV,VVV,FVV,VVV'
    );
END;
/

# Types de masquage:
# - FULL: Remplacer par zéros/espaces
# - PARTIAL: Masquer partie (ex: carte crédit ****1234)
# - RANDOM: Valeur aléatoire
# - REGEXP: Pattern personnalisé


[OK] BACKUP ET RECOVERY

# === MODES DE BACKUP ===

# ARCHIVELOG vs NOARCHIVELOG
──────────────────────────────

# Vérifier mode actuel
SELECT log_mode FROM v$database;

# Activer ARCHIVELOG (recommandé production)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

# POURQUOI ARCHIVELOG?
[OK] Point-in-time recovery
[OK] Backup à chaud (base ouverte)
[OK] Pas de perte de données

# NOARCHIVELOG:
[X] Recovery seulement au dernier backup
[X] Backup nécessite arrêt base
[OK] OK pour développement


# === RMAN (RECOVERY MANAGER) ===

# Outil officiel Oracle pour backup/recovery

# Se connecter à RMAN
────────────────────────

rman TARGET /
# Ou distant
rman TARGET sys/password@PROD_DB


# Backup complet (FULL)
────────────────────────

RMAN> BACKUP DATABASE;

# Avec compression
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

# Avec tag (pour identification)
RMAN> BACKUP DATABASE TAG 'WEEKLY_FULL_BACKUP';


# Backup incrémental (plus rapide)
────────────────────────────────────

# Niveau 0 (équivalent full)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

# Niveau 1 (seulement blocs modifiés depuis niveau 0)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;


# Backup tablespace spécifique
────────────────────────────────

RMAN> BACKUP TABLESPACE users, temp;


# Backup archive logs
──────────────────────

RMAN> BACKUP ARCHIVELOG ALL;

# Backup + suppression archives
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;


# Backup control file
──────────────────────

RMAN> BACKUP CURRENT CONTROLFILE;


# === STRATÉGIES DE BACKUP ===

# Configuration RMAN
──────────────────────

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';


# Script backup complet
────────────────────────

RUN {
    ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
    ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;
    
    BACKUP AS COMPRESSED BACKUPSET
        INCREMENTAL LEVEL 0
        DATABASE
        FORMAT '/backup/full_%d_%T_%s_%p.bkp'
        TAG 'FULL_BACKUP';
    
    BACKUP ARCHIVELOG ALL
        FORMAT '/backup/arch_%d_%T_%s_%p.arc'
        DELETE INPUT;
    
    BACKUP CURRENT CONTROLFILE
        FORMAT '/backup/cf_%d_%T_%s_%p.ctl';
    
    RELEASE CHANNEL ch1;
    RELEASE CHANNEL ch2;
}


# === RESTAURATION (RESTORE) ===

# Restaurer base complète
──────────────────────────

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '/backup/cf_ORCL_20241201_1_1.ctl';
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;


# Restaurer tablespace spécifique
───────────────────────────────────

ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;


# Restaurer table (Oracle 12c+)
────────────────────────────────

RMAN> RECOVER TABLE hr.employees
      UNTIL TIME "TO_DATE('2024-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')"
      AUXILIARY DESTINATION '/tmp/recover'
      REMAP TABLE hr.employees:employees_recovered;


# === FLASHBACK (RETOUR ARRIÈRE) ===

# Flashback Query (voir données passées)
──────────────────────────────────────────

SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE employee_id = 100;


# Flashback Table (annuler modifications)
───────────────────────────────────────────

-- Activer row movement
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- Flashback à timestamp
FLASHBACK TABLE employees TO TIMESTAMP 
    TO_TIMESTAMP('2024-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Flashback avant DROP
FLASHBACK TABLE employees TO BEFORE DROP;


# Flashback Database (recovery rapide)
────────────────────────────────────────

# Activer Flashback Database
ALTER DATABASE FLASHBACK ON;

# Configurer rétention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 4320;  -- Minutes (3 jours)

# Flashback database
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP 
    TO_TIMESTAMP('2024-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;


# Flashback Transaction (annuler transaction)
──────────────────────────────────────────────

BEGIN
    DBMS_FLASHBACK.TRANSACTION_BACKOUT (
        numtxns => 1,
        xids => XID_ARRAY('0A00150063000000')
    );
END;
/


# === DUPLICATION DE BASE (CLONAGE) ===

# Dupliquer base pour test/dev
────────────────────────────────

# Sur serveur cible
rman TARGET sys/password@PROD_DB AUXILIARY /

RMAN> DUPLICATE TARGET DATABASE TO TEST_DB
      FROM ACTIVE DATABASE
      NOFILENAMECHECK;


# === DATA PUMP (EXPORT/IMPORT) ===

# Export logique (niveau schéma/table)
────────────────────────────────────────

# Export schéma complet
expdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp SCHEMAS=hr

# Export tables spécifiques
expdp system/password DIRECTORY=dpump_dir DUMPFILE=employees.dmp TABLES=hr.employees,hr.departments

# Export requête
expdp system/password DIRECTORY=dpump_dir DUMPFILE=high_sal.dmp TABLES=hr.employees QUERY='WHERE salary > 10000'

# Export base complète
expdp system/password DIRECTORY=dpump_dir DUMPFILE=full_db.dmp FULL=Y


# Import logique
─────────────────

# Import schéma
impdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp SCHEMAS=hr

# Import avec remap (changer schéma/tablespace)
impdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp 
    REMAP_SCHEMA=hr:hr_dev 
    REMAP_TABLESPACE=users:users_dev

# Import table dans autre schéma
impdp system/password DIRECTORY=dpump_dir DUMPFILE=employees.dmp 
    TABLES=hr.employees 
    REMAP_TABLE=hr.employees:dev.employees_copy


# Créer directory pour Data Pump
──────────────────────────────────

CREATE DIRECTORY dpump_dir AS '/u01/app/oracle/dpump';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO hr;


# === BONNES PRATIQUES BACKUP ===

1. Stratégie 3-2-1
   [OK] 3 copies de données
   [OK] 2 médias différents
   [OK] 1 copie hors site

2. Tester régulièrement recovery
   [OK] Backup inutile si recovery impossible

3. ARCHIVELOG en production
   [OK] Point-in-time recovery

4. Backup incrémental quotidien
   [OK] Full hebdomadaire
   [OK] Archive logs fréquemment

5. Automatiser avec scripts
   [OK] Cron/Task Scheduler

6. Monitorer backups
   [OK] Alertes si échec

7. Documenter procédures
   [OK] RTO/RPO clairs

8. Crypter backups sensibles
   [OK] Protection données

9. Conserver archives longtemps
   [OK] Conformité réglementaire

10. Backup control file régulièrement
    [OK] Critical pour recovery


```
[OK] BACKUP ET RECOVERY (SAUVEGARDE ET RESTAURATION)

# === POURQUOI BACKUP/RECOVERY ? ===

[OK] Protection contre perte de données (panne matérielle, erreur humaine, corruption)
[OK] Conformité réglementaire (obligation légale de conserver données)
[OK] Disponibilité continue (minimiser downtime)
[OK] Test et développement (cloner environnements)

# TYPES DE PANNES:
1. Perte fichier données (data file)
2. Perte fichier control (control file)
3. Perte fichier redo log
4. Perte base de données complète
5. Erreur utilisateur (DROP TABLE, DELETE accidentel)


# === MODES DE SAUVEGARDE ===

# ARCHIVELOG vs NOARCHIVELOG
──────────────────────────────

NOARCHIVELOG Mode (défaut):
[X] Backup seulement quand base arrêtée (cold backup)
[X] Recovery seulement jusqu'au dernier backup
[X] Perte de données entre backups
[OK] Plus simple, moins d'espace disque

ARCHIVELOG Mode:
[OK] Backup pendant base active (hot backup)
[OK] Point-in-time recovery (restaurer à n'importe quel moment)
[OK] Perte de données minimale
[X] Nécessite plus d'espace disque
[OK] RECOMMANDÉ pour production


# Vérifier mode actuel
SELECT log_mode FROM v$database;

# Activer ARCHIVELOG mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


# === TYPES DE BACKUP ===

1. **Physical Backup** (sauvegarde fichiers physiques)
   - Cold Backup (base arrêtée)
   - Hot Backup (base active)
   - RMAN Backup (recommandé)

2. **Logical Backup** (export logique données)
   - Data Pump (expdp/impdp)
   - Export traditionnel (exp/imp)


# === RMAN (RECOVERY MANAGER) ===

RMAN = Outil Oracle pour backup/recovery
[OK] Backup incrémentiel
[OK] Compression
[OK] Validation automatique
[OK] Catalogue des backups
[OK] Pas de mode archive log required pour cold backup

# Se connecter à RMAN
rman target /
rman target sys/password@database

# Ou depuis SQL*Plus
RMAN TARGET sys/password@ORCL


# === BACKUP COMPLET (FULL BACKUP) ===

# Backup base complète
RMAN> BACKUP DATABASE;

# Backup avec compression
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

# Backup avec tag (étiquette)
RMAN> BACKUP DATABASE TAG 'weekly_full_backup';

# Backup vers emplacement spécifique
RMAN> BACKUP DATABASE FORMAT '/backup/db_%U.bkp';

# Variables FORMAT utiles:
# %U = Nom unique généré automatiquement
# %d = Nom base de données
# %t = Timestamp (YYYYMMDD)
# %s = Numéro backup set
# %p = Numéro piece


# === BACKUP INCRÉMENTIEL ===

# Backup différentiel level 0 (baseline, équivalent full)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

# Backup différentiel level 1 (seulement changements depuis level 0)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

# Backup cumulatif (changements depuis dernier level 0)
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

# POURQUOI incrémentiel ?
[OK] Backup plus rapide
[OK] Moins d'espace disque
[OK] Moins d'impact sur performance


# === BACKUP ARCHIVE LOGS ===

# Backup archive logs
RMAN> BACKUP ARCHIVELOG ALL;

# Backup archive logs + suppression après
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

# Backup base + archive logs
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# Backup archive logs récents
RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-7';


# === BACKUP TABLESPACE ===

# Backup tablespace spécifique
RMAN> BACKUP TABLESPACE users;

# Backup plusieurs tablespaces
RMAN> BACKUP TABLESPACE users, tools, example;


# === BACKUP DATAFILE ===

# Backup fichier de données spécifique
RMAN> BACKUP DATAFILE 4;

# Backup plusieurs datafiles
RMAN> BACKUP DATAFILE 4, 5, 6;


# === BACKUP CONTROL FILE ET SPFILE ===

# Backup control file
RMAN> BACKUP CURRENT CONTROLFILE;

# Backup spfile
RMAN> BACKUP SPFILE;

# Backup control file + spfile
RMAN> BACKUP CURRENT CONTROLFILE PLUS SPFILE;


# === SCRIPTS RMAN ===

# Créer script réutilisable
RMAN> CREATE SCRIPT weekly_backup {
    BACKUP INCREMENTAL LEVEL 0 DATABASE;
    BACKUP ARCHIVELOG ALL DELETE INPUT;
    BACKUP CURRENT CONTROLFILE;
    DELETE NOPROMPT OBSOLETE;
}

# Exécuter script
RMAN> RUN { EXECUTE SCRIPT weekly_backup; }

# Voir scripts
RMAN> LIST SCRIPT NAMES;

# Supprimer script
RMAN> DELETE SCRIPT weekly_backup;


# === VÉRIFICATION BACKUP ===

# Lister tous les backups
RMAN> LIST BACKUP;

# Lister backups base de données
RMAN> LIST BACKUP OF DATABASE;

# Lister backups d'un tablespace
RMAN> LIST BACKUP OF TABLESPACE users;

# Lister backups archive logs
RMAN> LIST BACKUP OF ARCHIVELOG ALL;

# Lister backups avec détails
RMAN> LIST BACKUP SUMMARY;

# Valider backup (vérifier intégrité)
RMAN> VALIDATE BACKUP;

# Restaurer (test sans appliquer)
RMAN> RESTORE DATABASE VALIDATE;


# === POLITIQUE DE RÉTENTION ===

# Configurer rétention (garder backups 7 jours)
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

# Ou basé sur nombre de backups
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

# Voir configuration
RMAN> SHOW ALL;

# Supprimer backups obsolètes
RMAN> DELETE OBSOLETE;

# Supprimer backups expirés (manquants)
RMAN> DELETE EXPIRED BACKUP;


# === RECOVERY (RESTAURATION) ===

# SCÉNARIOS DE RECOVERY:

# 1. COMPLETE RECOVERY (récupération complète)
──────────────────────────────────────────────

# Perte d'un datafile (base ouverte)
RMAN> SQL "ALTER DATABASE DATAFILE 4 OFFLINE";
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;
RMAN> SQL "ALTER DATABASE DATAFILE 4 ONLINE";


# 2. INCOMPLETE RECOVERY (point-in-time)
─────────────────────────────────────────

# Restaurer à une date/heure spécifique
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

RMAN> RUN {
    SET UNTIL TIME "TO_DATE('2024-12-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
}

ALTER DATABASE OPEN RESETLOGS;


# 3. RECOVERY BASE COMPLÈTE
────────────────────────────

# Base corrompue, restaurer tout
SHUTDOWN ABORT;
STARTUP NOMOUNT;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;


# 4. RECOVERY TABLESPACE
─────────────────────────

RMAN> SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL "ALTER TABLESPACE users ONLINE";


# 5. RECOVERY APRÈS DROP TABLE
───────────────────────────────

# Utiliser Flashback (Oracle 10g+)
FLASHBACK TABLE employees TO BEFORE DROP;

# Ou récupérer depuis recycleBin
SELECT * FROM recyclebin;
FLASHBACK TABLE "BIN$..." TO BEFORE DROP RENAME TO employees;


# === FLASHBACK TECHNOLOGY ===

# FLASHBACK TABLE (récupérer table supprimée ou modifiée)
──────────────────────────────────────────────────────────

# Activer row movement (requis)
ALTER TABLE employees ENABLE ROW MOVEMENT;

# Flashback à timestamp spécifique
FLASHBACK TABLE employees TO TIMESTAMP 
    TO_TIMESTAMP('2024-12-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS');

# Flashback à SCN (System Change Number)
FLASHBACK TABLE employees TO SCN 2894561;


# FLASHBACK QUERY (requêter données passées)
─────────────────────────────────────────────

# Voir données à une heure passée
SELECT * FROM employees 
AS OF TIMESTAMP TO_TIMESTAMP('2024-12-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE employee_id = 100;

# Comparer données actuelles vs passées
SELECT * FROM employees WHERE employee_id = 100
MINUS
SELECT * FROM employees 
AS OF TIMESTAMP TO_TIMESTAMP('2024-12-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE employee_id = 100;


# FLASHBACK DATABASE (restaurer base entière)
──────────────────────────────────────────────

# Activer flashback database (une fois)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

# Restaurer base à point antérieur
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP 
    TO_TIMESTAMP('2024-12-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

# Ou à SCN
FLASHBACK DATABASE TO SCN 2894561;


# === DATA PUMP BACKUP (LOGICAL) ===

# Export schéma complet
expdp hr/password DIRECTORY=backup_dir DUMPFILE=hr_backup_%U.dmp LOGFILE=hr_backup.log SCHEMAS=hr COMPRESSION=ALL

# Export base complète (DBA requis)
expdp system/password DIRECTORY=backup_dir DUMPFILE=full_backup_%U.dmp LOGFILE=full_backup.log FULL=Y PARALLEL=4

# Export avec cohérence (flashback)
expdp hr/password DIRECTORY=backup_dir DUMPFILE=hr_consistent.dmp FLASHBACK_TIME=SYSTIMESTAMP

# Import (restauration)
impdp hr/password DIRECTORY=backup_dir DUMPFILE=hr_backup_%U.dmp LOGFILE=hr_restore.log


# === COLD BACKUP (BACKUP À FROID) ===

# Backup manuel (copie fichiers)

# 1. Arrêter base
SHUTDOWN IMMEDIATE;

# 2. Copier fichiers
# - Data files (*.dbf)
# - Control files (*.ctl)
# - Redo logs (*.log)
# - Parameter file (init.ora or spfile)

# Linux/Unix
cp /u01/oradata/ORCL/*.dbf /backup/
cp /u01/oradata/ORCL/*.ctl /backup/
cp /u01/oradata/ORCL/*.log /backup/

# 3. Redémarrer base
STARTUP;


# === HOT BACKUP (BACKUP À CHAUD) ===

# Backup manuel en mode ARCHIVELOG

# 1. Mettre tablespace en backup mode
ALTER TABLESPACE users BEGIN BACKUP;

# 2. Copier datafiles
# cp /u01/oradata/ORCL/users01.dbf /backup/

# 3. Terminer backup mode
ALTER TABLESPACE users END BACKUP;

# 4. Backup control file
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control.bkp';

# 5. Backup archive logs
# Copier archive logs vers backup


# === STRATÉGIE DE BACKUP COMPLÈTE ===

# Script RMAN automatisé
──────────────────────────

#!/bin/bash
# daily_backup.sh

export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Backup avec RMAN
rman target / << EOF
RUN {
    # Backup incrémentiel quotidien
    BACKUP INCREMENTAL LEVEL 1 DATABASE;
    
    # Backup archive logs
    BACKUP ARCHIVELOG ALL DELETE INPUT;
    
    # Backup control file
    BACKUP CURRENT CONTROLFILE;
    
    # Supprimer backups obsolètes
    DELETE NOPROMPT OBSOLETE;
    
    # Valider backups récents
    VALIDATE BACKUPSET COMPLETED AFTER 'SYSDATE-1';
}
EXIT;
EOF

# Vérifier succès
if [ $? -eq 0 ]; then
    echo "Backup réussi - $(date)" >> /var/log/oracle_backup.log
else
    echo "Backup échoué - $(date)" >> /var/log/oracle_backup.log
    # Envoyer alerte email
    mail -s "ALERTE: Backup Oracle échoué" admin@company.com < /dev/null
fi


# === PLANIFICATION BACKUPS ===

# Stratégie 3-2-1:
─────────────────
3 copies de données (production + 2 backups)
2 types de média différents (disque + bande)
1 copie off-site (hors site)

# Calendrier recommandé:
────────────────────────
Dimanche:    Full backup (level 0)
Lundi-Samedi: Incrémental backup (level 1)
Quotidien:   Archive logs backup
Horaire:     Archive logs backup (toutes les 4h)


# Automatisation avec cron (Linux)
──────────────────────────────────

# Éditer crontab
crontab -e

# Backup complet dimanche 2h du matin
0 2 * * 0 /scripts/weekly_full_backup.sh

# Backup incrémentiel lundi-samedi 2h du matin
0 2 * * 1-6 /scripts/daily_incremental_backup.sh

# Backup archive logs toutes les 4h
0 */4 * * * /scripts/archivelog_backup.sh


# === MONITORING BACKUPS ===

# Voir derniers backups
SELECT 
    session_key,
    input_type,
    status,
    start_time,
    end_time,
    elapsed_seconds,
    output_bytes/1024/1024/1024 AS output_gb
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;

# Voir espace utilisé par backups
SELECT 
    device_type,
    SUM(bytes)/1024/1024/1024 AS total_gb
FROM v$backup_piece
GROUP BY device_type;

# Alertes backup échoués
SELECT 
    session_key,
    operation,
    status,
    mbytes_processed,
    start_time,
    end_time
FROM v$rman_backup_job_details
WHERE status != 'COMPLETED'
AND start_time > SYSDATE - 1;


# === BONNES PRATIQUES BACKUP/RECOVERY ===

1. **Toujours mode ARCHIVELOG en production**
   [OK] Point-in-time recovery
   [OK] Hot backup possible

2. **Tester régulièrement restauration**
   [OK] Backup inutile si recovery échoue
   [OK] Test mensuel minimum

3. **Automatiser backups**
   [OK] Cron/Scheduler
   [OK] Scripts RMAN
   [OK] Monitoring automatique

4. **Multiplexer control files**
   [OK] 3+ copies sur disques différents
   CONTROL_FILES = ('/disk1/control01.ctl', '/disk2/control02.ctl', '/disk3/control03.ctl')

5. **Multiplexer redo logs**
   [OK] 2+ membres par groupe
   [OK] Disques différents

6. **Conserver backups off-site**
   [OK] Protection contre sinistre site principal
   [OK] Cloud, datacenter secondaire

7. **Valider backups régulièrement**
   RMAN> VALIDATE BACKUP;

8. **Documenter procédures recovery**
   [OK] Runbook détaillé
   [OK] Test avec équipe

9. **Monitorer espace disque**
   [OK] Archive logs peuvent remplir disque rapidement
   [OK] Alertes automatiques

10. **Politique rétention claire**
    [OK] Conformité réglementaire
    [OK] Équilibre coût/protection


[OK] SÉCURITÉ AVANCÉE

# === PRINCIPE DE SÉCURITÉ ORACLE ===

Sécurité en profondeur (Defense in Depth):
1. Authentification - Qui êtes-vous ?
2. Autorisation - Que pouvez-vous faire ?
3. Audit - Qu'avez-vous fait ?
4. Cryptage - Protection données sensibles


# === AUTHENTIFICATION ===

# Méthodes d'authentification:
──────────────────────────────

1. **Mot de passe base de données** (défaut)
2. **Authentification OS**
3. **Authentification externe** (LDAP, Active Directory)
4. **Authentification réseau** (Kerberos)
5. **Multi-facteur** (Oracle Advanced Security)


# Politique de mot de passe
────────────────────────────

CREATE PROFILE secure_profile LIMIT
    -- Complexité
    PASSWORD_LIFE_TIME 90              -- Expiration 90 jours
    PASSWORD_GRACE_TIME 7              -- Période grâce
    PASSWORD_REUSE_TIME 365            -- Réutilisation après 1 an
    PASSWORD_REUSE_MAX 5               -- Max 5 réutilisations
    
    -- Verrouillage compte
    FAILED_LOGIN_ATTEMPTS 3            -- Max 3 échecs
    PASSWORD_LOCK_TIME 1               -- Verrouillage 1 jour
    
    -- Fonction de vérification (complexité)
    PASSWORD_VERIFY_FUNCTION verify_password_function;

# Appliquer profil
ALTER USER john_doe PROFILE secure_profile;


# Fonction de vérification mot de passe
────────────────────────────────────────

CREATE OR REPLACE FUNCTION verify_password_function(
    username VARCHAR2,
    password VARCHAR2,
    old_password VARCHAR2
) RETURN BOOLEAN IS
    n BOOLEAN;
    m INTEGER;
    differ INTEGER;
BEGIN
    -- Longueur minimum
    IF LENGTH(password) < 8 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Mot de passe doit avoir 8+ caractères');
    END IF;
    
    -- Contenir majuscule
    IF NOT REGEXP_LIKE(password, '[A-Z]') THEN
        RAISE_APPLICATION_ERROR(-20002, 'Mot de passe doit contenir majuscule');
    END IF;
    
    -- Contenir minuscule
    IF NOT REGEXP_LIKE(password, '[a-z]') THEN
        RAISE_APPLICATION_ERROR(-20003, 'Mot de passe doit contenir minuscule');
    END IF;
    
    -- Contenir chiffre
    IF NOT REGEXP_LIKE(password, '[0-9]') THEN
        RAISE_APPLICATION_ERROR(-20004, 'Mot de passe doit contenir chiffre');
    END IF;
    
    -- Contenir caractère spécial
    IF NOT REGEXP_LIKE(password, '[^A-Za-z0-9]') THEN
        RAISE_APPLICATION_ERROR(-20005, 'Mot de passe doit contenir caractère spécial');
    END IF;
    
    -- Ne pas contenir nom utilisateur
    IF INSTR(UPPER(password), UPPER(username)) > 0 THEN
        RAISE_APPLICATION_ERROR(-20006, 'Mot de passe ne peut contenir nom utilisateur');
    END IF;
    
    RETURN TRUE;
END;
/


# === PRINCIPE DU MOINDRE PRIVILÈGE ===

# Accorder seulement privilèges nécessaires

# [X] MAUVAIS - Trop de privilèges
GRANT DBA TO app_user;

# [OK] BON - Privilèges minimaux
GRANT CREATE SESSION TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;
GRANT EXECUTE ON hr.employee_pkg TO app_user;


# === RÔLES PERSONNALISÉS ===

# Créer rôle pour application
CREATE ROLE app_read_role;
CREATE ROLE app_write_role;
CREATE ROLE app_admin_role;

# Accorder privilèges aux rôles
GRANT SELECT ON hr.employees TO app_read_role;
GRANT SELECT ON hr.departments TO app_read_role;

GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_write_role;
GRANT SELECT, INSERT, UPDATE ON hr.departments TO app_write_role;

GRANT ALL ON hr.employees TO app_admin_role;
GRANT ALL ON hr.departments TO app_admin_role;

# Accorder rôles aux utilisateurs
GRANT app_read_role TO user_readonly;
GRANT app_read_role, app_write_role TO user_standard;
GRANT app_admin_role TO user_admin;


# === CRYPTAGE DONNÉES ===

# Transparent Data Encryption (TDE)
─────────────────────────────────────

# Activer TDE (une fois)
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "MasterKeyPassword";

# Crypter tablespace
CREATE TABLESPACE secure_data
DATAFILE '/u01/oradata/ORCL/secure_data01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

# Crypter colonne spécifique
ALTER TABLE employees MODIFY (
    salary NUMBER ENCRYPT USING 'AES256' NO SALT
);

# POURQUOI TDE ?
[OK] Cryptage transparent (applications ne voient aucun changement)
[OK] Protection données au repos
[OK] Conformité (GDPR, HIPAA, PCI-DSS)


# Cryptage réseau
──────────────────

# Configuration sqlnet.ora
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA384, SHA512)


# === VIRTUAL PRIVATE DATABASE (VPD) ===

# Row-level security - Filtrer lignes automatiquement

# Fonction de politique
CREATE OR REPLACE FUNCTION employee_security_policy(
    schema_var IN VARCHAR2,
    table_var IN VARCHAR2
) RETURN VARCHAR2 IS
    v_predicate VARCHAR2(2000);
BEGIN
    -- Utilisateur voit seulement son département
    v_predicate := 'department_id = (SELECT department_id FROM employees WHERE username = USER)';
    RETURN v_predicate;
END;
/

# Appliquer politique
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'emp_dept_policy',
        function_schema => 'HR',
        policy_function => 'employee_security_policy',
        statement_types => 'SELECT, INSERT, UPDATE, DELETE'
    );
END;
/

# Maintenant chaque utilisateur voit seulement employés de son département!


# === REDACTION (MASQUAGE DONNÉES) ===

# Masquer données sensibles

# Masquage complet
BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'redact_salary',
        column_name     => 'SALARY',
        function_type   => DBMS_REDACT.FULL,
        expression      => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_MANAGER'''
    );
END;
/

# Masquage partiel (ex: numéro carte crédit)
BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema   => 'SALES',
        object_name     => 'CREDIT_CARDS',
        policy_name     => 'redact_cc_number',
        column_name     => 'CARD_NUMBER',
        function_type   => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVFVVVVVVVV,VVVV-FVVV-FVVVV-VVVV,X,1,12',
        expression      => '1=1'
    );
END;
/
-- Résultat: 1234-5678-9012-3456 -> XXXX-XXXX-XXXX-3456


# === AUDIT ===

# Unified Audit (Oracle 12c+)
──────────────────────────────

# Activer audit unifié
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
@?/rdbms/admin/cataudit.sql
SHUTDOWN IMMEDIATE;
STARTUP;

# Créer politique audit
CREATE AUDIT POLICY sensitive_data_access
ACTIONS SELECT ON hr.employees,
        UPDATE ON hr.employees,
        DELETE ON hr.employees;

# Activer politique
AUDIT POLICY sensitive_data_access;

# Audit connexions
AUDIT CREATE SESSION;

# Audit privilèges système
AUDIT CREATE TABLE, DROP TABLE;

# Voir audits
SELECT 
    event_timestamp,
    dbusername,
    action_name,
    object_name,
    sql_text
FROM unified_audit_trail
WHERE event_timestamp > SYSDATE - 1
ORDER BY event_timestamp DESC;


# Audit traditionnel (pré-12c)
───────────────────────────────

# Activer audit
ALTER SYSTEM SET AUDIT_TRAIL = DB, EXTENDED SCOPE=SPFILE;
-- Redémarrer base

# Auditer table
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;

# Auditer privilège
AUDIT CREATE TABLE BY hr BY ACCESS;

# Voir audits
SELECT 
    timestamp,
    username,
    action_name,
    obj_name,
    sql_text
FROM dba_audit_trail
WHERE timestamp > SYSDATE - 1;


# === FINE-GRAINED AUDIT (FGA) ===

# Audit conditionnel avec détails

BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'audit_salary_access',
        audit_condition => 'SALARY > 100000',
        audit_column    => 'SALARY',
        statement_types => 'SELECT'
    );
END;
/

# Voir résultats FGA
SELECT 
    timestamp,
    db_user,
    object_name,
    sql_text,
    sql_bind
FROM dba_fga_audit_trail;


# === SÉPARATION DES RESPONSABILITÉS ===

# Database Vault - Séparation admin DBA vs données

# Créer domaine (realm)
BEGIN
    DVSYS.DBMS_MACADM.CREATE_REALM(
        realm_name    => 'HR_DATA_REALM',
        description   => 'Protect HR data',
        enabled       => DBMS_MACUTL.G_YES,
        audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL
    );
END;
/

# Ajouter objets au domaine
BEGIN
    DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
        realm_name   => 'HR_DATA_REALM',
        object_owner => 'HR',
        object_name  => 'EMPLOYEES',
        object_type  => 'TABLE'
    );
END;
/

# Autoriser utilisateurs spécifiques
BEGIN
    DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
        realm_name => 'HR_DATA_REALM',
        grantee    => 'HR_MANAGER'
    );
END;
/

# Maintenant même DBA ne peut pas accéder données HR!


# === GESTION IDENTITÉS ET ACCÈS ===

# Intégration Active Directory/LDAP
─────────────────────────────────────

# Configuration ldap.ora
DIRECTORY_SERVERS = (ldap-server.company.com:389)
DEFAULT_ADMIN_CONTEXT = "dc=company,dc=com"
DIRECTORY_SERVER_TYPE = AD

# Authentification externe
CREATE USER john_doe IDENTIFIED EXTERNALLY;
GRANT CONNECT TO john_doe;


# === MONITORING SÉCURITÉ ===

# Connexions suspectes
SELECT 
    username,
    machine,
    COUNT(*) AS failed_attempts,
    MAX(timestamp) AS last_attempt
FROM dba_audit_session
WHERE returncode != 0
AND timestamp > SYSDATE - 1
GROUP BY username, machine
HAVING COUNT(*) > 5;

# Changements privilèges
SELECT 
    grantee,
    privilege,
    admin_option,
    timestamp
FROM dba_priv_audit_opts
WHERE timestamp > SYSDATE - 7;

# Accès données sensibles
SELECT 
    username,
    obj_name,
    action_name,
    COUNT(*) AS access_count
FROM dba_audit_trail
WHERE obj_name IN ('EMPLOYEES', 'SALARIES', 'CREDIT_CARDS')
AND timestamp > SYSDATE - 1
GROUP BY username, obj_name, action_name;


# === BONNES PRATIQUES SÉCURITÉ ===

1. **Politique mots de passe stricte**
   [OK] 12+ caractères
   [OK] Complexité
   [OK] Expiration 90 jours

2. **Principe moindre privilège**
   [OK] Accorder minimum nécessaire
   [OK] Utiliser rôles

3. **Crypter données sensibles**
   [OK] TDE pour données au repos
   [OK] SSL/TLS pour données en transit

4. **Activer audit**
   [OK] Tracer accès données sensibles
   [OK] Réviser logs régulièrement

5. **Séparation des responsabilités**
   [OK] DBA ≠ propriétaire données
   [OK] Database Vault

6. **Mettre à jour patches**
   [OK] Critical Patch Updates trimestriels
   [OK] Tester en dev d'abord

7. **Sauvegarder régulièrement**
   [OK] Protection contre ransomware
   [OK] Backups off-site

8. **Restreindre accès réseau**
   [OK] Firewall
   [OK] Listener password
   [OK] Valide_node_checking

9. **Monitorer activité anormale**
   [OK] Alertes automatiques
   [OK] SIEM integration

10. **Formation utilisateurs**
    [OK] Sensibilisation sécurité
    [OK] Phishing awareness


[OK] ADMINISTRATION AVANCÉE

# === TABLESPACES ===

# Créer tablespace
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

# Tablespace temporaire
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/oradata/ORCL/temp02.dbf' SIZE 50M;

# Ajouter datafile
ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oradata/ORCL/app_data02.dbf' SIZE 100M;

# Redimensionner datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' RESIZE 500M;

# Tablespace en lecture seule
ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_data READ WRITE;

# Mettre tablespace offline
ALTER TABLESPACE app_data OFFLINE;
ALTER TABLESPACE app_data ONLINE;

# Supprimer tablespace
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;


# === GESTION MÉMOIRE ===

# Automatic Memory Management (AMM)
MEMORY_TARGET = 2G
MEMORY_MAX_TARGET = 4G

# Ou gestion manuelle SGA/PGA
SGA_TARGET = 1536M
SGA_MAX_SIZE = 2G
PGA_AGGREGATE_TARGET = 512M

# Voir utilisation mémoire
SELECT 
    component,
    current_size/1024/1024 AS current_mb,
    min_size/1024/1024 AS min_mb,
    max_size/1024/1024 AS max_mb
FROM v$memory_dynamic_components;


# === REDO LOGS ===

# Ajouter groupe redo log
ALTER DATABASE ADD LOGFILE GROUP 4 
('/u01/oradata/ORCL/redo04a.log', '/u02/oradata/ORCL/redo04b.log') 
SIZE 100M;

# Ajouter membre à groupe existant
ALTER DATABASE ADD LOGFILE MEMBER
'/u03/oradata/ORCL/redo01c.log' TO GROUP 1;

# Switch redo log manuel
ALTER SYSTEM SWITCH LOGFILE;

# Voir status redo logs
SELECT 
    group#,
    thread#,
    sequence#,
    bytes/1024/1024 AS size_mb,
    members,
    status
FROM v$log;


# === SESSIONS ET PROCESSUS ===

# Voir sessions actives
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    s.logon_time,
    sq.sql_text
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.username IS NOT NULL;

# Tuer session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

# Voir processus Oracle
SELECT 
    spid,
    program,
    pga_used_mem/1024/1024 AS pga_mb,
    pga_alloc_mem/1024/1024 AS pga_alloc_mb
FROM v$process
ORDER BY pga_used_mem DESC;


# === PERFORMANCE TUNING ===

# AWR (Automatic Workload Repository)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

# Générer rapport AWR
@?/rdbms/admin/awrrpt.sql

# ASH (Active Session History)
SELECT 
    session_id,
    session_serial#,
    sql_id,
    event,
    wait_class,
    wait_time,
    time_waited
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '1' HOUR;

# Top SQL consommateurs
SELECT 
    sql_id,
    executions,
    elapsed_time/1000000 AS elapsed_sec,
    cpu_time/1000000 AS cpu_sec,
    disk_reads,
    buffer_gets,
    SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

```
[OK] RAC (REAL APPLICATION CLUSTERS)

# === QU'EST-CE QUE RAC ? ===

RAC = Plusieurs instances Oracle accédant à la MÊME base de données
[OK] Haute disponibilité (High Availability)
[OK] Scalabilité horizontale (ajouter serveurs)
[OK] Load balancing automatique
[OK] Pas de Single Point of Failure (SPOF)

# ARCHITECTURE RAC:

┌─────────────────┐         ┌─────────────────┐
│   SERVEUR 1     │         │   SERVEUR 2     │
│                 │         │                 │
│  Instance 1     │         │  Instance 2     │
│  (SGA + Proc)   │         │  (SGA + Proc)   │
└────────┬────────┘         └────────┬────────┘
         │                           │
         │    Interconnect privé     │
         │   (Cache Fusion)          │
         └───────────┬───────────────┘
                     │
         ┌───────────[BLACK_DOWN-POINTING_TRIANGLE]────────────┐
         │   STOCKAGE PARTAGÉ     │
         │   (ASM/SAN)            │
         │                        │
         │  - Data files          │
         │  - Control files       │
         │  - Redo logs           │
         │  - Archive logs        │
         └────────────────────────┘

# POURQUOI RAC ?
[OK] Zéro downtime - Si serveur 1 tombe, serveur 2 continue
[OK] Performance - Distribuer charge sur plusieurs serveurs
[OK] Maintenance - Patcher serveurs un par un sans arrêt
[OK] Elasticité - Ajouter/retirer nœuds à chaud


# === COMPOSANTS RAC ===

1. **Instances multiples** - Une par serveur
2. **Stockage partagé** - ASM ou SAN
3. **Interconnect privé** - Réseau haute vitesse entre nœuds
4. **Voting Disk** - Arbitrage en cas de split-brain
5. **OCR (Oracle Cluster Registry)** - Métadonnées cluster
6. **Cache Fusion** - Synchronisation cache entre instances


# === VÉRIFIER ENVIRONNEMENT RAC ===

# Vérifier si RAC activé
SELECT parallel, instance_name FROM v$instance;

# Voir tous les nœuds du cluster
SELECT 
    inst_id,
    instance_name,
    host_name,
    status,
    startup_time
FROM gv$instance
ORDER BY inst_id;

# Voir services RAC
SELECT 
    name,
    network_name,
    failover_type,
    failover_method
FROM dba_services;


# === GESTION CLUSTER (CRSCTL) ===

# Voir status cluster
crsctl check cluster -all

# Status CRS
crsctl stat res -t

# Démarrer CRS
crsctl start crs

# Arrêter CRS
crsctl stop crs

# Redémarrer nœud
crsctl stop cluster -n node1
crsctl start cluster -n node1


# === GESTION INSTANCES RAC (SRVCTL) ===

# Voir status base de données
srvctl status database -d ORCL

# Démarrer base
srvctl start database -d ORCL

# Arrêter base
srvctl stop database -d ORCL

# Démarrer instance spécifique
srvctl start instance -d ORCL -i ORCL1

# Arrêter instance spécifique
srvctl stop instance -d ORCL -i ORCL1

# Voir configuration base
srvctl config database -d ORCL

# Ajouter instance au cluster
srvctl add instance -d ORCL -i ORCL3 -n node3


# === SERVICES RAC ===

# Créer service applicatif
srvctl add service -d ORCL -s app_service -r ORCL1,ORCL2 -P BASIC

# Démarrer service
srvctl start service -d ORCL -s app_service

# Arrêter service
srvctl stop service -d ORCL -s app_service

# Modifier service (ajouter instance)
srvctl modify service -d ORCL -s app_service -i ORCL1,ORCL2,ORCL3

# Status service
srvctl status service -d ORCL -s app_service


# === CONNEXION CLIENT RAC ===

# Connection string avec failover
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = on)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = app_service)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )


# === CACHE FUSION ===

# Cache Fusion = Transfert de blocs entre instances via interconnect

# Voir statistiques Cache Fusion
SELECT 
    name,
    value
FROM gv$sysstat
WHERE name LIKE '%gc%'
ORDER BY inst_id, name;

# Voir blocs transférés
SELECT 
    inst_id,
    block_gets,
    consistent_gets,
    physical_reads,
    gc_current_blocks_received,
    gc_cr_blocks_received
FROM gv$sysstat;


# === ASM (AUTOMATIC STORAGE MANAGEMENT) ===

# ASM = Gestionnaire volumes Oracle pour RAC

# Se connecter à ASM
sqlplus / as sysasm

# Voir disk groups
SELECT name, total_mb, free_mb, state FROM v$asm_diskgroup;

# Créer disk group
CREATE DISKGROUP data_dg EXTERNAL REDUNDANCY
DISK '/dev/oracleasm/disk1',
     '/dev/oracleasm/disk2',
     '/dev/oracleasm/disk3';

# Ajouter disque à disk group
ALTER DISKGROUP data_dg ADD DISK '/dev/oracleasm/disk4';

# Supprimer disque
ALTER DISKGROUP data_dg DROP DISK disk4;

# Voir fichiers ASM
SELECT name, type, bytes/1024/1024 AS mb FROM v$asm_file;


# === MONITORING RAC ===

# Performance par instance
SELECT 
    inst_id,
    name,
    value
FROM gv$sysstat
WHERE name IN ('user commits', 'physical reads', 'db block gets')
ORDER BY inst_id, name;

# Sessions par instance
SELECT 
    inst_id,
    COUNT(*) AS session_count
FROM gv$session
WHERE username IS NOT NULL
GROUP BY inst_id;

# Workload par instance
SELECT 
    inst_id,
    SUM(executions) AS total_executions,
    SUM(cpu_time)/1000000 AS total_cpu_sec
FROM gv$sql
GROUP BY inst_id;


# === LOAD BALANCING ===

# RAC distribue charge automatiquement

# Politique load balancing dans service
BEGIN
    DBMS_SERVICE.MODIFY_SERVICE(
        service_name => 'app_service',
        goal => DBMS_SERVICE.GOAL_THROUGHPUT,
        clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
    );
END;
/

# Types de load balancing:
# - Client-side (dans connection string)
# - Server-side (géré par RAC)


# === FAILOVER AUTOMATIQUE ===

# TAF (Transparent Application Failover)

# Configuration service avec TAF
BEGIN
    DBMS_SERVICE.CREATE_SERVICE(
        service_name => 'app_service_taf',
        network_name => 'app_service_taf',
        failover_type => 'SELECT',
        failover_method => 'BASIC',
        failover_retries => 180,
        failover_delay => 5
    );
END;
/

# Types de failover:
# - SESSION: Reconnexion automatique
# - SELECT: Requêtes SELECT continuent sans interruption
# - TRANSACTION: Transactions actives préservées (Oracle 12.2+)


# === MAINTENANCE RAC ===

# Rolling Patch (pas de downtime)

# 1. Patcher nœud 1 (instances sur nœud 2 continuent)
srvctl stop instance -d ORCL -i ORCL1
# Appliquer patch...
srvctl start instance -d ORCL -i ORCL1

# 2. Patcher nœud 2
srvctl stop instance -d ORCL -i ORCL2
# Appliquer patch...
srvctl start instance -d ORCL -i ORCL2


# === BONNES PRATIQUES RAC ===

1. **Interconnect dédié haute vitesse**
   [OK] 10 GbE minimum
   [OK] Réseau privé séparé

2. **Redondance voting disk et OCR**
   [OK] Minimum 3 voting disks
   [OK] OCR multiplexé

3. **Services applicatifs**
   [OK] Ne pas connecter directement à instance
   [OK] Utiliser services pour failover

4. **Monitoring interconnect**
   [OK] Latence < 1ms critique
   [OK] Alertes sur congestion

5. **Partitionner charges**
   [OK] Services différents par type workload
   [OK] Batch vs OLTP

6. **Tester failover régulièrement**
   [OK] Simuler panne nœud
   [OK] Valider TAF fonctionne


[OK] DATA GUARD (DISASTER RECOVERY)

# === QU'EST-CE QUE DATA GUARD ? ===

Data Guard = Solution haute disponibilité et disaster recovery Oracle
[OK] Base de données standby synchronisée avec primary
[OK] Protection contre sinistres (incendie, inondation, etc.)
[OK] Basculement automatique (failover)
[OK] Switchover planifié sans perte données

# ARCHITECTURE DATA GUARD:

┌────────────────────────┐         ┌────────────────────────┐
│   SITE PRIMAIRE        │         │   SITE STANDBY         │
│                        │         │                        │
│  Primary Database      │────────[BLACK_RIGHT-POINTING_TRIANGLE]│  Standby Database      │
│                        │  Redo   │  (Applique logs)       │
│  - Production          │  Ship   │  - Lecture seule       │
│  - Lecture/Écriture    │         │  - Prêt pour failover  │
│                        │         │                        │
└────────────────────────┘         └────────────────────────┘
     Applications                        Rapports/Backup


# TYPES DE STANDBY:

1. **Physical Standby**
   - Réplique identique bloc par bloc
   - Applique redo logs
   - Active Data Guard: Lecture + réplication simultanées

2. **Logical Standby**
   - Applique changements SQL logiques
   - Peut avoir structure différente
   - Moins utilisé


# === CONFIGURER DATA GUARD ===

# ÉTAPE 1: Préparer Primary Database
──────────────────────────────────────

# Activer ARCHIVELOG mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

# Activer FORCE LOGGING
ALTER DATABASE FORCE LOGGING;

# Configurer redo transport
ALTER SYSTEM SET log_archive_config='dg_config=(ORCL_PRIMARY,ORCL_STANDBY)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/arch1/ORCL/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORCL_STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STANDBY' SCOPE=BOTH;

# Paramètres standby
ALTER SYSTEM SET fal_server='ORCL_STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;


# ÉTAPE 2: Créer Standby Database
───────────────────────────────────

# Sur primary - Créer pfile
CREATE PFILE='/tmp/initORCL_STANDBY.ora' FROM SPFILE;

# Sur primary - Backup pour standby
RMAN TARGET /
BACKUP DATABASE PLUS ARCHIVELOG FORMAT '/backup/standby_%U';

# Transférer fichiers vers serveur standby
# - Backup files
# - Init file (modifié)

# Sur standby - Restaurer
RMAN TARGET /
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '/backup/standby_ctrl.bkp';
ALTER DATABASE MOUNT;
RESTORE DATABASE;


# ÉTAPE 3: Configurer Standby Database
────────────────────────────────────────

# Modifier paramètres standby
ALTER SYSTEM SET log_archive_config='dg_config=(ORCL_PRIMARY,ORCL_STANDBY)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/arch1/ORCL_STANDBY/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET fal_server='ORCL_PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET db_unique_name='ORCL_STANDBY' SCOPE=SPFILE;

# Démarrer apply (redo)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

# Ou en mode real-time apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


# === MONITORING DATA GUARD ===

# Status configuration (sur primary)
SELECT dest_id, status, error FROM v$archive_dest WHERE status != 'INACTIVE';

# Gap analysis (logs manquants)
SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;

# Lag entre primary et standby
SELECT name, value, unit, time_computed FROM v$dataguard_stats;

# Status MRP (apply process)
SELECT process, status, thread#, sequence#, block#, blocks FROM v$managed_standby;


# === DATA GUARD BROKER ===

# Broker = Interface de gestion Data Guard

# Activer broker sur primary et standby
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

# Connecter avec DGMGRL
dgmgrl sys/password@ORCL_PRIMARY

# Créer configuration
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS ORCL_PRIMARY CONNECT IDENTIFIER IS ORCL_PRIMARY;

# Ajouter standby
DGMGRL> ADD DATABASE ORCL_STANDBY AS CONNECT IDENTIFIER IS ORCL_STANDBY MAINTAINED AS PHYSICAL;

# Activer configuration
DGMGRL> ENABLE CONFIGURATION;

# Voir status
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE ORCL_PRIMARY;
DGMGRL> SHOW DATABASE ORCL_STANDBY;


# === SWITCHOVER (BASCULEMENT PLANIFIÉ) ===

# Switchover = Changer primary <-> standby (sans perte données)

# Vérifier éligibilité switchover
DGMGRL> VALIDATE DATABASE ORCL_PRIMARY;
DGMGRL> VALIDATE DATABASE ORCL_STANDBY;

# Exécuter switchover
DGMGRL> SWITCHOVER TO ORCL_STANDBY;

# Après switchover:
# - ORCL_STANDBY devient primary
# - ORCL_PRIMARY devient standby


# === FAILOVER (BASCULEMENT D'URGENCE) ===

# Failover = Primary down, activer standby

# Vérifier lag
DGMGRL> SHOW DATABASE ORCL_STANDBY 'ApplyLag';

# Failover
DGMGRL> FAILOVER TO ORCL_STANDBY;

# Après failover:
# - ORCL_STANDBY devient primary
# - Ancien primary doit être reconstruit


# === ACTIVE DATA GUARD ===

# Standby en lecture seule pendant apply

# Ouvrir standby en READ ONLY
ALTER DATABASE OPEN READ ONLY;

# Continuer apply en mode real-time
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

# Maintenant:
# [OK] Standby applique redo
# [OK] Standby accepte requêtes SELECT
# [OK] Décharger rapports/analytics du primary


# === SNAPSHOT STANDBY ===

# Convertir standby en base modifiable (test)

# Activer snapshot mode
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;

# Maintenant standby modifiable (test, dev)
# Pas de réplication pendant ce temps

# Revenir en standby
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


# === FAR SYNC ===

# Far Sync = Instance intermédiaire pour distance

Primary (Paris) -> Far Sync (Londres) -> Standby (New York)

# POURQUOI Far Sync ?
[OK] Latence réduite pour primary (Paris -> Londres rapide)
[OK] Protection longue distance (Londres -> New York asynchrone)


# === BONNES PRATIQUES DATA GUARD ===

1. **Mode MAXIMUM AVAILABILITY**
   [OK] Équilibre protection/performance
   ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

2. **Tester switchover régulièrement**
   [OK] Valider procédures
   [OK] Formation équipe

3. **Monitoring actif**
   [OK] Alertes sur lag > 5 minutes
   [OK] Monitoring gap

4. **Active Data Guard**
   [OK] Rentabiliser standby
   [OK] Rapports, backups

5. **Réseau dédié**
   [OK] Bande passante suffisante
   [OK] Latence minimale

6. **Automatiser avec Broker**
   [OK] Fast-Start Failover
   [OK] Moins d'erreurs manuelles


[OK] PARTITIONNEMENT AVANCÉ

# === TYPES DE PARTITIONNEMENT ===

1. **RANGE** - Par plage de valeurs
2. **LIST** - Par liste de valeurs
3. **HASH** - Par hash
4. **COMPOSITE** - Combinaison (RANGE-HASH, LIST-RANGE, etc.)
5. **REFERENCE** - Hérite partition de table parent
6. **INTERVAL** - RANGE automatique
7. **SYSTEM** - Application contrôle


# === RANGE PARTITIONING ===

# Partition par dates
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01')
);


# === INTERVAL PARTITIONING ===

# Partitions créées automatiquement

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION sales_initial VALUES LESS THAN (DATE '2024-01-01')
);

# Oracle crée automatiquement partition pour janvier 2024, février 2024, etc.


# === LIST PARTITIONING ===

# Partition par pays
CREATE TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(100),
    country VARCHAR2(50)
)
PARTITION BY LIST (country) (
    PARTITION cust_us VALUES ('USA', 'Canada'),
    PARTITION cust_eu VALUES ('France', 'Germany', 'UK'),
    PARTITION cust_asia VALUES ('Japan', 'China', 'India'),
    PARTITION cust_other VALUES (DEFAULT)
);


# === HASH PARTITIONING ===

# Distribution uniforme
CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE
)
PARTITION BY HASH (customer_id)
PARTITIONS 8;

# Oracle nomme automatiquement: SYS_P1, SYS_P2, ...


# === COMPOSITE PARTITIONING ===

# RANGE-HASH
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 4
(
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01')
);


# === REFERENCE PARTITIONING ===

# Partition enfant hérite de parent

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_date DATE
)
PARTITION BY RANGE (order_date) (
    PARTITION orders_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION orders_2024 VALUES LESS THAN (DATE '2025-01-01')
);

CREATE TABLE order_items (
    item_id NUMBER PRIMARY KEY,
    order_id NUMBER NOT NULL,
    product_id NUMBER,
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE (fk_order);

# order_items automatiquement partitionné comme orders!


# === OPÉRATIONS SUR PARTITIONS ===

# Ajouter partition
ALTER TABLE sales ADD PARTITION sales_2025 VALUES LESS THAN (DATE '2026-01-01');

# Supprimer partition
ALTER TABLE sales DROP PARTITION sales_2022;

# Truncate partition (vider)
ALTER TABLE sales TRUNCATE PARTITION sales_2023;

# Split partition
ALTER TABLE sales SPLIT PARTITION sales_2024 AT (DATE '2024-07-01')
INTO (PARTITION sales_2024_h1, PARTITION sales_2024_h2);

# Merge partitions
ALTER TABLE sales MERGE PARTITIONS sales_2024_h1, sales_2024_h2 INTO PARTITION sales_2024;

# Move partition vers autre tablespace
ALTER TABLE sales MOVE PARTITION sales_2024 TABLESPACE new_tablespace;

# Exchange partition (swap avec table)
ALTER TABLE sales EXCHANGE PARTITION sales_2023 WITH TABLE sales_archive_2023;


# === PARTITION PRUNING ===

# Oracle scanne seulement partitions pertinentes

# Requête scanne seulement partition 2024
SELECT * FROM sales
WHERE sale_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';

# Voir plan d'exécution
EXPLAIN PLAN FOR
SELECT * FROM sales WHERE sale_date > DATE '2024-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# Résultat montre: PARTITION RANGE ITERATOR


# === INDEX SUR TABLES PARTITIONNÉES ===

# Index local (un index par partition)
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

# Index global (un index pour toute table)
CREATE INDEX idx_sales_amount ON sales(amount) GLOBAL;

# Index local préférable généralement


# === VOIR INFORMATIONS PARTITIONS ===

# Lister partitions
SELECT 
    table_name,
    partition_name,
    high_value,
    num_rows,
    tablespace_name
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

# Statistiques par partition
SELECT 
    partition_name,
    num_rows,
    blocks,
    avg_row_len
FROM user_tab_partitions
WHERE table_name = 'SALES';


# === BONNES PRATIQUES PARTITIONNEMENT ===

1. **Partitionner grandes tables**
   [OK] > 2 GB
   [OK] Millions de lignes

2. **Clé de partition dans WHERE**
   [OK] Bénéficier partition pruning
   [OK] Vérifier plans d'exécution

3. **Maintenance par partition**
   [OK] TRUNCATE vieilles partitions
   [OK] Archiver/supprimer facilement

4. **Interval partitioning pour dates**
   [OK] Automatique, moins maintenance

5. **Index locaux généralement**
   [OK] Maintenance parallèle
   [OK] Rebuild partition sans impact autres

6. **Collecter statistiques régulièrement**
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'SALES', GRANULARITY => 'PARTITION');


Voulez-vous que je continue avec d'autres sections (JSON/XML, Spatial, Multitenant Architecture, etc.) ?

[OK] JSON DANS ORACLE

# === SUPPORT JSON ORACLE ===

Oracle 12c+: Support natif JSON
Oracle 21c+: Type de données JSON natif

# POURQUOI JSON dans Oracle ?
[OK] Applications modernes (REST API, NoSQL-like)
[OK] Flexibilité schéma (schema-less)
[OK] Interopérabilité (JavaScript, APIs)
[OK] Validation automatique
[OK] Index et requêtes SQL


# === STOCKER JSON ===

# Avec VARCHAR2/CLOB (Oracle 12c+)
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_data CLOB,
    CONSTRAINT customer_data_json CHECK (customer_data IS JSON)
);

# Avec type JSON natif (Oracle 21c+)
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_data JSON
);

# Insérer données JSON
INSERT INTO customers VALUES (1, '{
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "New York",
        "country": "USA"
    },
    "orders": [
        {"order_id": 101, "amount": 250.00},
        {"order_id": 102, "amount": 175.50}
    ]
}');


# === REQUÊTER JSON (DOT NOTATION) ===

# Accéder champs simples
SELECT 
    c.customer_id,
    c.customer_data.name,
    c.customer_data.email,
    c.customer_data.age
FROM customers c;

# Accéder objets imbriqués
SELECT 
    c.customer_data.name,
    c.customer_data.address.city,
    c.customer_data.address.country
FROM customers c;

# Accéder éléments tableau
SELECT 
    c.customer_data.name,
    c.customer_data.orders[0].order_id,
    c.customer_data.orders[0].amount
FROM customers c;


# === JSON_VALUE (EXTRAIRE VALEUR SCALAIRE) ===

# Syntaxe
JSON_VALUE(json_column, '$.path' RETURNING type ERROR ON ERROR)

# Exemples
SELECT 
    JSON_VALUE(customer_data, '$.name') AS customer_name,
    JSON_VALUE(customer_data, '$.age' RETURNING NUMBER) AS age,
    JSON_VALUE(customer_data, '$.address.city') AS city
FROM customers;

# Avec valeur par défaut
SELECT 
    JSON_VALUE(customer_data, '$.phone' DEFAULT 'N/A' ON ERROR) AS phone
FROM customers;


# === JSON_QUERY (EXTRAIRE OBJET/TABLEAU) ===

# Extraire objet
SELECT 
    JSON_QUERY(customer_data, '$.address') AS address_json
FROM customers;

# Extraire tableau
SELECT 
    JSON_QUERY(customer_data, '$.orders') AS orders_json
FROM customers;


# === JSON_TABLE (CONVERTIR JSON EN LIGNES) ===

# Transformer JSON en table relationnelle
SELECT 
    c.customer_id,
    jt.*
FROM customers c,
JSON_TABLE(c.customer_data, '$'
    COLUMNS (
        name VARCHAR2(100) PATH '$.name',
        email VARCHAR2(100) PATH '$.email',
        age NUMBER PATH '$.age',
        city VARCHAR2(50) PATH '$.address.city',
        country VARCHAR2(50) PATH '$.address.country'
    )
) jt;

# Avec tableau imbriqué
SELECT 
    c.customer_id,
    c.customer_data.name AS customer_name,
    jt.*
FROM customers c,
JSON_TABLE(c.customer_data, '$.orders[*]'
    COLUMNS (
        order_id NUMBER PATH '$.order_id',
        amount NUMBER PATH '$.amount'
    )
) jt;


# === JSON_EXISTS (TESTER EXISTENCE) ===

# Vérifier si chemin existe
SELECT *
FROM customers
WHERE JSON_EXISTS(customer_data, '$.address.country');

# Avec condition
SELECT *
FROM customers
WHERE JSON_EXISTS(customer_data, '$.orders[*]?(@.amount > 200)');


# === MODIFIER JSON ===

# JSON_MERGEPATCH (Oracle 19c+)
UPDATE customers
SET customer_data = JSON_MERGEPATCH(
    customer_data,
    '{"phone": "+1-555-1234", "age": 31}'
)
WHERE customer_id = 1;

# JSON_TRANSFORM (Oracle 21c+)
UPDATE customers
SET customer_data = JSON_TRANSFORM(
    customer_data,
    SET '$.age' = 32,
    SET '$.phone' = '+1-555-5678',
    REMOVE '$.temporary_field'
)
WHERE customer_id = 1;


# === INDEX JSON ===

# Index fonctionnel
CREATE INDEX idx_customer_email ON customers(
    JSON_VALUE(customer_data, '$.email')
);

# Index composite
CREATE INDEX idx_customer_city_country ON customers(
    JSON_VALUE(customer_data, '$.address.city'),
    JSON_VALUE(customer_data, '$.address.country')
);

# JSON Search Index (Oracle 21c+) - Indexe tout le JSON
CREATE SEARCH INDEX idx_customer_json_search ON customers(customer_data)
FOR JSON;


# === VALIDATION JSON ===

# Contrainte IS JSON
ALTER TABLE customers ADD CONSTRAINT chk_json CHECK (customer_data IS JSON);

# Validation avec schéma JSON
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_data JSON,
    CONSTRAINT customer_schema CHECK (customer_data IS JSON VALIDATE USING '{
        "type": "object",
        "properties": {
            "name": {"type": "string"},
            "email": {"type": "string", "format": "email"},
            "age": {"type": "number", "minimum": 0}
        },
        "required": ["name", "email"]
    }')
);


# === GÉNÉRER JSON DEPUIS SQL ===

# JSON_OBJECT
SELECT JSON_OBJECT(
    'employee_id' VALUE employee_id,
    'name' VALUE first_name || ' ' || last_name,
    'salary' VALUE salary,
    'department' VALUE department_id
) AS employee_json
FROM employees;

# JSON_ARRAY
SELECT JSON_ARRAY(
    employee_id,
    first_name,
    last_name
) AS employee_array
FROM employees;

# JSON_OBJECTAGG (agrégation)
SELECT 
    department_id,
    JSON_OBJECTAGG(
        employee_id VALUE first_name || ' ' || last_name
    ) AS employees
FROM employees
GROUP BY department_id;

# JSON_ARRAYAGG (agrégation en tableau)
SELECT 
    department_id,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id' VALUE employee_id,
            'name' VALUE first_name || ' ' || last_name,
            'salary' VALUE salary
        )
        ORDER BY salary DESC
    ) AS employees
FROM employees
GROUP BY department_id;


# === EXEMPLES PRATIQUES ===

# EXEMPLE 1: API REST - Retourner JSON
───────────────────────────────────────

CREATE OR REPLACE FUNCTION get_customer_json(p_customer_id NUMBER)
RETURN CLOB IS
    v_json CLOB;
BEGIN
    SELECT JSON_OBJECT(
        'customer_id' VALUE c.customer_id,
        'name' VALUE c.customer_data.name,
        'email' VALUE c.customer_data.email,
        'orders' VALUE (
            SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                    'order_id' VALUE jt.order_id,
                    'amount' VALUE jt.amount,
                    'status' VALUE o.status
                )
                ORDER BY o.order_date DESC
            )
            FROM customers c2,
            JSON_TABLE(c2.customer_data, '$.orders[*]'
                COLUMNS (
                    order_id NUMBER PATH '$.order_id',
                    amount NUMBER PATH '$.amount'
                )
            ) jt
            JOIN orders o ON o.order_id = jt.order_id
            WHERE c2.customer_id = p_customer_id
        )
    )
    INTO v_json
    FROM customers c
    WHERE c.customer_id = p_customer_id;
    
    RETURN v_json;
END;
/


# EXEMPLE 2: Import JSON batch
────────────────────────────────

CREATE OR REPLACE PROCEDURE import_customers_json(p_json_array CLOB) IS
BEGIN
    INSERT INTO customers (customer_id, customer_data)
    SELECT 
        jt.customer_id,
        jt.customer_json
    FROM JSON_TABLE(p_json_array, '$[*]'
        COLUMNS (
            customer_id NUMBER PATH '$.customer_id',
            customer_json CLOB FORMAT JSON PATH '$'
        )
    ) jt;
    
    COMMIT;
END;
/


[OK] XML DANS ORACLE

# === SUPPORT XML ORACLE ===

Type de données: XMLType
[OK] Validation schéma XML (XSD)
[OK] XQuery, XPath
[OK] Index XML
[OK] Transformation XSLT


# === STOCKER XML ===

# Table avec XMLType
CREATE TABLE xml_documents (
    doc_id NUMBER PRIMARY KEY,
    doc_name VARCHAR2(100),
    xml_content XMLType
);

# Insérer XML
INSERT INTO xml_documents VALUES (1, 'customer_001', XMLType('
<customer>
    <customerId>1001</customerId>
    <name>John Doe</name>
    <email>john@example.com</email>
    <address>
        <street>123 Main St</street>
        <city>New York</city>
        <country>USA</country>
    </address>
    <orders>
        <order>
            <orderId>5001</orderId>
            <amount>250.00</amount>
        </order>
        <order>
            <orderId>5002</orderId>
            <amount>175.50</amount>
        </order>
    </orders>
</customer>
'));


# === REQUÊTER XML (XPATH) ===

# EXTRACTVALUE (Oracle 11g, déprécié mais encore utilisé)
SELECT 
    doc_name,
    EXTRACTVALUE(xml_content, '/customer/name') AS customer_name,
    EXTRACTVALUE(xml_content, '/customer/email') AS email
FROM xml_documents;

# XMLQuery (recommandé)
SELECT 
    doc_name,
    XMLQuery('/customer/name/text()' PASSING xml_content RETURNING CONTENT).getStringVal() AS name
FROM xml_documents;

# XMLTable (transformer XML en lignes)
SELECT 
    d.doc_id,
    xt.*
FROM xml_documents d,
XMLTable('/customer'
    PASSING d.xml_content
    COLUMNS
        customer_id NUMBER PATH 'customerId',
        name VARCHAR2(100) PATH 'name',
        email VARCHAR2(100) PATH 'email',
        city VARCHAR2(50) PATH 'address/city',
        country VARCHAR2(50) PATH 'address/country'
) xt;

# XMLTable avec éléments répétés
SELECT 
    d.doc_id,
    xt.*
FROM xml_documents d,
XMLTable('/customer/orders/order'
    PASSING d.xml_content
    COLUMNS
        order_id NUMBER PATH 'orderId',
        amount NUMBER PATH 'amount'
) xt;


# === MODIFIER XML ===

# UpdateXML
UPDATE xml_documents
SET xml_content = UpdateXML(
    xml_content,
    '/customer/email/text()',
    'newemail@example.com'
)
WHERE doc_id = 1;

# XMLQuery avec XSLT
UPDATE xml_documents
SET xml_content = XMLQuery(
    'copy $i := $doc modify (
        replace value of node $i/customer/email with $new_email
    ) return $i'
    PASSING xml_content AS "doc", 'updated@example.com' AS "new_email"
    RETURNING CONTENT
)
WHERE doc_id = 1;


# === INDEX XML ===

# Index sur chemin spécifique
CREATE INDEX idx_xml_customer_name ON xml_documents(
    XMLCast(XMLQuery('/customer/name/text()' PASSING xml_content RETURNING CONTENT) AS VARCHAR2(100))
);

# Index XML complet
CREATE INDEX idx_xml_full ON xml_documents(xml_content)
INDEXTYPE IS XDB.XMLIndex;


# === VALIDATION XML SCHEMA ===

# Enregistrer schéma XSD
BEGIN
    DBMS_XMLSCHEMA.registerSchema(
        schemaURL => 'http://example.com/customer.xsd',
        schemaDoc => '<?xml version="1.0"?>
        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
            <xs:element name="customer">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element name="customerId" type="xs:integer"/>
                        <xs:element name="name" type="xs:string"/>
                        <xs:element name="email" type="xs:string"/>
                    </xs:sequence>
                </xs:complexType>
            </xs:element>
        </xs:schema>',
        local => TRUE,
        genTypes => FALSE
    );
END;
/

# Table avec validation
CREATE TABLE validated_xml (
    doc_id NUMBER PRIMARY KEY,
    xml_content XMLType
) XMLType COLUMN xml_content XMLSCHEMA "http://example.com/customer.xsd" ELEMENT "customer";


[OK] ORACLE SPATIAL (DONNÉES GÉOGRAPHIQUES)

# === QU'EST-CE QUE SPATIAL ? ===

Oracle Spatial = Extension pour données géographiques
[OK] Points, lignes, polygones
[OK] Calculs géographiques (distance, intersection)
[OK] Index spatiaux (R-Tree)
[OK] Conformité standards (OGC, SQL/MM)


# === TYPE SDO_GEOMETRY ===

# Structure SDO_GEOMETRY:
SDO_GEOMETRY(
    SDO_GTYPE,          -- Type géométrie (2001=Point 2D, 2002=Ligne, 2003=Polygone)
    SDO_SRID,           -- Système référence spatial (ex: 4326=WGS84/GPS)
    SDO_POINT,          -- Point simple (x, y, z)
    SDO_ELEM_INFO,      -- Info éléments (triplets)
    SDO_ORDINATES       -- Coordonnées
)


# === CRÉER TABLE SPATIALE ===

CREATE TABLE restaurants (
    restaurant_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    location SDO_GEOMETRY
);

# Insérer point (latitude, longitude)
INSERT INTO restaurants VALUES (
    1,
    'Pizza Palace',
    SDO_GEOMETRY(
        2001,           -- Point 2D
        4326,           -- WGS84 (GPS)
        SDO_POINT_TYPE(
            -73.9857,   -- Longitude (X)
            40.7484,    -- Latitude (Y)
            NULL        -- Z
        ),
        NULL,
        NULL
    )
);

# Insérer ligne (route)
INSERT INTO routes VALUES (
    1,
    'Route 66',
    SDO_GEOMETRY(
        2002,           -- Ligne 2D
        4326,
        NULL,
        SDO_ELEM_INFO_ARRAY(1, 2, 1),  -- Ligne simple
        SDO_ORDINATES_ARRAY(
            -73.9857, 40.7484,  -- Point 1
            -73.9750, 40.7550,  -- Point 2
            -73.9650, 40.7600   -- Point 3
        )
    )
);

# Insérer polygone (zone)
INSERT INTO zones VALUES (
    1,
    'Delivery Zone',
    SDO_GEOMETRY(
        2003,           -- Polygone 2D
        4326,
        NULL,
        SDO_ELEM_INFO_ARRAY(1, 1003, 1),  -- Polygone extérieur
        SDO_ORDINATES_ARRAY(
            -74.0, 40.7,    -- Coin 1
            -73.9, 40.7,    -- Coin 2
            -73.9, 40.8,    -- Coin 3
            -74.0, 40.8,    -- Coin 4
            -74.0, 40.7     -- Retour coin 1 (fermé)
        )
    )
);


# === MÉTADONNÉES SPATIALES ===

# Enregistrer métadonnées (obligatoire pour index spatial)
INSERT INTO user_sdo_geom_metadata VALUES (
    'RESTAURANTS',              -- Table
    'LOCATION',                 -- Colonne géométrie
    SDO_DIM_ARRAY(
        SDO_DIM_ELEMENT('X', -180, 180, 0.005),  -- Longitude
        SDO_DIM_ELEMENT('Y', -90, 90, 0.005)     -- Latitude
    ),
    4326                        -- SRID (WGS84)
);

COMMIT;


# === INDEX SPATIAL ===

# Créer index R-Tree
CREATE INDEX idx_restaurants_location ON restaurants(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;


# === REQUÊTES SPATIALES ===

# SDO_GEOM.SDO_DISTANCE (calculer distance)
───────────────────────────────────────────

# Distance entre deux points (en mètres avec SRID 4326)
SELECT 
    r.name,
    SDO_GEOM.SDO_DISTANCE(
        r.location,
        SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.9850, 40.7500, NULL), NULL, NULL),
        0.005,
        'unit=KM'
    ) AS distance_km
FROM restaurants r
ORDER BY distance_km;


# SDO_WITHIN_DISTANCE (dans rayon)
────────────────────────────────────

# Restaurants dans rayon de 5km
SELECT name, location
FROM restaurants r
WHERE SDO_WITHIN_DISTANCE(
    r.location,
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.9850, 40.7500, NULL), NULL, NULL),
    'distance=5 unit=KM'
) = 'TRUE';


# SDO_RELATE (relations spatiales)
───────────────────────────────────

# Points dans polygone (INSIDE)
SELECT r.name
FROM restaurants r, zones z
WHERE z.zone_id = 1
AND SDO_RELATE(
    r.location,
    z.boundary,
    'mask=INSIDE'
) = 'TRUE';

# Autres masques:
# - TOUCH: Se touchent
# - OVERLAP: Se chevauchent
# - CONTAINS: Contient
# - COVERS: Recouvre


# SDO_NN (Nearest Neighbor - plus proches)
────────────────────────────────────────────

# 5 restaurants les plus proches
SELECT 
    name,
    SDO_NN_DISTANCE(1) AS distance_km
FROM restaurants r
WHERE SDO_NN(
    r.location,
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.9850, 40.7500, NULL), NULL, NULL),
    'sdo_num_res=5 unit=KM',
    1
) = 'TRUE'
ORDER BY distance_km;


# === FONCTIONS GÉOMÉTRIQUES ===

# SDO_GEOM.SDO_AREA (aire polygone)
SELECT SDO_GEOM.SDO_AREA(boundary, 0.005, 'unit=SQ_KM') AS area_km2
FROM zones;

# SDO_GEOM.SDO_LENGTH (longueur ligne)
SELECT SDO_GEOM.SDO_LENGTH(path, 0.005, 'unit=KM') AS length_km
FROM routes;

# SDO_GEOM.SDO_BUFFER (zone tampon)
SELECT SDO_GEOM.SDO_BUFFER(
    location,
    5,          -- 5 km
    0.005,
    'unit=KM'
) AS buffer_zone
FROM restaurants;

# SDO_GEOM.SDO_INTERSECTION (intersection géométries)
SELECT SDO_GEOM.SDO_INTERSECTION(
    z1.boundary,
    z2.boundary,
    0.005
) AS intersection
FROM zones z1, zones z2
WHERE z1.zone_id = 1 AND z2.zone_id = 2;


# === CONVERSION FORMATS ===

# WKT (Well-Known Text)
SELECT 
    name,
    SDO_UTIL.TO_WKTGEOMETRY(location) AS wkt
FROM restaurants;

# GeoJSON (Oracle 19c+)
SELECT 
    name,
    SDO_UTIL.TO_GEOJSON(location) AS geojson
FROM restaurants;


# === EXEMPLES PRATIQUES ===

# EXEMPLE 1: Application livraison
──────────────────────────────────

-- Trouver livreurs disponibles dans rayon 10km de commande
SELECT 
    d.driver_id,
    d.driver_name,
    SDO_GEOM.SDO_DISTANCE(
        d.current_location,
        o.delivery_address,
        0.005,
        'unit=KM'
    ) AS distance_km
FROM drivers d, orders o
WHERE o.order_id = :order_id
AND d.status = 'AVAILABLE'
AND SDO_WITHIN_DISTANCE(
    d.current_location,
    o.delivery_address,
    'distance=10 unit=KM'
) = 'TRUE'
ORDER BY distance_km
FETCH FIRST 5 ROWS ONLY;


# EXEMPLE 2: Analyse zones chalandise
───────────────────────────────────────

-- Clients dans zone de livraison
SELECT 
    c.customer_id,
    c.customer_name,
    s.store_name
FROM customers c, stores s
WHERE SDO_RELATE(
    c.address_location,
    s.delivery_zone,
    'mask=INSIDE'
) = 'TRUE';


[OK] MULTITENANT ARCHITECTURE (CDB/PDB)

# === QU'EST-CE QUE MULTITENANT ? ===

Architecture conteneur (Oracle 12c+)
[OK] CDB = Container Database (racine)
[OK] PDB = Pluggable Database (bases individuelles)
[OK] Consolidation - Plusieurs PDB dans un CDB
[OK] Isolation - PDB indépendantes

# ARCHITECTURE:

┌────────────────────────────────────────┐
│         CDB (Container Database)       │
│                                        │
│  ┌──────────────────────────────────┐ │
│  │  CDB$ROOT (Instance + Metadata)  │ │
│  └──────────────────────────────────┘ │
│                                        │
│  ┌──────────────────────────────────┐ │
│  │  PDB$SEED (Template)             │ │
│  └──────────────────────────────────┘ │
│                                        │
│  ┌──────────────────────────────────┐ │
│  │  PDB1 (App 1)                    │ │
│  └──────────────────────────────────┘ │
│                                        │
│  ┌──────────────────────────────────┐ │
│  │  PDB2 (App 2)                    │ │
│  └──────────────────────────────────┘ │
│                                        │
│  ┌──────────────────────────────────┐ │
│  │  PDB3 (App 3)                    │ │
│  └──────────────────────────────────┘ │
└────────────────────────────────────────┘

# POURQUOI Multitenant ?
[OK] Consolidation - Réduire nombre serveurs
[OK] Économie ressources - Processus partagés
[OK] Gestion simplifiée - Un CDB, plusieurs PDB
[OK] Clonage rapide - Dupliquer PDB en minutes
[OK] Portabilité - Déplacer PDB entre CDB


# === CRÉER CDB ===

# Lors de création base (dbca ou CREATE DATABASE)
CREATE DATABASE cdb1
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/pdbseed/', '/pdb1/');


# === CRÉER PDB ===

# Se connecter à CDB root
sqlplus / as sysdba

# Créer PDB depuis seed
CREATE PLUGGABLE DATABASE pdb1
ADMIN USER pdb1admin IDENTIFIED BY password
FILE_NAME_CONVERT = ('/pdbseed/', '/pdb1/');

# Ouvrir PDB
ALTER PLUGGABLE DATABASE pdb1 OPEN;

# Créer PDB depuis autre PDB (clone)
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT = ('/pdb1/', '/pdb2/');


# === GÉRER PDB ===

# Voir toutes les PDB
SELECT name, open_mode, restricted FROM v$pdbs;

# Ouvrir PDB
ALTER PLUGGABLE DATABASE pdb1 OPEN;

# Fermer PDB
ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

# Ouvrir toutes les PDB
ALTER PLUGGABLE DATABASE ALL OPEN;

# Sauvegarder état
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;
-- Réouverture automatique après restart CDB


# === SE CONNECTER À PDB ===

# Depuis SQL*Plus
sqlplus user/password@//localhost:1521/pdb1

# Ou changer session
ALTER SESSION SET CONTAINER = pdb1;

# Vérifier conteneur actuel
SHOW CON_NAME;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;


# === CLONER PDB ===

# Clone local (même CDB)
CREATE PLUGGABLE DATABASE pdb_clone FROM pdb1
FILE_NAME_CONVERT = ('/pdb1/', '/pdb_clone/');

# Clone depuis PDB distante
CREATE PLUGGABLE DATABASE pdb_remote
FROM pdb1@dblink_to_remote_cdb
FILE_NAME_CONVERT = ('/remote/pdb1/', '/local/pdb_remote/');


# === UNPLUGGING/PLUGGING PDB ===

# Unplug (détacher PDB)
ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/backup/pdb1.xml';

# Drop PDB (garder fichiers)
DROP PLUGGABLE DATABASE pdb1 KEEP DATAFILES;

# Plug (réattacher PDB)
CREATE PLUGGABLE DATABASE pdb1 USING '/backup/pdb1.xml'
FILE_NAME_CONVERT = ('/old/path/', '/new/path/');

# Ouvrir
ALTER PLUGGABLE DATABASE pdb1 OPEN;


# === SNAPSHOTS PDB ===

# Créer snapshot
CREATE PLUGGABLE DATABASE pdb_snapshot FROM pdb1 AS SNAPSHOT;

# Snapshot = clone très rapide (utilise copy-on-write)


# === REFRESH PDB ===

# PDB refreshable (réplique lecture seule)
CREATE PLUGGABLE DATABASE pdb_replica FROM pdb1@remote_dblink
REFRESH MODE MANUAL;

# Refresh manuel
ALTER PLUGGABLE DATABASE pdb_replica REFRESH;

# Refresh automatique
CREATE PLUGGABLE DATABASE pdb_replica FROM pdb1@remote_dblink
REFRESH MODE EVERY 60 MINUTES;


# === LIMITES RESSOURCES PDB ===

# Limiter CPU
ALTER PLUGGABLE DATABASE pdb1
SET DEFAULT TABLESPACE users
STORAGE (MAXSIZE 10G);

# Avec Resource Manager
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
    DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
        plan => 'DEFAULT_CDB_PLAN',
        pluggable_database => 'pdb1',
        shares => 3,
        utilization_limit => 50,  -- Max 50% CPU
        parallel_server_limit => 50
    );
    
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/


# === BONNES PRATIQUES MULTITENANT ===

1. **Une PDB par application**
   [OK] Isolation
   [OK] Portabilité

2. **Clonage pour dev/test**
   [OK] Snapshot rapide
   [OK] Environnements identiques

3. **Backup par PDB**
   RMAN> BACKUP PLUGGABLE DATABASE pdb1;

4. **Limites ressources**
   [OK] Éviter qu'une PDB monopolise ressources

5. **Services pour connexion**
   [OK] Ne pas se connecter directement à PDB
   [OK] Utiliser services pour failover


[OK] OUTILS ET UTILITAIRES

# === SQL*Plus ===

# Formater sortie
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP '|'
COLUMN column_name FORMAT A30

# Spool (sauvegarder sortie)
SPOOL output.txt
SELECT * FROM employees;
SPOOL OFF

# Exécuter script
@/path/to/script.sql

# Variables
DEFINE emp_id = 100
SELECT * FROM employees WHERE employee_id = &emp_id;


# === SQL*Loader ===

# Charger données CSV
LOAD DATA
INFILE 'data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(employee_id, first_name, last_name, email, hire_date DATE "YYYY-MM-DD");


# === SQLcl (SQL Developer Command Line) ===

# Moderne alternative à SQL*Plus
sql username/password@database

# Format JSON
SET SQLFORMAT JSON
SELECT * FROM employees;

# Export formats multiples
SET SQLFORMAT csv
SET SQLFORMAT xml
SET SQLFORMAT html


# === DBMS_OUTPUT ===

# Afficher messages
SET SERVEROUTPUT ON
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/


# === DBMS_STATS ===

# Collecter statistiques
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');


# === DBMS_JOB / DBMS_SCHEDULER ===

# Planifier jobs
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'nightly_backup',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'backup_pkg.full_backup',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2',
        enabled         => TRUE
    );
END;
/


[OK] BONNES PRATIQUES GÉNÉRALES ORACLE

1. **Nomenclature cohérente**
   [OK] Tables: pluriel (employees, orders)
   [OK] PK: table_pk
   [OK] FK: fk_table_reference
   [OK] Index: idx_table_column

2. **Toujours ARCHIVELOG en production**
   [OK] Point-in-time recovery
   [OK] Hot backup

3. **Backup automatisés testés**
   [OK] RMAN daily
   [OK] Test restore mensuel

4. **Monitoring proactif**
   [OK] AWR, ASH
   [OK] Alertes tablespace 85%
   [OK] Sessions bloquées

5. **Sécurité en couches**
   [OK] Authentification forte
   [OK] Cryptage TDE
   [OK] Audit activé
   [OK] Principe moindre privilège

6. **Performance**
   [OK] Index appropriés
   [OK] Statistiques à jour
   [OK] Partitionnement grandes tables

7. **Haute disponibilité**
   [OK] RAC ou Data Guard
   [OK] Tester failover

8. **Documentation**
   [OK] Schémas à jour
   [OK] Procédures recovery
   [OK] Runbooks

9. **Gestion changements**
   [OK] Scripts versionnés (Git)
   [OK] Tests en dev d'abord
   [OK] Rollback planifié

10. **Formation continue**
    [OK] Nouvelles fonctionnalités
    [OK] Certifications Oracle


Ce guide couvre maintenant l'ensemble des fonctionnalités principales d'Oracle Database, de l'installation basique aux fonctionnalités enterprise avancées (RAC, Data Guard, Multitenant, JSON, XML, Spatial, etc.). Chaque section explique le POURQUOI, le COMMENT et le QUAND utiliser chaque fonctionnalité, avec des exemples pratiques pour débutants.