ORA-65096: Invalid Common User or Role Name - Multitenant CDB/PDB Fix

ORA-65096: Invalid Common User or Role Name

Error Overview

Error Text: ORA-65096: invalid common user or role name

This error occurs in Oracle multitenant container database (CDB) environments when attempting to create a user or role without following the naming conventions required by the CDB architecture. In CDB$ROOT, common users must be prefixed with C## (or c##). Inside a PDB, local users must not use the C## prefix.

Critical for Oracle 23ai: Starting with Oracle Database 23ai, multitenant architecture is mandatory. Every database is a CDB, meaning all non-CDB migrations and legacy scripts that create users without the C## prefix will encounter this error when run against CDB$ROOT.

Understanding Multitenant User Types

Common vs Local Users

Multitenant User Architecture
├── Common Users (C## prefix)
│   ├── Created in CDB$ROOT
│   ├── Visible across all containers (CDB + PDBs)
│   ├── Examples: C##DBA_ADMIN, C##MONITOR
│   └── Privileges can be granted commonly or locally
├── Local Users (no C## prefix)
│   ├── Created inside a specific PDB
│   ├── Visible only within that PDB
│   ├── Examples: APP_USER, HR_ADMIN
│   └── Standard user creation syntax
└── Oracle-Supplied Users
    ├── SYS, SYSTEM, DBSNMP, etc.
    ├── Pre-existing before CDB architecture
    └── Exempt from C## naming requirement

Common Scenarios

• Legacy script migration - Scripts written for non-CDB databases running CREATE USER app_user in CDB$ROOT
• Wrong container context - Connected to CDB$ROOT when intending to create a local user in a PDB
• C## prefix in PDB - Attempting to use C## prefix when connected to a PDB (ORA-65094)
• Oracle 23ai upgrades - All databases are now CDBs, breaking any non-CDB scripts
• Automated provisioning - CI/CD or infrastructure-as-code scripts not updated for multitenant

Diagnostic Steps

1. Determine Your Current Container Context

-- Check which container you are connected to
SHOW CON_NAME;

-- Get container ID (1 = CDB$ROOT, 2 = PDB$SEED, 3+ = PDBs)
SHOW CON_ID;

-- Alternative SQL query for container context
SELECT
  SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name,
  SYS_CONTEXT('USERENV', 'CON_ID') AS container_id,
  SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name,
  SYS_CONTEXT('USERENV', 'CDB_NAME') AS cdb_name
FROM dual;

-- Check if the database is a CDB
SELECT
  name,
  cdb,
  con_id,
  open_mode
FROM v$database;

2. List Available PDBs

-- View all PDBs and their status
SELECT
  con_id,
  name,
  open_mode,
  restricted,
  total_size / 1024 / 1024 AS size_mb
FROM v$pdbs
ORDER BY con_id;

-- Detailed PDB information
SELECT
  pdb_id,
  pdb_name,
  status,
  creation_time,
  con_uid
FROM dba_pdbs
ORDER BY pdb_id;

3. Check Existing Common and Local Users

-- List all common users (created in CDB$ROOT)
SELECT
  username,
  common,
  con_id,
  created,
  authentication_type,
  account_status
FROM cdb_users
WHERE common = 'YES'
  AND oracle_maintained = 'N'
ORDER BY username;

-- List local users across all PDBs
SELECT
  username,
  common,
  con_id,
  created,
  account_status
FROM cdb_users
WHERE common = 'NO'
  AND oracle_maintained = 'N'
ORDER BY con_id, username;

-- Check if a specific user already exists
SELECT
  username,
  common,
  con_id,
  account_status
FROM cdb_users
WHERE username = UPPER('&username');

4. Verify the Multitenant Configuration

-- Confirm CDB architecture
SELECT
  name,
  cdb,
  log_mode,
  open_mode,
  database_role,
  con_id
FROM v$database;

-- Check multitenant parameter
SHOW PARAMETER enable_pluggable_database;

-- Check max PDBs allowed
SHOW PARAMETER max_pdbs;

Resolution Steps

Solution 1: Create a Common User in CDB$ROOT (with C## prefix)

Use this when you need a user that spans the entire CDB and all PDBs.

-- Connect to CDB$ROOT
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Create common user with required C## prefix
CREATE USER C##DBA_ADMIN IDENTIFIED BY secure_password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users
  CONTAINER = ALL;

-- Grant common privileges (applied across all containers)
GRANT CREATE SESSION TO C##DBA_ADMIN CONTAINER = ALL;
GRANT SELECT_CATALOG_ROLE TO C##DBA_ADMIN CONTAINER = ALL;

-- Grant DBA role commonly (use with caution)
GRANT DBA TO C##DBA_ADMIN CONTAINER = ALL;

Solution 2: Create a Local User Inside a PDB

Use this for application users, schema owners, and PDB-specific accounts. This is the most common resolution.

-- Switch to the target PDB
ALTER SESSION SET CONTAINER = MY_PDB;

-- Or connect directly to the PDB
-- CONNECT sys/password@hostname:1521/my_pdb AS SYSDBA

-- Create local user (no C## prefix needed)
CREATE USER app_user IDENTIFIED BY secure_password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- Grant standard application privileges
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE SEQUENCE TO app_user;
GRANT CREATE PROCEDURE TO app_user;

Solution 3: Update Legacy Scripts for Multitenant

Convert existing non-CDB scripts to work with the multitenant architecture.

-- BEFORE (non-CDB script - causes ORA-65096 in CDB$ROOT):
-- CREATE USER app_user IDENTIFIED BY password;

-- AFTER (multitenant-aware script):
-- Option A: Switch to PDB first, then create local user
ALTER SESSION SET CONTAINER = MY_PDB;
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- Option B: Create as common user in CDB$ROOT
-- CREATE USER C##APP_USER IDENTIFIED BY password CONTAINER = ALL;

Wrapper Script for Multitenant Compatibility

-- Reusable script that works in both CDB$ROOT and PDB contexts
SET SERVEROUTPUT ON
DECLARE
  v_con_name VARCHAR2(128);
  v_is_cdb   VARCHAR2(3);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'CON_NAME') INTO v_con_name FROM dual;
  SELECT cdb INTO v_is_cdb FROM v$database;

  IF v_is_cdb = 'YES' AND v_con_name = 'CDB$ROOT' THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: Connected to CDB$ROOT.');
    DBMS_OUTPUT.PUT_LINE('To create a local user, first run:');
    DBMS_OUTPUT.PUT_LINE('  ALTER SESSION SET CONTAINER = <your_pdb>;');
    DBMS_OUTPUT.PUT_LINE('Or prefix with C## for a common user.');
    RAISE_APPLICATION_ERROR(-20001,
      'Script must be run inside a PDB, not CDB$ROOT. Use ALTER SESSION SET CONTAINER.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Connected to PDB: ' || v_con_name || '. Proceeding...');
    -- Place your CREATE USER statements here
  END IF;
END;
/

Solution 4: Emergency Override with _ORACLE_SCRIPT (Not Recommended)

The hidden parameter _ORACLE_SCRIPT bypasses the C## naming requirement. This is intended only for Oracle internal use and should never be used in production.

-- WARNING: For emergency/testing use ONLY. Never use in production.
-- This creates a user without C## in CDB$ROOT, which violates
-- multitenant design principles and can cause upgrade issues.

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
CREATE USER legacy_user IDENTIFIED BY password;
ALTER SESSION SET "_ORACLE_SCRIPT" = FALSE;

-- This user will NOT be a proper common user and may behave
-- unpredictably across PDBs. Use at your own risk.

Do Not Use _ORACLE_SCRIPT in Production

Setting _ORACLE_SCRIPT=TRUE bypasses critical multitenant safety checks. Users created this way may cause problems during database upgrades, PDB cloning, and PDB relocation. Always use the proper C## prefix for common users or create local users inside PDBs.

Prevention Strategies

1. Always Check Container Context Before DDL

-- Add this check at the top of every DBA script
SET SERVEROUTPUT ON
DECLARE
  v_con_name VARCHAR2(128);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'CON_NAME') INTO v_con_name FROM dual;
  DBMS_OUTPUT.PUT_LINE('Current container: ' || v_con_name);

  IF v_con_name = 'CDB$ROOT' THEN
    DBMS_OUTPUT.PUT_LINE('*** WARNING: You are in CDB$ROOT ***');
    DBMS_OUTPUT.PUT_LINE('*** Switch to a PDB for local user operations ***');
  END IF;
END;
/

2. Update All Scripts for Multitenant Architecture

-- Template for multitenant-safe user creation script
-- Usage: Run while connected to the target PDB

WHENEVER SQLERROR EXIT SQL.SQLCODE
SET VERIFY OFF
SET SERVEROUTPUT ON

-- Validate we are NOT in CDB$ROOT
DECLARE
  v_con_name VARCHAR2(128);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'CON_NAME') INTO v_con_name FROM dual;
  IF v_con_name = 'CDB$ROOT' THEN
    RAISE_APPLICATION_ERROR(-20001,
      'ERROR: This script must be run inside a PDB. Current container: ' || v_con_name);
  END IF;
  DBMS_OUTPUT.PUT_LINE('Creating user in PDB: ' || v_con_name);
END;
/

-- Proceed with user creation
CREATE USER &username IDENTIFIED BY &password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO &username;

3. Use Connection Strings That Target PDBs Directly

-- Instead of connecting to the CDB:
-- sqlplus sys/password@hostname:1521/ORCL as sysdba

-- Connect directly to the PDB using its service name:
sqlplus sys/password@hostname:1521/ORCLPDB1 as sysdba

-- Or use Easy Connect with PDB service:
sqlplus sys/password@hostname/orclpdb1 as sysdba

4. Configure SQL Developer and Tools for PDB Connections

-- In tnsnames.ora, define PDB-specific entries:
ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb1)
    )
  )

5. Audit User Creation Across Containers

-- Monitor user creation events
CREATE AUDIT POLICY audit_user_creation
  ACTIONS CREATE USER
  CONTAINER = ALL;

ALTER AUDIT POLICY audit_user_creation ENABLE;

-- Review user creation audit trail
SELECT
  event_timestamp,
  dbusername,
  action_name,
  object_name,
  sql_text,
  con_name
FROM unified_audit_trail
WHERE action_name = 'CREATE USER'
ORDER BY event_timestamp DESC;

Related Errors

• ORA-65094 - Invalid local user or role name (using C## prefix inside a PDB)
• ORA-01031 - Insufficient privileges (missing CREATE USER privilege)
• ORA-65049 - Creation of common users or roles not allowed from a PDB
• ORA-65050 - Common DDL not allowed inside a PDB
• ORA-01017 - Invalid username/password (after user creation issues)
• ORA-01045 - User lacks CREATE SESSION privilege

Quick Reference

Emergency Response Steps

1. Check your current container context with SHOW CON_NAME
2. If in CDB$ROOT and creating an application user, switch to the target PDB
3. Use ALTER SESSION SET CONTAINER = pdb_name to switch containers
4. Create local users inside PDBs without the C## prefix
5. Create common users in CDB$ROOT with the C## prefix
6. Update legacy scripts to include container context checks

Quick Commands

-- Check container context
SHOW CON_NAME;
SHOW CON_ID;

-- Switch to a PDB
ALTER SESSION SET CONTAINER = my_pdb;

-- Create local user in PDB (no C## prefix)
CREATE USER app_user IDENTIFIED BY password;

-- Create common user in CDB$ROOT (C## prefix required)
CREATE USER C##ADMIN IDENTIFIED BY password CONTAINER = ALL;

-- List all PDBs
SELECT name, open_mode FROM v$pdbs;

-- Check if database is a CDB
SELECT name, cdb FROM v$database;

Key Rules

• CDB$ROOT: Users must start with C## (common users)
• PDB: Users must NOT start with C## (local users)
• Oracle 23ai: All databases are CDBs — there is no non-CDB option
• _ORACLE_SCRIPT: Emergency bypass only, never for production use
• Best practice: Connect directly to PDBs for application user management