Instance vs Database

• Instance: the software running in memory (the memory structures (SGA, PGA) and background processes).
• Database: the physical files on disk (data files, control files, redo logs).

Multitenant Architecture: CDB and PDBs

The database is organized as a container database hosting one or more pluggable databases.

• CDB (Container Database): the physical host-level database that manages system resources and metadata (in XE it is called XE).
• PDB (Pluggable Database): a self-contained database (schemas and data) that runs inside a CDB (in XE the default is XEPDB1).
• CDB$ROOT: the root container of the CDB; holds system-level metadata and common users that span all hosted databases. It's used for admin operations only; never create application objects here.
• PDB$SEED: a read-only template used to create new PDBs.

A session is always positioned in exactly one container. Admins can move between them:

-- show where you are
SHOW CON_NAME;

-- switch between CDB root and the PDB
ALTER SESSION SET CONTAINER = XEPDB1;
ALTER SESSION SET CONTAINER = CDB$ROOT;

Database, Schema, User

• Database: the physical storage, files, and instance. In Oracle this is the CDB (XE) or a PDB (XEPDB1).
• User: an account you log in with, defined by username and password. Examples: MTITEK, SYS, C##ADMIN.
• Schema: a logical namespace that owns database objects (tables, views, sequences). Oracle automatically creates a schema with the same name when a user is created.

In Oracle, user and schema are essentially the same thing. This differs from databases like PostgreSQL, where users and schemas are independent objects.
If you create a user called MTITEK, Oracle also creates a schema called MTITEK.
MTITEK.MY_TABLE_1 means the table MY_TABLE_1 owned by the schema MTITEK.

User Types

• Common user: prefixed with C## (e.g., C##ADMIN), exists across all containers (CDB root and all PDBs). When granting privileges to a common user, CONTAINER=ALL applies the grant across the CDB and all PDBs.
• Local user: exists only in a specific PDB (e.g., MTITEK in XEPDB1). This is the choice for application accounts.
• SYS: built-in super admin, owns the data dictionary (connects with the SYSDBA role).
• SYSTEM: built-in admin user for day-to-day administrative tasks (no SYSDBA needed).

-- common user: exists across all containers
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE USER C##ADMIN IDENTIFIED BY CADMINPWD CONTAINER = ALL;
GRANT DBA TO C##ADMIN CONTAINER = ALL;

-- local user: exists only in the current PDB
ALTER SESSION SET CONTAINER = XEPDB1;
CREATE USER MTITEK IDENTIFIED BY MTITEKPWD;
GRANT CONNECT, RESOURCE TO MTITEK;

Roles

Connection roles are selected at connect time (the "Role" field in SQL Developer or the AS SYSDBA clause in sqlplus).
They allow you to connect to a database instance (even when it's idle/closed) and perform system operations.
They are not stored in the database's data dictionary; instead, they are controlled outside the database (via OS groups or password files).

Database roles are standard database-level permissions that are granted to users and live inside the database.
They are predefined, groupable sets of permissions created to manage the everyday tasks of developers, application owners, and database administrators (schema management and security).
They are listed in the DBA_ROLES view and can be granted to users.

Connection Roles

• SYSDBA: highest privilege role (equivalent to root or Administrator); required for startup/shutdown and admin tasks.
• SYSOPER: limited admin; can start/stop the database but cannot see user data.
• SYSBACKUP: backup and recovery operations.
• SYSDG: Data Guard operations.
• DEFAULT: normal connection with no administrative privileges; used for application users connecting to their default schema.

Database Roles

• DBA: full administration privileges within a database (e.g., creating users, altering schemas, granting privileges).
• CONNECT: basic session creation (grants CREATE SESSION).
• RESOURCE: create tables, sequences, and procedures. The typical pairing CONNECT, RESOURCE is enough for most application schemas.
• SELECT_CATALOG_ROLE: read access to data dictionary views.
• EXECUTE_CATALOG_ROLE: execute access to dictionary packages.

Connecting: Services, SID, and the Listener

Services

A service is a named access point to a database, used in connection strings. Oracle XE 21c exposes these services out of the box:

• XE: the CDB root, used for SYSDBA/admin connections.
• XEPDB1: the default PDB, used for application connections.
• XECDB: internal CDB service.
• SYS$BACKGROUND and SYS$USERS: internal services for background processes and user sessions.

For a local XE installation you only need these two services: XE for admin work and XEPDB1 for application work.

SID vs Service Name

• SID (System Identifier): uniquely identifies a specific database instance on a server. In XE the SID is XE.
• Service Name: a logical alias that points to one or more instances. It is the recommended way to connect; multiple service names can point to the same instance.

A SID connects directly to the instance with no flexibility.
A service name supports load balancing and PDBs.
PDB connections (e.g., XEPDB1) require a service name. Oracle does not register a SID for individual PDBs — only the CDB has a SID — so PDBs cannot be reached using SID-style connection syntax.

host:port:SID
jdbc:oracle:thin:@localhost:1521:XE

host:port/service
jdbc:oracle:thin:@//localhost:1521/XEPDB1

Listener and TNS

• Listener: a network process that listens for incoming connection requests (port 1521 by default) and routes them to the correct instance or service. Configured in listener.ora.
• TNS (Transparent Network Substrate): Oracle's network communication layer. tnsnames.ora maps a connection alias to a host, port, and service name, so clients can connect using a simple name instead of full connection details.

Connection Examples

username=SYS
role=SYSDBA
service=XE

username=MTITEK
role=DEFAULT
service=XEPDB1

username=C##ADMIN
role=DEFAULT
service=XEPDB1

SYSDBA  + XE      = CDB root, full admin access across all PDBs
SYSDBA  + XEPDB1  = directly into the PDB as admin
DEFAULT + XEPDB1  = normal application user connection

Physical Storage: Tablespaces

• Tablespace: a physical storage container (a logical layer over one or more data files) where data is stored on disk. Examples: SYSTEM, USERS, UNDOTBS1, TEMP.
• User/Schema: a logical owner of objects and a login account. It does not store data itself.

Analogy: the tablespace is the physical filing cabinet (where data lives on disk); the schema is the labeled folder inside the cabinet (who owns which documents).

• A user is assigned a default tablespace (e.g. USERS) where its objects are physically stored, with disk usage limited via QUOTA.
• A tablespace can hold objects from many different users/schemas, and a schema can spread its objects across multiple tablespaces.

ALTER SESSION SET CONTAINER = XEPDB1;
CREATE TABLESPACE MTITEK_DATA_TS DATAFILE '/opt/oracle/oradata/XE/XEPDB1/mtitek_data_ts_1.dbf' SIZE 500M AUTOEXTEND ON;

CREATE USER MTITEK_APP IDENTIFIED BY MTITEKPWD DEFAULT TABLESPACE MTITEK_DATA_TS QUOTA UNLIMITED ON MTITEK_DATA_TS;

GRANT CONNECT, RESOURCE TO MTITEK_APP;

MTITEK_APP is the schema/user.
MTITEK_DATA_TS is the tablespace that physically stores its data.

Built-in Tablespace Types

• SYSTEM / SYSAUX: Oracle's internal metadata and data dictionary. Never store user data here.
• UNDO: before-images of changed data, used for rollback and read consistency.
• TEMP: temporary data for sorting and joins; never needs backup.

Oracle Files for Data Integrity

• Data Files: store the actual table and index data on disk.
• Redo Log Files: record all real-time changes made to the database before they are written to data files. Used for crash recovery. Organized in groups and written in a circular manner.
• Archive Log Files: copies of redo logs saved before they are overwritten, when ARCHIVELOG mode is enabled. Required for point-in-time recovery and for Change Data Capture tools like LogMiner.
• Control File: small binary file storing database structure metadata (file locations, names, checkpoints, log sequence). Required to mount the database.
• Undo Tablespace / Undo Files: store the "before image" of data being changed; used for rollback, read consistency, and flashback queries.
• Temp Files: used for temporary operations like sorting and joins; do not need backup.
• Parameter File (SPFILE/PFILE): instance configuration settings (memory, paths, etc.), read at startup. SPFILE is binary and server-managed; PFILE is a plain-text fallback.
• Password File: credentials for SYSDBA/SYSOPER authentication outside the database (needed when the database is down).
• Flashback Logs: before-images used by FLASHBACK DATABASE to rewind the entire database to a previous point in time.