Introduction

Oracle Data Pump

SQL*Loader

Administration des bases de données

Chapitre 6 : Déplacement des données

Ines BAKLOUTI
[email protected]

Ecole Supérieure Privée d’Ingénierie et de Technologies

 Introduction
Oracle Data Pump
SQL*Loader

Plan

1 Oracle Data Pump

EXPDP / IMPDP
Package DBMS DATAPUMP

2 SQL*Loader
Log File
Bad File
Discard File
Control File

2/27
 Introduction
Oracle Data Pump
SQL*Loader

Introduction

Il existe plusieurs méthodes pour charger des données dans les tables
d’une base de données Oracle :
SQL*Loader
Utilitaire d’export / import ORACLE DATA PUMP

3/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Plan

1 Oracle Data Pump

EXPDP / IMPDP
Package DBMS DATAPUMP

2 SQL*Loader
Log File
Bad File
Discard File
Control File

4/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Oracle Data Pump

Les méthodes d’export/import classiques

se sont révélées très longues : pour 33 Gb
de données : 3h30 d’export et 7 à 8h
d’import !

Oracle a introduit avec la version 10g un nouvel utilitaire

d’export/import appelée EXPORT / IMPORT DATA PUMP
Oracle Data Pump est un utilitaire (coté serveur) de déplacement de
données et de méta-données de masse de manière très rapide entre
des bases de données Oracle
Oracle Data Pump fournit deux nouveaux utilitaires d’export et
import (expdp et impdp) qui offrent une meilleur performance en
utilisant le traitement parallèle

5/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Lancement du Data Pump

Notez Bien
Avant de pouvoir utiliser Data Pump, vous devez créer un Directory
Oracle où vous allez stocker vos exports.

Data Pump peut être appelé :

via Enterprise Manager (EM) Database Control
par les binaires EXPDP et IMPDP situés dans le dossier bin de
ORACLE HOME
via le package SYS.DBMS DATAPUMP

6/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Modes d’export / import avec Data Pump

Il existe 4 modes d’export/import avec Data Pump :

Export / Import COMPLET
Operation demandée par le paramètre FULL
Export / Import de SCHEMA (metadata)
Mode par defaut
Permet l’export ou l’import d’un ou plusieurs schemas de la base de
données
Export / Import de TABLE
Mode commandé par le parametre TABLES
Permet de sélectionner des tables à exporter à partir d’un schema
Export / Import de TABLESPACE
Permet d’exporter les tables d’au moins un espace disque logique
Remarque : seules les tables seront exportées, et non les espaces
disque logiques eux-memes

7/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Fichiers générés avec Data Pump

3 types de fichiers sont générés avec Data Pump

Fichiers SQL : contiennent les instructions LDD de la création des
objets de la base de données
Fichiers DUMP : contiennent les données exportées
Fichiers LOG : contiennent le journal d’historique de l’exécution du
job (export ou import)

8/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Privilèges nécessaires

Deux privilèges sont nécessaires pour l’export / import :

Le privilège EXP FULL DATABASE et IMP FULL DATABASE
Ces privilèges permettent d’exporter l’intégralité d’une BD, un
Tablespace, ou encore un schéma autre que le sien ou bien une table
située dans un autre schéma
Tous les utilisateurs qui possèdent le rôle DBA ont la possibilité
d’effectuer un import / export d’une base de données

9/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Paramètres d’export du Data Pump : expdp help=y

10/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Paramètres d’import du Data Pump : impdp help=y

11/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

EXPDP / IMPDP
Exemple

1 Création d’un utilisateur exp imp et accord de privilèges nécessaires :

SQL> create user exp imp identified by expimp ;
SQL> grant connect, resource to exp imp ;
SQL> grant exp full database, imp full database to exp imp ;

2 Création d’un répertoire pour stocker les fichiers d’export et les fichiers log : DIRECTORY oracle
SQL> connect sys/sys as sysdba
SQL> create or replace directory DATAPUMP as ’C :\oraclexe’ ;
SQL> grant read, write on directory DATAPUMP to exp imp ;

3 Création de la table à exporter

SQL> create table test( a number ) ;
SQL> insert into test values (1) ;
SQL> insert into test values (2) ;
SQL> insert into test values (3) ;
SQL> commit ;

4 Export de la table test du schema exp imp

SQL> host
C :\> expdp exp imp/expimp tables=exp imp.test DIRECTORY=DATAPUMP DUMPFILE=test.dump LOGFILE=test.log
=> Création de 2 fichiers test.dump + test.log sous Oracle Directory ’C :\oraclexe’

5 Importer les données dans la table test

SQL> host
C :\> impdp exp imp/expimp content=DATA ONLY tables=exp imp.test DIRECTORY=DATAPUMP DUMPFILE=test.dump
LOGFILE=test.log
=> Ajout des 3 lignes déjà existantes encore une fois dans la table test

6 Vérification que l’import a réussi

C :\> exit ( quitter cmd et revenir à SQL)
SQL> select * from test ;

12/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Vue d’export / import

Pour vérifier le déroulement du job d’export ou d’import, on peut

interroger la vue DBA DATAPUMP JOBS

13/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Package DBMS DATAPUMP

Procédures

14/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Package DBMS DATAPUMP

Exemple

1. Programmer l’export :
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
p dph NUMBER ;
BEGIN
p dph := DBMS DATAPUMP.open(operation=>’EXPORT’, job mode=>’TABLE’,
job name=>’DVP EXPORT’) ;
DBMS DATAPUMP.add file(handle=>p dph, filename=>’dvp.dmp’, directory=> ’DATAPUMP’,
filetype=>1) ;
DBMS DATAPUMP.add file(handle=>p dph, filename=>’tab dvp.log’, directory=>
’DATAPUMP’, filetype=>3) ;
DBMS DATAPUMP.metadata filter(handle =>p dph, name=>’SCHEMA EXPR’, value =>’IN
(”DVP”)’) ;
DBMS DATAPUMP.start job(p dph) ;
DBMS DATAPUMP.detach(p dph) ;
dbms output.put line(’Export terminé ’) ;
EXCEPTION
WHEN OTHERS THEN
dbms output.put line(’erreur :’||sqlerrm||’ Job-ID :’||p dph) ;
END ;
/

Export terminé
Procédure PL/SQL terminée avec succès
15/27
 Introduction
EXPDP / IMPDP
Oracle Data Pump
Package DBMS DATAPUMP
SQL*Loader

Package DBMS DATAPUMP

Exemple

2. Programmer l’import :
SET SERVEROUTPUT ON SIZE 1000000
DECLARE p dph NUMBER ;
BEGIN
p dph :=DBMS DATAPUMP.open(operation=>’IMPORT’, job mode=>’TABLE’,
job name=>’EMP IMPORT2’) ;
DBMS DATAPUMP.add file(handle=>p dph, filename=>’dvp.dmp’, directory =>’DATAPUMP’,
filetype=>1) ;
DBMS DATAPUMP.add file(handle=>p dph, filename=>’dvp imp.log’, directory =>
’DATAPUMP’, filetype=>3) ;
DBMS DATAPUMP.set parameter(handle=> p dph, name=>’TABLE EXISTS ACTION’,
value=>’REPLACE’) ;
DBMS DATAPUMP.start job(p dph) ;
DBMS DATAPUMP.detach(p dph) ;
dbms output.put line(’Import terminé ’) ;
EXCEPTION WHEN OTHERS THEN
dbms output.put line(’erreur :’||sqlerrm||’ Job-ID :’||p dph) ;
END ;
/

Import terminé
Procédure PL/SQL terminée avec succès.

16/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Plan

1 Oracle Data Pump

EXPDP / IMPDP
Package DBMS DATAPUMP

2 SQL*Loader
Log File
Bad File
Discard File
Control File

17/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

SQL*Loader
L’utilitaire SQL*Loader charge les données de fichiers externes dans des tables d’une base de
données Oracle
Cet utilitaire dispose d’un puissant moteur d’analyse (parse) des données, qui ne limite que
très peu le format des données du fichier

18/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Log File

Le fichier LOG FILE enregistre les activités SQL Loader durant un

chargement de données :
Les noms des fichiers CONTROL FILE, BAD FILE, DISCARD FILE,
Input Data File
Les champs et types de données qui ont été chargées
Messages d’erreurs sur les enregistrement non chargés
Le nombre d’enregistrements lus dans le fichier de données
Le nombre d’enregistrements rejetés en raison d’erreurs
Le nombre d’enregistrements rejetés en raison de critères de sélection
Le temps de chargement

19/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Bad File

Dès que SQL Loader rencontre une erreur en essayant de lire ou de

charger un enregistrement, il écrit dans un fichier BAD FILE et enregistre
les erreurs SQL Loader : enregistrement non conforme au format dans le
fichier de contrôle, violations de contraintes d’intégrité, tablespace plein,
etc.

20/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Discard File

Les enregistrements qui ne répondent pas aux conditions de sélection

spécifiées dans le Control File sont rejetés, écartés et sont écrits dans le
fichier DISCARD FILE.

21/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Control File

Le fichier de contrôle indique à SQL*Loader :

L’emplacement des fichiers bad file, discard file et log file
Les détails de configuration : Gestion de la mémoire Rejet des
enregistrements
L’emplacement des données à charger : input data files
la structure, types, longueurs, précisions des données à charger
Les noms de tables à charger
La correspondance entre les champs des données et les colonnes des
tables de base de données
Les critères de sélection qui définissent les enregistrements à insérer
dans les tables de base de données
Formattage des enregistrements de données : si le format est
délimité, retour chariot, si une colonne est de taille fixe, etc.

22/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Control File
Exemple 1

Soit le fichier  Emp.dat  contenant :

10001,”Scott Tiger”, 1000, 40
10002,”Frank Naude”, 500, 20
Pour charger le fichier emp.dat dans la table  emp  du schéma
HR, le control file doit contenir les informations suivantes :
load data
infile ’c :\Emp.dat‘
into table emp – { INSERT | REPLACE | TRUNCATE | APPEND }
fields terminated by ”,” optionally enclosed by ‘”‘
( empno, empname, sal, deptno )

Pour charger les données on doit exécuter la commande suivante :

SQL> host
C :\> Sqlldr user/password control=<control file>.ctl
log=<log file>.log bad=<bad file>.bad discard=<discard file>.dsc
23/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Control File
Exemple 2 : charger des données à taille fixe

Exemple 1 : Exemple 2 :
Soit le fichier  dept.txt  Control file
contenant : load data
COSC COMPUTER SCIENCE infile * – les datas ne
ENGL ENGLISH LITERATURE proviennent pas d’un file
MATH MATHEMATICS append into table departments
POLY POLITICAL SCIENCE – insérer en fin de table
Control File ( dept position (01 :04) char(4),
load data deptname position (05 :25)
infile ’c :\dept.txt‘ char(20) )
append into table departments begindata
– insérer en fin de table COSC COMPUTER SCIENCE
( dept position (01 :04) char(4), ENGL ENGLISH LITERATURE
deptname position (05 :25) MATH MATHEMATICS
char(20) ) POLY POLITICAL SCIENCE

24/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Control File
Exemple 3 : convertir les données au moment du chargement

Soit le fichier  emp.dat  contenant :

11111AAAAAAAAAA991201 Solution :
22222BBBBBBBBBB990112
Créer une séquence  db seq  :
On veut charger le fichier emp.dat dans une CREATE SEQUENCE db seq START WITH 1
table modified data contenant les champs INCREMENT BY 1 ;
suivants :
Editer un control file :
rec no : un numéro séquentiel load data
region : 31 infile ‘Emp.dat’
time loaded : temps de chargement, date badfile ‘bad.bad’
du jour INTO TABLE modified data
data1 : le premier champ numérique dans (rec no ” db seq.nextval ”,
emp.dat, avec 2 chiffres après la virgule region CONSTANT ’31’,
data2 : le deuxième champs en majuscule time loaded ” to char(SYSDATE, ’HH24 :MI’) ”,
dans emp.dat data1 position(1 :5) ” :data1/100”,
data3 : le troisième champ numérique data2 position(6 :15) ”upper( :data2)”,
dans emp.dat, date suivant le format  data3 position(16 :22) ”to date( :data3,
YYMMDD  ’YYMMDD’)
)
Mettre les mauvais enregistrements dans le
fichier  bad.bad 

25/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Control File
Exemple 4 : chargement sélectif des données

Soit le fichier  emp.dat  contenant les enregistrements suivants :

1111AAAAAAAAAA991202
2222BBBBBBBBBB990112
3333ABBBBBBBBB990112
4444ABBBBBBBBB990112
On veut charger dans la table emp les employés dont :
Le nom commence par B
La date d’embauche = 1999-01-12
Solution : le fichier de contrôle aura la forme suivante :
load data
infile ‘emp.dat’
append into table emp
when (05) = ‘B‘ and (15 :20) = ’990112’ – conditions à vérifier lors du chargement
trailing nullcols – considérer les colonnes non existantes dans un enregistrement comme
NULL
(empID POSITION (01 :04),
nom POSITION(05 :14),
date emb POSITION(15 :20) )

26/27
 Log File
Introduction
Bad File
Oracle Data Pump
Discard File
SQL*Loader
Control File

Control File
Exemple 5 : chargement dans plusieurs tables

LOAD DATA
INFILE ’C :\emp.dat’

REPLACE INTO TABLE emp

WHEN empno != ’ ’
( empno POSITION(1 :4) INTEGER,
ename POSITION(6 :15) CHAR,
deptno POSITION(17 :18) CHAR,
mgr POSITION(20 :23) INTEGER )

INTO TABLE proj

WHEN projno != ’ ’
( projno POSITION(25 :27) INTEGER,
empno POSITION(1 :4) INTEGER )

27/27