Utilisation de SET UNUSED en Oracle.

Une suppression de colonne dans une table existante et possédant de nombreuses lignes (données volumineuses) peut s'avérer une opération très longue. Dans ce cas, il est souhaitable de rendre la colonne inutilisable à l'aide de la clause SET UNUSED. Cette option de permet pas de libérer l'espace disque occupé par la colonne, mais elle permet de planifier l'opération de suppression de la colonne à un moment.

Exemple:
Rendre inutilisable la colonne TEL de la table EMPLOYEES. Dans un premier temps, la colonne est masquée comme inutilisée.

SQL> ALTER TABLE EMPLOYEES SET UNUSED (TEL);
Table altered

Pour connaître les tables qui contiennent des colonnes inutilisées, il faut interroger la vue du dictionnaire de données DBA_UNUSED_COL_TABS.

Connecter avec le compte sys as sysdba:
select * from dba_unused_col_tabs;

Dans un deuxième temps, il faut supprimer toutes les colonnes inutilisées de la table EMPLOYEES. Lors de cette opération, on demande à Oracle de réaliser un point de synchronisation (CHECKPOINT) toutes les 100 suppressions.

SQL> ALTER TABLE EMPLOYEES DROP UNUSED COLUMNS CHECKPOINT 100;
Table altered

Connect By Prior Oracle.

En utilisant le schéma SCOTT fourni par Oracle, voici une requête hiérarchique de la fonction CONNECT BY PRIOR:

    SELECT lpad(' ',2*level)||ename as "Arbre"
    FROM emp
    CONNECT BY PRIOR empno=mgr
    START WITH ename = 'KING'

 Ça nous donne le résultat suivant:

Arbre
-------------------------------------------
  KING
    JONES
      SCOTT
        ADAMS
      FORD
        SMITH
    BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
    CLARK

Il y a une autre fonction Oracle qui  permet d'afficher la concaténation du chemin hiérarchique pour chaque élément listé (SYS_CONNECT_BY_PATH).

    SELECT SYS_CONNECT_BY_PATH(ename, ' * ')
    FROM emp
    CONNECT BY PRIOR empno=mgr
    START WITH ename = 'KING'

Ce qui nous donne le résultat suivant:
SYS_CONNECT_BY_PATH(ENAME,'*')
-------------------------------------------
 * KING
 * KING * JONES
 * KING * JONES * SCOTT
 * KING * JONES * SCOTT * ADAMS
 * KING * JONES * FORD
 * KING * JONES * FORD * SMITH
 * KING * BLAKE
 * KING * BLAKE * ALLEN
 * KING * BLAKE * WARD
 * KING * BLAKE * MARTIN
 * KING * BLAKE * TURNER
 * KING * BLAKE * JAMES
 * KING * CLARK

13 rows selected

On constate que KING est le président, et par exemple le superviseur de SCOTT est JONES.

Récupérer les données effacées.

En faisant des tests à l'instant avec une table TELEPHONE et par erreur je ne sais pas ce qu'il est se passer avec l'outil PL/SQL Developer 8, les données ont été effacées sans faire le DELETE FROM TELEPHONE (quand je fais un ordre de SELECT * from TELEPHONE, il me donne rien).

J'ai vérifié les objets effacés dans la corbeille d'oracle avec la vue dba_recyclebin, mais aucune trace. Alors, j'étais obligé de manipuler les requêtes avec FLASHBACK, dont voici toutes les étapes:


SQL> flashback TABLE telephone TO timestamp (systimestamp - interval '1' minute);
Done
SQL> select * from telephone;