Oracle Database / Basic: relational and transaction foundations / Lesson 1

Instances, databases, services and session context

Locate an application connection inside Oracle architecture before interpreting names, privileges or failures.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish an instance, database, container and service.
Read session identity without assuming administrative access.

Start with the two different lifetimes

An Oracle database consists of persistent structures such as data files, control files and online redo logs. An instance is the memory and background processes that manage access to those structures. Stopping an instance does not mean deleting the database. Conversely, an available listener is not proof that a requested database service is ready. In the single-instance mental model one instance opens a database; Oracle RAC changes that relationship by allowing multiple instances to access one database. RAC is an architectural and licensing choice, not a synonym for every Oracle installation.

A service selects a destination

In a multitenant deployment, a container database has a root and pluggable databases. Application schemas normally belong in the intended PDB, not the root. A service name is a logical connection target that can identify an application workload and route it to the right container. A SID identifies an instance and should not be substituted blindly for a service name in a connection string. Record the supplied service, database edition and server release as separate facts. A client version alone cannot establish the server capabilities that accepted a connection.

Inspect a session fingerprint

SYS_CONTEXT exposes useful USERENV attributes without inventing an administrator account. SESSION_USER records the authenticated database user. CURRENT_SCHEMA controls unqualified object resolution and can differ from that user; changing it does not confer ownership or privileges. CON_NAME identifies the current container in a multitenant session. The example deliberately returns context-dependent values rather than a fabricated deployment result. Capture these fields from an already authorized diagnostic session when comparing two apparently identical reports. Redact infrastructure identifiers before moving evidence into a public support request.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema,
       SYS_CONTEXT('USERENV','CON_NAME') AS container_name
FROM dual;

One context row is expected; names depend on the authorized session. DUAL supplies one row for expression evaluation.

Resolve ownership explicitly

In Oracle a schema is the collection of objects owned by a database user, rather than a separately created PostgreSQL-style namespace. A schema-qualified reference such as LAB_OWNER.SHIPMENTS states ownership. A synonym is another name for an object and does not itself grant access. Unquoted identifiers are normally stored and resolved in uppercase; quoted mixed-case identifiers require matching quotes and case. This explains why an object visible in a graphical browser can still fail under copied SQL that changed quoting, owner or container. Prefer consistent uncomplicated names for training fixtures.

Use the appropriate dictionary scope

USER_TABLES describes tables owned by the current user. ALL_TABLES describes accessible tables, while DBA views normally require additional authorization. These prefixes express scope, not three independent physical copies of business rows. The inventory query is read-only and returns zero rows legitimately when no table matches. Do not respond by granting broad catalog access or creating a duplicate production object. First compare identity, owner and container. The course uses synthetic freight records and all Oracle fragments remain non-runnable because no database engine is connected or tested.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT owner, table_name
FROM all_tables
WHERE table_name = 'SHIPMENTS'
ORDER BY owner;

Returns accessible SHIPMENTS tables only; absence can mean another container, ownership or missing privileges.

Common errors and fixes

ORA-00942

Why it happens: The object is absent from the session context or inaccessible to this principal.
How to fix it: Compare owner, container and direct access requirements before changing SQL.
Prevent it next time: Document service and owner together in application configuration reviews.

ORA-12514

Why it happens: The listener does not currently know the requested service.
How to fix it: Have the endpoint owner verify the intended registered service.
Prevent it next time: Preserve the exact service name instead of guessing SID replacements.

A synonym seems to bypass permissions

Why it happens: A name alias was mistaken for an authorization grant.
How to fix it: Review the underlying object and the authorized privilege path.
Prevent it next time: Treat naming and access control as independent design decisions.

Check your understanding

1. A client connects successfully but sees no expected tables. Which context facts should be compared first?

Show worked solution

Compare service, container, session user, current schema and intended owner. Then inspect accessible dictionary rows. Successful authentication establishes a session, not the correct application destination or permission to every object. Do not recreate tables before establishing that the target is correct.

2. Does changing CURRENT_SCHEMA make a report run with its owner’s privileges?