Oracle資料泵建立使用者避坑（表空間配額、許可權）

尋道者_紛彩之心發表於2022-04-24

# Oracle impdp 建立使用者避坑指南（表空間配額，許可權）

本篇文章非常長。沒有耐心的同學可以直接跳到[總結](##總結)

實驗環境：12.1.0 RAC

## 實驗一：remap_schema，remap_tablespace能夠修改新使用者預設表空間和表空間配額？

我們要匯出源庫的wcj2使用者所有資料，wcj2使用者目前只有wcj2表空間的訪問許可權。注意裡面有一張tt表，表空間時wcj，這是之前wcj2擁有unlimited tablespace許可權時建立的。

```sql
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使用者的資料：

```bash
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的配額

```sql
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

```bash
[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表是無法匯入的。

```sql
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

```bash
[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？

查一下就知道了。

```sql
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表空間。

```sql
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表空間的配額。

```sql
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下。

```sql
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。

清理之前的實驗資料

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

重新匯入

```bash
[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
```

怎麼回事？怎麼只有一個使用者已存在的錯誤？資料全部正常匯入。

查一下:

```sql
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

清理舊實驗資料

```sql
 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.

```bash
[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一樣的，重新命名的表空間不能訪問，查一下

```sql
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

清理舊實驗資料

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

現在，重新命名的表空間不再是wcj3tbs變成了users，impdp會怎麼做呢？

```bash
[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
```

正常匯入

```sql
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表空間不能訪問。

清理舊實驗資料

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

匯入：

```bash
[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的配額

```sql
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的配額設定一樣呢？

再做一個實驗：

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

User altered.
```

```sql
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使用者的配額設定已經變了：

```sql
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重新匯入看看

```bash
[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是一樣的？

查一查

```sql
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的配額設定會是新指定的值嗎？

```sql
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

```bash
[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
```

```sql
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表空間。

```sql
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。

匯入失敗，所有表都無法匯入。

```bash
[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不存在。

```bash
[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，並且授權，然後再重新匯出。

```sql
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
```

匯出

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

匯入前準備：

```sql
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，執行匯入

```bash
[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是生效的。否則就沒生效。

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

```bash
[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_tablespac

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70016585/viewspace-2888418/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

Oracle建立表空間、建立資料庫使用者、賦許可權
2018-01-24
Oracle資料庫
表空間配額和UNLIMITED TABLESPACE許可權
2014-04-11
MIT
Oracle 建立表空間、建立使用者以及授權、檢視許可權
2010-05-25
Oracle
Oracle建立表空間、使用者、分配許可權語句
2016-08-24
Oracle
oracle建立使用者，表空間，臨時表空間，分配許可權步驟詳解
2018-10-11
Oracle
oracle建立/刪除表空間、建立/刪除使用者並賦予許可權
2017-07-19
Oracle
複製建立已有資料庫使用者、表空間、許可權的指令碼
2021-03-05
資料庫指令碼
資料庫使用者表空間配額管理
2010-07-28
資料庫
Oracle檢視使用者預設表空間、臨時表空間、系統許可權、物件許可權、角色許可權舉例說明
2016-12-27
Oracle物件
Oracle建立表空間、建立使用者、授權、授權物件的訪問以及檢視許可權集合
2015-03-06
Oracle物件
oracle資料庫使用者建立、許可權分配
2007-08-12
Oracle資料庫
Oracle建立表空間、建立使用者以及授權
2016-09-08
Oracle
【轉】使用Navicat for Oracle新建表空間、使用者及許可權賦予
2016-10-01
Oracle
unlimited tablespace許可權和quota 配額
2016-06-14
MIT
Oracle資料庫scott使用者建立view檢視許可權
2021-01-02
Oracle資料庫View
oracle資料泵方式更換資料預設表空間.
2015-07-04
Oracle
Oracle 使用者許可權管理與常用許可權資料字典列表
2017-03-21
Oracle
ORACLE如何檢視錶空間路徑及使用者許可權
2013-04-23
Oracle
Oracle建立表空間和使用者
2021-04-28
Oracle
oracle 建立表空間和使用者
2018-06-14
Oracle
oracle一個建立使用者、建立表空間、授權、建表的完整過程
2017-11-09
Oracle
oracle建立臨時表空間和資料表空間以及刪除
2018-10-19
Oracle
Oracle 9i資料庫的使用者建立以及許可權分配
2008-05-10
Oracle資料庫
Oracle 使用者對錶空間配額quota說明
2017-04-12
Oracle
Oracle 使用者、物件許可權、系統許可權
2014-01-01
Oracle物件
Oracle RAC建立表空間和使用者
2017-12-25
Oracle
oracle RAC 建立使用者和表空間
2013-02-19
Oracle
oracle建立表空間
2015-12-09
Oracle
PLSQL來Oracle建立表空間和建立使用者
2017-09-25
SQLOracle
oracle使用者許可權
2009-04-03
Oracle
oracle 使用者許可權
2009-11-13
Oracle
12c 資料泵傳輸表空間
2018-01-23
資料泵 TTS（傳輸表空間技術）
2016-12-06
TTS
Oracle資料庫使用者許可權控制 - Role - Synonym
2010-08-06
Oracle資料庫
【許可權管理】Oracle中檢視、回收使用者許可權
2013-11-21
Oracle
Oracle新建表空間、使用者及授權
2018-04-09
Oracle
oracle 快速建表空間，使用者，賦權
2017-02-04
Oracle
Oracle 11g建立使用者並賦許可權
2017-10-22
Oracle