Syntaxe SQL et SQL*Plus - Administration Oracle

📊 GESTION DES TABLESPACES

1. Consulter le Tablespace par Défaut

sql

SELECT property_value
FROM database_properties
WHERE property_name='DEFAULT_PERMANENT_TABLESPACE';

2. Création d'un Tablespace Permanent

sql

-- Version complète avec EXTENT MANAGEMENT

CREATE [BIGFILE | SMALLFILE] TABLESPACE Ts_nom
DATAFILE 'nom_fichier' [SIZE valeur [K|M|G|T]]
AUTOEXTEND {OFF | ON NEXT valeur [K|M|G|T]}
[MAXSIZE {UNLIMITED | valeur [K|M|G|T]}]
EXTENT MANAGEMENT LOCAL {AUTOALLOCATE | UNIFORM [SIZE valeur [K|M|G|T]]}
SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}
[BLOCKSIZE valeur]
[LOGGING | NOLOGGING]
[FORCE LOGGING]
[ONLINE | OFFLINE];

sql

-- Version avec DEFAULT STORAGE

CREATE [BIGFILE | SMALLFILE] TABLESPACE Ts_nom
DATAFILE 'nom_fichier' [SIZE valeur [K|M|G|T]]
DEFAULT STORAGE (
INITIAL valeur [K|M|G|T]
NEXT valeur [K|M|G|T]
MINEXTENTS valeur
MAXEXTENTS valeur
);

Exemple pratique :

sql
 CREATE TABLESPACE data
DATAFILE 'E:\oracle\ora\[Link]' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

3. Définir un Tablespace par Défaut

sql

ALTER DATABASE DEFAULT TABLESPACE data;

4. Ajouter un Fichier de Données à un Tablespace

sql

ALTER TABLESPACE nom

ADD DATAFILE spécification_fichier_data [,...];

5. Modifier la Taille d'un Fichier de Données

Pour SMALLFILE tablespace :

sql

ALTER DATABASE
DATAFILE numéro_fichier[,...] RESIZE valeur [K|M|G|T];

Pour BIGFILE tablespace :

sql

ALTER TABLESPACE nom_tablespace_bigfile RESIZE valeur [K|M|G|T];

6. Allocation d'un Tablespace à un Objet

sql

CREATE TABLE ETUDIANT (

matricule NUMBER(8),
nom VARCHAR2(10)
) TABLESPACE data;
7. Mettre un Tablespace ONLINE/OFFLINE

sql

ALTER TABLESPACE nom ONLINE;

ALTER TABLESPACE nom OFFLINE;

8. Déplacer un Fichier de Données

sql

-- Étape 1: Mettre le tablespace OFFLINE

ALTER TABLESPACE nom OFFLINE;

-- Étape 2: Copier/Coller physiquement le fichier (OS)

-- Étape 3: Indiquer le nouvel emplacement

ALTER DATABASE RENAME FILE 'ancien_nom' TO 'nouveau_nom';

-- Étape 4: Mettre le tablespace ONLINE

ALTER TABLESPACE nom ONLINE;

9. Supprimer un Tablespace

sql

DROP TABLESPACE nom

[INCLUDING CONTENTS
[AND DATAFILE]
[CASCADE CONSTRAINTS]];

10. Supprimer un Fichier de Données

sql

ALTER TABLESPACE nom DROP DATAFILE 'nom_complet';

🕐 TABLESPACE TEMPORAIRE
1. Création d'un Tablespace Temporaire

sql
 CREATE [BIGFILE | SMALLFILE] TEMPORARY TABLESPACE Ts_nom
TEMPFILE 'nom_fichier' [SIZE valeur [K|M|G|T]] [REUSE]
AUTOEXTEND {OFF | ON NEXT valeur [K|M|G|T]}
[MAXSIZE {UNLIMITED | valeur [K|M|G|T]}]
EXTENT MANAGEMENT LOCAL {AUTOALLOCATE | UNIFORM [SIZE valeur [K|M|G|T]]};

↩️ TABLESPACE UNDO
1. Création d'un Tablespace UNDO

sql

CREATE [BIGFILE | SMALLFILE] UNDO TABLESPACE Ts_nom

DATAFILE 'nom_fichier' [SIZE valeur [K|M|G|T]]
AUTOEXTEND {OFF | ON NEXT valeur [K|M|G|T]}
[MAXSIZE {UNLIMITED | valeur [K|M|G|T]}]
[ONLINE | OFFLINE];

2. Modifier le Tablespace UNDO par Défaut

sql

ALTER SYSTEM SET UNDO_TABLESPACE=NOMTBS_UNDO;

👤 GESTION DES UTILISATEURS

1. Création d'un Utilisateur
Authentification par Oracle Server :

sql

CREATE USER nom_user IDENTIFIED BY password;

Authentification par OS :

sql

CREATE USER name IDENTIFIED EXTERNALLY;

Syntaxe complète :
 sql

CREATE USER nom_user

IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE nom_tablespace_D]
[TEMPORARY TABLESPACE nom_tablespace_T]
[QUOTA {entier [K|M] | UNLIMITED} ON nom_tablespace...]
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}]
[PROFILE {nom_profil | DEFAULT}];

Exemple :

sql

CREATE USER TABD

IDENTIFIED BY motdepasse123
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA 50M ON data
ACCOUNT UNLOCK;

2. Modification d'un Utilisateur

Gestion des verrous et mots de passe :

sql

ALTER USER username

IDENTIFIED {BY password | EXTERNALLY}
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}];

Modification des quotas :

sql

ALTER USER username

[DEFAULT TABLESPACE nom_tablespace_D]
[TEMPORARY TABLESPACE nom_tablespace_T]
[QUOTA {entier [K|M] | UNLIMITED} ON nom_tablespace...];

Exemple :
 sql

ALTER USER TABD QUOTA 100M ON tablespace1;

3. Suppression d'un Utilisateur

sql

DROP USER username [CASCADE];

📋 GESTION DES PROFILS

1. Création d'un Profil (Limites de Ressources)

sql

CREATE PROFILE profilename LIMIT

[SESSIONS_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value];

Où : max_value := {integer | UNLIMITED | DEFAULT}

Exemple :

sql

CREATE PROFILE developer_profile LIMIT

SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
CONNECT_TIME 480
IDLE_TIME 60;

2. Création d'un Profil (Paramètres Mots de Passe)

sql
 CREATE PROFILE profilename LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[PASSWORD_REUSE_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_LOCK_TIME max_value]
[PASSWORD_VERIFY_FUNCTION PlsqlFunction];

3. Activation des Limites de Ressources

sql

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

Vérifier l'état :

sql

SHOW PARAMETER resource_limit;

4. Modification d'un Profil

sql

ALTER PROFILE profilename LIMIT

[SESSIONS_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value];

Exemple :

sql

ALTER PROFILE developer_profile LIMIT SESSIONS_PER_USER 5;

5. Suppression d'un Profil

sql

DROP PROFILE profilename [CASCADE];

6. Affichage des Profils

sql

SELECT [Link], p.resource_name, [Link]

FROM dba_users u, dba_profiles p
WHERE [Link] = [Link]
AND username = 'TABD';

🔐 GESTION DES PRIVILÈGES SYSTÈME

1. Attribution de Privilèges Système

sql

GRANT {priv_système|rôle} [, {priv_système|rôle}]

TO {username|rôle|PUBLIC} [, {username|rôle|PUBLIC}]
[WITH ADMIN OPTION];

Exemples :

sql

-- Accorder le privilège de création de table

GRANT CREATE TABLE TO TABD;

-- Accorder avec option ADMIN

GRANT CREATE SESSION TO TABD WITH ADMIN OPTION;

-- Accorder à plusieurs utilisateurs

GRANT CREATE TABLE, CREATE VIEW TO user1, user2;

-- Accorder à tous les utilisateurs

GRANT CREATE SESSION TO PUBLIC;

2. Révocation de Privilèges Système

sql

REVOKE {priv_système|rôle} [, {priv_système|rôle}]

FROM {username|rôle|PUBLIC} [, {username|rôle|PUBLIC}];

Exemple :
 sql

REVOKE CREATE TABLE FROM TABD;

🔓 GESTION DES PRIVILÈGES OBJET

1. Attribution de Privilèges Objet

sql

GRANT {priv_objet [(liste_colonne)] [, priv_objet [(liste_colonne)]] | ALL [PRIVILEGES]}

ON [schéma.]objet
TO {username|rôle|PUBLIC} [, {username|rôle|PUBLIC}]
[WITH GRANT OPTION];

Exemples :

sql

-- Accorder tous les privilèges sur une table

GRANT ALL PRIVILEGES ON personnel TO directeur;

-- Accorder SELECT et UPDATE sur des colonnes spécifiques

GRANT SELECT, UPDATE(salaire) ON personnel TO sous_directeur, chef_service;

-- Accorder UPDATE sur des colonnes spécifiques

GRANT UPDATE(ename, sal) ON emp TO TABD;

-- Accorder SELECT à tous les utilisateurs

GRANT SELECT ON grille_salaire TO PUBLIC;

-- Avec option GRANT

GRANT SELECT ON ma_table TO user1 WITH GRANT OPTION;

2. Révocation de Privilèges Objet

sql

REVOKE [GRANT OPTION FOR] {liste_privilèges | ALL PRIVILEGES}

ON nom_objet
FROM {liste_autorisations | PUBLIC}
[RESTRICT | CASCADE];
Exemples :

sql

-- Retirer un privilège SELECT

REVOKE SELECT ON grille_salaire FROM PUBLIC;

-- Retirer tous les privilèges

REVOKE ALL PRIVILEGES ON personnel FROM chef_service;

-- Retirer avec CASCADE

REVOKE SELECT ON ma_table FROM user1 CASCADE;

👥 GESTION DES RÔLES

1. Création d'un Rôle

sql

CREATE ROLE rolename

[NOT IDENTIFIED | IDENTIFIED BY password];

Exemples :

sql

-- Rôle sans mot de passe

CREATE ROLE hr_clerk1;

-- Rôle avec mot de passe

CREATE ROLE hr_clerk IDENTIFIED BY bonus;

2. Attribution de Privilèges à un Rôle

sql

-- Attribution de privilèges système

GRANT CREATE TABLE, CREATE VIEW TO hr_clerk;

-- Attribution de privilèges objet

GRANT SELECT, INSERT ON employees TO hr_clerk;
3. Attribution d'un Rôle à un Utilisateur

sql

GRANT rolename TO username [WITH ADMIN OPTION];

Exemple :

sql

GRANT hr_clerk TO TABD;

GRANT sales_clerk TO user1 WITH ADMIN OPTION;

4. Activation et Désactivation des Rôles

sql

SET ROLE {
rolename [IDENTIFIED BY password] [, rolename [IDENTIFIED BY password]]
| ALL [EXCEPT rolename [, rolename]...]
| NONE
};

Exemples :

sql

-- Activer un rôle simple

SET ROLE hr_clerk;

-- Activer un rôle avec mot de passe

SET ROLE sales_clerk IDENTIFIED BY commission;

-- Activer tous les rôles sauf un

SET ROLE ALL EXCEPT sales_clerk;

-- Désactiver tous les rôles

SET ROLE NONE;

5. Modification d'un Rôle

sql
 ALTER ROLE rolename
[NOT IDENTIFIED | IDENTIFIED BY password];

6. Suppression d'un Rôle

sql

DROP ROLE rolename;

7. Révocation d'un Rôle

sql

REVOKE rolename FROM username;

📊 VUES DU DICTIONNAIRE DE DONNÉES

Vues sur les Tablespaces

sql

-- Informations sur les tablespaces

SELECT * FROM DBA_TABLESPACES;
SELECT * FROM V$TABLESPACE;

-- Informations sur les fichiers temporaires

SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM V$TEMPFILE;

-- Informations sur les segments

SELECT * FROM DBA_SEGMENTS;

-- Informations sur les extents

SELECT * FROM DBA_EXTENTS;

Vues sur les Utilisateurs

sql
 -- Informations sur les utilisateurs
SELECT * FROM USER_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM DBA_USERS;

Vues sur les Profils

sql

-- Informations sur les profils

SELECT * FROM DBA_PROFILES;

-- Profil d'un utilisateur spécifique

SELECT profile FROM DBA_USERS WHERE username = 'TABD';

Vues sur les Privilèges

sql

-- Privilèges système
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

-- Privilèges objet
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

Vues sur les Rôles

sql
 -- Tous les rôles
SELECT * FROM DBA_ROLES;

-- Rôles accordés aux utilisateurs

SELECT * FROM DBA_ROLE_PRIVS;

-- Rôles accordés aux rôles

SELECT * FROM ROLE_ROLE_PRIVS;

-- Privilèges système des rôles

SELECT * FROM ROLE_SYS_PRIVS;

-- Privilèges objet des rôles

SELECT * FROM ROLE_TAB_PRIVS;

Vues sur les Propriétés de la Base

sql

-- Propriétés de la base de données

SELECT * FROM DATABASE_PROPERTIES;

📝 EXEMPLES COMPLETS
Scénario 1 : Création Complète d'un Utilisateur

sql
 -- 1. Créer un tablespace
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/app_data01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 2. Créer un profil
CREATE PROFILE app_profile LIMIT
SESSIONS_PER_USER 3
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90
PASSWORD_LOCK_TIME 1;

-- 3. Créer l'utilisateur
CREATE USER app_user
IDENTIFIED BY Welcome123
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp
QUOTA 50M ON app_data
PROFILE app_profile
ACCOUNT UNLOCK;

-- 4. Accorder les privilèges

GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;

Scénario 2 : Gestion par Rôles

sql
 -- 1. Créer un rôle
CREATE ROLE dev_role;

-- 2. Attribuer des privilèges au rôle

GRANT CREATE SESSION TO dev_role;
GRANT CREATE TABLE TO dev_role;
GRANT CREATE VIEW TO dev_role;
GRANT CREATE PROCEDURE TO dev_role;

-- 3. Créer plusieurs utilisateurs

CREATE USER dev1 IDENTIFIED BY pass1
DEFAULT TABLESPACE users QUOTA 50M ON users;

CREATE USER dev2 IDENTIFIED BY pass2

DEFAULT TABLESPACE users QUOTA 50M ON users;

-- 4. Attribuer le rôle aux utilisateurs

GRANT dev_role TO dev1, dev2;

Scénario 3 : Gestion des Privilèges Objet

sql

-- 1. Créer une table

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER
);

-- 2. Accorder différents niveaux d'accès

GRANT SELECT ON employees TO PUBLIC;
GRANT SELECT, INSERT, UPDATE ON employees TO hr_manager;
GRANT SELECT, UPDATE(salary) ON employees TO hr_clerk;
GRANT ALL PRIVILEGES ON employees TO hr_director WITH GRANT OPTION;

🔍 COMMANDES SQL*PLUS UTILES

sql
 -- Afficher les paramètres système
SHOW PARAMETER nom_parametre;
SHOW PARAMETER resource_limit;

-- Décrire une structure

DESC nom_table;
DESC DBA_USERS;

-- Formater l'affichage
COLUMN column_name FORMAT A20;
SET LINESIZE 200;
SET PAGESIZE 50;

-- Exécuter un script
@chemin/[Link];

-- Spool (enregistrer les résultats)

SPOOL /tmp/[Link];
SELECT * FROM DBA_USERS;
SPOOL OFF;

⚡ PRIVILÈGES SYSTÈME COURANTS

sql
 -- Privilèges de session
GRANT CREATE SESSION TO username;
GRANT ALTER SESSION TO username;

-- Privilèges de création d'objets

GRANT CREATE TABLE TO username;
GRANT CREATE VIEW TO username;
GRANT CREATE PROCEDURE TO username;
GRANT CREATE SEQUENCE TO username;
GRANT CREATE TRIGGER TO username;
GRANT CREATE SYNONYM TO username;

-- Privilèges ANY (sur tous les schémas)

GRANT CREATE ANY TABLE TO username;
GRANT DROP ANY TABLE TO username;
GRANT SELECT ANY TABLE TO username;

-- Privilèges de tablespace
GRANT UNLIMITED TABLESPACE TO username;

-- Privilèges DBA
GRANT DBA TO username;

📌 NOTES IMPORTANTES
1. WITH ADMIN OPTION : Permet de transmettre des privilèges système

2. WITH GRANT OPTION : Permet de transmettre des privilèges objet

3. CASCADE : Révoque en cascade tous les privilèges transmis

4. RESTRICT : Empêche la révocation si des privilèges ont été transmis

5. PUBLIC : Accorde le privilège à tous les utilisateurs

6. UNLIMITED : Quota illimité sur un tablespace

7. DEFAULT : Utilise les valeurs par défaut du profil DEFAULT