创建 dump 目录与用户
create directory mydump as '/u01/oracle';
grant read, write on directory mydump to dumpuser grant read,write on directory backup to finance;
导出用户的数据
expdp dumpuser/password directory=mydump dumpfile=user.dmp
导出指定表数据
expdp dumpuser/password directory=mydump dumpfile=table.dmp tables=test1,test2
按用户导
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=backup;
并行进程进程数
expdp scott/tiger@orcl directory=backup dumpfile=scott3.dmp parallel=40 job_name=scott3
指定查询条件
expdp scott/tiger@orcl directory=backup dumpfile=expdp.dmp Tables=emp query='WHERE age=20';
按表空间导
expdp system/manager DIRECTORY=backup DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
导整个数据库
expdp system/manager DIRECTORY=backup DUMPFILE=full.dmp FULL=y;
导入该用户数据
impdp dumpuser/password directory=mydump dumpfile=user.dmp
导出表数据
impdp dumpuser/password directory=mydump dumpfile=table.dmp
$ impdp \'/ as sysdba\' directory=backup dumpfile=user.dmp logfile=user.log tablespaces=dave,bl table_exists_action=replace;
导到指定Schemas下
impdp scott/tiger DIRECTORY=backup DUMPFILE=expdp.dmp SCHEMAS=scott;
改变表的所有者
impdp system/manager DIRECTORY=backup DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
导入表空间
impdp system/manager DIRECTORY=backup DUMPFILE=tablespace.dmp TABLESPACES=example;
导入数据库
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
追加数据
impdp system/manager DIRECTORY=backup DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION
创建目录
mkdir /opt/oracle/backup
创建用户
create user backup identified by passw0rd; GRANT resource,connect,CREATE SESSION,CREATE TABLE,SELECT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,INSERT ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE TO backup; create directory backup as '/opt/oracle/backup'; grant read,write on directory backup to backup;
例 66.1. expdp
$ expdp backup/passw0rd dumpfile=backup.dmp directory=backup Export: Release 11.2.0.1.0 - Production on Tue Dec 1 16:19:56 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "BACKUP"."SYS_EXPORT_SCHEMA_01": backup/******** dumpfile=backup.dmp directory=backup Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB 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/COMMENT Master table "BACKUP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for BACKUP.SYS_EXPORT_SCHEMA_01 is: /opt/oracle/backup/backup.dmp Job "BACKUP"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:20:28
运行完成后会在/opt/oracle/backup/目录生成backup.dmp文件
例 66.2. impdp
首先将dmp文件复制到恢复目录中，确认oracle用户有权限访问该文件。
mv backup.dmp /opt/oracle/backup/ chown oracle:oinstall /opt/oracle/backup/backup.dmp
如果不知道目录可以使用下面语句查看
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='BACKUP'; DIRECTORY_NAME ------------------------------ DIRECTORY_PATH ------------------------------ BACKUP /opt/oracle/backup
$ impdp backup/passw0rd directory=backup dumpfile=backup.dmp Import: Release 11.2.0.1.0 - Production on Tue Dec 1 16:32:34 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31655: no data or metadata objects selected for job ORA-39154: Objects from foreign schemas have been removed from import Master table "BACKUP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "BACKUP"."SYS_IMPORT_FULL_01": backup/******** directory=backup dumpfile=backup.dmp Job "BACKUP"."SYS_IMPORT_FULL_01" successfully completed at 16:32:36
impdp \'/ as sysdba\' dumpfile=backup:expdp.dmp sqlfile=backup:expdp.sql
grep 'TABLESPACE' /opt/oracle/backup/expdp.sql | awk '{print $2}' | sort -u
TABLESPACE
"TS_DATA_DEF"
"TS_DATA_IDX"
UNLIMITED
原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。
