Overblog Suivre ce blog
Editer l'article Administration Créer mon blog
16 juin 2011 4 16 /06 /juin /2011 11:54

 

EXPORTER une BASE ORACLE avec EXPDP (DATA PUMP EXPORT)

Comment exporter ses données sous Oracle 10G et 11G ?.

Avec l'utilitaire DATA PUMP EXPORT.

EXPDP est apparu depuis la version 10G d'Oracle, l'utilitaire EXP est toujours disponible et fonctionnel.
A savoir que les fichiers d'exports générés par EXPDP et EXP sont incompatibles entre eux.

Nous allons voir ici comment exporter une base de données, un schéma, une table, un tablespace.

L'utilitaire EXPDP a besoin d'un répertoire de destination pour écrire son export, à partir de la 10G, on utilise un objet DIRECTORY.

 

Création DIRECTORY Oracle pour EXPDP.

 
 SQL> CREATE OR REPLACE DIRECTORY DBTEST_EXPORT AS 'C:\EXPORT\'; Directory created. SQL> GRANT READ, WRITE ON DIRECTORY DBTEST_EXPORT TO scott; Grant succeeded. SQL> COLUMN OWNER FORMAT A10 SQL> COLUMN DIRECTORY_NAME FORMAT A20 SQL> COLUMN DIRECTORY_PATH FORMAT A40 SQL> SELECT * FROM all_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ---------- -------------------- ---------------------------------------- SYS ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/admin SYS WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/work SYS KEYDIR C:\WINDOWS SYS DBTEST_EXPORT C:\EXPORT\ 4 rows selected. SQL> 

 

Exporter toute la base de données (FULL Export) avec EXPDP.

 
 C:\>SET ORACLE_SID=DBTEST C:\>expdp system/pwd full=Y directory=DBTEST_EXPORT dumpfile=full_dbtest.dmp logfile=full_dbtest.log Export: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 12:54:29 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=Y directory=DBTEST_EXPORT dumpfile=full_dbtest.dmp logfile=full_dbtest.log Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA ... ... Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: C:\EXPORT\FULL_DBTEST.DMP Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 13:07:59 C:\> 

 

Exporter un schéma sous Oracle avec EXPDP.

 
 C:\>SET ORACLE_SID=DBTEST C:\>expdp system/pwd schemas=SCOTT directory=DBTEST_EXPORT dumpfile=SCOTT.dmp logfile=SCOTT.log Export: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 13:19:02 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=SCOTT directory=DBTEST_EXPORT dumpfile=SCOTT.dmp logfile=SCOTT.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 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/TABLESPACE_QUOTA 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/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.656 KB 4 rows . . exported "SCOTT"."EMP" 7.578 KB 8 rows . . exported "SCOTT"."NEW_EMP" 7.585 KB 8 rows . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows . . exported "SCOTT"."VEHICULE" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: C:\EXPORT\SCOTT.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:19:23 C:\> 

 

Exporter des tables sous Oracle avec EXPDP.

 
 C:\>SET ORACLE_SID=DBTEST C:\>expdp system/pwd tables=scott.DEPT,scott.SALGRADE directory=DBTEST_EXPORT dumpfile=DEPT_SALG.dmp logfile=DEPT_SALG.log Export: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 13:31:18 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=scott.DEPT,scott.SALGRADE directory=DBTEST_EXPORT dumpfile=DEPT_SALG.dmp logfile=DEPT_SALG.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 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.656 KB 4 rows . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: C:\EXPORT\DEPT_SALG.DMP Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 13:31:32 C:\> 

 

Exporter des tablespaces sous Oracle avec EXPDP.

 
 C:\>SET ORACLE_SID=DBTEST C:\>expdp system/pwd TABLESPACES=rman_catalog directory=DBTEST_EXPORT dumpfile=tbs_rman.dmp logfile=tbs_rman.log Export: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 13:46:00 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** TABLESPACES=rman_catalog directory=DBTEST_EXPORT dumpfile=tbs_rman.dmp logfile=tbs_rman.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.25 MB 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/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ... . . exported "RMAN"."RCVER" 4.929 KB 1 rows . . exported "RMAN"."RLH" 21.56 KB 293 rows . . exported "RMAN"."ROUT" 8.398 KB 32 rows . . exported "RMAN"."RSR" 11.28 KB 2 rows . . exported "RMAN"."RT" 7.078 KB 1 rows . . exported "RMAN"."TF" 7.687 KB 1 rows . . exported "RMAN"."TFATT" 7.757 KB 1 rows . . exported "RMAN"."TS" 8.281 KB 7 rows . . exported "RMAN"."TSATT" 6.578 KB 7 rows . . exported "RMAN"."AL" 0 KB 0 rows ... Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is: C:\EXPORT\TBS_RMAN.DMP Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 13:46:24 C:\> 

 

HELP arguments et options utilitaire EXPDP.

 
 C:\>EXPDP help=Y Export: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 13:58:03 Copyright (c) 2003, 2005, Oracle. All rights reserved. USERID must be the first parameter on the command line. Keyword Description (Default) ------------------------------------------------------------------------------ ATTACH Attach to existing job, e.g. ATTACH [=job name]. COMPRESSION Reduce size of dumpfile contents where valid keyword values are: (METADATA_ONLY) and NONE. 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. ENCRYPTION_PASSWORD Password key for creating encrypted column data. 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. SAMPLE Percentage of data to be exported; 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. VERSION Version of objects to export where valid keywords are: (COMPATIBLE), LATEST, or any valid database version. The following commands are valid while in interactive mode. Note: abbreviations are allowed Command Description ------------------------------------------------------------------------------ ADD_FILE Add dumpfile to dumpfile set. CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle. EXIT_CLIENT Quit client session and leave job running. FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands. HELP Summarize interactive commands. KILL_JOB Detach and delete job. PARALLEL Change the number of active workers for current job. PARALLEL=. START_JOB Start/resume current job. STATUS Frequency (secs) job status is to be monitored where the default (0) will show new status when available. STATUS[=interval] STOP_JOB Orderly shutdown of job execution and exits the client. STOP_JOB=IMMEDIATE performs an immediate shutdown of the Data Pump job. C:\> 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Import Oracle avec IMPDP Data Pump

Partager cet article

Repost 0
Published by Daniel Roesch - dans IMPORT - EXPORT
commenter cet article

commentaires