Datapump import/export are used for data migration. You can export TABLES, SCHEMA, TABLESPACE or DATABASE and restore it on another server.
Quickfacts:
1. Datapump produces a dumpfile with extention .dmp or .imp
2. Privilege 'exp_full_database' is required to export full database
3. Privilege 'imp_full_database' is required to import full database
4. In datapump, you can user a parfile to store your commands
Getting the help page
expdp help=y
Export: Release 10.1.0.2.0 - Production on Tuesday, 23 March, 2004 8:33
Copyright (c) 2003, Oracle. All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
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 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.
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.
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.
expdp help=y
=====================================================================================
Export: Release 10.1.0.2.0 - Production on Tuesday, 23 March, 2004 8:33
Copyright (c) 2003, Oracle. All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
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 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.
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.
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.
==============================================================================================
Example 1: Joe is a user on a database, Joe needs to export the whole database.
Solution:
c:\> sqlplus sys/**** as sysdba
grant exp_full_database to joe;
commit;
exit;
c:\> expdp joe/**** full=y dumpfile=joe_database.dmp logfile=joe.log compression=n
^full database is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'
Example 2: System needs to export Joe's schema with compression
Solution:
c:\> expdp system/**** full=n schemas=joe dumpfile=joe_schema.dmp logfile=joe1.log compression=y
^Joe's schema is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'
^Dumpfile will be compressed
Example 3: Joe needs to export 2 tables (Student, Lecturer) to a location on disc ('c:\oracle')
Solution:
c:\> sqlplus joe/****
create directory oracle as 'c:\oracle';
commit;
exit;
c:\> expdp joe/**** full=n tables=Student,Lecturer directory=oracle dumpfile=joe_tables.dmp logfile=joe2.log
^Joe's table are exported
^Dumpfile will be created in 'c:\oracle'
^There should be no space between the tables...e.g tables=(student1,student2,student3)
IMPORT
Example 4: Joe needs to import 2 tables (Student, Lecturer) from a directory called 'oracle'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp tables=Student,Lecturer logfile=joe3.log
^Joe's table will be imported
Example 5: Joe needs to import 2 tables (Student, Lecturer) and change the tables name to 'student1' and 'student2'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp logfile=joe3.log remap_table=joe.Student:student1,joe.Lecturer:student2
^Joe's table will be imported and renamed
^Tables must be separated by comma, the colon (:) must separate the oldname and new name
Example 6: System needs to import the whole database from a dumpfile on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=y dumpfile=database.dmp logfile=db.log
Example 7: System needs to import joe's schema from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db1.log
^Joe's schema will be imported
Example 8: System needs to import joe's schema and change name to DANIEL and also change the tablespace to daniel's tablespace
from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n dumpfile=database.dmp logfile=db2.log remap_schema=joe:daniel remap_tablespace=users:daniel_tbs
^Joe's schema will be imported and changed to Daniel
^Tablespace will be changed from users to Daniel_tbs
^you MUST physically create this tablespace and grant access to Daniel.
Example 9: System needs to import joe's schema using a parameter file
Solution:
create a parfile called 'joe.txt'
USERNAME=system/****
FULL=N
SCHEMA=(joe,daniel,class)
DIRECTORY=oracle
DUMPFILE=database.dmp
LOGFILE=db4.log
Save file and exit
c:\> impdp parfile=joe.txt
Parfile makes it more convenient to adjust datapump commands. It also help when you need to constantly run same commands.
No comments:
Post a Comment