Une demande qui revient souvent et qui est faite par les équipes de DEV est : Comment puis je retrouver facilement les différences entre mes 2 schémas de bases de données suite aux évolutions de version.
Étant donné qu’il faut pour cela scruter et parcourir de nombreux éléments du dictionnaire, la solution n’est pas simple. SQL DEVELOPER dans ses dernières versions possède dans la boite à outils une commande « Database Diff »   ou « Différence de base de données »  (en français) qui correspond à ce besoin.
Pour la tester, j’utilise la version 1.5.5 du 14/07/2009,  vous pouvez la récupérez  depuis cette page, je vous rappelle que ce produit est complètement gratuit.
Je vais faire une copie du schéma HR  (schéma d’exemple d’une application de Ressources humaines) contenu dans ma base 11GR1 sous Windows XP en un schéma  HR_V2, pour ce faire, la boite à outils contient aussi l’utilitaire « Copie de base de données »  qui nous facilitera la tache. Puis je ferai quelques modifications structurelles et je regarderai si SQL DEVELOPER est capable de retrouver et de lister correctement  ces modifications.

1)  Je créé d’abord un utilisateur HR_V2

avec des caractéristiques équivalentes à mon schéma HR, voici les ordres DDL générées par SQLDEVELOPER

CREATE USER HR_V2 IDENTIFIED BY HR
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
-- ROLES
GRANT "CONNECT" TO HR_V2 ;
GRANT "RESOURCE" TO HR_V2 ;
-- SYSTEM PRIVILEGES
GRANT ALTER SESSION TO HR_V2;
GRANT CREATE SYNONYM TO HR_V2;
GRANT CREATE DATABASE LINK TO HR_V2;
GRANT CREATE VIEW TO HR_V2 ;
-- QUOTAS
ALTER USER HR_V2 QUOTA UNLIMITED ON USERS;

2) Création des connexions

J’ai déjà, depuis SQL DEVELOPER une connexion vers le schéma HR (nommée EASY11_HR) , j’en créé une nouvelle EASY11_HR_V2 vers le schéma HR_V2.

3) Depuis l’onglet « Outils » de la fenêtre principale je choisis « Copie de base de données » .

je sélectionne ma connexion source et ma connexion destination, et je laisse cocher « Créer des objets ». En deux clics, vous déclenchez l’enchaînement des opérations, création des objets et déplacement des données avec la gestion des contraintes associées.


Le fichier journal de cette opération, qui est stocké sur votre disque et dont vous avez une vision à l’écran, montre qu’il y a quelques erreurs  :

ALTER TABLE "COUNTRIES" ADD CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE
Rapport d'erreur :
Erreur SQL : ORA-02260: la table ne peut avoir qu'une clé primaire
02260. 00000 -  "table can have only one primary key"

CREATE UNIQUE INDEX "LOC_ID_PK" ON "LOCATIONS" ("LOCATION_ID")
Erreur à  la ligne de commande : 6, colonne : 20
Rapport d'erreur :
Erreur SQL : ORA-00955: ce nom d'objet existe déja
00955. 00000 -  "name is already used by an existing object"

Les contraintes de clés primaires sont créés avec la table , puis il y a de nouveau une tentative d’ajout de la contrainte d’où l’erreur de type « ORA-22603 » ou « ORA-00955 » pour les indexes des contraintes d’unicité. Dans notre cas et le plus souvent, il suffit de ne pas en tenir compte.
On trouve aussi deux triggers qui ne sont pas générés correctement : les triggers sont compilés avec des erreurs car le dernier  « / »  dans le code du trigger n’est pas bien interprété par SQL DEVELOPER (la syntaxe est correcte et une exécution du code sous SQLPLUS se déroule bien), une compilation des triggers suffit pour les remettre en état.
L’inconvénient de ma méthode est que les ordres générés par l’outil et la commande de copie ne sont pas complètement identiques à ceux d’origine (différences entre majuscules et minuscules ou positionnement des espaces), il y aura donc des écarts  induits inutilement.
Pour les limiter j’ai régénéré les triggers et les procédures avec exactement le même code que celui présent dans mon schéma d’origine.

4) Altérations du schéma HR_V2 qui pourraient correspondre à une évolution applicative :

• Ajout d’une table et ses contraintes: table MANAGERS avec une clé primaire
• Insertion de lignes dans cette table
• Ajout d’une colonne à une table existante:  ajout de la colonne JOB_MANAGER à la table JOBS
• Ajout d’une contrainte de type clé étrangère à la table JOBS pour la nouvelle colonne qui sera relié à la colonne MGR_ID de la table MANAGER
• Suppression des 2 tables de test, T1 et TE1

5) Utilisation de la commande « Différence de base de données » :

L’outil nous demande si nous avons la licence « Change Management Pack » :

Donc pas le droit de l’utiliser si vous ne l’avez pas,  considérons avec raison que nous sommes en règle.
Il faut choisir le schéma source , le schéma cible, et cocher la case « Poursuivez le récapitulatif » ceci pour prendre en compte tous les objets du schéma source (si on veut réaliser une comparaison partielle, on peut ne pas cocher la case et sélectionner les objets souhaités par type ou par nom sur l’écran suivant de l’assistant).

6) Évaluation du résultat qui apparaît comme un nouvel onglet nommé « Rapport de différence » :

Tous les objets comportant une différence par rapport au schéma source sont répertoriés avec les informations concernant le type, le propriétaire, le nom, l’ordre DDL et le nombre de différences.
Si on sélectionne  un objet, le texte des ordres SQL pour se remettre en conformité est affiché dans la fenêtre juste dessous :

Le schéma source est donc le schéma qui sert de référence à la comparaison, pour chaque écart trouvé, l’outil nous propose l’ordre DDL pour ramené le schéma cible vers la source. Dans l’exemple utilisé, il faut supprimer une colonne et une contrainte de la table JOBS pour revenir à une définition semblable à celle du schéma d’origine HR. Il faut aussi recréer les tables T1 et TE1 qui ne sont plus présentes:

Les écarts concernant les synonymes ne sont pas à prendre en compte car ils sont induits par le fait d’avoir le nom du schéma en préfixe de chaque synonyme.
On s’aperçoit que suivant le sens de la comparaison certain objets peuvent nous échappés, comme  ici:  la table MANAGERS est créée dans le schéma HR_V2 mais aucune information n’apparaît la concernant dans le rapport des différences.
Il est donc nécessaire de réaliser l’opération dans les 2 sens pour ne rien omettre.
Ainsi en positionnant le schéma HR_V2 en tant que source, j’obtiens cette fois le rapport de différence ou les opérations concernant la table MANAGERS apparaissent clairement :

Noter que par défaut seuls les objets pour lesquels une différence est détectée sont affichés par défaut, vous pouvez cocher la case « Afficher les objets égaux » pour avoir la liste de tous les objets comparés y compris ceux pour lesquels il n’y a pas de différence.

7) Génération d’un fichier de commandes :

Pour générer un fichier SQL contenant tous les autres DDL nécessaire pour revenir à l’identique,  il faut cliquer sur l’icone « SQL » en haut à gauche du rapport. Tous les ordres associés aux éléments sélectionnés (dont la case est cochée)  seront contenu dans le fichier choisi.

C’est donc particulièrement facile avec SQLDEVELOPER  de trouver les différences entre deux schémas et de récupérer les ordres SQL correspondants, c’est une aide précieuse pour toutes les équipes de développement. Il faut cependant lire correctement les résultats et ne pas oublier de faire la comparaison dans les deux sens pour ne pas manquer certains éléments.
Il reste dommage que la licence « Change Management Pack » soit nécessaire, elle implique de fait de travailler sur une version « Enterprise » puisqu’aucun pack n’est possible dans la version Standard du noyau.