数据泵expdp/impdp基本操作

1.创建备份目录   

 

[root@rac1 ~]# cd /u01
[root@rac1 u01]# mkdir backup
[root@rac1 u01]# ls
app  backup
[root@rac1 u01]# chown oracle:oinstall backup

2.准备数据 

SQL>select * from dba_data_files;
SQL> create tablespace leo datafile'+DATA/leo/datafile/cndba01.dbf' size 500m; 
Tablespace created.
SQL> create user leo identified by leo ;
User created.
SQL> grant connect,resource to leo;
Grant succeeded.
SQL> conn leo/leo
Connected.
SQL> create table leo tablespace leo as select * from all_objects;
Table created.
SQL> select count(*) from leo;
  COUNT(*)
----------
     68339
SQL> create directory backup as '/u01/backup';
Directory created.
SQL> grant read,write on directory backup to leo;
Grant succeeded.

3数据泵基本操作

3.1整库库导出与导入   

expdp /'/as sysdba/'  directory=backup dumpfile=leofull.dmp logfile=leofull.log full=y 
--删除表空间进行测试 
SQL> drop tablespace leo including contents and datafiles; 
Tablespace dropped. 
impdp /'/as sysdba/'  directory=backup dumpfile=leo.dmp logfile=leoimp.log full=y 
--数据恢复成功 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68340

3.2用户级别导出与导入   

--导出用户 
两种方式 
expdp /'/as sysdba/' schemas=leo directory=backup dumpfile=leo2.dmp logfile=leo2.log 
--用户leo 要赋操作目录的权限 
expdp leo/leo schemas=leo directory=backup dumpfile=leo.dmp logfile=leoexp.log 
--导入用户 
--同库测试先 
SQL> drop user leo cascade; 
User dropped. 
SQL> conn /as sysdba 
Connected. 
--导入用户之前要先建用户 
SQL> create user leo identified by leo ; 
User created. 
SQL> grant read,write on directory backup to leo; 
Grant succeeded. 
impdp /'/as sysdba/' schemas=leo directory=backup dumpfile=leo.dmp logfile=leoimp.log 
--用户leo 要赋操作目录的权限 
impdp leo/leo schemas=leo directory=backup dumpfile=leo.dmp logfile=leoimp.log 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68339 

3.3表级导出与导入   

导出表 
expdp /'/as sysdba/'  directory=backup dumpfile=leotable.dmp logfile=leotable.log tables=leo.leo 
expdp leo/leo directory=backup dumpfile=leotable2.dmp logfile=leotable2.log  tables=leo 
导入表 
本库测试 
drop table leo; 
impdp /'/as sysdba/'  directory=backup dumpfile=leotable.dmp logfile=leotable.log tables=leo.leo 
impdp leo/leo directory=backup dumpfile=leotable2.dmp logfile=leotable2.log  tables=leo 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68339 

4.4表空间导出与导入   

 expdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo 
[oracle@rac1 ~]$ expdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo 
Export: Release 11.2.0.4.0 - Production on Wed May 3 11:00:27 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo 
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 8 MB 
Processing object type TABLE_EXPORT/TABLE/TABLE 
. . exported "LEO"."LEO"                                 6.706 MB   68340 rows 
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is: 
  /u01/backup/tbspace1.dmp 
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed May 3 11:00:34 2017 elapsed 0 00:00:05 
SQL> drop tablespace leo including contents and datafiles; 
Tablespace dropped. 
--在导入表空间之前要先建表空间,在导入表空间回复表空间数据 
SQL> create tablespace leo datafile'+DATA/cndba/datafile/cndba01.dbf' size 100m; 
Tablespace created. 
 impdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo 
[oracle@rac1 backup]$ impdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo 
Import: Release 11.2.0.4.0 - Production on Wed May 3 11:06:44 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
. . imported "LEO"."LEO"                                 6.706 MB   68340 rows 
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at Wed May 3 11:06:48 2017 elapsed 0 00:00:02 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68339

5.5导入过程中指定remap user 和 remap tablespace 以及忽略remap功能的TRANSFORM=segment_attributes:n 使用。 

--remap user 
SQL> create user leo1 identified by leo1; 
User created. 
SQL>  grant read,write on directory backup to leo1; 
Grant succeeded. 
SQL> grant connect,resource to leo1; 
Grant succeeded. 
SQL> exit 
[oracle@rac1 backup]$ impdp leo1/leo1 remap_schema=leo:leo1 directory=backup dumpfile=leo.dmp logfile=leoexp.log 
Import: Release 11.2.0.4.0 - Production on Wed May 3 11:44:10 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded 
Starting "LEO1"."SYS_IMPORT_FULL_01":  leo1/******** remap_schema=leo:leo1 directory=backup dumpfile=leo.dmp logfile=leoexp.log 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
. . imported "LEO1"."LEO"                                6.706 MB   68340 rows 
Job "LEO1"."SYS_IMPORT_FULL_01" successfully completed at Wed May 3 11:44:15 2017 elapsed 0 00:00:03 
[oracle@rac1 backup]$ sqlplus /nolog 
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 3 11:44:26 2017 
Copyright (c) 1982, 2013, Oracle.  All rights reserved. 
SQL> conn leo1/leo1 
Connected. 
--remap  tablespace 
SQL> set lines 200 
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='LEO'; 
USERNAME         DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE 
------------------------------ ------------------------------ ------------------------------ 
LEO          USERS         TEMP 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68340 
expdp /'/as sysdba/' directory=backup dumpfile=leo_user.dmp schemas=leo1 
SQL> drop user leo1 cascade; 
User dropped. 
SQL> SET LINES 200 
impdp /'/as sysdba/' directory=backup dumpfile=leo_user.dmp logfile=leoimp.log remap_tablespace=users:leo 
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='LEO1'; 
USERNAME         DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE 
------------------------------ ------------------------------ ------------------------------ 
LEO1          LEO         TEMP 

--忽略remap功能的TRANSFORM=segment_attributes:n 使用 参数说明参考链接: 
http://www.cndba.cn/Expect-le/article/1890 
SQL> set long 9999; 
SQL>  SELECT dbms_metadata.get_ddl('TABLE','LEO','LEO') FROM dual; 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
  CREATE TABLE "LEO"."LEO" 
   ( "OWNER" VARCHAR2(30) NOT NULL ENABLE, 
 "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE, 
 "SUBOBJECT_NAME" VARCHAR2(30), 
 "OBJECT_ID" NUMBER NOT NULL ENABLE, 
 "DATA_OBJECT_ID" NUMBER, 
 "OBJECT_TYPE" VARCHAR2(19), 
 "CREATED" DATE NOT NULL ENABLE, 
 "LAST_DDL_TIME" DATE NOT NULL ENABLE, 
 "TIMESTAMP" VARCHAR2(19), 
 "STATUS" VARCHAR2(7), 
 "TEMPORARY" VARCHAR2(1), 
 "GENERATED" VARCHAR2(1), 
 "SECONDARY" VARCHAR2(1), 
 "NAMESPACE" NUMBER NOT NULL ENABLE, 
 "EDITION_NAME" VARCHAR2(30) 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "LEO" 
expdp /'/as sysdba/' dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo 
SQL> drop tablespace leo including contents and datafiles; 
Tablespace dropped. 
不加TRANSFORM=SEGMENT_ATTRIBUTES:N:table: 
看到了报错:提示leo表空间不存在,无法导入 
[oracle@rac1 ~]$ impdp /'/as sysdba/' dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo 
Import: Release 11.2.0.4.0 - Production on Wed May 3 10:45:30 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo 
Processing object type TABLE_EXPORT/TABLE/TABLE 
ORA-39083: Object type TABLE:"LEO"."LEO" failed to create with error: 
ORA-00959: tablespace 'LEO' does not exist 
Failing sql is: 
CREATE TABLE "LEO"."LEO" ("OWNER" VARCHAR2(30 BYTE) NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPOR 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed May 3 10:45:34 2017 elapsed 0 00:00:02 
加TRANSFORM=SEGMENT_ATTRIBUTES:N:table: 使用默认表空间users
[oracle@rac1 ~]$ impdp /'/as sysdba/' dumpfile=leo.dmp logfile=imp_leo.log directory=backup tables=leo.leo TRANSFORM=SEGMENT_ATTRIBUTES:N:table 
Import: Release 11.2.0.4.0 - Production on Wed May 3 10:46:18 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=leo.dmp logfile=imp_leo.log directory=backup tables=leo.leo TRANSFORM=SEGMENT_ATTRIBUTES:N:table 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
. . imported "LEO"."LEO"                                 6.706 MB   68340 rows 
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed May 3 10:46:22 2017 elapsed 0 00:00:03 
SQL> SELECT dbms_metadata.get_ddl('TABLE','LEO','LEO') FROM dual; 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
  CREATE TABLE "LEO"."LEO" 
   ( "OWNER" VARCHAR2(30) NOT NULL ENABLE, 
 "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE, 
 "SUBOBJECT_NAME" VARCHAR2(30), 
 "OBJECT_ID" NUMBER NOT NULL ENABLE, 
 "DATA_OBJECT_ID" NUMBER, 
 "OBJECT_TYPE" VARCHAR2(19), 
 "CREATED" DATE NOT NULL ENABLE, 
 "LAST_DDL_TIME" DATE NOT NULL ENABLE, 
 "TIMESTAMP" VARCHAR2(19), 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
 "STATUS" VARCHAR2(7), 
 "TEMPORARY" VARCHAR2(1), 
 "GENERATED" VARCHAR2(1), 
 "SECONDARY" VARCHAR2(1), 
 "NAMESPACE" NUMBER NOT NULL ENABLE, 
 "EDITION_NAME" VARCHAR2(30) 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "USERS" 
上一篇:用数据泵技术实现逻辑备份Oracle 11g R2 数据泵技术详解(expdp impdp)


下一篇:【OCP 062新题】OCP题库更新出现大量新题-9