8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Related articles.

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.

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.

For plugin operations.

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;
EOF
We 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?

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:

Hope this helps. Regards Tim...

Back to the Top.