76.3. expdp/impdp

76.3. expdp/impdp
上一页	第 76 章 CLI	下一页

创建 dump 目录与用户

create directory mydump as '/u01/oracle';

grant read, write on directory mydump to dumpuser
grant read,write on directory backup to finance;

76.3.1. expdp

导出用户的数据

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;

76.3.2. impdp

导入该用户数据

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

76.3.3. 数据泵演示

创建目录

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;

例 76.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文件

例 76.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

76.3.4. 查看dmp文件的表空间

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

上一页	上一级	下一页
76.2. exp/imp	起始页	76.4. RMAN