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-10-11
Oracle
複製建立已有資料庫使用者、表空間、許可權的指令碼
2021-03-05
資料庫指令碼
Oracle資料庫scott使用者建立view檢視許可權
2021-01-02
Oracle資料庫View
oracle 建立表空間和使用者
2018-06-14
Oracle
Oracle建立表空間和使用者
2024-05-26
Oracle
oracle建立臨時表空間和資料表空間以及刪除
2018-10-19
Oracle
當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
2019-06-03
oracle新建使用者，表空間，並授權
2024-05-10
Oracle
Oracle新建表空間、使用者及授權
2018-04-09
Oracle
Oracle 19C 建立使用者&表空間
2024-08-16
Oracle
Oracle 建立表空間和使用者指令碼
2022-03-11
Oracle指令碼
Oracle使用者角色許可權管理
2018-04-11
Oracle
Oracle新建使用者、表空間、表
2024-03-20
Oracle
Oracle OCP(47)：表空間的建立
2019-05-08
Oracle
DB2建立資料庫，建立表空間
2021-06-02
DB2資料庫
教程示例：控制儲存空間和資料夾的訪問許可權
2018-07-04
訪問許可權
16、表空間建立表空間
2020-12-03
Oracle 資料庫安全許可權配置標準
2021-02-20
Oracle資料庫
PostgreSQL資料庫使用者許可權管理
2023-01-18
SQL資料庫
oracle匯出使用者、表空間和角色的建立語句
2020-09-29
Oracle
Oracle 刪除使用者、表空間、資料檔案、使用者下的所有表
2024-05-26
Oracle
Oracle中新建表空間、使用者
2024-04-16
Oracle
許可權系統：一文搞懂功能許可權、資料許可權
2024-11-10
授權|取消授權MYSQL資料庫使用者許可權
2024-11-26
MySql資料庫
2.4.11 Step 10: 建立額外的表空間
2020-03-03
怎麼控制報表的資料許可權
2020-06-23
Oracle建立使用者、角色、授權、建表
2018-04-27
Oracle
Oracle表空間
2019-06-11
Oracle
oracle 表空間
2024-09-27
Oracle
MySQL建立使用者，配置許可權、密碼策略
2019-01-25
MySql密碼
MySQL新增新使用者、為使用者建立資料庫、為新使用者分配許可權
2018-09-14
MySql資料庫
Rbac使用者角色許可權表設計
2019-02-16
oracle 普通表空間資料檔案壞塊
2023-12-30
Oracle
表空間限額
2019-10-24
Oracle案例11——Oracle表空間資料庫檔案收縮
2018-07-04
Oracle資料庫
【USER】Oracle 一個普通使用者有多少許可權
2021-08-26
Oracle
增加oracle表空間
2018-10-10
Oracle
oracle temp 表空間
2019-05-21
Oracle