Create an Oracle database the current way: build a container database with DBCA, add pluggable databases with CREATE PLUGGABLE DATABASE, then verify, connect and drop them.
In current Oracle releases, "creating a database" almost always means creating a pluggable database (PDB) inside an existing container database (CDB). Connected to the CDB root as a privileged user, this is the whole job:
CREATE PLUGGABLE DATABASE salespdb
ADMIN USER sales_admin IDENTIFIED BY "Str0ng#Passw0rd";
ALTER PLUGGABLE DATABASE salespdb OPEN;
ALTER PLUGGABLE DATABASE salespdb SAVE STATE;
If you do not have a CDB yet, you create one first with Database Configuration Assistant (DBCA). Both steps are covered below, along with the errors people usually hit.
Decide what you actually need
Oracle uses the word "database" differently from MySQL, PostgreSQL or SQL Server.
You want
In Oracle you create
A place for one application's tables, like CREATE DATABASE app in MySQL
A user (schema) inside an existing PDB
An isolated database with its own users, tablespaces and service name
A PDB
A new Oracle instance with its own memory, redo logs and background processes
A CDB, using DBCA
If a PDB already exists and you only need somewhere to put tables, create a user and stop there:
ALTER SESSION SET CONTAINER = salespdb;
CREATEUSER app_owner IDENTIFIED BY "An0ther#Passw0rd"
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANTCREATE SESSION, CREATE TABLE, CREATEVIEW, SEQUENCE, app_owner;
Your long query needed undo that was already overwritten. How Oracle read consistency uses undo, how to read V$UNDOSTAT, and why committing inside a loop causes it.
CDB$ROOT, which holds Oracle's own metadata and common users such as SYS.
PDB$SEED, a read-only template used to create new PDBs.
Zero or more user-created PDBs. Each looks like a complete, separate database to the applications that connect to it.
The older non-CDB architecture was deprecated in 12c and is desupported from Oracle Database 21c onward, so every new database on a current release is a CDB. You do not need the Multitenant licence for small setups: without it you may have up to three user-created PDBs per CDB. Oracle AI Database Free also includes PDBs, within its resource limits.
Create a container database with DBCA
DBCA is installed with the database software. Start it from the Oracle home as the software owner (oracle on Linux, or from the Start menu's Oracle folder on Windows):
$ORACLE_HOME/bin/dbca
The wizard steps that matter:
Database Operation: choose Create a database.
Creation Mode: Typical configuration asks for a global database name, storage location, character set, an administrative password and a PDB name on one screen. Advanced configuration exposes every setting.
Global database name and SID: for example orcl. The SID identifies the instance on this host.
Create as Container database: leave selected, and name the first PDB (for example salespdb).
Character set: keep the default AL32UTF8 unless you have a specific reason not to. Changing it later is difficult.
Passwords: set passwords for SYS, SYSTEM and the PDB admin user.
Summary: review, then Finish. Creation takes several minutes.
Earlier releases offered to configure Enterprise Manager Database Express on the management options page. EM Express is not part of current releases; use SQL Developer, Oracle Enterprise Manager or the cloud console instead.
The same thing without the GUI
On servers, run DBCA in silent mode. This creates a CDB named orcl with one PDB:
Passwords on a command line end up in shell history. For anything beyond a test machine, omit the password flags so DBCA prompts for them, or use a response file with restricted permissions. Run dbca -help to see the options your release accepts.
A hand-written CREATE DATABASE statement still exists, but it requires you to build the parameter file and run the catalog scripts yourself. DBCA does all of that correctly; use it.
Create a pluggable database with SQL
Connect to the root container as a user with the CREATE PLUGGABLE DATABASE privilege (SYS or a common user you have granted it to):
CONNECT/AS SYSDBA
SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
From the seed
CREATE PLUGGABLE DATABASE hrpdb
ADMIN USER hr_admin IDENTIFIED BY "Str0ng#Passw0rd"
DEFAULT TABLESPACE users
DATAFILE SIZE 100M AUTOEXTEND ON;
This works as written when Oracle Managed Files is on, meaning DB_CREATE_FILE_DEST is set. Check with SHOW PARAMETER db_create_file_dest. If it is empty, tell Oracle where to put the copied seed files:
CREATE PLUGGABLE DATABASE hrpdb
ADMIN USER hr_admin IDENTIFIED BY "Str0ng#Passw0rd"
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/pdbseed/',
'/u01/app/oracle/oradata/ORCL/hrpdb/');
Find the seed's real path first with SELECT name FROM v$datafile WHERE con_id = 2;.
By cloning an existing PDB
CREATE PLUGGABLE DATABASE hrpdb_test FROM hrpdb;
On current releases the source can stay open read/write during the clone, provided the CDB runs in ARCHIVELOG mode with local undo. Local undo is the DBCA default; ARCHIVELOG mode is not, so check with ARCHIVE LOG LIST. Otherwise open the source PDB read-only first.
Open it and keep it open
A new PDB is created in MOUNTED state. Open it, then save that state so it reopens automatically after the instance restarts:
ALTER PLUGGABLE DATABASE hrpdb OPEN;
ALTER PLUGGABLE DATABASE hrpdb SAVE STATE;
Forgetting SAVE STATE is the usual reason an application cannot connect after a server reboot.
Verify and connect
SELECT name, open_mode, restricted FROM v$pdbs ORDERBY con_id;
NAME OPEN_MODE RESTRICTED
---------- ---------- ----------
PDB$SEED READ ONLY NO
SALESPDB READ WRITE NO
HRPDB READ WRITE NO
Each PDB registers a service of the same name with the listener. Applications connect to that service, never to the root:
sqlplus hr_admin@//dbhost:1521/hrpdb
Confirm the listener knows about it with lsnrctl services. The admin user you named has the PDB_DBA role, which carries few privileges by default; grant it what it needs (or DBA) from inside the PDB.
Undo: drop a PDB
ALTER PLUGGABLE DATABASE hrpdb_test CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE hrpdb_test INCLUDING DATAFILES;
This is immediate and permanent. There is no recycle bin for a PDB; recovery means restoring from an RMAN backup. To remove an entire CDB, use dbca -silent -deleteDatabase -sourceDB orcl.
Common errors
Error
Cause
Fix
ORA-65016: FILE_NAME_CONVERT must be specified
No OMF destination and no file name conversion given
Set DB_CREATE_FILE_DEST, or add FILE_NAME_CONVERT
ORA-65010: maximum number of pluggable databases created
PDB limit for your edition or MAX_PDBS reached
Drop an unused PDB, or check licensing before raising MAX_PDBS
ORA-65096 when creating a user
You ran CREATE USER in CDB$ROOT, where user names must start with C##
ALTER SESSION SET CONTAINER = <pdb>; then create the user
ORA-01109: database not open
The PDB is still MOUNTED
ALTER PLUGGABLE DATABASE <pdb> OPEN;
ORA-12514: listener does not currently know of service
PDB closed, or the service name is misspelled
Open the PDB; check lsnrctl services
ORA-01031: insufficient privileges
Not connected as a common user with CREATE PLUGGABLE DATABASE, or not in the root
Connect to CDB$ROOT as SYSDBA or a suitably granted C## user
One more trap: ALTER SYSTEM and CREATE TABLESPACE run in whichever container your session is in. Run SHOW CON_NAME before any DDL so you do not create application objects in the root.
Create, find, drop, disable and enable primary keys in Oracle, including composite keys, identity columns, the index behind the constraint and the ORA errors you will hit.
