Export / Import
Export / Import
There are two sets of Oracle Export/Import utilities:
In general, you should use new Oracle Data Pump utilities (available since Oracle Database 10g) because they are superior. But sometimes you have to use old utilities. For example:
- You import files that were created by the original export utility;
- You export files that will be imported by the original import utility;
The table below shows which exp/imp releases to use when moving data between different releases of the Oracle database.
From → To | Export with | Import with |
---|---|---|
11.1 → 11.2 | DP Exp 11.1 | DP Imp 11.2 |
10.2 → 11.2 | DP Exp 10.2 | DP Imp 11.2 |
10.1 → 11.2 | DP Exp 10.1 | DP Imp 11.2 |
9.2 → 11.2 | Orig Exp 9.2 | Orig Imp 11.2 |
8.1.7 → 11.2 | Orig Exp 8.1.7 | Orig Imp 11.2 |
8.0.6 → 11.2 | Orig Exp 8.0.6 | Orig Imp 11.2 |
7.3.4 → 11.2 | Orig Exp 7.3.4 | Orig Imp 11.2 |
Note that files generated by the original exp utility cannot be imported with the Data Pump Import utility, and vice versa.
From → To | Export with | Import with |
---|---|---|
11.2 → 11.1 | DP Exp 11.2 | DP Imp 11.1 |
11.1 → 10.2 | DP Exp 11.1 | DP Imp 10.2 |
10.2 → 10.1 | DP Exp 10.2 | DP Imp 10.1 |
Data Pump Import cannot read a dump file created by a database version newer than the current version, unless that dump file was created with the VERSION param set to the version of the target database.
From → To | Export with | Import with |
---|---|---|
8.1.5 → 8.0.6 | Orig Exp 8.0.6 | Orig Imp 8.0.6 |
8.1.7 → 8.1.6 | Orig Exp 8.1.6 | Orig Imp 8.1.6 |
9.0.1 → 8.1.6 | Orig Exp 8.1.6 | Orig Imp 8.1.6 |
9.0.1 → 9.0.2 | Orig Exp 9.0.1 | Orig Imp 9.0.2 |
9.0.2 → 10.1.0 | Orig Exp 9.0.2 | Orig Imp 10.1.0 |
10.1.0 → 9.0.2 | Orig Exp 9.0.2 | Orig Imp 9.0.2 |
Export / Import (original, old)
Full database export/import requires powerful privs (seeEXP_FULL_DATABASErole,IMP_FULL_DATABASErole) that regular users should never have. Regular users nowdays usually have onlyCREATE SESSIONpriv and can export their own objects, but the import of anything requires the whole bunch of system privs likeCREATE TABLE,CREATE INDEX, etc; the old (outdated)RESOURCErole had them all.
Full database export:
exp PARFILE=param.dat
where parameter file (param.dat):
USERID=SYSTEM/pass@s3 FULL=Y COMPRESS=Y CONSISTENT=Y LOG=export.log
If this is not a scheduled / automated procedure, remove password (to be prompted for it at the right moment); otherwise set restrictive permissions onparam.dat(anyway this is not safe).
Export all objects owned by userpro17(the whole user’s schema):
exp system owner=pro17
Export tableusrlistowned byappadm:
exp SYSTEM TABLES=APPADM.USRLIST
GRANTS=Y INDEXES=N LOG=EXPORT.LOG
Being Oracle userdbsman export (over a network) some tables:
exp dbsman@s4 tables=(usrlist,jobs,depts)
Full database export, old style, Windows / Oracle 7.3:
exp73 SYSTEM@s2 FULL=Y LOG=EXPORT.LOG
Full database import (requires some preliminary work); full db export file (expdat.dmp) must be present in the current working directory:
imp SYSTEM FULL=Y LOG=IMPORT.LOG
Being Oracle userdbsman import some of your tables from the default export file (expdat.dmp, must be in the current working dir):
imp dbsman tables=(usrlist,jobs,depts)
Import using parameter file:
imp PARFILE=param.dat
Parameter file to be used to import a table to another schema assuming you have a full database export fileexpdat.dmp (you'll be prompted for SYSTEM's password):
USERID=system TABLES=(hw_computers) FROMUSER=pro08 TOUSER=dbsman LOG=imp002.log
Parameter file to be used to import some user tables from a full database export file (database is available throughsrv2alias; user must exist; you'll be prompted for SYSTEM's password):
USERID=system@srv2 TABLES=(usrlist,usrlist2,hw_ip_addr) FROMUSER=eco30 TOUSER=eco30 LOG=imp005.log
Param file to be used to import the whole user's schema from a full db exp file (user must exist; you'll be prompted for SYSTEM's password):
USERID=system FROMUSER=a301 TOUSER=a301 LOG=imp007.log
If tables being imported already exist in the database and there are referential constraints between them, some/all rows may be rejected due to constraint violation. Tables are imported in the order of their location in exp dump file, which can be wrong for constraints. In this case you should import related tables separately, in the right order. Or, you can disable constraints and re-enable after import. Also, if tables exist, useIGNORE=y, otherwise when imp
fails to create table, it does not attempt to insert rows - just skips to the next object.
USERID=SYSTEM FILE=expdat.dmp GRANTS=n IGNORE=y BUFFER=262144 FROMUSER=dbsman TOUSER=dbsman TABLES=(tab0202,tab0220) LOG=imp0029.log
Export / Import (Data Pump)
Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later. Oracle Data Pump is made up of three distinct parts:
- The command line clients,
expdp
andimpdp
. - TheDBMS_DATAPUMPPL/SQL pkg (Data Pump API).
- TheDBMS_METADATAPL/SQL pkg (Metadata API).
The Data Pump clients, expdp
and impdp
, invoke the Data Pump Export utility and Data Pump Import utility, respectively. Dump files generated by the Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, old-style exports cannot be imported with the Data Pump Import utility.
The Data Pump is more effective than original exp
/ imp
(due to parallel execition, etc), can unload into a group of files [of the specified length], however, it requires more undo space.
To make full use of Data Pump technology, you must have EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
Data Pump supports 2 access methods:direct pathandexternal tables. Both methods support the same external data representation, so data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the best. To handle external tables Data Pump usesORACLE_DATAPUMPaccess driver.
Warning!
The files that Data Pump creates when it uses external tables are not compatible with files created when you manually create an external table!
Because Data Pump is server-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. It means you may need to create OS dirs and Oracle directory objects. A dir object maps a name to a dir path on the filesystem:
CREATE DIRECTORY dpump_dir1 AS
'/u06/exp/datafiles';
The creator (usuallyDBA) can give permissions to other users:
GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO pro17;
These permissions allow Oracle to read / write files on your behalf. To access these files outside of the Oracle you need OS permissions.
The default dir object,DATA_PUMP_DIR, is created at database creation and is available only to privileged users. To see the path:
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = 'DATA_PUMP_DIR';
The client can also set the env variableDATA_PUMP_DIR, provided it maps to an existing OS dir and is accessible.
The Data Pump Export / Import modes:
- Full (param name isFULL).
- Schema (param name isSCHEMAS).
- Table (param name isTABLES).
- Tablepsace (param name isTABLESPACES).
- Transportable Tablespace (TRANSPORT_TABLESPACES).
You can interact with Data Pump using following modes:
- Logging mode / command line interface.
- Logging mode / parameter file interface.
- Interactive command mode.
Warning!
Never invoke Import as SYSDBA, except at the request of Oracle tech support!
There are 2 sources of import:dump file set andanother database (network import). If import source is dump and mode is not specified, then import attempts to load the entire dump file set in the mode in which the export operation was run.
Logging mode parameters (Export and Import)
ATTACH attaches client session to an existing exp/imp job and automatically places you in the interactive cmd interface;
CONTENT={DATA_ONLY |METADATA_ONLY |ALL } specifies what to unload (load) during export (import); default isALL;
DIRECTORY specifies the default location (dir object) for dump file sets and log files (default isDATA_PUMP_DIR);
DUMPFILE specifies the names, and optionally, the dir objects of dump files (default isexpdat.dmp);
ENCRYPTION_PASSWORD specifies a key for encrypting encrypted column data during export or accessing encrypted column data during import;
ESTIMATE={BLOCKS |STATISTICS } specifies the method that will be used to estimate how much disk space each table in the export job will consume, or how much data will be generated in a network import operation; for import it can be used only whenNETWORK_LINK is specified; the default isBLOCKS;
EXCLUDE enables you to filter the metadata that is exported (imported) by specifying objects and object types that you want to exclude (see examples);
FLASHBACK_SCN specifies the system change number that export (import) will use to enable the Flashback Query utility;
FLASHBACK_TIME the SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback Query utility;
FULL={y |n } specifies a full database export (import); the following system schemas are not exported because the metadata they contain is exported as part of other objects in the dump file set: SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP, WMSYS; grants on objects owned by the SYS schema are never exported; default isn;
HELP displays online help;
INCLUDE enables you to filter the metadata that is exported (imported) by specifying objects and object types; during export dependent objects and grants are also exported;
JOB_NAME identifies the export (import) job in subsequent actions (ATTACH), and becomes the name of the master table in the current user's schema (the master table is used to control the job);
LOGFILE specifies the name for the log file of exp (imp) job; default isexport.log (import.log);
NOLOGFILE={y |n } specifies whether to suppress creation of a log file (default isn);
PARALLEL (Enterprise Edition only) specifies the max num of threads of active execution operating on behalf of the exp / imp job (default is1);
PARFILE the name of an export (import) param file;
QUERY allows you to filter data that is exported (imported) by specifying a clause for a SQL SELECT stmt, which is applied to all tables in the exp (imp) job or to a specific table;
SCHEMAS enables a schema-mode export (import) and specifies a comma-separated list of schemas (by default, current user's schema); only priv users can exp (imp) other user's schemas; this is the default mode for export and network-based import;
STATUS specifies how frequently (in seconds) the job status info is written to stdout; default is0 (never);
TABLES invokes a table-mode export (import) and specifies a comma-separated list of tables (partitions, subpartitions);
TABLESPACES enables a tablespace-mode exp (imp) and specifies a list of TS names to be exported (imported);
TRANSPORT_FULL_CHECK={y |n } specifies whether or not to verify that the specified transportable tablespace set has no dependencies; default isn;
TRANSPORT_TABLESPACES specifies that you want to perform a transportable tablespace mode export (import);
VERSION specifies the version of database objects to be exported (imported); can be used to create a dump file set compatible with a prev release of Oracle (does not mean that dump set can be used with versions prior to 10.1); default isCOMPATIBLE;
Export only parameters
COMPRESSION={METADATA_ONLY |NONE } specifies whether to compress metadata before writing to the dump file set;NONE disables compression for the entire unload (default isMETADATA_ONLY);
ESTIMATE_ONLY={y |n } instructs Export to estimate the space that a job would consume, without actually performing export (default isn);
FILESIZE the max size of each dump file; if the size is reached for any member of the dump file set, that file is closed and an attempt is made to create a new one (file specification must contain a substitution variable); default is0 (unlimited);
SAMPLE specifies the percentage of data to be sampled and unloaded from the source database;
Import only parameters
NETWORK_LINK enables a network import when you specify the name of a valid database link to a source system;
REMAP_DATAFILE changes the name of the source datafile to the target in all SQL stmts where source datafile is referenced:CREATE TABLESPACE |LIBRARY |DIRECTORY;
REMAP_SCHEMA loads all objects from the source schema into a target schema;
REMAP_TABLESPACE remaps all objects selected for import with persistent data in the source TS to be created in the target TS;
REUSE_DATAFILES={y |n } specifies whether or not the imp job should reuse existing datafiles for TS creation (default isn);
SKIP_UNUSABLE_INDEXES={y |n } specifies whether or not Import skips loading tables that have indexes that were set to theIndex Unusable state (by either the system or the user); default is set by db config param of the same name;
SQLFILE a file into which all SQL DDL that Import would have executed, based on other params, is written;
STREAMS_CONFIGURATION={y |n } specifies whether or not to import any general Streams metadata that may be present in the export dump file; default isy;
TABLE_EXISTS_ACTION={SKIP |APPEND |TRUNCATE |REPLACE } tells Import what to do if the table it is trying to create already exists; default isSKIP (orAPPEND, if CONTENT=DATA_ONLY);
TRANSFORM enables you to alter object creation DDL for specific objects, as well as for all applicable objects being loaded;
TRANSPORT_DATAFILES a list of datafiles to be imported into the target database by a transportable-mode import (files must already have been copied from src db);
Interactive command mode
You can start the interactive cmd mode while export / import job is running (use another terminal or stop the current expdp / impdp session - this does not affect the job). To start an interactive session, run expdp / impdp withATTACH param. In general, you must specify the job name (queryDBA_DATAPUMP_JOBS,USER_DATAPUMP_JOBS), but if there is currently only one job in the user's schema, name is not required. For example:
expdp sysadm/psmtks72 ATTACH=sysadm.export_job
or
expdp sysadm/psmtks72 ATTACH
The same syntax is valid for impdb
.
You will see the interactive cmd mode prompt Export>
(Import>
). There are following cmds available in
interactive cmd mode:
ADD_FILE : add additional dump files (export only);
CONTINUE_CLIENT : exit interactive and enter logging mode;
EXIT_CLIENT : stop client session, but leave the job running;
FILESIZE : redefine the default size to be used for any subsequent dump files (export only);
HELP : display a summary of available cmds;
KILL_JOB : detach all currently attached client sessions and kill the current job;
PARALLEL : increase / decrease the number of active worker processes for the current job (Enterprise Edition only);
START_JOB : restart a stopped job to which you are attached;
STATUS : display detailed status for the current job and/or set status interval;
STOP_JOB : stop the current job for later restart;
Examples
To export 2 tables (items,equip) owned by alex:
expdp alex/dkmtv45s
TABLES=items,equip LOGFILE=x_items.log
To export the whole user's schema:
expdp alex/dkmtv45s
SCHEMAS=alex DUMPFILE=user_dumps:exp%U.dmp
FILESIZE=10M LOGFILE=exp_alex.log
ParameterDUMPFILE specifies both non-default dir and the name of the export file,%U is used to generate sequential numbers in filenames in case the export file exceeds 10M.
The schema-mode export done by DBA:
expdp dbsman SCHEMAS=alex,mistique
To export a user's schema excluding functions and procedures:
expdp alex/dkmtv45s
SCHEMAS=alex EXCLUDE=FUNCTION,PROCEDURE
To perform a full database export you must be a DBA or, at least, a privileged user (the one with EXP_FULL_DATABASE role):
expdp sysadm FULL=y
You can put all params in a parameter file and run export like this:
expdp sysadm/aspw6xhe PARFILE=param.dat
Parameter file (param.dat) for the full database export:
FULL=y DIRECTORY=dpump_dir1 FILESIZE=650M DUMPFILE=expful%U.dmp LOGFILE=expfull.log
Another param.dat for the full database export:
FULL=y EXCLUDE=TABLE:"LIKE 'APP_LOG%'" EXCLUDE=SCHEMA:"='MISTIQUE'" FILESIZE=1G DUMPFILE=expful%U.dmp PARALLEL=2 JOB_NAME=expfull
To estimate the space that would be consumed in a table-mode export, without actually performing the export operation:
expdp alex/dkmtv45s ESTIMATE_ONLY=y
TABLES=emp,dept LOGFILE=estimate.log
To import a user's schema:
impdp sysadm SCHEMAS=zorg TABLE_EXISTS_ACTION=REPLACE
To import tables:
impdp alex/dkmtv45s TABLES=items,equip
EXCLUDE=CONSTRAINT,GRANT LOGFILE=items.log
To perform a full database import excluding some schemas and objects:
impdp sysadm PARFILE=imp_param.dat
whereimp_param.dat is like this:
FULL=y DIRECTORY=dpump_dir1 EXCLUDE=SCHEMA:"='MANAGER4'" EXCLUDE=TABLE:"LIKE 'RPT_AG%'"
To move an imported table to another schema:
impdp alex/dkmtv45s
TABLES=members REMAP_SCHEMA=zorg:alex
Unprivileged users can perform remap only if their schema is the target.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战