Oracle Database basics: SQL*Plus, CDB vs PDB, services and creating your first user

Since Oracle 21c, every database is multitenant: one container database (CDB) holding one or more pluggable databases (PDBs). Applications live in PDBs. Connecting to the wrong container is the first thing that confuses newcomers.

#CDB and PDBs

CDB (e.g. ORCL)
├── CDB$ROOT        Oracle's own metadata, common users (C##...)
├── PDB$SEED        Template for new PDBs, read-only
└── SALESPDB        Your application schemas

#Connect

On the database server as the oracle OS user:

. oraenv <<< ORCL
sqlplus / as sysdba

This lands you in CDB$ROOT. Check and switch:

SHOW CON_NAME
SHOW PDBS
ALTER SESSION SET CONTAINER = SALESPDB;

Remotely, use the service name of the PDB (Easy Connect syntax):

sqlplus app_admin@//db01.internal:1521/salespdb.example.com
sql app_admin@//db01.internal:1521/salespdb.example.com      # SQLcl

List services the listener knows:

lsnrctl services

SELECT name, pdb FROM v$services;     -- from CDB root

Tip

If you get ORA-12514: TNS:listener does not currently know of service requested, the service name is wrong or the PDB is not open. Check SHOW PDBS and lsnrctl services.

#Open PDBs and keep them open

ALTER PLUGGABLE DATABASE SALESPDB OPEN;
ALTER PLUGGABLE DATABASE SALESPDB SAVE STATE;     -- reopen automatically after restart

#Create an application user (in the PDB)

ALTER SESSION SET CONTAINER = SALESPDB;

CREATE TABLESPACE app_data DATAFILE SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 32G;
-- with OMF (db_create_file_dest set) no file name is needed

CREATE USER app_shop IDENTIFIED BY "Change-Me-Long-1"
  DEFAULT TABLESPACE app_data
  QUOTA UNLIMITED ON app_data;

GRANT CREATE SESSION TO app_shop;

That user can log in and own objects in its own schema only if you also grant creation privileges. For a schema owner used by migrations:

GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO app_owner;

For the runtime account, grant object privileges on the owner's tables instead:

GRANT SELECT, INSERT, UPDATE, DELETE ON app_owner.orders TO app_shop;

Or group them in a role:

CREATE ROLE shop_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_owner.orders TO shop_rw;
GRANT shop_rw TO app_shop;

Warning

Avoid granting DBA, RESOURCE plus UNLIMITED TABLESPACE, or ANY privileges (SELECT ANY TABLE) to application users. They are far broader than an application needs.

Oracle 23ai adds schema-level privileges, for example GRANT SELECT ANY TABLE ON SCHEMA app_owner TO app_shop;, which covers current and future tables in one schema.

#Common users vs local users

• Local users exist in one PDB. Use these for applications.
• Common users (names start with C## by default) exist in every container, created from root. Use only for DBA tooling.

#Basic health checks

SELECT instance_name, status, version_full FROM v$instance;
SELECT name, open_mode, database_role, log_mode FROM v$database;

-- tablespace usage
SELECT tablespace_name, ROUND(used_percent, 1) AS used_pct
FROM dba_tablespace_usage_metrics ORDER BY used_percent DESC;

-- sessions per user
SELECT username, status, COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username, status;

-- recent errors
SELECT originating_timestamp, message_text FROM v$diag_alert_ext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY AND message_text LIKE '%ORA-%'
ORDER BY originating_timestamp DESC FETCH FIRST 20 ROWS ONLY;

#Useful SQL*Plus settings

SET LINESIZE 200 PAGESIZE 100
COLUMN username FORMAT a20
SET TIMING ON
SET SERVEROUTPUT ON

SQLcl adds history, tab completion and INFO table_name; it is worth installing.

#Managed services differ

On Amazon RDS for Oracle there is no OS access or SYSDBA; you use the master user and the rdsadmin packages. On Autonomous Database you connect as ADMIN with a wallet or TLS, and many DBA tasks are automatic.

#Common errors

#Prevent it next time

• Connect applications through PDB services, never the CDB.
• SAVE STATE for PDBs that must open after restarts.
• Separate owner and runtime users; grant through roles.