Blog

le 22/08/2011
Thomas Bordet
Archives blog-arkzoyd @fr

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]

Thomas Bordet

Voir tous ses articles

2 réflexions sur “Utiliser Datapump en PL/SQL”

ArKZoYD
27/08/2011 à 10:45

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
Michel Stevelinck
27/08/2011 à 10:20

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 ?

Les commentaires sont fermés.

Blog

Utiliser Datapump en PL/SQL

Export du schéma SCOTT

Import du schéma SCOTT dans l’utilisateur DEMO

2 réflexions sur “Utiliser Datapump en PL/SQL”

Articles récents

Export du schéma `SCOTT`

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