Export Oracle avec EXPDP Data Pump

Jeudi 16 juin 2011 4 16 /06 /Juin /2011 11:54

Export Oracle avec EXPDP Data Pump

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

Plus
- Email
- Lien
- Imprimer

Par Daniel Roesch - Publié dans : IMPORT / EXPORT
Ecrire un commentaire - Voir les 0 commentaires

Précédent : Commandes UNIX pour DBA Oracle Retour à l'accueil Suivant : Import Oracle avec IMPDP Data Pump

Administration Oracle

PLSQL Tutoriel

Fonctions Oracle

DBA Scripts

Commandes SQL