Comment installer et utiliser Oracle Database en 2026

#!/bin/bash

docker pull gvenzl/oracle-xe:23-full

docker run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -e APP_USER=devuser \
  -e APP_USER_PASSWORD=DevPass123 \
  -e ORACLE_PASSWORD=SysPass123 \
  gvenzl/oracle-xe:23-full

# Attendre le démarrage (environ 2-3 min)
sleep 180

docker logs oracle-xe

# Connexion JDBC : jdbc:oracle:thin:@localhost:1521/XE?user=devuser&password=DevPass123

DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS departments CASCADE;

CREATE TABLE departments (
  dept_id NUMBER(4) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  dept_name VARCHAR2(50) NOT NULL,
  location VARCHAR2(100)
);

CREATE TABLE employees (
  emp_id NUMBER(6) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100) UNIQUE,
  hire_date DATE DEFAULT SYSDATE,
  salary NUMBER(8,2) CHECK (salary > 0),
  dept_id NUMBER(4),
  CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

COMMENT ON TABLE employees IS 'Table des employés avec contraintes';

CREATE OR REPLACE PROCEDURE audit_salary_update(
  p_emp_id IN NUMBER,
  p_new_salary IN NUMBER
)
IS
  v_old_salary NUMBER(8,2);
BEGIN
  SELECT salary INTO v_old_salary FROM employees WHERE emp_id = p_emp_id;
  
  IF p_new_salary > v_old_salary * 1.2 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Augmentation > 20% non autorisée');
  END IF;
  
  UPDATE employees SET salary = p_new_salary WHERE emp_id = p_emp_id;
  
  DBMS_OUTPUT.PUT_LINE('Salaire mis à jour: ' || p_new_salary || ' (ancien: ' || v_old_salary || ')');
  
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20002, 'Employé non trouvé');
END;
/

-- Test
EXEC audit_salary_update(1, 5000.00);

-- Trigger BEFORE INSERT pour audit
CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.email := LOWER(:NEW.email);
  :NEW.hire_date := SYSDATE;
  DBMS_OUTPUT.PUT_LINE('Nouvel employé: ' || :NEW.first_name);
END;
/

-- Index bitmap pour colonnes low-cardinality
CREATE BITMAP INDEX idx_dept_id ON employees(dept_id);

-- Partitionnement par range (Oracle Enterprise)
ALTER TABLE employees MODIFY PARTITION BY RANGE (hire_date) (
  PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p2025 VALUES LESS THAN (DATE '2026-01-01')
);

-- Vérifier
SELECT index_name, index_type FROM user_indexes WHERE table_name = 'EMPLOYEES';

-- Expliquer un plan d'exécution
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(d e) */ e.first_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Gather stats pour optimizer
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'EMPLOYEES');

-- Query avec hints
SELECT /*+ INDEX(e idx_dept_id) */ COUNT(*) FROM employees e WHERE dept_id = 1;