Skip to content

Manage Tablespaces in CDB and PDB

    Manage Tablespaces in a CDB

    Management of tablespaces in a container database (CDB) is no different to that of a non-CDB database. Provided you are logged in as a privileged user and pointing to the root container, the usual commands are all available.

    CONN / AS SYSDBA
    
    SQL> SHOW CON_NAME
    
    CON_NAME
    ------------------------------
    CDB$ROOT
    SQL> 
    
    CREATE TABLESPACE dummy
      DATAFILE '/u01/app/oracle/oradata/cdb1/dummy01.dbf' SIZE 1M
      AUTOEXTEND ON NEXT 1M;
      
    Tablespace created.
    
    SQL>
    
    ALTER TABLESPACE dummy ADD
      DATAFILE '/u01/app/oracle/oradata/cdb1/dummy02.dbf' SIZE 1M
      AUTOEXTEND ON NEXT 1M;
     
    Tablespace altered.
    
    SQL> 
    
    DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES;
    
    Tablespace dropped.
    
    SQL>
    Manage Tablespaces in a PDB

    The same tablespace management commands are available from a pluggable database (PDB), provided you are pointing to the correct container. You can connect using a common user then switch to the correct container.

    SQL> CONN / AS SYSDBA
    Connected.
    SQL> ALTER SESSION SET CONTAINER = pdb1;
    
    Session altered.
    
    SQL> SHOW CON_NAME
    
    CON_NAME
    ------------------------------
    PDB1
    SQL>

    Alternatively, connect directly to the PDB as a local user with sufficient privilege.

    SQL> CONN [email protected]
    Enter password: 
    Connected.
    SQL> SHOW CON_NAME
    
    CON_NAME
    ------------------------------
    PDB1
    SQL>

    Once pointed to the correct container, tablespaces can be managed using the same commands you have always used. Make sure you put the datafiles in a suitable location for the PDB.

    CREATE TABLESPACE dummy
      DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy01.dbf' SIZE 1M
      AUTOEXTEND ON NEXT 1M;
      
    Tablespace created.
    
    SQL>
    
    ALTER TABLESPACE dummy ADD
      DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy02.dbf' SIZE 1M
      AUTOEXTEND ON NEXT 1M;
     
    Tablespace altered.
    
    SQL> 
    
    DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES;
    
    Tablespace dropped.
    
    SQL>
    Undo Tablespaces

    Management of the undo tablespace in a CDB is unchanged from that of a non-CDB database.

    In contrast, a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible.

    CONN [email protected]
    
    SELECT tablespace_name FROM dba_tablespaces;
    
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    SYSAUX
    TEMP
    USERS
    
    SQL>

    But we can see the datafile associated with the CDB undo tablespace.

    SELECT name FROM v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/cdb1/undotbs01.dbf
    /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
    /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
    /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
    
    SQL>
    
    SELECT name FROM v$tempfile;
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/cdb1/pdb1/temp01.dbf
    
    SQL>
    Temporary Tablespaces

    Management of the temporary tablespace in a CDB is unchanged from that of a non-CDB database.

    A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD.

    CONN [email protected]
    
    CREATE TEMPORARY TABLESPACE temp2
      TEMPFILE '/u01/app/oracle/oradata/cdb1/pdb1/temp02.dbf' SIZE 5M
      AUTOEXTEND ON NEXT 1M;
      
    Tablespace created.
    
    SQL>
    
    DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
    
    Tablespace dropped.
    
    SQL>
    Default Tablespaces

    Setting the default tablespace and default temporary tablespace for a CDB is unchanged compared to a non-CDB database.

    There are a two ways to set the default tablespace and default temporary tablespace for a PDB. The ALTER PLUGGABLE DATABASE command is the recommended way.

    CONN [email protected]
    ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users;
    ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;

    For backwards compatibility, it is also possible to use the ALTER DATABASE command.

    CONN [email protected]
    ALTER DATABASE DEFAULT TABLESPACE users;
    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;