You can use this clause to separate the data for multiple schemas into different PDBs. For example, when you move a non-CDB to a PDB, and the non-CDB had several schemas that each supported a different application, you can use this clause to separate the data belonging to each schema into a separate PDB. This technique assumes that each schema used a separate tablespace in the non-CDB.
You can use this clause to specify one of the following options:
List one or more tablespaces to include.
Specify ALL, the default, to include all tablespaces.
Specify ALL EXCEPT to include all tablespaces, except for the tablespaces listed.
Specify NONE to exclude all tablespaces.
If the creation mode of the user tablespaces must be different from the creation mode for the Oracle-supplied tablespaces (such as SYSTEM and SYSAUX), then specify one of the following in the USER_TABLESPACES clause:
COPY: The files of the tablespaces are copied to a new location.
MOVE: The files of the tablespaces are moved to a new location.
NOCOPY: The files of the tablespaces are not copied or moved.
SNAPSHOT COPY: The tablespaces are cloned with storage snapshots.
NO DATA: The data model definition of the tablespaces is cloned but not the tablespaces’ data.
When the compatibility level of the CDB is 12.2.0 or higher, the tablespaces that are excluded by this clause are created offline in the new PDB, and they have no data files associated with them. When the compatibility level of the CDB is lower than 12.2.0, the tablespaces that are excluded by this clause are offline in the new PDB, and all data files that belong to these tablespaces are unnamed and offline.
This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces. Do not include these tablespaces in a tablespace list for this clause.
The following are examples that use the USER_TABLESPACES clause.
Example 5-4 USER_TABLESPACES Clause That Includes One Tablespace
Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1, tbs2, and tbs3. This USER_TABLESPACES clause includes the tbs2 tablespace, but excludes the tbs1 and tbs3 tablespaces.
USER_TABLESPACES=('tbs2')
Example 5-5 USER_TABLESPACES Clause That Includes a List of Tablespaces
Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1, tbs2, tbs3, tbs4, and tbs5. This USER_TABLESPACES clause includes the tbs1, tbs4, and tbs5 tablespaces, but excludes the tbs2 and tbs3 tablespaces.
USER_TABLESPACES=('tbs1','tbs4','tbs5')
Example 5-6 USER_TABLESPACES Clause That Includes All Tablespaces Except for Listed Ones
Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1, tbs2, tbs3, tbs4, and tbs5. This USER_TABLESPACES clause includes the tbs2 and tbs3 tablespaces, but excludes the tbs1, tbs4, and tbs5 tablespaces.
USER_TABLESPACES=ALL EXCEPT('tbs1','tbs4','tbs5')
Example 5-7 USER_TABLESPACES in a Different Creation Mode
This example shows a full CREATE PLUGGABLE DATABASE statement that plugs in a non-CDB and only includes the tbs3 user tablespace from the non-CDB. The example copies the files for Oracle-supplied tablespaces (such as SYSTEM and SYSAUX) to a new location, but moves the files of the tbs3 user tablespace.
郑全 发表于 2021-8-18 22:49
可以用来从其他PDB中，或者NON_CDB中创建PDB时，指定表空间，或者排除表空间，是 SYSTEM, SYSAUX, or TEMP ...
从ORCLPDB复制一个PDB, ORCLPDB 包含多个业务表空间，我们只复制 TBS_PDB.
SQL> alter session set container=orclpdb;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB READ WRITE NO
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
5 USERS YES NO YES 3
8 TSP_RESUME YES NO YES 3
6 TBS_PDB YES NO YES 3
7 TEMP_ORCLPDB NO NO YES 3
8 rows selected.
--开始复制PDB
SQL> conn / as sysdba
Connected.
SQL> create pluggable database pdb_user from orclpdb
2 user_tablespaces=('tbs_pdb');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB1 READ WRITE NO
6 PDB_USER MOUNTED
SQL> alter pluggable database pdb_user open;
Pluggable database altered.
SQL> alter session set container=pdb_user;
Session altered.
--看到所有表空间
SQL> select *from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 6
1 SYSAUX YES NO YES 6
2 UNDOTBS1 YES NO YES 6
3 TEMP NO NO YES 6
5 USERS YES NO YES 6
6 TBS_PDB YES NO YES 6
7 TEMP_ORCLPDB NO NO YES 6
8 TSP_RESUME YES NO YES 6
8 rows selected.
但有文件的只有TBS_PDB。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/ORCLCDB/C9D71BF3B3C325FBE0538685A8C0EB07/DATAFILE/system.295.1080944313
+DATA/ORCLCDB/C9D71BF3B3C325FBE0538685A8C0EB07/DATAFILE/sysaux.296.1080944313
+DATA/ORCLCDB/C9D71BF3B3C325FBE0538685A8C0EB07/DATAFILE/undotbs1.302.1080944313
+DATA/ORCLCDB/C9D71BF3B3C325FBE0538685A8C0EB07/DATAFILE/tbs_pdb.294.1080944313
从DBA_TABLESPACES中，只看到 TBS_PDB表空间。
SQL> select tablespace_name,status,CONTENTS from dba_tablespaces where contents='PERMANENT';
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------------------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
TBS_PDB ONLINE PERMANENT
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB READ WRITE NO
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
5 USERS YES NO YES 3
8 TSP_RESUME YES NO YES 3
6 TBS_PDB YES NO YES 3
7 TEMP_ORCLPDB NO NO YES 3
8 rows selected.
SQL> conn / as sysdba
Connected.
SQL> create pluggable database pdb_exclude from orclpdb
2 user_tablespaces=('none');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB1 READ WRITE NO
6 PDB_USER READ WRITE NO
7 PDB_EXCLUDE MOUNTED
SQL> alter pluggable database pdb_exclude open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB1 READ WRITE NO
6 PDB_USER READ WRITE NO
7 PDB_EXCLUDE READ WRITE NO
SQL> alter session set container=pdb_exclude;
Session altered.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 7
1 SYSAUX YES NO YES 7
2 UNDOTBS1 YES NO YES 7
3 TEMP NO NO YES 7
5 USERS YES NO YES 7
6 TBS_PDB YES NO YES 7
7 TEMP_ORCLPDB NO NO YES 7
8 TSP_RESUME YES NO YES 7
8 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/ORCLCDB/C9D73C10628B27A2E0538685A8C0B956/DATAFILE/system.312.1080944851
+DATA/ORCLCDB/C9D73C10628B27A2E0538685A8C0B956/DATAFILE/sysaux.310.1080944851
+DATA/ORCLCDB/C9D73C10628B27A2E0538685A8C0B956/DATAFILE/undotbs1.311.1080944851
SQL> select tablespace_name ,contents from dba_tablespaces;