Oracle impdp 创建用户避坑指南（表空间配额，权限）

最新推荐文章于 2024-04-25 10:54:02 发布
最新推荐文章于 2024-04-25 10:54:02 发布
阅读量1.5k 收藏 8
点赞数
分类专栏： oracle 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/abtain/article/details/124378838
版权

Oracle impdp 创建用户避坑指南（表空间配额，权限）

本篇文章非常长。没有耐心的同学可以直接跳到总结

实验环境：12.1.0 RAC

实验一：remap_schema，remap_tablespace能够修改新用户默认表空间和表空间配额？

我们要导出源库的wcj2用户所有数据，wcj2用户目前只有wcj2表空间的访问权限。注意里面有一张tt表，表空间时wcj，这是之前wcj2拥有unlimited tablespace权限时创建的。

SYS@dborcl1> col username for a30
SYS@dborcl1> select username,default_tablespace,temporary_tablespace from dba_users where username='WCJ2';

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ2			       WCJ2			      TEMP

WCJ2@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2 			182648832	  -1	  22296 	-1 NO

WCJ2@dborcl1> select table_name,tablespace_name from user_tables;   

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2
T2			       WCJ2
T3			       WCJ2
T4			       WCJ2
T5			       WCJ2
TT			       WCJ

6 rows selected.

现在，导出wcj2用户的数据：

expdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp logfile=expwcj2.log logtime=logfile \
schemas=wcj2 cluster=no job_name=expwcj2

我的实验环境源库和目标库都是同一套数据库，版本是12c，因此wcj2表空间既存在与源库，也在目标库。打算导入时remap_schema为wcj3，并且wcj3的默认表空间为wcj3tbs。分以下几种情况分别讨论：

1. 手动创建wcj3用户，授予基本权限，创建wcj3tbs表空间，但不给wcj3配置wcj3tbs的配额

create tablespace wcj3tbs datafile '+dg_data' size 1G autoextend off;
create user wcj3 identified by wcj3 default tablespace wcj3tbs;
grant connect,resource to wcj3;#现在，wcj3是无法访问任何表空间的。因为从12c开始，resource角色不再带有unlimited tablespace权限。

开始导入：

1)不设置remap_tablespace
[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3  table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 17:49:12 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 17:49:14.447: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 17:49:14.700: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 table_exists_action=replace 
21-APR-22 17:49:14.752: Processing object type SCHEMA_EXPORT/USER
21-APR-22 17:49:14.826: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 17:49:14.836: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 17:49:14.887: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 17:49:14.944: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 17:49:14.992: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 17:49:15.047: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 17:49:15.126: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 17:49:15.352: ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error:
ORA-01950: no privileges on tablespace 'WCJ'
Failing sql is:
CREATE TABLE "WCJ3"."TT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ" 
21-APR-22 17:49:15.376: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 17:49:16.278: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 17:49:16.729: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 17:49:17.189: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 17:49:17.240: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 17:49:17.248: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 17:49:17.284: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 17:49:17.375: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 17:49:17.384: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 17:49:17.402: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 17:49:18.434: Job "SYS"."IMPWCJ2" completed with 2 error(s) at Thu Apr 21 17:49:18 2022 elapsed 0 00:00:04

可以看到，不加remap_tablespace的情况下，属于wcj表空间的tt表是无法导入的。

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2
T2			       WCJ2
T3			       WCJ2
T4			       WCJ2
T5			       WCJ2

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2				182583296	  -1	  22288 	-1 NO

并且表空间配额和源库用户wcj2是一样的，但是默认表空间和wcj2不一样。

2)remap_tablespace=wcj:wcj3tbs

现在的情况是，只修改wcj表空间为wcj3tbs

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:wcj3tbs \
table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 16:33:03 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 16:33:05.574: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 16:33:05.847: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:wcj3tbs table_exists_action=replace 
21-APR-22 16:33:05.902: Processing object type SCHEMA_EXPORT/USER
21-APR-22 16:33:05.986: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 16:33:05.997: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 16:33:06.055: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 16:33:06.112: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 16:33:06.156: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 16:33:06.210: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 16:33:06.305: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 16:33:06.593: ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error:
ORA-01950: no privileges on tablespace 'WCJ3TBS'
Failing sql is:
CREATE TABLE "WCJ3"."TT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ3TBS" 
21-APR-22 16:33:06.621: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 16:33:07.463: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 16:33:08.096: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 16:33:08.637: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 16:33:08.712: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 16:33:08.722: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 16:33:08.785: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 16:33:08.897: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 16:33:08.906: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 16:33:08.919: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 16:33:09.871: Job "SYS"."IMPWCJ2" completed with 2 error(s) at Thu Apr 21 16:33:09 2022 elapsed 0 00:00:05

报错了，一个是说wcj3用户已存在，这个可以忽略。

ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error: ORA-01950: no privileges on tablespace 'WCJ3TBS'是个什么情况？没有访问wcj3tbs的权限，可以理解，没分配配额嘛。

但是问题来了，为何其他的表却成功了？难道其他的表能访问wcj3tbs？

查一下就知道了。

SYS@dborcl1> conn wcj3/wcj3;
Connected.

WCJ3@dborcl1> col table_name for a30
WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2
T2			       WCJ2
T3			       WCJ2
T4			       WCJ2
T5			       WCJ2

WCJ3@dborcl1> col username for a30
WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

什么情况，这几张导入成功的表居然用的是wcj2表空间，这是wcj2用户的默认表空间，不是wcj3的呀。难道wcj3能够访问wcj2这个表空间？我明明没为wcj3分配任何的表空间配额呀。

测试一下，wcj3究竟能不能访问呢wcj2表空间。

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2				182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1> create table tt tablespace wcj2 as select * from t1;

Table created.

WCJ3@dborcl1> insert into tt values(10,'sd');  

1 row created.

WCJ3@dborcl1> commit;

Commit complete.

居然可以访问。而且还分配了wcj2表空间的配额。

WCJ3@dborcl1> create table ttt as select * from t1;
create table ttt as select * from t1
                                  *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'WCJ3TBS'

而wcj3的默认表空间反而用不了。

我们可以得出结论：remap_tablespace=wcj:wcjtbs时。impdp为wcj3分配了和源库用户wcj2一样的表空间配额

现在可以为wcj3分配表空间wcj3tbs的配额。然后重新导入。此时tt表就可导入成功，而且在wcj3tbs下。

SYS@dborcl1> alter user wcj3 quota unlimited on wcj3tbs;

User altered.
WCJ3@dborcl1> select * from user_ts_quotas; 

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ3TBS 				0	  -1	      0 	-1 NO
WCJ2				182648832	  -1	  22296 	-1 NO
3)remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs

第二种情况，此时将所有的表空间都更换成了wcj3tbs。

清理之前的实验数据

 drop user wcj3 cascade;
 create user wcj3 identified by wcj3 default tablespace wcj3tbs;
 grant connect,resource to wcj3;

重新导入

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 \
remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 17:06:39 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 17:06:40.818: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 17:06:41.080: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs table_exists_action=replace 
21-APR-22 17:06:41.136: Processing object type SCHEMA_EXPORT/USER
21-APR-22 17:06:41.213: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 17:06:41.221: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 17:06:41.274: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 17:06:41.329: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 17:06:41.380: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 17:06:41.435: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 17:06:41.515: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 17:06:41.802: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 17:06:42.633: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 17:06:43.046: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 17:06:43.536: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 17:06:43.582: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 17:06:43.617: . . imported "WCJ3"."TT"                                 5.570 KB       7 rows
21-APR-22 17:06:43.624: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 17:06:43.662: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 17:06:43.758: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 17:06:43.765: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 17:06:43.781: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 17:06:44.725: Job "SYS"."IMPWCJ2" completed with 1 error(s) at Thu Apr 21 17:06:44 2022 elapsed 0 00:00:04

怎么回事？怎么只有一个用户已存在的错误？数据全部正常导入。

查一下:

sqlplus wcj3/wcj3
WCJ3@dborcl1> col table_name for a30
WCJ3@dborcl1> col username for a30
WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
TT			       WCJ3TBS
T5			       WCJ3TBS
T4			       WCJ3TBS
T3			       WCJ3TBS
T2			       WCJ3TBS
T1			       WCJ3TBS

6 rows selected.
 
WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ3TBS 			182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1> create table ttt tablespace wcj2 as select * from t1;
create table ttt tablespace wcj2 as select * from t1
                                                  *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'WCJ2'


WCJ3@dborcl1>  create table ttt tablespace users as select * from t1;
 create table ttt tablespace users as select * from t1
                                                    *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

原来impdp为wcj3分配了wcj3tbs的配额，但是却没有wcj2表空间的配额了。这时候，用户的权限和源库的发生了出入。想想和2）有何区别，在2）中我们没有修改wcj2为wcj3tbs，因此wcj3用户的配额设置和wcj2用户一样。而现在，remap_tablespace=wcj2:wcj3tbs，修改了wcj2用户配额设置项wcj2为wcj3tbs，因此配额设置就有出入了。

4)remap_tablespace=wcj:users

清理旧实验数据

 drop user wcj3 cascade;
 create user wcj3 identified by wcj3 default tablespace wcj3tbs;
 grant connect,resource to wcj3;

这种情况impdp又会怎么做？wcj改为了users，users存在于目标库中，但是wcj3没有权限去访问。因此tt表还是无法导入。其他表应该还是属于wcj2表空间，配额设置应该也是wcj2.

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:users \
table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 17:24:37 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 17:24:39.318: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 17:24:39.586: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:users table_exists_action=replace 
21-APR-22 17:24:39.637: Processing object type SCHEMA_EXPORT/USER
21-APR-22 17:24:39.712: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 17:24:39.721: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 17:24:39.772: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 17:24:39.828: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 17:24:39.876: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 17:24:39.932: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 17:24:40.050: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 17:24:40.312: ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'
Failing sql is:
CREATE TABLE "WCJ3"."TT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "USERS" 
21-APR-22 17:24:40.338: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 17:24:41.164: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 17:24:41.927: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 17:24:42.452: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 17:24:42.503: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 17:24:42.508: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 17:24:42.544: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 17:24:42.645: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 17:24:42.656: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 17:24:42.668: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 17:24:43.534: Job "SYS"."IMPWCJ2" completed with 2 error(s) at Thu Apr 21 17:24:43 2022 elapsed 0 00:00:05

和remap_tablespace=wcj:wcj3tbs一样的，重命名的表空间不能访问，查一下

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2
T2			       WCJ2
T3			       WCJ2
T4			       WCJ2
T5			       WCJ2

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1>  select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2				182583296	  -1	  22288 	-1 NO

和猜测的一样。这里不要以为remap_tablespace=wcj:users没有生效，实际可以看到报错是没有权限访问users，而不是没有权限访问wcj，因此实际生效了的，只不过是没做 alter user wcj3 quota unlimietd on users这么个操作而已。

5)remap_tablespace=wcj:users,wcj2:users

清理旧实验数据

 drop user wcj3 cascade;
 create user wcj3 identified by wcj3 default tablespace wcj3tbs;
 grant connect,resource to wcj3;

现在，重命名的表空间不再是wcj3tbs变成了users，impdp会怎么做呢？

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 \
remap_tablespace=wcj:users,wcj2:users table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 17:19:02 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 17:19:05.039: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 17:19:05.442: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:users,wcj2:users table_exists_action=replace 
21-APR-22 17:19:05.545: Processing object type SCHEMA_EXPORT/USER
21-APR-22 17:19:05.656: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 17:19:05.672: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 17:19:05.726: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 17:19:05.787: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 17:19:05.837: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 17:19:05.891: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 17:19:06.153: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 17:19:06.439: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 17:19:07.284: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 17:19:07.764: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 17:19:08.233: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 17:19:08.288: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 17:19:08.333: . . imported "WCJ3"."TT"                                 5.570 KB       7 rows
21-APR-22 17:19:08.341: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 17:19:08.390: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 17:19:08.508: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 17:19:08.522: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 17:19:08.539: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 17:19:09.624: Job "SYS"."IMPWCJ2" completed with 1 error(s) at Thu Apr 21 17:19:09 2022 elapsed 0 00:00:05

正常导入

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       USERS
T2			       USERS
T3			       USERS
T4			       USERS
T5			       USERS
TT			       USERS

6 rows selected.

WCJ3@dborcl1>  select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS				182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1> create table tt1 as select * from t1;
create table tt1 as select * from t1
                                  *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'WCJ3TBS'

impdp为wcj3用户分配了users的配额。和remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs类似，看来只有修改了源库用户wcj2的配额设置中包含的表空间，wcj3用户才会重新分配新的配额。和4）对比可以知道，users不在wcj2的配额设置包含的表空间中。因此impdp没有为新用户wcj3做 alter user wcj3 quota unlimited on users的操作。这些结论接着往下看可以验证。

6)remap_tablespace=wcj2:wcj3tbs

之前同时重命名了wcj2和wcj的都成功了，而只重命名wcj却都没成功。现在我只重命名wcj2，会发生什么？可以预料到的是，tt表肯定无法成功，因为wcj表空间不能访问。

清理旧实验数据

 drop user wcj3 cascade;
 create user wcj3 identified by wcj3 default tablespace wcj3tbs;
 grant connect,resource to wcj3;

导入：

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj2:wcj3tbs table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 17:38:35 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 17:38:37.185: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 17:38:37.486: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj2:wcj3tbs table_exists_action=replace 
21-APR-22 17:38:37.551: Processing object type SCHEMA_EXPORT/USER
21-APR-22 17:38:37.655: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 17:38:37.667: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 17:38:37.748: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 17:38:37.809: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 17:38:37.853: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 17:38:37.908: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 17:38:37.996: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 17:38:38.606: ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error:
ORA-01950: no privileges on tablespace 'WCJ'
Failing sql is:
CREATE TABLE "WCJ3"."TT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ" 
21-APR-22 17:38:38.645: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 17:38:39.912: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 17:38:40.667: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 17:38:41.261: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 17:38:41.327: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 17:38:41.351: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 17:38:41.433: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 17:38:41.556: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 17:38:41.652: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 17:38:41.709: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 17:38:42.784: Job "SYS"."IMPWCJ2" completed with 2 error(s) at Thu Apr 21 17:38:42 2022 elapsed 0 00:00:06

和预料的一样，tt表无法创建，访问不了wcj表空间。

那么wcj3的配额情况呢？究竟是配置了wcj2表空间的配额，还是wcj3tbs的配额

WCJ3@dborcl1>  select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ3TBS
T2			       WCJ3TBS
T3			       WCJ3TBS
T4			       WCJ3TBS
T5			       WCJ3TBS

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ3TBS 			182583296	  -1	  22288 	-1 NO

配置了表空间wcj3tbs的配额。这是不是能说明，当remap_tablespace修改了源库用户wcj2配额设置的表空间时，新用户wcj3的配额设置会使用新的表空间，当没有修改时，就和源库用户wcj2的配额设置一样呢？

再做一个实验：

SYS@dborcl1> alter user wcj2 quota unlimited on wcj;

User altered.

SYS@dborcl1> drop user wcj3 cascade;

User dropped.

SYS@dborcl1> create user wcj3 identified by wcj3 default tablespace wcj3tbs;

User created.

SYS@dborcl1> grant connect,resource to wcj3;

Grant succeeded.

现在，wcj2用户的配额设置已经变了：

WCJ2@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2				182583296	  -1	  22288 	-1 NO
WCJ				    65536	  -1	      8 	-1 NO

不加remap_tablesapce重新导入看看

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_quota.dmp\
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3  table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 17:59:13 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 17:59:15.459: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 17:59:15.709: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2_quota.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 table_exists_action=replace 
21-APR-22 17:59:15.760: Processing object type SCHEMA_EXPORT/USER
21-APR-22 17:59:15.858: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 17:59:15.875: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 17:59:15.923: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 17:59:15.985: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 17:59:16.037: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 17:59:16.135: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 17:59:16.222: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 17:59:16.479: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 17:59:17.285: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 17:59:17.765: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 17:59:18.220: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 17:59:18.274: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 17:59:18.319: . . imported "WCJ3"."TT"                                 5.570 KB       7 rows
21-APR-22 17:59:18.323: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 17:59:18.367: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 17:59:18.464: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 17:59:18.473: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 17:59:18.488: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 17:59:19.520: Job "SYS"."IMPWCJ2" completed with 1 error(s) at Thu Apr 21 17:59:19 2022 elapsed 0 00:00:05

完全没有报错，这是为何？难道和源库用户wcj2是一样的？

查一查

WCJ2@dborcl1> conn wcj3/wcj3
Connected.
WCJ3@dborcl1>  select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2
T2			       WCJ2
T3			       WCJ2
T4			       WCJ2
T5			       WCJ2
TT			       WCJ

6 rows selected.

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1>  select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2				182583296	  -1	  22288 	-1 NO
WCJ				    65536	  -1	      8 	-1 NO

看来确实是和wcj2一样的配额。上面的结论对了一半。当我remap_tablespace修改了源库用户wcj2配额设置的表空间时，wcj3的配额设置会是新指定的值吗？

SYS@dborcl1>  drop user wcj3 cascade;

User dropped.

SYS@dborcl1> create user wcj3 identified by wcj3 default tablespace wcj3tbs;

User created.

SYS@dborcl1> grant connect,resource to wcj3;

Grant succeeded.

指定remap_tablesapce=wcj:users,wcj2:wcj3tbs

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_quota.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 \
remap_tablespace=wcj:users,wcj2:wcj3tbs table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Thu Apr 21 18:10:40 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
21-APR-22 18:10:41.857: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
21-APR-22 18:10:42.130: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2_quota.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 remap_tablespace=wcj:users,wcj2:wcj3tbs table_exists_action=replace 
21-APR-22 18:10:42.183: Processing object type SCHEMA_EXPORT/USER
21-APR-22 18:10:42.278: ORA-31684: Object type USER:"WCJ3" already exists
21-APR-22 18:10:42.293: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-APR-22 18:10:42.342: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-APR-22 18:10:42.396: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-APR-22 18:10:42.440: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21-APR-22 18:10:42.492: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-APR-22 18:10:42.587: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-APR-22 18:10:42.859: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-APR-22 18:10:43.648: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
21-APR-22 18:10:44.112: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
21-APR-22 18:10:44.544: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
21-APR-22 18:10:44.591: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
21-APR-22 18:10:44.631: . . imported "WCJ3"."TT"                                 5.570 KB       7 rows
21-APR-22 18:10:44.634: . . imported "WCJ3"."T2"                                     0 KB       0 rows
21-APR-22 18:10:44.675: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21-APR-22 18:10:44.780: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
21-APR-22 18:10:44.790: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-APR-22 18:10:44.806: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-APR-22 18:10:45.818: Job "SYS"."IMPWCJ2" completed with 1 error(s) at Thu Apr 21 18:10:45 2022 elapsed 0 00:00:05

WCJ3@dborcl1>  select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ3TBS
T2			       WCJ3TBS
T3			       WCJ3TBS
T4			       WCJ3TBS
T5			       WCJ3TBS
TT			       USERS

6 rows selected.

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ3TBS 			182583296	  -1	  22288 	-1 NO
USERS				    65536	  -1	      8 	-1 NO

WCJ3@dborcl1>  select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

全部成功导入。看来上面的结论没有错。

至此我们可以确定，在手动创建目标库用户的情况下，impdp不会修改目标库用户wcj3的默认表空间，但是可能修改wcj3的配额设置。如果remap_tablespace参数没有修改源库用户wcj2的quota所配置的表空间为新的值，那么impdp会将目标库用户wcj3的quota设置和源库用户wcj2一样(源库用户配额值为0时不会同步)。如果remap_tablespace参数修改了源库用户wcj2的quota配置的表空间为新的值（users、wcj3tbs），那么impdp会将新用户wcj3的配额设置为新的值。

为了后续实验，恢复wcj2的配额只有wcj2表空间。

SYS@dborcl1> alter user wcj2 quota 0 on wcj;

User altered.

2. 手动创建wcj3用户，授予基本权限，创建wcj3tbs表空间，并给wcj3配置wcj3tbs的配额

实验结果：

如果没指定remap_tablespace,那么tt表无法导入，其他表成功，wcj3的quota增加了wcj2，并且表的表空间为wcj2；

如果指定了remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs，全部导入成功，wcj3的quota只有wcj3tbs，并且表的表空间为wcj3tbs;

如果指定了remap_tablespace=wcj2:wcj3tbs，tt表无法导入，其他表导入成功，wcj3的quota只有wcj3tbs，并且表的表空间为wcj3tbs；

如果指定了remap_tablespace=wcj:wcj3tbs，全部导入成功，wcj3的quota增加了wcj2，tt表表空间为wcj3tbs，其他表的表空间为wcj2；

3. 不创建wcj3用户，不创建wcj3tbs表空间

实验结果：

1）删除表空间wcj2，不指定remap_tablespace。

导入失败，所有表都无法导入。

[oracle@oamsdb01l /home/oracle]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_quota_0.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Fri Apr 22 10:14:59 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
22-APR-22 10:15:03.437: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
22-APR-22 10:15:03.865: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2_quota_0.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 table_exists_action=replace 
22-APR-22 10:15:03.967: Processing object type SCHEMA_EXPORT/USER
22-APR-22 10:15:04.069: ORA-39083: Object type USER:"WCJ3" failed to create with error:
ORA-00959: tablespace 'WCJ2' does not exist
Failing sql is:
 CREATE USER "WCJ3" IDENTIFIED BY VALUES 'S:B00CF3E470AA3EED94769833EBA004161720AEFF2EC798BC0B0C55465C2F;H:AAE604F20F4CBA4F0E9B0D5F32F8EF0E;T:3D8604E1492E55480621B75F5CD078D8FEF5BCCE6717CA9B5C47E8B9ED61FB2547A9C9BAF36DDF99591C63ABFDF78E71FBDB51B7BF398863FDB24A478665FC88F185BFE298CB9DD9F31BE24AAD87F9B5;E9EFA57F3F877F79' DEFAULT TABLESPACE "WCJ2" TEMPORARY TABLESPACE "TEMP"
22-APR-22 10:15:04.084: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-22 10:15:04.163: ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
GRANT CREATE TABLE TO "WCJ3"
22-APR-22 10:15:04.163: ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
GRANT CREATE SESSION TO "WCJ3"
22-APR-22 10:15:04.170: Processing object type SCHEMA_EXPORT/ROLE_GRANT
22-APR-22 10:15:04.252: ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
 GRANT "CONNECT" TO "WCJ3"
22-APR-22 10:15:04.252: ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
 GRANT "RESOURCE" TO "WCJ3"
22-APR-22 10:15:04.261: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
22-APR-22 10:15:04.326: ORA-39083: Object type DEFAULT_ROLE:"WCJ3" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
 ALTER USER "WCJ3" DEFAULT ROLE ALL
22-APR-22 10:15:04.349: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
22-APR-22 10:15:04.422: ORA-39083: Object type TABLESPACE_QUOTA:"WCJ3" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "WCJ3" QUOTA UNLIMITED ON "WCJ2"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''WCJ2'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;  
22-APR-22 10:15:04.436: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
22-APR-22 10:15:04.471: ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema WCJ3 is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DBORCL', inst_scn=>'7776456');COMMIT; END; 
22-APR-22 10:15:04.538: Processing object type SCHEMA_EXPORT/TABLE/TABLE
22-APR-22 10:15:04.803: ORA-39083: Object type TABLE:"WCJ3"."T1" failed to create with error:
ORA-00959: tablespace 'WCJ2' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T1" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ2" 
22-APR-22 10:15:04.803: ORA-39083: Object type TABLE:"WCJ3"."T4" failed to create with error:
ORA-00959: tablespace 'WCJ2' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T4" ("ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(50 BYTE), "TIME1" TIMESTAMP (6)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
22-APR-22 10:15:04.803: ORA-39083: Object type TABLE:"WCJ3"."T3" failed to create with error:
ORA-00959: tablespace 'WCJ2' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T3" ("ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(50 BYTE), "TIME1" TIMESTAMP (6)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
22-APR-22 10:15:04.803: ORA-39083: Object type TABLE:"WCJ3"."T2" failed to create with error:
ORA-00959: tablespace 'WCJ2' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T2" ("ID" NUMBER(*,0), "NAME" VARCHAR2(10 BYTE)) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "WCJ2" 
22-APR-22 10:15:04.803: ORA-39083: Object type TABLE:"WCJ3"."T5" failed to create with error:
ORA-00959: tablespace 'WCJ2' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T5" ("ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(50 BYTE), "TIME1" TIMESTAMP (6)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
22-APR-22 10:15:04.803: ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."TT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ" 
22-APR-22 10:15:04.837: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
22-APR-22 10:15:04.897: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
22-APR-22 10:15:04.921: ORA-39112: Dependent object type CONSTRAINT:"WCJ3"."SYS_C0010042" skipped, base object type TABLE:"WCJ3"."T1" creation failed
22-APR-22 10:15:04.948: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
22-APR-22 10:15:04.962: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
22-APR-22 10:15:04.972: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
22-APR-22 10:15:06.132: Job "SYS"."IMPWCJ2" completed with 15 error(s) at Fri Apr 22 10:15:06 2022 elapsed 0 00:00:03
2）删除表空间wcj2，remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs。

导入失败，wcj3tbs不存在。

[oracle@oamsdb01l /home/oracle]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_quota_0.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 table_exists_action=replace \
remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs

Import: Release 12.1.0.2.0 - Production on Fri Apr 22 10:32:08 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
22-APR-22 10:32:11.327: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
22-APR-22 10:32:12.156: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2_quota_0.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 remap_schema=wcj2:wcj3 table_exists_action=replace remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs 
22-APR-22 10:32:12.321: Processing object type SCHEMA_EXPORT/USER
22-APR-22 10:32:12.515: ORA-39083: Object type USER:"WCJ3" failed to create with error:
ORA-00959: tablespace 'WCJ3TBS' does not exist
Failing sql is:
 CREATE USER "WCJ3" IDENTIFIED BY VALUES 'S:B00CF3E470AA3EED94769833EBA004161720AEFF2EC798BC0B0C55465C2F;H:AAE604F20F4CBA4F0E9B0D5F32F8EF0E;T:3D8604E1492E55480621B75F5CD078D8FEF5BCCE6717CA9B5C47E8B9ED61FB2547A9C9BAF36DDF99591C63ABFDF78E71FBDB51B7BF398863FDB24A478665FC88F185BFE298CB9DD9F31BE24AAD87F9B5;E9EFA57F3F877F79' DEFAULT TABLESPACE "WCJ3TBS" TEMPORARY TABLESPACE "TEMP"
22-APR-22 10:32:12.552: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-22 10:32:12.720: ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
GRANT CREATE TABLE TO "WCJ3"
22-APR-22 10:32:12.720: ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:

这次报的是找不到wcj3tbs，remap_tablespace似乎生效了。

试试生成wcj3tbs之后，再导入，全部成功。wcj3的quota只有wcj3tbs，默认表空间为wcj3tbs，且所有表的表空间也是wcj3tbs。

到这里应该可以想到，remap_tablespace=wcj2:wcj3tbs确实是生效了，之前有一个实验不知哪里出错，误以为remap_tablespace无法生效。下面复现一下之前的实验。

验证remap_tablespace有效性

此前wcj2的默认临时表空间时temp，在目标库也是存在的。现在我要修改wcj2的临时表空间为wcj2temp，表空间为wcj2tbs，并且授权，然后再重新导出。

SYS@dborcl1> create temporary tablespace wcj2temp tempfile '+dg_data' size 1G autoextend off;

Tablespace created.
SYS@dborcl1> create tablespace wcj2tbs datafile '+dg_data' size 1G autoextend off;

Tablespace created.
SYS@dborcl1> create user wcj2 identified by wcj2 default tablespace wcj2tbs temporary tablespace wcj2temp;

User created.
SYS@dborcl1> grant connect,resource to wcj2;

Grant succeeded.

SYS@dborcl1> alter user wcj2 quota unlimited on wcj2tbs;

User altered.

使用之前的dmp文件导入表数据到wcj2
impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_quota_0.dmp logfile=impwcj2.log logtime=all \
cluster=no job_name=impwcj2  table_exists_action=replace remap_tablespace=wcj:wcj2tbs,wcj2:wcj2tbs



'
WCJ2@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2TBS
T2			       WCJ2TBS
T3			       WCJ2TBS
T4			       WCJ2TBS
T5			       WCJ2TBS
TT			       WCJ2TBS

6 rows selected.

WCJ2@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2TBS 			182648832	  -1	  22296 	-1 NO

WCJ2@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ2			       WCJ2TBS			      WCJ2TEMP

导出

expdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_tbs.dmp nologfile=yes cluster=no schemas=wcj2

导入前准备：

drop user wcj3 cascade;
create tablespace wcj3tbs datafile '+dg_data' size 1G autoextend off;
drop tablespace wcj2temp including contents;
drop tablespace wcj2tbs including contents;

现在，wcj2tbs和wcj2temp都没了，remap_tablespace=wcj2tbs:wcj3tbs，执行导入

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_tbs.dmp \
logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2  table_exists_action=replace remap_schema=wcj2:wcj3  \
remap_tablespace=wcj2tbs:wcj3tbs

Import: Release 12.1.0.2.0 - Production on Fri Apr 22 11:33:22 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
22-APR-22 11:33:25.863: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
22-APR-22 11:33:26.322: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2_tbs.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 table_exists_action=replace remap_schema=wcj2:wcj3 remap_tablespace=wcj2tbs:wcj3tbs 
22-APR-22 11:33:26.496: Processing object type SCHEMA_EXPORT/USER
22-APR-22 11:33:26.713: ORA-39083: Object type USER:"WCJ3" failed to create with error:
ORA-00959: tablespace 'WCJ2TEMP' does not exist
Failing sql is:
 CREATE USER "WCJ3" IDENTIFIED BY VALUES 'S:7EA9DF10F2B1A352BC52C784E846CCBAF5543741CBAA721D3578BAE2D230;H:AAE604F20F4CBA4F0E9B0D5F32F8EF0E;T:6B4F82E946EEFA7BECDB9255F847CC4A82CA8DADDEA661B59C7849A759DBF566A38FA222A504A0C8E060B5006BD5DB789378B2943A7D6F81ACCA3B3DDF2A0AFC44B134585144C602A187C4449ED9F197;E9EFA57F3F877F79' DEFAULT TABLESPACE "WCJ3TBS" TEMPORARY TABLESPACE "WCJ2TEMP"
22-APR-22 11:33:26.724: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-22 11:33:26.799: ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
GRANT CREATE TABLE TO "WCJ3"
22-APR-22 11:33:26.799: ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
GRANT CREATE SESSION TO "WCJ3"
22-APR-22 11:33:26.812: Processing object type SCHEMA_EXPORT/ROLE_GRANT
22-APR-22 11:33:26.960: ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
 GRANT "CONNECT" TO "WCJ3"
22-APR-22 11:33:26.960: ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'WCJ3' does not exist
Failing sql is:
 GRANT "RESOURCE" TO "WCJ3"
22-APR-22 11:33:26.975: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
22-APR-22 11:33:27.250: ORA-39083: Object type DEFAULT_ROLE:"WCJ3" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
 ALTER USER "WCJ3" DEFAULT ROLE ALL
22-APR-22 11:33:27.280: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
22-APR-22 11:33:27.353: ORA-39083: Object type TABLESPACE_QUOTA:"WCJ3" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "WCJ3" QUOTA UNLIMITED ON "WCJ3TBS"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''WCJ3TBS'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP_CO
22-APR-22 11:33:27.361: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
22-APR-22 11:33:27.425: ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema WCJ3 is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DBORCL', inst_scn=>'7925345');COMMIT; END; 
22-APR-22 11:33:27.494: Processing object type SCHEMA_EXPORT/TABLE/TABLE
22-APR-22 11:33:27.790: ORA-39083: Object type TABLE:"WCJ3"."T4" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T4" ("ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(50 BYTE), "TIME1" TIMESTAMP (6)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABL
22-APR-22 11:33:27.790: ORA-39083: Object type TABLE:"WCJ3"."T2" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T2" ("ID" NUMBER(*,0), "NAME" VARCHAR2(10 BYTE)) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "WCJ3TBS" 
22-APR-22 11:33:27.790: ORA-39083: Object type TABLE:"WCJ3"."T3" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T3" ("ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(50 BYTE), "TIME1" TIMESTAMP (6)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLES
22-APR-22 11:33:27.790: ORA-39083: Object type TABLE:"WCJ3"."T5" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T5" ("ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(50 BYTE), "TIME1" TIMESTAMP (6)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABL
22-APR-22 11:33:27.790: ORA-39083: Object type TABLE:"WCJ3"."T1" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."T1" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ3TBS" 
22-APR-22 11:33:27.790: ORA-39083: Object type TABLE:"WCJ3"."TT" failed to create with error:
ORA-01918: user 'WCJ3' does not exist
Failing sql is:
CREATE TABLE "WCJ3"."TT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) 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 "WCJ3TBS" 
22-APR-22 11:33:27.819: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
22-APR-22 11:33:27.872: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
22-APR-22 11:33:27.904: ORA-39112: Dependent object type CONSTRAINT:"WCJ3"."SYS_C0010442" skipped, base object type TABLE:"WCJ3"."T1" creation failed
22-APR-22 11:33:27.923: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
22-APR-22 11:33:27.968: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
22-APR-22 11:33:27.993: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
22-APR-22 11:33:30.489: Job "SYS"."IMPWCJ2" completed with 15 error(s) at Fri Apr 22 11:33:30 2022 elapsed 0 00:00:05

果然，ORA-39083: Object type USER:"WCJ3" failed to create with error: ORA-00959: tablespace 'WCJ2TEMP' does not exist，说的就是临时表空间不存在。那么，创建临时表空间wcj2temp，不创建wcj2tbs，如果成功，说明remap_tablespace=wcj2tbs:wcj3tbs是生效的。否则就没生效。

 create temporary tablespace wcj2temp tempfile '+dg_data' size 1G autoextend off;

[oracle@oamsdb01l /home/oracle/expdp_dmp]$ impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=expwcj2_tbs.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2  table_exists_action=replace remap_schema=wcj2:wcj3  remap_tablespace=wcj2tbs:wcj3tbs

Import: Release 12.1.0.2.0 - Production on Fri Apr 22 11:37:33 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
22-APR-22 11:37:35.386: Master table "SYS"."IMPWCJ2" successfully loaded/unloaded
22-APR-22 11:37:35.689: Starting "SYS"."IMPWCJ2":  "/******** AS SYSDBA" directory=expdp_dmp dumpfile=expwcj2_tbs.dmp logfile=impwcj2.log logtime=all cluster=no job_name=impwcj2 table_exists_action=replace remap_schema=wcj2:wcj3 remap_tablespace=wcj2tbs:wcj3tbs 
22-APR-22 11:37:35.748: Processing object type SCHEMA_EXPORT/USER
22-APR-22 11:37:35.872: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-22 11:37:35.932: Processing object type SCHEMA_EXPORT/ROLE_GRANT
22-APR-22 11:37:35.990: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
22-APR-22 11:37:36.046: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
22-APR-22 11:37:36.105: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
22-APR-22 11:37:36.203: Processing object type SCHEMA_EXPORT/TABLE/TABLE
22-APR-22 11:37:36.522: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
22-APR-22 11:37:37.623: . . imported "WCJ3"."T3"                                 47.55 MB 2000000 rows
22-APR-22 11:37:38.121: . . imported "WCJ3"."T4"                                 47.55 MB 2000000 rows
22-APR-22 11:37:38.596: . . imported "WCJ3"."T5"                                 47.55 MB 2000000 rows
22-APR-22 11:37:38.654: . . imported "WCJ3"."T1"                                 5.546 KB       5 rows
22-APR-22 11:37:38.711: . . imported "WCJ3"."TT"                                 5.570 KB       7 rows
22-APR-22 11:37:38.719: . . imported "WCJ3"."T2"                                     0 KB       0 rows
22-APR-22 11:37:38.765: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
22-APR-22 11:37:38.891: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
22-APR-22 11:37:38.913: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
22-APR-22 11:37:38.933: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
22-APR-22 11:37:40.060: Job "SYS"."IMPWCJ2" successfully completed at Fri Apr 22 11:37:40 2022 elapsed 0 00:00:05

成功了，这说明remap_tablespace是生效的。而remap_tablesapce 无法修改临时表空间，这就导致临时表空间不存在时自动创建用户无法成功。

3）保留表空间wcj2，不指定remap_tablespace
  • 回到之前的实验，依然是不创建wcj3,不创建wcj3tbs表空间。保留表空间wcj2，不指定remap_tablespace，tt表无法导入，其他表成功，且自动创建的用户wcj3默认表空间和临时表空间和wcj2一样，wcj3的quota只有wcj2。

4. 不创建wcj3用户，创建wcj3tbs表空间

实验结果：

  • 不指定remap_tablespace，导入失败，无法创建用户，因为没有表空间wcj2
  • remap_tablespace=wcj:wcj3tbs，导入失败，无法创建用户，因为没有表空间wcj2
  • remap_tablespace=wcj:wcj3tbs,wcj2:wcj3tbs，成功导入，wcj3默认表空间为wcj3tbs，只有wcj3tbs的配额，所有表都在wcj3tbs下。
  • remap_tablespace=wcj2:wcj3tbs，tt表无法导入，其他表成功导入，wcj3默认表空间为wcj3tbs，只有wcj3tbs的配额，所有表都在wcj3tbs下。

实验二：源库用户拥有unlimited tablespace权限如何处理？

首先看一下源库的环境:

WCJ2@dborcl1> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM COM
------------------------------ ---------------------------------------- --- ---
WCJ2			       UNLIMITED TABLESPACE			NO  NO
WCJ2			       CREATE TABLE				NO  NO
WCJ2			       CREATE SESSION				NO  NO
WCJ2@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2TBS 				0	  -1	      0 	-1 YES

WCJ2@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ2			       WCJ2TBS			      WCJ2TEMP
WCJ2@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2TBS
T2			       WCJ2TBS
T3			       WCJ2TBS
T4			       WCJ2TBS
T5			       WCJ2TBS
TT			       WCJ2TBS

6 rows selected.

我们即将要导出的schema是wcj2，该用户默认表空间时wcj2tbs，临时表空间为wcj2temp。quota配置只有一个wcj2tbs，但是有unlimited tablespace权限。目标库首先创建好了wcj3tbs表空间。

现在的问题是，schema导出wcj2之后，impdp会如何处理unlimited tablespace权限，quota又会有什么影响。

导出

expdp \'/ as sysdba\' directory=expdp_dmp dumpfile=wcj2_unlimi.dmp nologfile=yes cluster=no schemas=wcj2
1)手动创建用户
(1)remap_tablespace=wcj2tbs:wcj3tbs

导入准备

drop user wcj3 cascade;
create user wcj3 identified by wcj3 default tablespace wcj3tbs temporary tablespace temp;
alter user wcj3 quota unlimited on wcj3tbs;
grant resource,connect to wcj3;

导入

impdp \' / as sysdba\' directory=expdp_dmp dumpfile=wcj2_unlimi.dmp nologfile=yes \
cluster=no remap_schema=wcj2:wcj3 remap_tablespace=wcj2tbs:wcj3tbs;

导入成功，看看wcj3是个啥情况

WCJ2@dborcl1> conn wcj3/wcj3;
Connected.
WCJ3@dborcl1> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM COM
------------------------------ ---------------------------------------- --- ---
WCJ3			       UNLIMITED TABLESPACE			NO  NO
WCJ3			       CREATE TABLE				NO  NO
WCJ3			       CREATE SESSION				NO  NO

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ3TBS 			182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP
WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ3TBS
T2			       WCJ3TBS
T3			       WCJ3TBS
T4			       WCJ3TBS
T5			       WCJ3TBS
TT			       WCJ3TBS

6 rows selected.

继承了unlimited权限，quota设置符合规则，手动创建用户的默认表空间和临时表空间没有被更改，符合规则，表所在表空间符合规则。

(2)remap_tablespace=wcj2tbs:users

导入准备

drop user wcj3 cascade;
create user wcj3 identified by wcj3 default tablespace wcj3tbs temporary tablespace temp;
alter user wcj3 quota unlimited on wcj3tbs;
grant resource,connect to wcj3;

导入

impdp \' / as sysdba\' directory=expdp_dmp dumpfile=wcj2_unlimi.dmp nologfile=yes cluster=no remap_schema=wcj2:wcj3 \
remap_tablespace=wcj2tbs:users;

导入成功，看看wcj3是个啥情况

WCJ3@dborcl1> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM COM
------------------------------ ---------------------------------------- --- ---
WCJ3			       UNLIMITED TABLESPACE			NO  NO
WCJ3			       CREATE TABLE				NO  NO
WCJ3			       CREATE SESSION				NO  NO

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS				182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      TEMP

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       USERS
T2			       USERS
T3			       USERS
T4			       USERS
T5			       USERS
TT			       USERS

6 rows selected.

继承了unlimited权限，quota设置符合规则，手动创建用户的默认表空间和临时表空间没有被更改，符合规则，表所在表空间符合规则。

2)自动创建用户
(1)remap_tablespace=wcj2tbs:wcj3tbs

导入准备

 drop user wcj3 cascade;

导入

impdp \' / as sysdba\' directory=expdp_dmp dumpfile=wcj2_unlimi.dmp nologfile=yes \
cluster=no remap_schema=wcj2:wcj3 remap_tablespace=wcj2tbs:wcj3tbs;

没有报错，现在去看看wcj3是个啥情况

SYS@dborcl1> alter user wcj3 identified by wcj3;

User altered.

SYS@dborcl1> conn wcj3/wcj3;
Connected.
WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ3TBS 			182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM COM
------------------------------ ---------------------------------------- --- ---
WCJ3			       UNLIMITED TABLESPACE			NO  NO
WCJ3			       CREATE TABLE				NO  NO
WCJ3			       CREATE SESSION				NO  NO

WCJ3@dborcl1> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ3TBS			      WCJ2TEMP

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ3TBS
T2			       WCJ3TBS
T3			       WCJ3TBS
T4			       WCJ3TBS
T5			       WCJ3TBS
TT			       WCJ3TBS

6 rows selected.

继承了unlimited tablespace权限。quota符合之前的规则。默认表空间和临时表空间分别重命名和继承，也符合之前的规则。表所在表空间符合规则。

(2)remap_tablespace=wcj2tbs:users

导入准备

 drop user wcj3 cascade;

导入

impdp \' / as sysdba\' directory=expdp_dmp dumpfile=wcj2_unlimi.dmp nologfile=yes \
cluster=no remap_schema=wcj2:wcj3 remap_tablespace=wcj2tbs:users

没有报错，现在去看看wcj3是个啥情况

SYS@dborcl1> alter user wcj3 identified by wcj3;

User altered.

SYS@dborcl1> conn wcj3/wcj3;
Connected.
WCJ3@dborcl1> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM COM
------------------------------ ---------------------------------------- --- ---
WCJ3			       UNLIMITED TABLESPACE			NO  NO
WCJ3			       CREATE TABLE				NO  NO
WCJ3			       CREATE SESSION				NO  NO

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS				182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1>  select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       USERS			      WCJ2TEMP

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       USERS
T2			       USERS
T3			       USERS
T4			       USERS
T5			       USERS
TT			       USERS

6 rows selected.

继承了unlimited tablespace权限。quota符合之前的规则。默认表空间和临时表空间分别重命名和继承，也符合之前的规则。表所在表空间符合规则。

(3)不指定remap_tablespace

导入准备

 drop user wcj3 cascade;

导入

impdp \' / as sysdba\' directory=expdp_dmp dumpfile=wcj2_unlimi.dmp nologfile=yes \
cluster=no remap_schema=wcj2:wcj3

没有报错，现在去看看wcj3是个啥情况

SYS@dborcl1> alter user wcj3 identified by wcj3;

User altered.

SYS@dborcl1> conn wcj3/wcj3;
Connected.
WCJ3@dborcl1> select * from user_sys_privs;

USERNAME		       PRIVILEGE				ADM COM
------------------------------ ---------------------------------------- --- ---
WCJ3			       UNLIMITED TABLESPACE			NO  NO
WCJ3			       CREATE TABLE				NO  NO
WCJ3			       CREATE SESSION				NO  NO

WCJ3@dborcl1> select * from user_ts_quotas;

TABLESPACE_NAME 		    BYTES  MAX_BYTES	 BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
WCJ2TBS 			182648832	  -1	  22296 	-1 NO

WCJ3@dborcl1>  select username,default_tablespace,temporary_tablespace from user_users;

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
WCJ3			       WCJ2TBS			      WCJ2TEMP

WCJ3@dborcl1> select table_name,tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
T1			       WCJ2TBS
T2			       WCJ2TBS
T3			       WCJ2TBS
T4			       WCJ2TBS
T5			       WCJ2TBS
TT			       WCJ2TBS

继承了unlimited tablespace权限。quota符合之前的规则。默认表空间和临时表空间继承源库用户，也符合之前的规则。表所在表空间符合规则。

总结

  • 手动创建用户特性

    • impdp不会改变手动创建用户的默认表空间和临时表空间。

    • impdp根据情况改变表空间的配额设置，默认情况下配额设置和源库用户一样。如果remap_tablespace没有修改源库用户配额设置中指定的表空间，那么配额设置和源库完全一样（如果源库用户user_ts_quotas视图中某条记录的max_bytes=0，这条记录将不会同步）。如果remap_tablespace修改了源库用户配额设置中指定的表空间，那么新用户的表空间配额将以remap_tablespace指定的为准。剩余remap_tablespace没指定的则和源库用户一样。举例，

      源库用户A，配额设置中配置了users, tbs_a。导入时remap_schema=A:B ，不指定remap_tablespace。那么B用户的配额也是users,tbs_a;如果A用户的user_ts_quotas视图中tbs_a行的max_bytes=0，那么B用户的配额是users。

      导入时remap_schema=A:B ，remap_tablespace=users:tbs_b。那么B用户的配额是tbs_b, tbs_a。

      导入时remap_schema=A:B ，remap_tablespace=users:tbs_b,tbs_a,tbs_b。那么B用户的配额是tbs_b。

    • 如果源库用户拥有unlimited tablespace权限，那么目标用户也会继承unlimited tablespace，不管是不是手动创建用户。unlimited tablespace不影响quota、默认表空间和临时表空间的规则。并且视情况在导入以后回收unlimited tablespace权限。在相应的表空间分配quota。

      revoke unlimited tablespace from username;
alter user username quota unlimited on tablespace_name;

  • 自动创建用户特性

    • 需要保证导出时连接用户有DATAPUMP_EXP_FULL_DATABASE权限。
    • 导入之后需要修改自动创建用户的密码。
    • 指定remap_tablespace修改了源库用户的默认表空间时，impdp会改变自动创建用户的默认表空间。临时表空间无法修改，要使用自动创建用户，必须在目标库有源库用户的临时表空间。
    • impdp会根据情况修改新建用户的配额设置。如果remap_tablespace参数没有修改源库用户的quota所配置的表空间为新的值，那么impdp会将新建用户的quota设置和源库用户一样(如果源库用户user_ts_quotas视图中某条记录的max_bytes=0，这条记录将不会同步)。如果remap_tablespace参数修改了源库用户wcj2的quota配置的表空间为新的值（users、wcj3tbs），那么impdp会将新用户wcj3的配额设置为新的值。这和手动创建用户的情况是一样的。
    • 如果源库用户拥有unlimited tablespace权限，那么目标用户也会继承unlimited tablespace，不管是不是手动创建用户。unlimited tablespace不影响quota、默认表空间和临时表空间的规则。并且视情况在导入以后回收unlimited tablespace权限。在相应的表空间分配quota。
ChunkWen
关注
  • 0
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle的impdp需要建表空间,impdp时，不想事先手动建立表空间
weixin_32685111的博客
04-08 2209
你可以参考如下的操作方式：使用system用户创建表空间与用户：SQL> create tablespace p12datafile 'E:\oracle\product\10.2.0\oradata\orcl\p01.dbf'3size 50M4/表空间已创建。SQL> create user utmp identified by utmp2default table...
remap_tablespace修改数据库表空间
一花一草一世界
12-29 7763
首先，以schemas方式导出用户JCMS24的全部数据： expdp system/system directory=data_pump_dir dumpfile=test_user.dmp schemas=JCMS24 说明：使用system导出是为了导出JCMS24 schema中的全部内容，包括用户创建和权限授予部分，使用用户本身导出则不包含这些内容。 其次，drop用户及其全部对象
oracle impdp remap_tablespace
mhabbyo的博客
11-10 6762
impdp habby/habby schemas=TEST1  directory=dump572 dumpfile=TEST1_20181109.dmp  remap_Tablespace=old_tablespacename:new_tablespacename_test1   logfile=TEST1_20181109.log     导出时候和导入时不在同一个表空间的: rem...
Oracle expdp/impdp 及 exp/imp 命令详解
最新发布
ITSM/ITIL/网络安全/IT运维
04-25 3041
Oracle 数据库提供 expdp / impdp （Data Pump，数据泵）以及 exp / imp 两种工具进行数据的导入导出，可以对数据库进行逻辑备份。2、导出用户 scott 的 emp 表和 HR 的 jobs 表，目录对象为：默认，转储文件名称为：默认[EXPDAT.DMP]， 日志文件为：默认[export.log]1、导出用户 scott 和 HR 的所有对象，目录对象为：默认，转储文件名称为：默认[EXPDAT.DMP]， 日志文件为：默认[export.log]
数据库oracle开发手记——expdp/impdp 详细参数解释
u_nightroad的博客
05-10 5753
数据泵导出实用程序提供了一种用于在Oracle数据库之间传输 数据对象的机制。该实用程序可以使用以下命令进行调用: 示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导出的运行方式。具体方法是: 在 'expdp' 命令后输入 各种参数。要指定各参数, 请使用关键字: 格式:expdp KEY...
oracle中imp命令详解
zslsh44的专栏
01-17 3730
oracle中imp命令详解 Oracle的导入实用程序(Import utility)允许从数据库提取数据，并且将数据写入操作系统文 件。imp使用的基本格式：imp[username[/password[@service]]]，以下例举imp常用用 法。 1. 获取帮助 imp help=y 2. 导入一个完整数据库 imp system/manager file=bi
创建数据库表空间用户及数据备份.rar_oracle_创建数据对象
09-24
在Oracle数据库管理中，创建数据库表空间、用户以及数据备份是至关重要的操作，它们构成了数据库管理的基础。下面将详细介绍这些概念及其实施步骤。 首先，**表空间（Tablespace）**是Oracle数据库中存储数据的基本...
oracle创建huike用户与表空间并授权
03-24
在本教程中，我们将详细探讨如何在Oracle中创建名为“huike”的用户，为该用户分配表空间，并进行相应的权限授予。首先，我们需要了解Oracle数据库中的用户和表空间的概念。 **用户（User）**： 在Oracle中，用户是...
Oracle数据库创建用户与数据库备份小结(必看篇)
09-09
- `UNLIMITED TABLESPACE`允许用户使用无限的表空间配额。 - `CONNECT`角色允许用户连接到数据库。 - `RESOURCE`角色允许用户创建数据库对象，但不能创建数据库结构。 - `DBA`角色是最高权限，可以进行所有数据库...
oracle11g expdp impdp 分区表重映射导出导入数据迁移方案
12-06
oracle expdp impdp 分区表重映射导出导入 数据迁移方案，以SI01用户为例子，将用户分区表导出后，将分区表重映射到新的表空间，完成数据迁移和检查。照方案例子按步去做，一定能成功。
Oracle如何查看impdp正在执行的内容
09-09
主要给大家介绍了关于Oracle如何查看impdp正在执行的内容的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Oracle具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
Oracle中如何使用imp语句导入不同表空间？
07-16
在实际工作中，有时需要将exp导出的历史备份dmp文件进行恢复，若之前的建表语句及表空间名无法找到，则直接用imp语句进行导入的话可能报错表空间不存在，且导入数据占用空间可能会非常大。使用本文方法可将exp导出的数据用imp导入不同的表空间，从而有效解决此问题。
oracle导入时表空间不一致解决方法
10-19
能够解决oracle导入时表空间不一致问题。不错，已经帮助我好几次了，共享下！
oracle数据泵expdp impdp使用说明
05-15
oracle数据泵expdp impdp使用说明
oracle的数据泵权限,关于expdp,impdp操作需要的权限
weixin_39975900的博客
04-06 1456
01-- $ORACLE_HOME/rdbms/admin/catdpb.sql0203...04Rem NAME05Rem catdpb.sql - Main install script for all DataPump package body06Rem components0708...0910----------------------...
expdp / impdp 之 remap_schema remap_tablespace
07-07 1615
改变一个对象的owner， 以及对象所在的tablespace， oracle 的exp/imp提供了以下两种方式：A. exp/imp 的 fromuser touser 参数B.expdpimpdp 的 remap_sch...
impdp导schema报ORA-31625异常处理
cqszpx的博客
06-23 890
数据库：oracle 11.2.0.1 系统：windows 2008 R2 Enterprise 异常描述：impdp导入schema时报错ORA-31625 D:\app\Administrator\product\11.2.0\dbhome_1\BIN>impdp system/oracle_4U@ 192.168.133.166:1521/medxdb directory=hr_dir dumpfile=11gora.dmp remap_schema=ora1:hr Import: Release
oracle导入时没有表空间,IMPDP导入没有某个表空间的处理方法__加上参数TRANSFORM就可以了...
weixin_39825322的博客
04-03 890
TRANSFORM导入和导出的时候，有些表空间不一样。比如导出的时候该对象是在A表空间，导入到另一个库没有表空间A就会报错。但是使用这个参数就会正常导入，该对象会存储在该用户下的默认表空间中。常用TRANSFORM=segment_attributes:n[oracle@db ~]$ impdp -help |grep TRANSFORM -A2TRANSFORMMetadata transfor...
oracle如何把一个dmp数据库备份还原成空库，并清理表空间
札记
11-25 1828
1. 通过exp命令完全导出数据库，生成dmp文件 EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y  2.清空所有表数据 truncate table tablename 3.plsql 以需要导出数据的用户登录 新建command window 执行下面代码  -----------    set heading o
impdp 导入表 指定用户和表空间
06-08
您可以使用以下命令来使用 impdp 工具导入指定用户和表空间的表： ``` impdp <用户名>/<密码> directory=<目录名> dumpfile=<导出文件名>.dmp logfile=<日志文件名>.log remap_schema=<原用户名>:<新用户名> remap_tablespace=<原表空间名>:<新表空间名> ``` 其中，`<用户名>` 和 `<密码>` 是目标数据库的用户名和密码；`<目录名>` 是导出文件所在的目录；`<导出文件名>.dmp` 是导出文件的名称；`<日志文件名>.log` 是导入过程的日志文件名；`<原用户名>` 是导出文件中的用户名称，`<新用户名>` 是您想要导入表所属的新用户名称；`<原表空间名>` 是导出文件中的表空间名称，`<新表空间名>` 是您想要将表导入的新表空间名称。 举个例子，如果您想要将 `hr` 用户在 `users` 表空间中的表导入到 `hr_new` 用户在 `users_new` 表空间中，则您可以使用以下命令： ``` impdp hr/hr directory=dpump_dir1 dumpfile=hr.dmp logfile=imp_hr.log remap_schema=hr:hr_new remap_tablespace=users:users_new ``` 希望这可以帮助到您！

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值