Name: Oracle 19c Administration
Rating: 5 (3 reviews)

Vue d’ensemble

Les outils Data Pump, Export, Import et SQL*Loader sont des outils très puissants comportant de nombreuses fonctionnalités ; un ouvrage entier pourrait leur être consacré.

L’objectif de ce chapitre est donc de présenter les principes de fonctionnement généraux de ces différents outils, de donner quelques conseils sur leur utilisation, illustrés par quelques exemples classiques d’utilisation. Pour approfondir le sujet, vous pouvez consulter la documentation Database Utilities.

Oracle propose trois utilitaires permettant d’administrer les données dans une base :

Data Pump Export : permet d’exporter dans un fichier binaire propriétaire Oracle tout ou une partie des objets (structure et/ou données) d’une base de données.
Data Pump Import : permet d’importer dans une base de données tout ou une partie des objets (structure et/ou données) préalablement exportés par l’outil Data Pump Export.
SQL*Loader : permet de charger dans des tables d’une base de données, des données stockées dans des fichiers ASCII.

Les outils Data Pump sont apparus en version 10. Dans les versions précédentes, il existait deux outils équivalents simplement appelés Export et Import. Ces outils existent toujours pour des raisons de compatibilité ascendantes...

Data Pump

1. Présentation

a. Architecture

Data Pump est un utilitaire serveur qui peut être utilisé pour déplacer des données et/ou des métadonnées (définitions) entre des bases Oracle.

Data Pump comporte trois éléments :

un package PL/SQL DBMS_DATAPUMP ;
un package PL/SQL DBMS_METADATA ;
deux outils clients ligne de commande expdp (export) et impdp (import).

Les outils clients expdp et impdp servent d’interface avec le package DBMS_DATAPUMP, qui est, en quelque sorte, l’API (Application Programming Interface) de Data Pump. Ce package est complètement documenté, ce qui permet d’utiliser directement les fonctionnalités Data Pump dans un programme.

Les opérations proprement dites d’export et d’import sont effectuées par le package DBMS_DATAPUMP et donc, sur le serveur Oracle. Cela inclut notamment la lecture et/ou l’écriture des fichiers : les fichiers générés lors d’un export sont écrits sur le serveur et les fichiers chargés lors d’un import sont lus sur le serveur. L’accès aux fichiers sur le serveur s’effectue grâce à des objets DIRECTORY ; un objet DIRECTORY est un alias vers un répertoire du système d’exploitation. Ces objets DIRECTORY doivent être créés par le DBA (cf. section L’objet DIRECTORY).

Lorsqu’un travail Data Pump est créé, Oracle crée différentes structures pour gérer l’opération, parmi lesquelles :

une table dite "maître" dans le schéma de l’utilisateur qui crée le travail (cette table porte le même nom que le travail) ;
un processus de contrôle maître (nommé DMnn) qui contrôle l’exécution du travail.

La table "maître" contient différentes informations sur le travail, utilisées notamment pour redémarrer le travail. La table "maître" est supprimée lorsque le travail se termine normalement ou lorsque le travail est supprimé avec la commande KILL_JOB (voir plus loin) ; en cas de besoin, cette table peut être directement supprimée à la main, à l’aide d’un ordre SQL DROP TABLE.

b. Les modes d’export...

SQL*Loader

1. Vue d’ensemble

a. Présentation

SQL*Loader est un outil très puissant qui permet de charger des données :

à partir d’un ou plusieurs fichiers externes ;
avec des enregistrements de longueur fixe ou de longueur variable (avec délimiteurs) ;
dans une ou plusieurs tables ;
en appliquant des traitements, des contrôles ou des filtres sur les données.

b. Fonctionnement général

En entrée, SQL*Loader prend un fichier de contrôle (rien à voir avec le fichier de contrôle d’une base de données) qui pilote le chargement et un ou plusieurs fichiers de données ASCII (pas des fichiers de données d’une base de données Oracle).

En sortie, SQL*Loader alimente la base de données Oracle et génère un fichier journal (log), un fichier des rejets (bad - données erronées) et un fichier des refus (discard - données écartées).

Pour des petits volumes, les données peuvent être directement incluses dans le fichier de contrôle.

Le fichier discard contient des enregistrements qui ont été refusés (écartés) par SQL*Loader car ils ne respectaient pas des conditions spécifiées dans le fichier de contrôle.

Le fichier bad contient des enregistrements qui ont été rejetés soit par SQL*Loader (format de l’enregistrement non valide par rapport à la description du fichier de contrôle), soit par Oracle (violation d’une contrainte d’intégrité, type de données non valide, etc.).

Les enregistrements rejetés ou refusés sont écrits tels quels dans les fichiers bad et discard qui ont donc, la même structure que les fichiers de données utilisés en entrée ; après correction éventuelle des enregistrements, les fichiers bad et discard peuvent être utilisés comme fichiers d’entrée.

Le fichier journal donne un grand nombre d’informations sur le résultat du chargement :

date ;
nom des fichiers utilisés ;
paramètres utilisés ;
tables cibles et mode d’alimentation ;
conditions éventuelles sur les enregistrements ;
nombre d’enregistrements chargés ;
nombre...

Extraire des données dans un fichier texte

1. En SQL

En SQL, il suffit d’écrire un script avec la requête SELECT souhaitée et de rediriger la sortie vers un fichier (SPOOL). En complément, il est nécessaire de passer un certain nombre de commandes SQL*Plus pour supprimer les affichages jugés indésirables (titres des colonnes, nombre de lignes sélectionnées, etc.).

Exemple de script SQL : export avec des enregistrements de longueur fixe

-- un peu de configuration de l'environnement SQL*Plus 
-- pas d'echo des requêtes 
SET ECHO OFF 
-- masquer les titres de colonnes 
SET HEADING OFF 
-- masquer l'affichage du nombre de lignes dans le résultat 
SET FEEDBACK OFF 
-- dimensionner la largeur de la ligne à 1000 caractères 
-- (pas utile ici, mais c'est à titre d'exemple)  
SET LINESIZE 1000 
-- supprimer le saut de ligne à chaque changement de page 
SET NEWPAGE NONE 
-- suppression des espaces en fin de ligne 
SET TRIMSPOOL ON 
-- pas d'affichage à l'écran (plus rapide) 
SET TERMOUT OFF 
 
-- rediriger la sortie vers un fichier .txt 
SPOOL adherent.txt 
 
-- faire une requête SELECT qui concatène les différentes colonnes et 
-- utilise si besoin la fonction SQL RPAD pour ajouter des espaces aux 
-- colonnes de longueur variable et les rendre ainsi de longueur fixe 
SELECT      -- première méthode avec largeurs fixes 
           RPAD(prenom,10,' ') 
     ||    RPAD(nom,12,'...

Utiliser Oracle SQL Developer

EM Express ne propose aucune page pour effectuer des exports, des imports ou des chargements de données. Pour cela, il est par contre possible d’utiliser Oracle SQL Developer.

1. Data Pump

a. Introduction

Dans le panneau DBA, le dossier Data Pump donne accès aux différentes fonctionnalités qui permettent d’utiliser Data Pump :

Les deux sous-dossiers Travaux d’export et Travaux d’import permettent de voir les travaux d’export ou d’import actuellement en cours d’exécution.

Dans le menu contextuel qui s’affiche lorsque vous effectuez un clic droit sur le dossier Data Pump vous avez la possibilité de démarrer un assistant qui vous permettra d’effectuer un export ou un import avec Data Pump :

Les deux assistants interdisent d’utiliser une connexion SYS pour effectuer un export ou un import à l’aide de Data Pump. Il vous faudra donc créer une autre connexion (SYSTEM par exemple).

Ces deux assistants exploitent directement le package DBMS_DATAPUMP pour exécuter les travaux d’export ou d’import.

b. Assistant Export Data Pump

La première étape permet de sélectionner la connexion à utiliser, le contenu de l’export et le type d’export.

Les étapes suivantes dépendent du type d’export. Par exemple, dans le cas d’un export de niveau Schémas contenant les données et les métadonnées (DDL), l’assistant vous propose successivement :