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

Multitenant : Default Tablespace Clause During PDB Creation in Oracle Database 12c Release 2 (12.2)

This article describes the change in behaviour of the DEFAULT TABLESPACE clause of the CREATE PLUGGABLE DATABASE command between Oracle database 12.1 and 12.2.

Default Tablespace Clause in 12.1
Default Tablespace Clause in 12.2

Default Tablespace Clause in 12.1

In both Oracle database 12.1 and 12.2 the DEFAULT TABLESPACE clause of the CREATE PLUGGABLE DATABASE command can be used to create a new default tablespace for a pluggable database created from the seed.

The following example gives both the Oracle Managed Files (OMF) and non-OMF syntax. All further examples will assume you are using OMF. You can add the appropriate FILE_NAME_CONVERT or PDB_FILE_NAME_CONVERT settings if you need them.

CONN / AS SYSDBA

-- Oracle Managed Files (OMF) syntax.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

-- Non-OMF syntax.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/')
  DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/cdb1/pdb2/users01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;


ALTER PLUGGABLE DATABASE pdb2 OPEN;

Once the PDB is created you can see the presence of the extra tablespace and the database default tablespace setting in the PDB.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb2;

SELECT tablespace_name
FROM   dba_tablespaces
ORDER BY 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
USERS

SQL>


SELECT property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_PERMANENT_TABLESPACE'; 

PROPERTY_VALUE
----------------------------------------------------------------------------------------------------
USERS

SQL>

The DEFAULT TABELSPACE clause can't be used when creating a pluggable database from a user-defined PDB. In the examples below we attempt to use it both to specify a new default tablespace and reference an existing tablespace. Both result in an error.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE users2 DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

  DEFAULT TABLESPACE users2 DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M
  *
ERROR at line 2:
ORA-00922: missing or invalid option

SQL> 


CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE users;

  DEFAULT TABLESPACE users
  *
ERROR at line 2:
ORA-00922: missing or invalid option

SQL>

Default Tablespace Clause in 12.2

In Oracle database 12.2 the DEFAULT TABLESPACE clause can be used regardless of the source of the clone. If the source is the seed PDB, the clause is used to create a new default tablespace, as it was in Oracle 12.1. If the source is a user-defined PDB, the clause specifies which existing tablespace in the new PDB should be set as the default tablespace.

To see this in action, create a new pluggable database from the seed as we did before.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

ALTER PLUGGABLE DATABASE pdb2 OPEN;

The USERS tablespace will be the default tablespace in this PDB and any others cloned from it, so let's try something different. Create a new tablespace in the PDB, but don't set it as the database default tablespace.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb2;

CREATE TABLESPACE test_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

Create a new PDB as a clone of this user-defined PDB, but tell it to use the TEST_TS tablespace as the database default tablespace.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE test_ts;

ALTER PLUGGABLE DATABASE pdb3 OPEN;

We can see the tablespaces in the new PDB are the same as the source, but it's now using the TEST_TS tablespace, rather than the USERS tablespace, as the database default tablespace.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb3;

SELECT tablespace_name
FROM   dba_tablespaces
ORDER BY 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
TEST_TS
UNDOTBS1
USERS

SQL>


SELECT property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_PERMANENT_TABLESPACE'; 

PROPERTY_VALUE
----------------------------------------------------------------------------------------------------
TEST_TS

SQL>

Attempting to create a new tablespace, rather than reference an existing tablespace, during the creation process still results in an error.

CONN / AS SYSDBA
-- Clean up.
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE another_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

  DEFAULT TABLESPACE another_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M
                                *
ERROR at line 2:
ORA-00922: missing or invalid option
SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.