8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : USER_TABLESPACES Clause in Oracle Database 12c
The USER_TABLESPACES
clause of the CREATE PLUGGABLE DATABASE
command gives control over which tablespaces are included in a PDB created from a clone or a plugin operation.
- The USER_TABLESPACES Clause in 12.1.0.2 Onward
- The Extended USER_TABLESPACES Clause in 12.2.0.1 Onward
Related articles.
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
The USER_TABLESPACES Clause in 12.1.0.2 Onward
In Oracle database 12.1.0.2 the USER_TABLESPACES
clause was added to the CREATE PLUGGABLE DATABASE
command, allowing you to control which tablespaces are included in a PDB created from a clone or a plugin operation.
USER_TABLESPACES = { ( 'tablespace' [, 'tablespace' ]... ) | NONE | ALL [ EXCEPT ( 'tablespace' [, 'tablespace' ]... ) ] }
Some simple examples are listed below. Remember, the SYSTEM, SYSAUX and TEMP tablespaces are always included.
USER_TABLESPACES=ALL
: All user tablespaces will be included in the final PDB. This is the same as the default action when the clause is omitted.USER_TABLESPACES=('ts1', 'ts2')
: Only the "ts1" and "ts2" user tablespaces will be included in the final PDB.USER_TABLESPACES=ALL EXCEPT('ts1')
: All user tablespaces except the "ts1" tablespace will be included in the final PDB.USER_TABLESPACES=NONE
: No user tablespaces will be included in the final PDB.
Let's try some of these examples using regular clones. Although this functionality was available in 12.1.0.2, we're running testing it in 12.2, so we can do hot clones. In addition, we are using Oracle Managed Files (OMF), so we don't need to name datafiles, or provide file name conversion information. For examples of clones without using OMF, check out the article here.
Create a pluggable database called "pdb2" to use as our starting point. The new pluggable database includes three additional tablespaces we will use for the tests.
CONN / AS SYSDBA CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_admin IDENTIFIED BY Password1; ALTER PLUGGABLE DATABASE pdb2 OPEN; ALTER SESSION SET CONTAINER = pdb2; CREATE TABLESPACE ts1 DATAFILE SIZE 1M; CREATE TABLESPACE ts2 DATAFILE SIZE 1M; CREATE TABLESPACE ts3 DATAFILE SIZE 1M; -- Check the status of the tablespaces. SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name LIKE 'TS%' ORDER BY 1; TABLESPACE_NAME STATUS ------------------------------ --------- TS1 ONLINE TS2 ONLINE TS3 ONLINE SQL> -- Check the status of the datafiles. SET LINESIZE 110 COLUMN file_name FORMAT A90 SELECT file_name, status FROM dba_data_files WHERE tablespace_name LIKE 'TS%' ORDER BY 1; FILE_NAME STATUS ------------------------------------------------------------------------------------------ --------- /u02/oradata/CDB1/8245598298A44438E055000000000001/datafile/o1_mf_ts1_g6rpst7l_.dbf AVAILABLE /u02/oradata/CDB1/8245598298A44438E055000000000001/datafile/o1_mf_ts2_g6rpt7gc_.dbf AVAILABLE /u02/oradata/CDB1/8245598298A44438E055000000000001/datafile/o1_mf_ts3_g6rpt7ht_.dbf AVAILABLE SQL>
We can see the three tablespaces are online and there is a datafile associated with each of them.
Create a new pluggable database called "pdb3" based on "pdb2", but only bringing across the "ts1" and "ts2" tablespaces.
CONN / AS SYSDBA CREATE PLUGGABLE DATABASE pdb3 FROM pdb2 USER_TABLESPACES=('ts1', 'ts2'); ALTER PLUGGABLE DATABASE pdb3 OPEN; ALTER SESSION SET CONTAINER = pdb3; -- Check the status of the tablespaces. SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name LIKE 'TS%' ORDER BY 1; TABLESPACE_NAME STATUS ------------------------------ --------- TS1 ONLINE TS2 ONLINE TS3 OFFLINE SQL> -- Check the status of the datafiles. SET LINESIZE 110 COLUMN file_name FORMAT A90 SELECT file_name, status FROM dba_data_files WHERE tablespace_name LIKE 'TS%' ORDER BY 1; FILE_NAME STATUS ------------------------------------------------------------------------------------------ --------- /u02/oradata/CDB1/824576FAA9994614E055000000000001/datafile/o1_mf_ts1_g6rq6og0_.dbf AVAILABLE /u02/oradata/CDB1/824576FAA9994614E055000000000001/datafile/o1_mf_ts2_g6rq6og0_.dbf AVAILABLE SQL>
The result is the "ts3" tablespace is marked as offline, and there is no datafile present for it.
Let's try again, but this time bringing across all tablespaces except the "ts1" tablespace.
CONN / AS SYSDBA -- Drop the existing PDB. ALTER PLUGGABLE DATABASE pdb3 CLOSE; DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES; CREATE PLUGGABLE DATABASE pdb3 FROM pdb2 USER_TABLESPACES=ALL EXCEPT('ts1'); ALTER PLUGGABLE DATABASE pdb3 OPEN; ALTER SESSION SET CONTAINER = pdb3; -- Check the status of the tablespaces. SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name LIKE 'TS%' ORDER BY 1; TABLESPACE_NAME STATUS ------------------------------ --------- TS1 OFFLINE TS2 ONLINE TS3 ONLINE SQL> -- Check the status of the datafiles. SET LINESIZE 110 COLUMN file_name FORMAT A90 SELECT file_name, status FROM dba_data_files WHERE tablespace_name LIKE 'TS%' ORDER BY 1; FILE_NAME STATUS ------------------------------------------------------------------------------------------ --------- /u02/oradata/CDB1/8245970E95E3480CE055000000000001/datafile/o1_mf_ts2_g6rqp2ql_.dbf AVAILABLE /u02/oradata/CDB1/8245970E95E3480CE055000000000001/datafile/o1_mf_ts3_g6rqp2ql_.dbf AVAILABLE SQL>
This time the "ts1" tablespace is offline, and there is no datafile associated with it.
Let's try again, but this time bringing across none of the tablespaces.
CONN / AS SYSDBA -- Drop the existing PDB. ALTER PLUGGABLE DATABASE pdb3 CLOSE; DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES; CREATE PLUGGABLE DATABASE pdb3 FROM pdb2 USER_TABLESPACES=NONE; ALTER PLUGGABLE DATABASE pdb3 OPEN; ALTER SESSION SET CONTAINER = pdb3; -- Check the status of the tablespaces. SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name LIKE 'TS%' ORDER BY 1; TABLESPACE_NAME STATUS ------------------------------ --------- TS1 OFFLINE TS2 OFFLINE TS3 OFFLINE SQL> -- Check the status of the datafiles. SET LINESIZE 110 COLUMN file_name FORMAT A90 SELECT file_name, status FROM dba_data_files WHERE tablespace_name LIKE 'TS%' ORDER BY 1; no rows selected SQL>
As expected, all three tablespaces are marked as offline, and none of them have datafiles.
The Extended USER_TABLESPACES Clause in 12.2.0.1 Onward
In Oracle 12.2 the USER_TABLESPACES
clause was extended to allow you to specify how those tablespaces are processed during clone and plugin operations. These are independent of how the rest of the clone or plugin operation is processed. It is just relevant to the affected tablespaces.
USER_TABLESPACES = { ( 'tablespace' [, 'tablespace' ]... ) | ALL [ EXCEPT ( 'tablespace' [, 'tablespace' ]... ) ] | NONE } [ SNAPSHOT COPY | NO DATA | COPY | MOVE | NOCOPY ]
The new options are explained below.
For clone operations.
SNAPSHOT COPY
: Create the specified tablespaces using snapshot clones.NO DATA
: Create the specified tablespaces, but don't bring the data.
For plugin operations.
COPY
: Copy the datafiles to the new location.MOVE
: Move the datafiles to the new location.NOCOPY
: Don't copy or move the datafiles to the new location.
The COPY
, MOVE
and NOCOPY
plugin processing is independent of that specified for the PDB plugin operation. These might be useful when splitting a multi-application non-CDB instance into multiple separate PDBs.
Imagine we had a non-CDB instance containing three user tablespaces, each supporting a separate application. We might convert this to three separate PDBs by first describing the non-CDB instance as an XML file.
export ORACLE_SID=orcl sqlplus / as sysdba <<EOF SHUTDOWN IMMEDIATE; STARTUP OPEN READ ONLY; BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/tmp/orcl.xml'); END; / SHUTDOWN IMMEDIATE; EXIT; EOFWe can now connect to a container database and create three new PDBs based on the non-CDB instance with the following plugin operations. Notice in each case the plugin operation specifies
COPY
at the PDB level, and MOVE
at the user tablespaces level.
-- Application 1 CONN / AS SYSDBA CREATE PLUGGABLE DATABASE app1_pdb USING '/tmp/orcl.xml' COPY USER_TABLESPACES=('ts1') MOVE; ALTER SESSION SET CONTAINER=app1_pdb; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ALTER PLUGGABLE DATABASE app1_pdb OPEN; -- Application 2 CONN / AS SYSDBA CREATE PLUGGABLE DATABASE app2_pdb USING '/tmp/orcl.xml' COPY USER_TABLESPACES=('ts2') MOVE; ALTER SESSION SET CONTAINER=app2_pdb; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ALTER PLUGGABLE DATABASE app2_pdb OPEN; -- Application 3 CONN / AS SYSDBA CREATE PLUGGABLE DATABASE app3_pdb USING '/tmp/orcl.xml' COPY USER_TABLESPACES=('ts3') MOVE; ALTER SESSION SET CONTAINER=app3_pdb; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ALTER PLUGGABLE DATABASE app3_pdb OPEN;
What is happening here on a per-PDB basis?
- The datafiles to support the main tablespaces (SYSTEM, SYSAUX, TEMP) are copied from the non-CDB location into the new PDB location to create the equivalent tablespaces in the new PDB.
- The datafiles to support the specified user tablespace are moved into place, which means they are no longer in the original location. This is fine as each PDB will require separate datafiles anyway.
- Once the new PDB is created we run the "noncdb_to_pdb.sql" script to clean it the data dictionary of the PDB.
- We open the PDB.
Once the plugins were complete, we would go through each PDB, cleaning out the unwanted tablespace definitions and schema definitions etc.
For more information see:
- CREATE PLUGGABLE DATABASE
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...