Combien de fois vous a-t-on demandé d’exporter le schéma X de la base de données A et de le réimporter dans le schéma Y de B ? Bien sur parfois, X=Y ; d’autres fois A=B ; Parfois il ne s’agit pas d’un schéma mais juste d’une table. Souvent il faut changer
le nom des tablespaces et quand ça devient intéressant, il faut partitionner la table.

Avec la 9i, il fallait : exporter les données, créer la table dans votre base de données cible en changeant tablespace ou autre critère manuellement, les réimporter puis reconstruire les index… Data Pump offre quant à lui la possibilité de faire, en une opération l’import des structures et données et, au moins pour le tablespace, sans recréer la table manuellement au préalable. Merci au mot clé NETWORK_LINK qui permet de se servir d’une base de données (Et non pas d’un fichier d’export) comme source de l’import…

Bref, vous avez probablement oublié comment on faisait un export/import avec les commandes exp et imp ! Si ce n’est pas le cas, ou que vous vous demandez encore comment on peut faire avec Data Pump, voici 3 exemples qui illustrent la simplicité de Data Pump et, dans la plupart des cas, comment arriver à vos fins en une seule commande ; vous pourrez décliner ces commandes à votre aise pour répondre à vos préoccupations du moment (Vous trouverez toutes les informations dont vous aurez besoin dans Oracle Database Utilities – Data Pump Import). Enfin, même en 11.1, il existe encore quelques limites qu’on aimerait voir lever ; nous y reviendrons en fin de post.

Exemple 1 : Créer une copie d’un utilisateur dans la même base de données.

Et ça marche aussi, si la base de données cible est différente de la base de données source 😉 !
Supposons que vous vouliez copier le schéma de l’utilisateur SCOTT dans un nouvel utilisateur MYSCOTT dans la même base de données, il suffit :

• De se connecter à la base de données cible et de créer un database link vers le schéma SCOTT de votre base source (i.e. vers la même base de données si vous voulez copier le schéma dans la même base de données)

sqlplus / as sysdba

SQL> create database link scott_redx
   connect to scott identified by tiger
     using 'redx';

SQL> exit;

Une fois le lien de base de données créé, vous pouvez lancer l’import data pump. Celui-ci créera tous les objects, y compris le nouvel utilisateur :

$ impdp "/ as sysdba" schemas=scott 
    remap_schema=scott:myscott    
    nologfile=y network_link=scott_redx

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 17:39:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=scott remap_schema=scott:myscott nologfile=y network_link=scott_redx
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "MYSCOTT"."DEPT"                                 4 rows
. . imported "MYSCOTT"."EMP"                                 14 rows
. . imported "MYSCOTT"."SALGRADE"                             5 rows
. . imported "MYSCOTT"."BONUS"                                0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:39:28

Une fois l’opération terminée, vous pouvez supprimer le dababase link :

sqlplus / as sysdba
SQL> drop database link scott_redx;

SQL> exit;

Exemple 2 : Copier une table dans un nouveau tablespace

On supposera cette fois que vous travaillez sur une base de test et que vous vouliez copier la table SCOTT.DEPT de votre base de données de production. Pour le test, le tablespace de production n’existe pas… Comme dans l’exemple précédent, vous allez créer un database link depuis votre base de données de test vers votre base de données de production ; pour cela, donnez à SCOTT la possibilité de créer des database link, connectez-vous à votre base de données de test et créez le lien vers la production :

sqlplus / as sysdba

SQL> grant create database link to scott;

SQL> connect scott
Password :

SQL> create database link scott_redx
     connect to scott identified by tiger  using 'redx';

SQL> exit;

Une fois le database link créé, il vous suffit de lancer impdp comme ci-dessous sur la base de données de test :

$ impdp scott tables=dept           
    remap_tablespace=users:demo   
    nologfile=y network_link=scott_redx

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 19:39:15

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** tables=scott.dept remap_tablespace=users:demo nologfile=y network_link=scott_redx
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."DEPT"                                 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 19:39:26

L’import terminé, vous pouvez supprimer le dababase link :

sqlplus scott
Password:

SQL> drop database link scott_redx;

SQL> exit;

Exemple 3 : Une limite de l’import via un database link

Il faudrait sans doute dire que l’export/import via des fichiers permet en plus de filtrer les données… le mot clé QUERY permet en effet, d’ajouter une clause WHERE sur les objets que vous exportez ou importez. Voici un exemple d’export/import où on ajoute un filtre lors de l’import. Commencez par donner accès à un directory à l’utilisateur SCOTT et à donner le privilège IMP_FULL_DATABASE à MYSCOTT pour qu’il puisse faire l’import d’un autre utilisateur :

SQL> grant read, write
on directory DATA_PUMP_DIR to scott;

SQL> exit

Vous pouvez ensuite lancer l’export Data Pump comme ci-dessous :

$ expdp scott tables=dept dumpfile=DATA_PUMP_DIR:scott_emp.dmpdp   
                     nologfile=y;

Export: Release 11.1.0.6.0 - Production on S
unday, 27 January, 2008 19:58:09

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc                                  tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=dept dumpfile=DAT                                  A_PUMP_DIR:scott/********_emp.dmpdp nologfile=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/REDX/dpdump/scott_emp.dmpdp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 19:58:34

Et voici comment faire l’import dans le schéma MYSCOTT en ajoutant une clause WHERE sur la table DEPT :

$ impdp myscott dumpfile=DATA_PUMP_DIR:scott_emp.dmpdp tables=dept 
query='dept:"where deptno=10"' remap_schema=scott:myscott nologfile=y

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 20:03:56

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MYSCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MYSCOTT"."SYS_IMPORT_TABLE_01":  myscott/******** dumpfile=DATA_PUMP_DIR:scott_emp.dmpdp tables=dept query=dept:"where deptno=10" remap_schema=scott:myscott/******** nologfile=y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MYSCOTT"."DEPT"                            5.937 KB       1 out of 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "MYSCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 20:04:11

Exemple 3 bis : Utiliser DBMS_RLS pour obtenir la même fonctionnalité via l’import avec un Database Link.

Si vous voulez faire un import d’un sous-ensemble de la table SCOTT.DEPT dans MYSCOTT.DEPT en utilisant un database link, vous pouvez toujours faire croire à Data Pump que la table contient moins de lignes qu’en réalité… DBMS_RLS (aka Virtual Private Database, VPD) peut simplement faire ce travail pour vous. Dans ce cas, il faudra que vous vous connectiez à la base de données source non pas avec SCOTT mais avec un utilisateur dédié (dans l’exemple MYSCOTT). Pour permettre de vous connecter à la base source, créez un database Link comme ci-dessous sur la base cible :

sqlplus myscott

SQL> create database link myscott_redx
connect to myscott identified by tiger using 'redx';

SQL> exit

Puis donnez le role exp_full_database a MYSCOTT sur la base source :

sqlplus sys@redx

SQL> grant exp_full_database to myscott;

SQL> exit;

L’étape suivant consiste à ajouter la fonction de la « Policy » VPD sur la base source et de l’activer :

sqlplus scott@redx

SQL> create or replace function filter_myscott(object_schema    VARCHAR2,
                                       object_name      VARCHAR2)
return varchar2 as
retval varchar2(200);
begin
if user='MYSCOTT' then retval:='deptno=10'; end if;
return retval;
end;
/

SQL> BEGIN
dbms_rls.add_policy(object_schema => 'scott',
 object_name => 'dept',
 policy_name => 'datapump export',
 policy_function => 'filter_myscott');
END;
/

Vous pouvez vérifier que la table DEPT ne se;ble alors plus n’avoir qu’une ligne pour MYSCOTT :

SQL> connect myscott@redx

SQL> select * from scott.dept;

DE DNAME          LOC
-- -------------- -------------
10 ACCOUNTING     NEW YORK

Il ne vous reste qu’à lancer l’import par le database link et les colonnes seront filtrées selon les conditions de votre VPD :

$ impdp myscott tables=scott.dept 
 remap_schema=scott:myscott           
 remap_tablespace=users:demo    
 nologfile=y network_link=myscott_redx

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 21:42:29

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MYSCOTT"."SYS_IMPORT_TABLE_01":  myscott/******** tables=scott.dept remap_schema=scott:myscott/******** remap_tablespace=users:demo nologfile=y network_link=myscott/********_redx
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "MYSCOTT"."DEPT"                                 1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/RLS_POLICY
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "MYSCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 21:42:44

Voila, plus que 2 regrets :

• Data Pump ne permet pas de faire des chargements avec des validations intermédiaires (cf le mot clé COMMIT de la commande « imp »)
• Le mot clé remap_table ne fonctionne qu’avec les tablespaces transportables et ça serait vraiment utile de pouvoir renommer la table avant de l’importer pour éviter un conflit avec une table existante. Aujourd’hui vous ne pouvez faire qu’un ALTER TABLE RENAME après l’import dans le cas du Database Link.

Bon, cela dit… comme vous pouvez le constater, et sans même parler des performances, Data Pump a surement changer beacoup de chose dans votre vie de DBA…