oracle impdp 变动用户、表空间（ORA-00959：表空间不存在）

oracle impdp 变动用户、表空间

• 引言：

  expdp 'sys/123456 AS SYSDBA' directory=dump_dir dumpfile=mz_schema_%U.dmp logfile=mz_schema_2016.log schemas=HCPROD

  impdp 'sys/HCPROD AS SYSDBA'  directory=dir_dir dumpfile=mz_schema_%U.dmp logfile=impdp_mz_schema_2016.log schemas=HCPROD table_exists_action=replace remap_talespace=qz_prod:users

  expdp 'sys/HCPROD AS SYSDBA' directory=dump_dir dumpfile=accaddr_mz_schema_%U.dmp logfile=accaddr_mz_schema_2016.log schemas=HCPROD

  impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile=accaddr_mz_schema_%U.dmp logfile=impdp_accaddrdemo_mz_schema_2016.log schemas=HCPROD table_exists_action=replace

  ***************************************************************************

  本人遇到ORA-00959: tablespace 'qz_prod' does not exist（表空间不存在）这个问题的背景：

  源数据库里的HCPROD用户使用了表空间qz_prod，而目标数据库里不存在表空间qz_prod。

  故而执行impdp 'sys/HCPROD AS SYSDBA' directory=dir_dir dumpfile=mz_schema_%U.dmp logfile=impdp_mz_schema_2016.log schemas=HCPROD table_exists_action=replace时，提示ORA-00959: tablespace 'qz_prod' does not exist（表空间不存在），此时就需要调整REMAP_SCHEMA参数。

  改为

  impdp 'sys/HCPROD AS SYSDBA'  directory=dir_dir dumpfile=mz_schema_%U.dmp logfile=impdp_mz_schema_2016.log schemas=HCPROD table_exists_action=replaceremap_talespace=qz_prod:users

  注释：

  以schemas作业模式执行expdp时，expdp不会创建表空间qz_prod的对象类型到导出文件里，这样，impdp时就不能导出文件里的表空间qz_prod的对象类型来在目标数据库里创建一个名为qz_prod的表空间这个数据库对象。而目标数据库里又不存在表空间qz_prod。所以，执行impdp时，就会提示ORA-00959: tablespace 'qz_prod' does not exist（表空间不存在）。

  只要参数schema、参数tablespace、参数table（以及其他数据库对象类型）三种作业模式的值是expdp时存在的，expdp都会为这些值创建对应的对象类型（即数据库对象的元数据）。也就是说，参数schema，对应创建的对象类型包含有用户元数据；参数tablespace，对应创建的对象类型包含有表空间元数据（和用户元数据？）。这样，

  impdp里的参数schema的值即用户如果在被导入的数据库里不存在，则impdp这个程序会自动创建。impdp里的参数tablespace的值即表空间如果在被导入的数据库里不存在，则impdp这个程序也会自动创建。

  =============

• impdp admuser1/密码 schema=srcUser

  impdp里的admuser1用户是用于执行impdp这个程序的用户，不是被导入数据的用户，被导入数据的用户是srcUser。

  原来说法：

  impdp里的参数schema如果在被导入的数据库里不存在，则impdp这个程序会自动创建。impdp里的参数table（以及其他数据库对象类型）也类似。impdp里的参数tablespace如果在被导入的数据库里不存在，则impdp这个程序不会自动创建，提示ORA-00959: tablespace 'SMS_MT' does not exist（表空间不存在），此时就需要调整REMAP_SCHEMA参数。

  改为

  现在说法：

  只要参数schema、参数tablespace、参数table（以及其他数据库对象类型）三种作业模式的值是expdp时存在的，expdp都会为这些值创建对应的对象类型（即数据库对象的元数据）。也就是说，参数schema，对应创建的对象类型包含有用户元数据；参数tablespace，对应创建的对象类型包含有表空间元数据（和用户元数据？）。这样，

  impdp里的参数schema的值即用户如果在被导入的数据库里不存在，则impdp这个程序会自动创建。impdp里的参数tablespace的值即表空间如果在被导入的数据库里不存在，则impdp这个程序也会自动创建。

  impdp里的参数tablespace、参数schema列出的是要（从导出文件里）导入的方案名。如果方案名不是在expdp时要导出的方案名，则会提示出错。impdp里的参数table（以及其他数据库对象类型）也类似。

  参数schema、参数tablespace、参数table（以及其他数据库对象类型）三者关系：

  schema下的table（以及其他数据库对象类型）可以存放在多个不同的tablespace里。

  或者说

  schema使用多个不同的tablespace来存放属于自己的table（以及其他数据库对象类型）。

  所以，三者使用形式只能为：

  schema=，

  schema=，tablespace=，

  schema=，tablespace=，table=

  impdp里三者都不写出来时，则默认（从导出文件里）导入在expdp时expdp参数schema里所列出的所有的方案名

  impdp里三者都不写出来时，则默认（从导出文件里）导入在expdp时expdp所使用的那一种作业模式（即参数schema、参数tablespace、参数table（以及其他数据库对象类型）三者中的一个）下的所有数据库对象

  expdp里三者都不写出来时，则默认导出用于执行expdp这个程序的用户（即expdp admuser1/密码）下的所有数据库对象，即schema作业模式。就table=时，则默认导出用于执行expdp这个程序的用户（即expdp admuser1/密码）下的表。

  ****************************************

  1、

  1.1导出方案dong下的表csdn5：

  C:\Users\uname>expdp 'sys/123456 AS SYSDBA' directory=dump_dir dumpfile=exp_schema_%

  U.dmp logfile=exp_schema_2016.log schemas=dong tables=csdn5

  Export: Release 11.2.0.1.0 - Production on 星期三 4月 20 17:02:58 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  UDE-00010: 已请求多个作业模式, schema 和 tables。

  注释：

  这个说明参数schema、参数tablespace、参数table（以及其他数据库对象类型）三者不能同时出现，一次只能出现一个参数的作业模式。

  故上述语句改为：

  expdp 'sys/123456 AS SYSDBA' directory=dump_dir dumpfile=exp_schema_%

  U.dmp logfile=exp_schema_2016.log  tables=dong.csdn5

  1.2导入表csdn5到目标数据库，目标数据库原先不存在名为dong的用户

  C:\Users\Administrator>impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile

  =mz_schema_%U.dmp logfile=impdp_schema_22016.log tables=csdn5 table_exists_actio

  n=replace

  Import: Release 11.2.0.1.0 - Production on 星期四 4月 21 10:43:41 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  ORA-39002: 操作无效

  ORA-39166: 找不到对象 SYS.CSDN5。

  说明tables作业模式时默认从后缀dmp的导出文件里导入的是执行impdp这个程序的用户（即impdp 'sys/HCPROD AS SYSDBA'  ）下的表csdn5.

  故而要改为：tables=dong.csdn5

  【附加例子：

  C:\Users\Administrator>impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile

  =mz_schema_%U.dmp logfile=impdp_schema_22016.log schemas=dong  tables=dong.csdn5

   table_exists_action=replace

  Import: Release 11.2.0.1.0 - Production on 星期四 4月 21 10:56:46 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  UDI-00010: 已请求多个作业模式, schema 和 tables。

  C:\Users\Administrator>impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile

  =mz_schema_%U.dmp logfile=impdp_schema_22016.log tables=csdn5 remap_schema=dong:

  sys table_exists_action=replace

  Import: Release 11.2.0.1.0 - Production on 星期四 4月 21 10:57:59 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  ORA-39002: 操作无效

  ORA-39166: 找不到对象 SYS.CSDN5。

  】

  C:\Users\Administrator>impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile

  =mz_schema_%U.dmp logfile=impdp_schema_22016.log tables=dong.csdn5 table_exists_

  action=replace

  Import: Release 11.2.0.1.0 - Production on 星期四 4月 21 10:44:26 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"

  启动 "SYS"."SYS_IMPORT_TABLE_01":  "sys/******** AS SYSDBA" directory=dump_dir d

  umpfile=mz_schema_%U.dmp logfile=impdp_schema_22016.log tables=dong.csdn5 table_

  exists_action=replace

  处理对象类型 TABLE_EXPORT/TABLE/TABLE（指的是表的元数据即表结构）

  ORA-39083: 对象类型 TABLE:"DONG"."CSDN5" 创建失败, 出现错误:

  ORA-01918: 用户 'DONG' 不存在

  失败的 sql 为:

  CREATE TABLE "DONG"."CSDN5" ("ACCOUNT" VARCHAR2(320 BYTE), "PASSWORD" VARCHAR2(3

  20 BYTE), "EMAIL" VARCHAR2(320 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTU

  SED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 104

  8576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUP

  S 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DE

  说明tables作业模式时不会自动创建处理（原先不存在于目标数据库里的）用户这个对象类型（即在执行一次impdp命令后在其打印出的结果里没有例如“处理对象类型 TABLE_EXPORT/USER/USER”的语句）

  处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

  ORA-31693: 表数据对象 "DONG"."CSDN5" 无法加载/卸载并且被跳过, 错误如下:

  ORA-00942: 表或视图不存在（因为上述处理对象类型 TABLE_EXPORT/TABLE/TABLE（指的是表的元数据即表结构）失败而未创建表结构成功，所以导致这里的错误）

  处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

  ORA-39083: 对象类型 TABLE_STATISTICS 创建失败, 出现错误:

  ORA-20000: TABLE "DONG"."CSDN5" does not exist or insufficient privileges

  失败的 sql 为:

  DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH2

  4:MI:SS';   s varchar2(60) := 'DONG';   t varchar2(60) := 'CSDN5';   p varchar2(

  1);   sp varchar2(1);   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (

  type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flag

  s) VALUES (''C'',5,:1,:2,:3,

  作业 "SYS"."SYS_IMPORT_TABLE_01" 已经完成, 但是有 3 个错误 (于 10:44:32 完成)

  故改为如下语句：

  C:\Users\Administrator>impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile

  =mz_schema_%U.dmp logfile=impdp_schema_22016.log tables=dong.csdn5 remap_schema=

  dong:sys table_exists_action=replace

  Import: Release 11.2.0.1.0 - Production on 星期四 4月 21 10:58:20 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"

  启动 "SYS"."SYS_IMPORT_TABLE_01":  "sys/******** AS SYSDBA" directory=dump_dir d

  umpfile=mz_schema_%U.dmp logfile=impdp_schema_22016.log tables=dong.csdn5 remap_

  schema=dong:sys table_exists_action=replace

  处理对象类型 TABLE_EXPORT/TABLE/TABLE

  处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

  . . 导入了 "SYS"."CSDN5"                               1.308 GB 32196561 行

  处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

  作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 10:59:03 成功完成

  1.2续、目标数据库原先不存在名为dong的用户

  C:\Users\Administrator>impdp 'sys/HCPROD AS SYSDBA'  directory=dump_dir dumpfile

  =mz_schema_%U.dmp logfile=impdp_schema_22016.log

  Import: Release 11.2.0.1.0 - Production on 星期四 4月 21 11:25:10 2016

  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  已成功加载/卸载了主表 "SYS"."SYS_IMPORT_FULL_01"

  启动 "SYS"."SYS_IMPORT_FULL_01":  "sys/******** AS SYSDBA" directory=dump_dir du

  mpfile=mz_schema_%U.dmp logfile=impdp_schema_22016.log

  处理对象类型 TABLE_EXPORT/TABLE/TABLE

  ORA-39083: 对象类型 TABLE:"DONG"."CSDN5" 创建失败, 出现错误:

  ORA-01918: 用户 'DONG' 不存在

  失败的 sql 为:

  CREATE TABLE "DONG"."CSDN5" ("ACCOUNT" VARCHAR2(320 BYTE), "PASSWORD" VARCHAR2(3

  20 BYTE), "EMAIL" VARCHAR2(320 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTU

  SED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 104

  8576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUP

  S 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DE

  处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

  处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

  作业 "SYS"."SYS_IMPORT_FULL_01" 已经完成, 但是有 1 个错误 (于 11:25:15 完成)

  说明

  impdp里三者都不写出来时，则默认（从导出文件里）导入在expdp时expdp所使用的那一种作业模式（即参数schema、参数tablespace、参数table（以及其他数据库对象类型）三者中的一个）下的所有数据库对象

  说明tables作业模式时不会自动创建处理（原先不存在于目标数据库里的）用户这个对象类型（即在执行一次impdp命令后在其打印出的结果里没有例如“处理对象类型 TABLE_EXPORT/USER/USER”的语句）

  C:\Users\Administrator>

  2、remap_*参数一般都是在impdp时用到，即将从后缀dmp的导出文件里要导入到目标数据库里的数据库对象的属性重新映射，即改变其属性值：

  参数tables（以及其他数据库对象类型）时，首先可以使用的是“remap_table=导出文件里要导入到目标数据库里的表A的表名：目标数据库里已存在的想要被导入的表B的表名”。如果表B所在的用户名B与表A所在的用户名A不同，则用remap_schema。如果表B所在的用户名B用于存放表B的表空间B的名字与表A所在的用户名A用于存放表A的表空间A的名字不同，则remap_tablespace。

  参数schema时上述三个remap_*参数也都可以用，使用remap_tablespace的意思是将用户名A存放在表空间A里的所有数据库对象改存放在表空间B里。

  参数tablespace也类似。

  总结说，remap_*参数都是针对数据库对象的三个属性（即所属的schema、tablespace、table）。

  remap_data参数是在impdp和expdp时都用到的唯一一个remap_*参数。例如，expdp时，remap_data参数表示从源数据库里的表A的列A上的数据导出到后缀dmp的导出文件里的表B的列B上。

  正文：

impdp admuser1/admuser DUMPFILE=CRV_ADMUSER.DMP remap_schema=admuser:admuser1 remap_tablespace=PMDB_DAT1:PMDB_DAT2

说明：

REMAP_SCHEMA可以定义用户的切换，其格式为：

remap_schema=old_schema_name:new_schema_name

REMAP_TABLESPACE可以定义切换对象的不同表空间，其格式为：

remap_tablespace=old_tablespace_name:new_tablespace_name

在使用impdp时，会发现，有很多参数选项与imp不同，比如说，找不到了fromuser和touser参数，也找不到了ignore参数，indexes等参数也不再存在。

当然这些功能都还在，而且更加强大，impdp有了不少新的替代参数。

以下三个参数极大的增强了用户转换及表空间转换的操作：

REMAP_DATAFILE        Redefine datafile references in all DDL statements.

REMAP_SCHEMA          Objects from one schema are loaded into another schema.

REMAP_TABLESPACE      Tablespace object are remapped to another tablespace.

REMAP_SCHEMA可以定义用户的切换，其格式为：

remap_schema=old_schema_name:new_schema_name

REMAP_TABLESPACE可以定义切换对象的不同表空间，其格式为：

remap_tablespace=old_tablespace_name:new_tablespace_name

以前类似IGNORE的忽略创建错误，可以使用CONTENT参数：

CONTENT               Specifies data to load where the valid keywords are:

                              (ALL), DATA_ONLY, and METADATA_ONLY.

如果数据结构已经存在可以指定CONTENT=DATA_ONLY，仅导入数据。

而关于索引的排除，可以使用EXCLUDE参数：

EXCLUDE               Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.

IMPDP导入时忽略索引可以使用类似： EXCLUDE=CONSTRAINT EXCLUDE=INDEX

如果导入时遇到如下错误，就需要调整REMAP_SCHEMA参数：

遇到如下错误，那就需要制定REMAP_TABLESPACE参数：

ORA-00959: tablespace 'SMS_MT' does not exist（表空间不存在）

参考：

oracle通过impdp导入不同表用户、不同表空间的数据

1. impdp smtj2012/密码@localhost/devdb DIRECTORY=dump_dir DUMPFILE=bkmmtdb_smtj20130417120001.dmp remap_schema=smtj:smtj2012 remap_tablespace=smtj:smtj2012