Import Oracle avec IMPDP Data Pump

Jeudi 16 juin 2011 4 16 /06 /Juin /2011 20:29

Import Oracle avec IMPDP Data Pump

IMPORTER une BASE ORACLE avec IMPDP (DATA PUMP IMPORT).

Comment importer ses données dans Oracle 10G et 11G ?.

Avec l'utilitaire DATA PUMP IMPORT.

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

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

L'utilitaire IMPDP a besoin d'un répertoire source pour aller lire le fichier d'export, à partir de la 10G, on utilise un objet DIRECTORY.

Création DIRECTORY Oracle pour IMPDP.

 
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>

Importer des tablespaces dans Oracle avec IMPDP.

 
C:\>SET ORACLE_SID=DBTEST

C:\>impdp system/pwd TABLESPACES=rman_catalog directory=DBTEST_EXPORT dumpfile=tbs_rman.dmp
logfile=itbs_rman.log

Import: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 20:22:14

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

Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01":  system/******** TABLESPACES=rman_catalog 
directory=DBTEST_EXPORT dumpfile=tbs_rman.dmp logfile=itbs_rman.log

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RMAN"."CCF"                                11.06 KB       2 rows
. . imported "RMAN"."CKP"                                7.789 KB       2 rows
. . imported "RMAN"."CONF"                               6.484 KB       1 rows
. . imported "RMAN"."CONFIG"                             5.242 KB       1 rows
. . imported "RMAN"."DB"                                 6.476 KB       1 rows
. . imported "RMAN"."DBINC"                              14.10 KB       2 rows
. . imported "RMAN"."DF"                                 9.148 KB       6 rows
. . imported "RMAN"."DFATT"                              7.210 KB       6 rows
. . imported "RMAN"."NODE"                               6.828 KB       1 rows
. . imported "RMAN"."OFFR"                               7.851 KB       4 rows
. . imported "RMAN"."ORL"                                6.023 KB       3 rows
. . imported "RMAN"."RCVER"                              4.929 KB       1 rows
. . imported "RMAN"."RLH"                                21.56 KB     293 rows
. . imported "RMAN"."ROUT"                               8.398 KB      32 rows
. . imported "RMAN"."RSR"                                11.28 KB       2 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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at 20:22:34

C:\>

Importer des Tables dans Oracle avec IMPDP.

 
C:\>SET ORACLE_SID=DBTEST

C:\>impdp system/pwd tables=scott.DEPT,scott.SALGRADE directory=DBTEST_EXPORT dumpfile=DEPT_SALG.dmp
logfile=IDEPT_SALG.log

Import: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 21:09:34

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

Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** tables=scott.DEPT,scott.SALGRADE 
directory=DBTEST_EXPORT dumpfile=DEPT_SALG.dmp logfile=IDEPT_SALG.log

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . imported "SCOTT"."SALGRADE"                          5.585 KB       5 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 "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 21:09:39

C:\>

Importer un schéma dans Oracle avec IMPDP.

 
C:\>SET ORACLE_SID=DBTEST

C:\>impdp system/pwd schemas=SCOTT directory=DBTEST_EXPORT dumpfile=SCOTT.dmp logfile=ISCOTT.log

Import: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 21:37:03

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

Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas=SCOTT directory=DBTEST_EXPORT 
dumpfile=SCOTT.dmp logfile=ISCOTT.log

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/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . imported "SCOTT"."EMP"                               7.578 KB       8 rows
. . imported "SCOTT"."NEW_EMP"                           7.585 KB       8 rows
. . imported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
. . imported "SCOTT"."VEHICULE"                              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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:37:08

C:\>

Importer une base de données FULL dans Oracle avec IMPDP.

 
C:\>SET ORACLE_SID=DBTEST

C:\>impdp system/pwd full=Y directory=DBTEST_EXPORT dumpfile=full_dbtest.dmp logfile=ifull_dbtest.log

Import: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 21:48:58

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

Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=Y directory=DBTEST_EXPORT 
dumpfile=full_dbtest.dmp logfile=ifull_dbtest.log 

Processing object type DATABASE_EXPORT/TABLESPACE
....
....
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
.......
.......
Processing object type DATABASE_EXPORT/AUDIT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 21:51:59

C:\>

HELP arguments et options utilitaire IMPDP.

C:\>IMPDP help=Y

Import: Release 10.2.0.1.0 - Production on Thursday, 16 June, 2011 22:02:08

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].
CONTENT               Specifies data to load where the valid keywords are:
                      (ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY             Directory object to be used for dump, log, and sql files.
DUMPFILE              List of dumpfiles to import from (expdat.dmp),
                      e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD   Password key for accessing encrypted column data.
                      This parameter is not valid for network import jobs.
ESTIMATE              Calculate job estimates where the valid keywords are:
                      (BLOCKS) and STATISTICS.
EXCLUDE               Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN         SCN used to set session snapshot back to.
FLASHBACK_TIME        Time used to get the SCN closest to the specified time.
FULL                  Import everything from source (Y).
HELP                  Display help messages (N).
INCLUDE               Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME              Name of import job to create.
LOGFILE               Log file name (import.log).
NETWORK_LINK          Name of remote database link to the source system.
NOLOGFILE             Do not write logfile.
PARALLEL              Change the number of active workers for current job.
PARFILE               Specify parameter file.
QUERY                 Predicate clause used to import a subset of a table.
REMAP_DATAFILE        Redefine datafile references in all DDL statements.
REMAP_SCHEMA          Objects from one schema are loaded into another schema.
REMAP_TABLESPACE      Tablespace object are remapped to another tablespace.
REUSE_DATAFILES       Tablespace will be initialized if it already exists (N).
SCHEMAS               List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE               Write all the SQL DDL to a specified file.
STATUS                Frequency (secs) job status is to be monitored where
                      the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION   Action to take if imported object already exists.
                      Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES                Identifies a list of tables to import.
TABLESPACES           Identifies a list of tablespaces to import.
TRANSFORM             Metadata transform to apply to applicable objects.
                      Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
                      OID, and PCTSPACE.
TRANSPORT_DATAFILES   List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK  Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
                      Only valid in NETWORK_LINK mode import operations.
VERSION               Version of objects to export where valid keywords are:
                      (COMPATIBLE), LATEST, or any valid database version.
                      Only valid for NETWORK_LINK and SQLFILE.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command               Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT       Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT           Quit client session and leave job running.
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.
                      START_JOB=SKIP_CURRENT will start the job after skipping
                      any action which was in progress when job was stopped.
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

• Export Oracle avec EXPDP Data Pump

Plus
- Email
- Lien
- Imprimer

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

Précédent : Export Oracle avec EXPDP Data Pump Retour à l'accueil Suivant : ORA-39142 incompatible version number...

Administration Oracle

PLSQL Tutoriel

Fonctions Oracle

DBA Scripts

Commandes SQL