Infos uttiles dans le dictionnaire :
vue DBA_DATAPUMP_JOB : jobs actifs de DataPump et statuts
vue DBA_DATAPUMP_SESSIONS : les sessions d’utilisateurs ayant lancé des jobs DataPump.
Datapump est un utilitaire qui permet de transférer des données entre une base Oracle…et une base Oracle ! (il correspond aux anciens utilitaires export/import d’Oracle V9 et précédentes)
DataPump export : extrait des données
DataPump import : injecte des données (préalablement exportées)
syntaxe :
expdp KEYWORD=value or KEYWORD=(value1,value2,…,valueN)
Exemples:
expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
SQL> create or replace directory dd_dir as ‘/work/pprun/oexp’;
SQL> grant read, write on directory dd_dir to public;
$> impdp system schemas=SCOTT logfile=impSCOTT.log directory=dd_dir dumpfile=expSCOTT.dmp
Quelques différences minimales à connaitre par rapport à l’export / import classique :
- les executables s’appellent respectivement expdp et impdp (toujours dans $ORACLE_HOME/bin)
- appelable aussi via un package standard : le package SYS.DBMS_DATAPUMP
- on peut filtrer des tables (option INCLUDE / EXCLUDE)
- on peut exporter les metadonnées seulement
- le parametre OWNER est remplace par SCHEMA
- necessite un objet Oracle ‘ directory ‘ pour fonctionner
- peut être parallelisé
- peut se passer d’un fichier intermédiaire
Datapump et Directory
Datapump n’accède pas directement au file system et passe par l’intermédiaire d’un DIRECTORY logique oracle.
Cet objet doit exister dans la base. ET donc soit on utilise le DIRECTORY par défaut DATA_PUMP_DIR (que l’on retrouve par) :
SQL> select * from dba_directories
where directory_name=’DATA_PUMP_DIR’OWNER DIRECTORY_NAME DIRECTORY_PATH
—————————— ——————– ——————————
SYS DATA_PUMP_DIR /oracle/10GDB/rdbms/log/
ou alors on la crée par un ‘ CREATE DIRECTORY’.
SQL> CREATE OR REPLACE DIRECTORY ma_dir AS ‘/tmp/’;
SQL> GRANT READ, WRITE ON DIRECTORY ma_dir TO scott;
Datapump sans fichier d’export
Il est possible de ne pas stocker le résultat d’export dans un fichier, amis d’utiliser un flux entre base source et base cible via un DATABASE LINK.. Ceci se fait avec l’otion NETWORK_LINK de impdp.
SQL> CREATE DATABASE LINK …
$> impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott– Attention : les users destination et cible doivent avoir le même niveau de privilège vis à vis de EXP_FULL_DATABASE et IMP_FULL_DATABASE sinon on obtient une erreur ORA-39149
Options d’export
Keyword Description (Default)
————— ———————————————————-
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
COMPRESSION Réduction de la taille du contenu du fichier de vidage, les mots-clés valides étant : (METADATA_ONLY) et NONE.
ENCRYPTION_PASSWORD Clé de mot de passe pour la création de données de colonne cryptées.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Options d’import
Keyword Description (Default)
——————————————————————————
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply (Y/N) to specific objects.
Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.
ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
Datapump et PLSQL
Il existe un package SYS.DBMS_DATAPUMP, fourni avec Oracle 10g, qui offre un certain nombre de procédure pour executer / automatiser les transfert de données en PLSQL.
Voici un tout petit exemple :
DECLARE
hand NUMBER;
BEGIN
hand := Dbms_DataPump.Open(operation => ‘EXPORT’,
job_mode => ‘FULL’,
job_name => ‘FULLEXPJOB’,
version => ‘COMPATIBLE’);
Dbms_DataPump.Add_File(handle => hand,
filename => ‘expdp_plsql.log’,
directory => ‘DMPDIR’,
filetype => 3);
Dbms_DataPump.Add_File(handle => hand,
filename => ‘expdp_plsql.dmp’,
directory => ‘DMPDIR’,
filetype => 1);
– Dbms_DataPump.Set_Parameter(handle => hand,
– name => ‘ESTIMATE’,
– value => ‘STATISTICS’);
Dbms_DataPump.Start_Job(hand);
END;
