Oracle Interview Questions

A curated set of conceptual Oracle interview questions — the "explain the concept" kind you'll actually be asked in a real screen or on-site, as distinct from the hands-on puzzle queries covered on the tricky-queries page in this track.

Q: What genuinely distinguishes Oracle Database from other relational databases like MySQL or PostgreSQL?#

Beyond being a commercial, licensed product rather than open source, the biggest structural difference is PL/SQL: a full procedural language that runs inside the database engine itself, tightly integrated with SQL (a PL/SQL function can be called directly inside a SELECT, for instance). Oracle also has a distinctive concurrency model built on undo-based read consistency (covered below), Oracle-specific tooling like CONNECT BY hierarchical queries and analytic functions that predate the equivalent standard-SQL features in other databases, and a long track record in large-scale enterprise deployments (banking, telecom, ERP) that shaped decades of feature development toward reliability and tooling at very large scale rather than developer convenience for small projects.

Q: How does PL/SQL compare to stored procedures in other databases, like T-SQL in SQL Server or PL/pgSQL in PostgreSQL?#

All three solve the same underlying problem — procedural logic (loops, conditionals, exception handling) that runs inside the database rather than in application code — and the core concepts (variables, cursors, exception handlers) map fairly directly between them. The differences are mostly syntactic and ecosystem-specific: PL/SQL's block structure (DECLARE/BEGIN/EXCEPTION/END), its package concept for grouping related procedures (T-SQL has no exact equivalent; PostgreSQL added something comparable more recently with schemas and extensions), and its particularly deep integration with SQL data types via %TYPE/%ROWTYPE. Someone comfortable writing stored procedures in one of these three generally picks up either of the others quickly — the concepts transfer even where the exact keywords don't.

Q: What is a materialized view for, and when would you reach for one instead of a regular view?#

A regular view is just a stored query — it adds no performance benefit on its own, since it re-runs the underlying SELECT in full every time it's queried. A materialized view physically stores its result set and refreshes it on a schedule (on demand, on a timer, or on commit), trading some data freshness for avoiding the cost of re-computing an expensive aggregate or join on every single query. It's the right tool specifically when a query is expensive (a large aggregation, a multi-table join over millions of rows) and is read far more often than the underlying data actually changes — a reporting dashboard is the textbook case: querying a precomputed summary table is dramatically cheaper than re-aggregating raw transactional data on every page load.

Q: What's the difference between ROWID and a primary key?#

ROWID is Oracle's internal physical address for a row — which data file, block, and position within that block currently holds it — and every table has one implicitly, with no need to declare it. A primary key is a logical, business-defined unique identifier chosen and enforced by a constraint. The key practical difference: ROWID can change for reasons that have nothing to do with the data itself — a partition move, certain table reorganizations, an export/import cycle — while a primary key value is stable for as long as the row exists, precisely because it's meant as a durable identifier and ROWID isn't. ROWID is genuinely useful as a short-lived handle within a single operation (re-locating a row you just fetched, or picking exactly one survivor among duplicate rows), but it should never be stored elsewhere as if it were a foreign key.

Q: How do Oracle sequences differ from an auto-increment column in MySQL or a SERIAL column in PostgreSQL?#

An auto-increment/SERIAL column is tied directly to one specific table column and generates its next value automatically on insert. An Oracle sequence is a completely independent schema object, generating unique numbers with .NEXTVAL, that isn't tied to any particular table or column at all — the same sequence could, in principle, be shared across several tables, though most schemas dedicate one sequence per table by convention. Historically this meant populating a key from a sequence required an explicit INSERT ... VALUES (seq.NEXTVAL, ...) or a BEFORE INSERT trigger; since Oracle 12c, GENERATED ... AS IDENTITY wraps a sequence automatically and behaves, from the application's point of view, like the auto-increment/SERIAL columns other databases have always had. Both approaches accept gaps in the generated values as normal (from rollbacks, caching, or restarts) rather than treating gap-free numbering as a guarantee.

Q: Explain Oracle's read consistency model — how does it let readers avoid blocking writers?#

Oracle guarantees every query a consistent snapshot of the data as of when it started, without ever taking a read lock to get it. It does this with undo: before a row is modified, its prior value is written to an undo segment, and any query that needs an older version of that row (because it started before the modifying transaction committed) reconstructs it on the fly from undo rather than reading the live row. The practical result is that in Oracle, readers never block writers and writers never block readers — the only lock contention that exists is between two writers touching the same row. This is a meaningfully stronger default than a database relying on shared read locks, where a reader can be made to wait behind an in-progress writer holding a lock on the same rows.

Q: What are the trade-offs of adding an index, and how do you decide what to index?#

An index speeds up reads that filter, join, or sort on the indexed column(s) — often turning a full table scan into a targeted lookup. That benefit isn't free: every index has to be updated on every INSERT, UPDATE, or DELETE that touches its columns, and it consumes storage. Good candidates are primary/foreign keys, columns used in frequent WHERE clauses, and join/sort columns; a table with many indexes on rarely-queried columns pays that write-side cost on every single write for no real read-side benefit. Oracle also offers a genuine choice between index types for different situations — a B-tree for typical high-cardinality columns, versus a bitmap index for low-cardinality columns in read-heavy analytic workloads, where a B-tree would barely help but a bitmap index combines efficiently with other bitmap indexes at the bit level.

Q: What's the advantage of grouping procedures into a package rather than leaving them as standalone objects?#

A package bundles related procedures, functions, and shared state into one named, versioned unit — with a public specification (what callers can see) separate from a body that can also hold private helper logic invisible outside the package entirely. Beyond the organizational win, packages let related logic be deployed and changed together, keep implementation details genuinely encapsulated rather than merely convention-hidden, and are loaded by Oracle as a single compiled unit, which in practice tends to reduce parsing/loading overhead compared to many individually-managed standalone procedures. In any Oracle schema with more than a handful of related procedures, a package is the conventional structure — a lone standalone procedure is more the exception, typically reserved for something genuinely self-contained with nothing else it logically belongs alongside.