Top 60 Oracle Database Interview Questions (2026)

Oracle Database is a relational database management system (RDBMS) that stores data in tables and lets you query and change it with SQL. It adds PL/SQL, a procedural language, so logic like validation and batch processing can run inside the database itself instead of in the application layer.

Teams use it for transaction-heavy, mission-critical systems: banking, telecom, ERP, and any workload that needs strong consistency, high availability, and deep tuning controls. It runs on servers, on Exadata hardware, and as a managed cloud service.

SQL is a declarative language for querying and changing data: one statement at a time, with no loops or variables. PL/SQL is Oracle's procedural extension that wraps SQL in blocks with variables, conditions, loops, cursors, and exception handling, so you can express step-by-step logic that plain SQL can't.

You use SQL to say what data you want and PL/SQL to say how to process it step by step, all running inside the database so there's no round-trip per statement.

A primary key uniquely identifies each row: its values are unique and can't be NULL, and a table has at most one. A unique key also enforces uniqueness but allows a single NULL, and a table can have several of them for different business rules.

Both create a supporting index automatically. The primary key is the row's identity; unique keys enforce business rules like 'no two employees share an email'.

Inner join returns only matching rows. Left outer join keeps all left rows plus matches. Right outer join keeps all right rows. Full outer join keeps unmatched rows from both sides. Cross join returns the Cartesian product, every left row paired with every right row.

A self join joins a table to itself, useful for hierarchies like employee-to-manager. Oracle supports both ANSI join syntax (JOIN ... ON) and its older (+) outer-join operator.

WHERE filters individual rows before grouping happens, while HAVING filters whole groups after GROUP BY has aggregated them. Because WHERE runs first, it can't reference an aggregate like COUNT or SUM, but HAVING can, since by then the groups and their aggregate values already exist.

The order of evaluation is the key: WHERE runs first, then GROUP BY, then HAVING. Putting a non-aggregate condition in HAVING still works but reads wrong and can be slower.

DELETE removes rows one at a time, is transactional (you can roll it back), fires triggers, and can have a WHERE clause. TRUNCATE removes all rows fast, is DDL so it auto-commits and can't be rolled back, and resets the table's high water mark. DROP removes the whole table, its data, and its structure.

Use DELETE for selective removal, TRUNCATE to empty a table quickly, and DROP when the table itself should be gone.

NULL means the value is unknown or missing, not zero and not an empty string. Because it's unknown, any comparison with = or != returns unknown rather than true or false, so those rows drop out of your results silently.

Test with IS NULL and IS NOT NULL. Use NVL or COALESCE to substitute a value when a column might be NULL.

DUAL is a special one-row, one-column table owned by SYS that exists so you can SELECT a computed value or function result even when there's no real table involved. Every SELECT needs a FROM clause, and DUAL fills that slot when you just want an expression evaluated once.

You use it for things like the current date, arithmetic, or a sequence's next value. Oracle optimizes it internally, so it's cheap.

A sequence is a database object that generates unique numbers on demand, typically to populate primary keys without a race between concurrent inserts. NEXTVAL returns the next number in the series and CURRVAL returns the last value your own session fetched, so each session tracks where it is independently.

Sequences are built for concurrency, so they guarantee uniqueness but not a gap-free run: rollbacks and caching leave gaps. That's a feature, not a bug, because it avoids serializing every insert.

Constraints are rules the database enforces on column values, so bad data is rejected at write time instead of leaking into the application and corrupting reports later. Oracle supports NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK, and enforcing them in the database means every application sees the same rules.

Foreign keys enforce referential integrity between tables, CHECK enforces a condition like salary greater than zero, and they can be enabled, disabled, or deferred.

GROUP BY collapses rows that share the same values in the listed columns into one row per group, so you can compute a summary for each group rather than for the whole table. Common aggregates you apply within those groups are COUNT, SUM, AVG, MIN, and MAX, giving counts and totals per category.

Every column in the SELECT list must either be in the GROUP BY or wrapped in an aggregate, otherwise Oracle raises 'not a GROUP BY expression'.

ORDER BY sorts the final result set by one or more columns, ascending by default or descending when you add DESC. It's the last clause to run, after WHERE, GROUP BY, and HAVING, which is why it can reference column aliases from the SELECT list that earlier clauses can't.

In Oracle, NULLs sort last in ascending order and first in descending order by default. Override with NULLS FIRST or NULLS LAST when you need a specific placement.

CHAR is fixed length: a CHAR(10) always stores exactly 10 characters, padding shorter values with trailing spaces. VARCHAR2 is variable length: it stores only the characters you actually put in, up to the declared maximum, so it uses less space and won't surprise you with padded spaces during comparisons.

Use VARCHAR2 for almost everything. CHAR fits only truly fixed-width codes.

A subquery is a query nested inside another. A single-row subquery returns one value for use with =, a multi-row subquery returns a list for use with IN or ANY, and a correlated subquery references the outer query and runs once per outer row.

Subqueries appear in WHERE, in the SELECT list, and in the FROM clause (an inline view). Correlated subqueries are the slow ones to watch, because they can execute row by row.

IN checks whether a value is in a list or subquery result. EXISTS checks whether a correlated subquery returns any row at all, stopping at the first match. Both can express membership, but they behave differently with NULLs and on large sets.

EXISTS often wins when the subquery is large because it short-circuits, while IN can be clearer for small, static lists. Modern Oracle optimizers frequently rewrite one into the other anyway.

COMMIT makes all changes in the current transaction permanent and visible to others. ROLLBACK undoes uncommitted changes back to the start of the transaction or to a savepoint. SAVEPOINT marks a point you can partially roll back to without discarding the whole transaction.

A transaction is the unit of work: either all its changes stick or none do. DDL like CREATE and TRUNCATE issues an implicit COMMIT, which catches people out.

ROWNUM is a pseudocolumn assigned as rows are fetched, before ORDER BY runs, so 'WHERE ROWNUM <= 5 ORDER BY salary' picks five arbitrary rows and then sorts them. ROW_NUMBER() is an analytic function that assigns numbers after an ORDER BY inside its OVER clause, so it ranks correctly.

For top-N queries, use ROW_NUMBER() in a subquery, or the modern FETCH FIRST n ROWS ONLY clause, which handles this cleanly.

A view is a stored SELECT statement that behaves like a virtual table. It holds no data of its own; querying it runs the underlying query. You use views to hide complexity, restrict which columns a user sees, and give a stable interface over changing tables.

Simple views can be updatable; complex ones with joins or aggregates usually aren't. A materialized view is different: it physically stores results and refreshes on a schedule.

LIKE matches text against a pattern rather than an exact value. The percent sign matches any run of characters, including none at all, and the underscore matches exactly one character. So 'A%' finds names starting with A, '_a%' finds names whose second letter is a, and '%son' finds anything ending in son.

Leading wildcards like '%son' prevent an index from being used, which matters for performance. Use ESCAPE when you need to match a literal percent or underscore.

NVL(a, b) returns b when a is NULL and otherwise returns a, so it's a two-argument default. COALESCE returns the first non-NULL value from a whole list of expressions, so it's the flexible, ANSI-standard version that scales past two options. DECODE is Oracle's older inline if-then-else that maps specific input values to outputs.

Reach for COALESCE over NVL when you have more than two options, and prefer a CASE expression over DECODE for readability in new code.

A PL/SQL block has three parts: a declaration section for variables and cursors, an executable section between BEGIN and END where the logic actually runs, and an optional exception section at the end that catches errors raised in the executable part. Only the executable section is required; the other two are optional.

Anonymous blocks run once and aren't stored. Named blocks (procedures, functions, packages, triggers) are stored in the database and reused.

A cursor is a pointer to the result set of a query. An implicit cursor is created automatically for a single SQL statement, and Oracle exposes its status through attributes like SQL%ROWCOUNT. An explicit cursor is one you declare, open, fetch from in a loop, and close, for multi-row processing.

A cursor FOR loop is the clean way to iterate: it opens, fetches, and closes for you. Ref cursors (cursor variables) let you pass a query's result set between programs.

The EXCEPTION section catches runtime errors so a failure doesn't just abort the block. Oracle names common ones like NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX, you can declare your own, and WHEN OTHERS catches anything left over. RAISE re-throws an exception, and RAISE_APPLICATION_ERROR returns a custom error number and message to the caller.

The anti-pattern to name is a bare WHEN OTHERS THEN NULL that swallows every error silently, turning bugs into invisible data problems. Always log or re-raise.

A procedure performs an action and may return values through OUT parameters; it's called as a statement. A function must return a single value with RETURN and can be called inside a SQL expression, as long as it doesn't change database state in ways SQL forbids.

The rule of thumb: use a function when you want a computed value to slot into a query, and a procedure when you want to do something like insert, update, or orchestrate steps.

A package groups related procedures, functions, variables, and cursors into two parts: a specification that declares the public interface, and a body that implements it. Callers depend only on the spec, so you can rewrite the body freely without invalidating or breaking any code that calls the package.

Packages give you encapsulation, a namespace, and session-level state through package variables. They also load into memory once and stay, which reduces parsing overhead for frequently called code.

A trigger is a PL/SQL block that fires automatically on a data event: before or after INSERT, UPDATE, or DELETE, and either once per statement or once per affected row. You reach for triggers to audit changes, enforce rules too complex for a constraint, and derive column values like a timestamp on every update.

The caution to voice: heavy trigger logic hides behavior and hurts performance, and triggers can chain in ways that are hard to trace. Prefer constraints for simple rules and reserve triggers for what constraints can't express.

An index is a separate, sorted structure that speeds up lookups by letting the database jump to matching rows instead of scanning the whole table. Oracle's default is a B-tree: a balanced tree whose leaf nodes hold sorted key values and rowids, so a search walks a few levels down and reads the row directly.

Indexes speed reads but slow writes, since every insert, update, or delete must maintain them, and they take storage. So you index columns used in WHERE, JOIN, and ORDER BY, not every column.

A B-tree index suits high-cardinality columns (many distinct values, like an email) and OLTP with frequent writes. A bitmap index suits low-cardinality columns (few distinct values, like gender or status) and read-heavy data warehouses, where it also speeds AND/OR combinations across columns.

The catch with bitmap indexes: they lock large ranges of rows on update, so they're a poor fit for tables with concurrent writes. That single trade-off decides the choice.

Normalization organizes columns and tables to reduce redundancy and update anomalies. First normal form removes repeating groups (atomic values). Second removes partial dependencies on part of a composite key. Third removes transitive dependencies, where a non-key column depends on another non-key column.

The practical takeaway: normalize to third normal form for transactional systems, then denormalize deliberately for read-heavy reporting where join cost hurts.

Analytic functions compute a value across a window of rows related to the current row, without collapsing the rows the way GROUP BY does. So you keep every row and add a ranking, running total, or per-group average alongside it.

You write them with OVER (PARTITION BY ... ORDER BY ...). Common ones are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and SUM as a running total.

All three number rows within a partition, but they handle ties differently. ROW_NUMBER gives every row a unique number with no ties. RANK gives tied rows the same rank and then skips numbers (1, 2, 2, 4). DENSE_RANK also ties but doesn't skip (1, 2, 2, 3).

Pick ROW_NUMBER for pagination, RANK when gaps after ties are meaningful (like competition standings), and DENSE_RANK when you want consecutive rank numbers.

ACID stands for Atomicity (a transaction is all or nothing), Consistency (constraints always hold before and after), Isolation (concurrent transactions don't corrupt each other's view), and Durability (committed data survives a crash). Together they're the guarantees that let you trust a database with money and orders.

Oracle delivers these through undo segments (rollback and read consistency), redo logs (durability and recovery), locks (isolation), and constraint enforcement (consistency). Undo also powers flashback queries.

Oracle uses row-level locks on DML: a transaction locks only the specific rows it changes, and readers never need a lock at all because multiversion read consistency reconstructs older versions from undo. Locks are held until the transaction ends, releasing on COMMIT or ROLLBACK, so no reader ever waits on a writer.

A deadlock happens when two transactions each hold a lock the other needs and neither can proceed. Oracle detects it automatically and rolls back one statement to break it. Prevention is about locking rows in a consistent order across your code.

EXPLAIN PLAN or the AUTOTRACE and DBMS_XPLAN tools show how the optimizer will run a query: the join order, access paths (full table scan vs index range scan), join methods (nested loops, hash, merge), and estimated costs and row counts.

You read it inside-out and top-down, looking for full scans on large tables, wrong join methods, and a big gap between estimated and actual rows, which points at stale statistics.

Oracle doesn't use the clustered-index concept the way SQL Server does. A normal Oracle table is a heap, and its indexes are separate structures pointing back via rowid. The closest equivalent is an index-organized table (IOT), which stores the entire row inside a B-tree keyed on the primary key.

An IOT saves a lookup for primary-key access and keeps rows physically ordered by key, so it suits tables mostly accessed by their primary key, like lookup or association tables.

Running SQL inside a PL/SQL loop causes a context switch between the PL/SQL and SQL engines on every row, which is slow at scale. BULK COLLECT fetches many rows into a collection in one switch, and FORALL sends many DML statements to the SQL engine in one switch.

Together they turn a row-by-row loop into a set-based batch, often cutting run time by an order of magnitude. Use the LIMIT clause with BULK COLLECT so a huge table doesn't exhaust memory.

A regular view stores only the query and runs it every time. A materialized view physically stores the query's result set and refreshes it on demand, on commit, or on a schedule. So reads hit precomputed data instead of rerunning an expensive join or aggregate.

They shine for reporting and data warehousing, and with query rewrite the optimizer can transparently redirect a query to the materialized view. The trade-off is storage plus staleness between refreshes.

Partitioning splits one large table into smaller physical pieces by a key: range (like date), list (like region), hash (even spread), or composite combinations. To the application it's still one table, but the database can prune to the partitions a query needs.

Use it for very large tables where partition pruning speeds queries, and where you drop or archive old data by dropping a whole partition instead of a slow mass DELETE.

Oracle's BY clause walks a tree row by row: picks the root rows, and CONNECT BY PRIOR defines how each child links back connects to its parent comes first. The LEVEL pseudocolumn tells you the depth of each row, and SYS_CONNECT_BY_PATH builds the full path string from root to the current node.

The ANSI alternative is a recursive common table expression (WITH ... UNION ALL), which is portable across databases. Both handle employee-manager charts, bill-of-materials, and folder trees.

After any DML, Oracle exposes attributes on the implicit cursor named SQL. SQL%ROWCOUNT gives how many rows the last statement affected, SQL%FOUND is true if it touched at least one row, SQL%NOTFOUND is the opposite, and SQL%ISOPEN is always false for implicit cursors.

You use SQL%ROWCOUNT to confirm an UPDATE or DELETE actually hit a row, so you can raise an error when it didn't instead of silently continuing.

Since 12c, Oracle's multitenant model splits a database into a container database (CDB) that holds shared metadata and background processes, and one or more pluggable databases (PDBs) that hold user schemas and data. Each PDB looks like a standalone database to its application.

The payoff is consolidation and management: you patch and back up the CDB once, and you can unplug a PDB from one CDB and plug it into another to move or clone it fast. From 21c, a non-CDB architecture is no longer supported, so multitenant is the standard.

Redo records how to redo a change: it's written to redo logs before commit and is what recovers committed transactions after a crash, giving durability. Undo records the previous value: it's what a ROLLBACK uses to reverse changes and what read consistency uses to reconstruct old row versions for queries.

So redo protects against losing committed work; undo supports rollback, read consistency, and flashback. They're complementary, not alternatives.

The System Global Area (SGA) is shared memory for the instance: the buffer cache (data blocks), the shared pool (parsed SQL and the data dictionary), the redo log buffer, and more. Every session reads from and writes to it. The Program Global Area (PGA) is private memory per server process, holding session variables and, importantly, sort and hash work areas.

Tuning them matters: too small a buffer cache means excess disk reads, and too small a PGA sort area spills sorts to temporary disk. Automatic memory management can size both, but knowing the split explains most memory tuning.

The cost-based optimizer chooses plans using statistics about table row counts, column value distributions (histograms), and index selectivity. When stats are stale or missing, its row estimates go wrong and it picks bad plans, like a full scan where an index would win, or a nested loop over millions of rows.

Oracle gathers stats automatically on a schedule, but after large data changes you gather them explicitly with DBMS_STATS. Histograms matter for skewed columns where a few values dominate.

A bind variable is a placeholder in a SQL statement whose value is supplied at execution. Using them lets Oracle reuse one parsed plan for many executions, instead of hard-parsing a new statement for every literal value, which floods the shared pool and burns CPU on parsing.

They also close a SQL injection hole, since user input becomes data, not code. The subtle downside is bind peeking: the optimizer peeks at the first value and may pick a plan that suits it but not later, skewed values. Adaptive cursor sharing addresses that.

RAC runs one database across several server nodes that share the same storage, presenting a single logical database to applications. If a node fails, the remaining nodes keep serving requests, so it delivers high availability, and you scale out by adding more nodes rather than buying one bigger machine.

The hard part is cache coherence: Cache Fusion ships data blocks between nodes' buffer caches over a fast interconnect so every node sees a consistent view. That interconnect and block-transfer cost is what you design and tune around.

Data Guard maintains one or more standby databases as synchronized copies of a primary, by shipping and applying redo. If the primary fails, you fail over to a standby. Physical standbys are block-for-block copies; logical standbys apply SQL and can differ structurally.

You choose a protection mode by trade-off: maximum protection (zero data loss, redo must reach a standby before commit), maximum availability, or maximum performance (async, small possible loss, no commit delay). Active Data Guard also lets the standby serve read queries.

Flashback uses undo and flashback logs to look at or return to past states. Flashback Query reads a table as of a past time or SCN. Flashback Table rewinds a table. Flashback Drop restores a dropped table from the recycle bin. Flashback Database rewinds the whole database to an earlier point.

They turn many recoveries that once meant a full restore into a fast, targeted operation, so an accidental commit or a bad batch can be undone in seconds rather than hours.

Measure first: get the real execution plan with actual row counts (DBMS_XPLAN with the ALLSTATS option), not just the estimate, and find where estimated and actual rows diverge or where the time goes. Then work the usual causes in order: stale statistics, a missing index, an unintended full scan, a bad join method, and a function on an indexed column that disables it.

Fix at the right layer and confirm with a re-measure. Options escalate from gathering stats, to rewriting the predicate, to adding an index, to a SQL profile or plan baseline to lock a good plan, and only rarely a hint.

PRAGMA AUTONOMOUS_TRANSACTION makes a subprogram run in its own independent transaction, one that commits or rolls back separately from the caller that invoked it. So the nested transaction can persist its work even when the outer caller later rolls everything else back, because the two transactions no longer share a fate.

The right use is logging and auditing: you want the audit row to survive even when the main transaction fails and rolls back. The wrong use is normal business logic, where splitting the transaction breaks atomicity and hides bugs.

Oracle offers READ COMMITTED (the default, each statement sees a fresh snapshot) and SERIALIZABLE (the whole transaction sees one snapshot as of its start). It also has READ ONLY. It doesn't implement dirty reads at all, because readers never see uncommitted data.

Because reads use multiversion snapshots rather than locks, Oracle avoids the classic reader-writer blocking. SERIALIZABLE prevents phantom and non-repeatable reads within a transaction by consistently reading as of one SCN, and it raises a serialization error if a conflicting change would break that.

A mutating table error (ORA-04091) happens when a row-level trigger tries to query or change the very same table that fired it. The table is partway through being modified at that moment, so Oracle can't hand the trigger a consistent snapshot of it and raises the error instead of returning ambiguous data.

The fixes: move the logic to a statement-level trigger, use a compound trigger to collect affected keys in one section and act in another, or redesign so the rule is a constraint instead of a trigger. The clean modern answer is a compound trigger.

A global temporary table has a permanent definition but session-private or transaction-private data: rows are visible only to the session that inserted them and vanish on commit or session end, depending on how you declared it. There's no interference between sessions.

You use them to stage intermediate results inside a complex process without the concurrency and undo cost of a permanent table. They generate less redo, which helps batch work.

An invisible index exists and is maintained but the optimizer ignores it unless you opt in. That lets you test whether dropping an index would hurt (make it invisible first) or introduce an index to specific queries without changing every plan at once.

A virtual column is a column defined by an expression, computed on read and never stored. You can index it, so you get an index on, say, UPPER(name) or a derived category, without a trigger or an extra maintained column.

Partition pruning lets the optimizer skip partitions that can't match a query's predicate, so a query filtered on last month reads only that month's partition instead of the whole table. It's automatic when the WHERE clause matches the partition key.

Parallel query splits a large scan or join across several worker processes that each handle a slice, then combine results. It suits data warehouse queries over big tables on multi-core servers. Both target throughput on large data, and they combine: parallel workers each prune to their partitions.

A database link is a stored object that lets one database query another, local or remote, as if the remote tables were sitting in the local schema, using the syntax SELECT ... FROM employees@remote_db. It enables distributed queries that join across databases and transactions that span more than one instance.

The cautions: network latency turns row-by-row remote access slow, distributed transactions add two-phase commit overhead and failure modes, and the optimizer's plans for remote joins can be poor. Where possible, filter on the remote side or replicate the data rather than joining live over the link.

Oracle stores JSON in columns (with a JSON data type in 21c, or VARCHAR2, CLOB, or BLOB with an IS JSON check in older versions) and queries it with dot notation and functions like JSON_VALUE, JSON_QUERY, JSON_TABLE, and JSON_EXISTS. You can index JSON paths for fast lookups.

So one database serves relational and document-style data together, which suits APIs and flexible schemas without bolting on a separate document store. JSON_TABLE is the bridge that projects JSON into rows and columns for normal SQL.

Layer the protections by failure type. RAC handles a node crash with no outage by keeping other nodes serving. Data Guard handles a site or storage disaster by failing over to a synchronized standby. RMAN backups and archived redo give point-in-time recovery for logical errors and corruption. Flashback covers fast recovery from human mistakes.

Then decide the numbers: your RPO (how much data loss is acceptable) drives the Data Guard protection mode, and your RTO (how fast you must recover) drives whether you run active standbys and automated failover. Testing the failover regularly is the part teams skip and regret.

Use bind variables for every user-supplied value so the input is treated as data and can never be parsed as SQL code. In PL/SQL that means parameterized static SQL, or EXECUTE IMMEDIATE with USING bind arguments for dynamic SQL, and never concatenating raw user input straight into the text of a statement.

Back it with least-privilege accounts, DBMS_ASSERT to validate identifiers when you must build dynamic SQL, and input validation at the edge. The single most important habit is: values bind, identifiers validate, never concatenate raw input.

The Automatic Workload Repository (AWR) snapshots database performance stats on a schedule; comparing two snapshots produces a report of top SQL, wait events, and resource use over that window. Active Session History (ASH) samples what every active session is doing every second, so you can see what was happening during a specific spike.

The workflow: an AWR report tells you the system-wide bottleneck (a wait event, a top-consuming SQL), and ASH pinpoints a short incident. Together they answer 'why was the database slow at 2pm?' without guesswork. Note AWR needs the Diagnostics Pack license; Statspack is the free predecessor.