ArKZoYd: Utiliser Datapump en PL/SQL

Le PL/SQL, lorsqu'on utilise une base de données Oracle, est plus facile à porter d'une plateforme à une autre qu'un script Perl, il permet de gérer finement les exceptions, il est facilement intégrable à tous les outils de la base de données à commencer par DBMS_SCHEDULER et il est simple pour n'importe quel DBA ! Alors, si vous faîtes l'effort de lire Oracle® Database PL/SQL Packages and Types Reference - DBMS_DATAPUMP, il est probable que vous troquerez bientôt la ligne de commande expdp pour un bloc PL/SQL ou une procédure...

Cet article présente un script PL/SQL qui utilise le package DBMS_DATAPUMP pour exporter le schéma SCOTT puis crée et l'importe dans l'utilisateur DEMO

Export du schéma `SCOTT`

La ligne de commande qui exporte le schéma SCOTT, en supposant que vous ayez préalablement créé le directory TMP ressemble à ceci :

expdp \"/ as sysdba\" directory=tmp \
           flashback_time=\"systimestamp\" schemas="(scott)" \
           dumpfile=scott.dmp logfile=scott.log cluster=n parallel=1

Vous pouvez écrire la même commande en PL/SQL à l'aide du package DBMS_DATAPUMP :

sqlplus / as sysdba

set serveroutput on
declare 
   vhandle number;
   cscn    number;
begin
   vhandle:=dbms_datapump.open(
      operation=> 'EXPORT',
      job_mode => 'SCHEMA',
      job_name => 'EXPORT_SCOTT',
      version  => 'COMPATIBLE');
   dbms_output.put_line('Handle: '||to_char(vhandle));
   dbms_datapump.add_file(
      handle    => vhandle,
      filename  => 'scott.dmp',
      directory => 'TMP',
      filetype  => dbms_datapump.ku$_file_type_dump_file );
   dbms_datapump.add_file(
      handle    => vhandle,
      filename  => 'scott.log',
      directory => 'TMP',
      filetype  => dbms_datapump.ku$_file_type_log_file );
   select current_scn into cscn from v$database;
   dbms_datapump.set_parameter(
      handle => vhandle,
      name   => 'FLASHBACK_SCN',
      value  => cscn);
   dbms_datapump.metadata_filter(
      handle => vhandle,
      name   => 'SCHEMA_LIST',
      value  => '''SCOTT''');
   dbms_datapump.set_parallel(
      handle     => vhandle,
      degree     => 1);
   dbms_datapump.start_job(
      handle     => vhandle,
      cluster_ok => 0);
end;
/
Handle: 9

set tab off
col operation format a10
col state format a15
select operation, state, degree
  from user_datapump_jobs
 where JOB_NAME='EXPORT_SCOTT';

OPERATION  STATE               DEGREE
---------- --------------- ----------
EXPORT     EXECUTING                1

/

OPERATION  STATE               DEGREE
---------- --------------- ----------
EXPORT     COMPLETING               1

/

no rows selected

!cat /tmp/scott.log
Starting "SYS"."EXPORT_SCOTT":  
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
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
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYS"."EXPORT_SCOTT" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.EXPORT_SCOTT is:
  /tmp/scott.dmp
Job "SYS"."EXPORT_SCOTT" successfully completed at 19:05:53

Vous remarquez l'affichage de la valeur du "handler". Bien que ça ne soit pas obligatoire, ça facilite la reprise dans le cas où votre script échoue après la commande OPEN. En cas d'échec alors que le job n'est pas encore créé et que la commande ATTACH ne fonctionne pas encore, il suffit de se déconnecter de la session. Cela vous permet de fermer le handler et de reprendre les opérations ; quoiqu'il en soit connaître la valeur du handler est sans doute raisonnable pour continuer les opérations.

Import du schéma `SCOTT` dans l'utilisateur `DEMO`

Pour créer l'utilisateur DEMO et importer le fichier généré précédemment dans cet utilisateur, vous pouvez utiliser la commande ci-dessous :

impdp \"/ as sysdba\" directory=tmp schemas="(scott)" \
           remap_schema=scott:demo dumpfile=scott.dmp \
           logfile=demo.log cluster=n parallel=1

Vous pouvez également lancer le script ci-dessous :

set serveroutput on
declare 
   vhandle number;
begin
   vhandle:=dbms_datapump.open(
      operation=> 'IMPORT',
      job_mode => 'SCHEMA',
      job_name => 'IMPORT_SCOTT',
      version  => 'COMPATIBLE');
   dbms_output.put_line('Handle: '||to_char(vhandle));
   dbms_datapump.add_file(
      handle    => vhandle,
      filename  => 'scott.dmp',
      directory => 'TMP',
      filetype  => dbms_datapump.ku$_file_type_dump_file );
   dbms_datapump.add_file(
      handle    => vhandle,
      filename  => 'demo.log',
      directory => 'TMP',
      filetype  => dbms_datapump.ku$_file_type_log_file );
   dbms_datapump.metadata_filter(
      handle => vhandle,
      name   => 'SCHEMA_LIST',
      value  => '''SCOTT''');
   dbms_datapump.metadata_remap(
      handle      => vhandle,
      name        => 'REMAP_SCHEMA',
      old_value   => 'SCOTT',
      value       => 'DEMO');
   dbms_datapump.start_job(
      handle     => vhandle,
      cluster_ok => 0);
end;
/

Handle: 12

declare
   vhandle number;
begin
   vhandle:=dbms_datapump.attach(
      job_name => 'IMPORT_SCOTT',
      job_owner => 'SYS');
   dbms_datapump.stop_job (
      handle    => vhandle,
      immediate => 1,
      delay     => 0);
end;
/ 

set tab off
col operation format a10
col state format a15
select operation, state, degree
  from user_datapump_jobs
 where JOB_NAME='IMPORT_SCOTT';

OPERATION  STATE               DEGREE
---------- --------------- ----------
IMPORT     COMPLETING               1

/

no rows selected

!cat /tmp/demo.log
Master table "SYS"."IMPORT_SCOTT" successfully loaded/unloaded
Starting "SYS"."IMPORT_SCOTT":  
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."DEPT"                               5.929 KB       4 rows
. . imported "DEMO"."EMP"                                8.562 KB      14 rows
. . imported "DEMO"."SALGRADE"                           5.859 KB       5 rows
. . imported "DEMO"."BONUS"                                  0 KB       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
Job "SYS"."IMPORT_SCOTT" successfully completed at 19:07:49

Notes:

En cas d'échec d'un job, vous pouvez investiguer les erreurs en visualisant le contenu de la table maitre qui a le nom du job que vous lancez. Pour arrêter un job en concervant cette table, utilisez keep_master=>1 dans la procédure dbms_datapump.stop_job

Si vous voulez filtrer ou mettre en correspondance avec précision certains objets, les vues suivantes sont très utiles : DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, TABLE_EXPORT_OBJECTS et TABLESPACE_EXPORT_OBJECTS ; elles listent les types d'objets exportés par type d'export datapump (FULL, SCHEMA, TABLE ou TABLESPACE)

Il est possible d'utiliser le paramètre flashback_time=\"systimestamp\" pour effectuer un export consistant à la consistent=y de la commande exp. Il existe toutefois plusieurs problèmes liés à l'utilisation de ce paramètre ; vous lirez avec intérêt les notes :
Expdp Reports Terminates Due To Ora-01466 When Using Flashback_time [ID 1165825.1]

Bug 5472500: EXPDP WITH FLASHBACK_SCN DOES NOT EXPORT ALL EXPECTED ROWS

How to Connect AS SYSDBA when Using Export or Import [ID 277237.1]

2 commentaires:

Michel Stevelinck a dit…: Merci pour vos articles. Il me semble qu'Oracle recommande d'utiliser le user "system" pour effectuer des exp et expdp. je vois dans vos exemples que vous utilisez le user SYS "/ as sysdba". Qu'en est-il vraiment ?; 12:20 PM
ArKZoYD a dit…: Michel,

C'est effectivement une bonne question. La réponse m'échappe encore un peu. Voilà ce que je sais :

1. Avec une 11.2 au moins, ça ne pose pas de problème. J'ai fait des tests en faisant un export en chargeant plusieurs tables simultanément avec des commit pour assurer les points de synchro ; l'export est bien consistent, connecté SYSTEM ou SYS.

2. Avec 10g, il semble que les paramètres FLASHBACK_XXX soient sensibles, y compris connecté SYSTEM d'ailleurs... Les notes dans l'article référencent en particulier un bug qui semble encore dans les derniers patch set. Cela étant, j'ai fait des exports datapump avec SYS sans problèmes en 10g... applications arrếtées !

3. Flashback Query ne fonctionne pas avec SYS, y compris avec 11g. Pour s'en persuader, il suffit de se connecter SYS et d'utiliser dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER().

4. Je soupconne que Datapump utilise une fonction du genre de l'UPI OCI CHANGE USER en interne ce qui permet d'utiliser flashback database. A moins qu'il utilise "AS OF SCN". C'est facile à vérifier, il suffit de tracer le SQL

Grégory; 12:45 PM