DBAtlas · Database operations atlas

Create a new PDB from PDB$SEED

OracleMultitenant

Command

-- In CDB$ROOT, as SYSDBA:
CREATE PLUGGABLE DATABASE <pdb>
  ADMIN USER pdbadmin IDENTIFIED BY "<password>" ROLES = (DBA)
  DEFAULT TABLESPACE users
    DATAFILE '/u02/oradata/<cdb>/<pdb>/users01.dbf' SIZE 512M AUTOEXTEND ON NEXT 64M
  FILE_NAME_CONVERT = ('/u02/oradata/<cdb>/pdbseed/', '/u02/oradata/<cdb>/<pdb>/')
  STORAGE (MAXSIZE 50G);
-- With OMF/ASM, instead of FILE_NAME_CONVERT: CREATE_FILE_DEST = '+DATA'
ALTER PLUGGABLE DATABASE <pdb> OPEN;
ALTER PLUGGABLE DATABASE <pdb> SAVE STATE;
SELECT con_id, name, open_mode, restricted FROM v$pdbs ORDER BY con_id;

What it does

Creates a new, empty pluggable database from the seed template; defines the local admin user, the default tablespace and the total size limit in the same command.

When to use it

When opening an isolated database for a new application/schema; when preparing test and development environments.

Caution

The new PDB comes up MOUNTED; it needs OPEN and SAVE STATE (to stay open after a CDB restart). Wrong FILE_NAME_CONVERT paths write the files to the wrong directory; without OMF a full file path is mandatory for DATAFILE (ORA-02236). Licensing: in 19c EE, 3 user PDBs per CDB can be used without the Multitenant option.

Open in DBAtlas →

Related