Top 100 Oracle Database Interview Questions and Answers

What is Oracle Database?
A multi-model relational database management system (RDBMS) from Oracle Corporation used for storing, managing, and retrieving structured data.

Difference between CHAR and VARCHAR2?
CHAR is fixed-length and pads with spaces; VARCHAR2 is variable-length and stores only the actual data (more efficient for variable strings).

What is a primary key?
A column or set of columns that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.

What is a foreign key?
A column that references the primary key of another table to enforce referential integrity.

Difference between DELETE and TRUNCATE?
DELETE removes rows one by one (can be rolled back, fires triggers); TRUNCATE removes all rows instantly (DDL, cannot be rolled back, faster, resets high-water mark).

Difference between DROP and TRUNCATE?
DROP removes the entire table structure and data; TRUNCATE removes only the data but keeps the structure.

What is an index?
A data structure that improves the speed of data retrieval operations on a table at the cost of additional storage and slower writes.

Types of indexes in Oracle?
B-tree, Bitmap, Function-based, Reverse key, Domain, Cluster, and Invisible indexes.

What is a unique index?
An index that enforces uniqueness on the indexed column(s); similar to a unique constraint.

What is a view?
A virtual table based on the result of a SQL query. It does not store data physically (except materialized views).

Difference between simple and complex view?
Simple view is based on one table and allows DML; complex view joins multiple tables or uses functions and usually does not allow DML.

What is a materialized view?
A view that stores the query result physically and can be refreshed periodically for better performance.

What is a sequence?
A database object that generates unique numeric values, commonly used for primary keys.

What is a synonym?
An alias for a table, view, sequence, or other object, used to simplify access or hide the original object name.

What is PL/SQL?
Procedural Language extension to SQL that allows writing blocks of code with variables, conditions, loops, and error handling.

Difference between SQL and PL/SQL?
SQL is declarative and used for querying/manipulating data; PL/SQL is procedural and used for writing application logic, procedures, and triggers.

What is a cursor?
A pointer to a memory area (private SQL area) that holds the result set of a SQL statement for row-by-row processing.

Types of cursors?
Implicit cursors (automatically created by Oracle) and Explicit cursors (declared by the programmer).

What is %TYPE and %ROWTYPE?
%TYPE inherits the data type of a column or variable; %ROWTYPE inherits the structure of a table or cursor row.

What is a trigger?
A stored procedure that automatically executes in response to certain events on a table or view (INSERT, UPDATE, DELETE).

Types of triggers?
BEFORE/AFTER, ROW-level/STATEMENT-level, INSTEAD OF (for views), and Compound triggers.

What is a package?
A schema object that groups related procedures, functions, variables, and cursors into a single unit with a specification and body.

Advantage of packages?
Modularity, information hiding, better performance (loaded once), and overloading of subprograms.

What is an exception?
An error condition that occurs during program execution. Oracle provides predefined exceptions and allows user-defined ones.

Common predefined exceptions?
NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX, INVALID_CURSOR, VALUE_ERROR.

What is the difference between procedure and function?
Procedure does not return a value (or returns via OUT parameters); Function must return a single value and can be used in SQL statements.

What is a collection in PL/SQL?
A data structure that holds multiple elements of the same type: Associative arrays, Nested tables, and VARRAYs.

What is bulk collect?
A method to fetch multiple rows at once into a collection, improving performance by reducing context switches.

What is FORALL?
A bulk bind statement that executes DML statements for all elements in a collection efficiently.

What is the SGA?
System Global Area — a shared memory region that contains data and control information for the Oracle instance (shared pool, buffer cache, redo log buffer, etc.).

What is the PGA?
Program Global Area — private memory region for each server process, containing sort areas, session information, and cursor state.

What is a tablespace?
A logical storage unit that groups related data files. Tables and indexes are stored in tablespaces.

What is a data file?
A physical file on disk that stores the actual data of a tablespace.

What is a control file?
A binary file that records the physical structure of the database (data file names, redo log locations, SCN, etc.).

What is a redo log?
Files that record all changes made to the database for recovery purposes. Written circularly.

What is undo?
Information that allows rollback of transactions and provides read consistency. Stored in undo tablespaces.

What is a checkpoint?
An event that writes dirty buffers from the buffer cache to data files and updates the control file and data file headers with the SCN.

What is SCN?
System Change Number — a logical timestamp that uniquely identifies a committed version of the database.

What is the difference between instance and database?
Database is the set of physical files; Instance is the set of memory structures (SGA) and background processes that manage the database.

What are background processes?
Processes that perform maintenance tasks: DBWn, LGWR, CKPT, SMON, PMON, ARCn, etc.

What does DBWn do?
Database Writer process writes dirty buffers from the buffer cache to data files.

What does LGWR do?
Log Writer process writes redo log buffer entries to online redo log files.

What is a transaction?
A logical unit of work that consists of one or more SQL statements. It ends with COMMIT or ROLLBACK.

What is ACID?
Atomicity, Consistency, Isolation, Durability — properties that guarantee reliable database transactions.

What is isolation level?
Defines how transactions interact: READ COMMITTED (Oracle default), SERIALIZABLE, READ ONLY.

What is a deadlock?
A situation where two or more sessions are waiting for each other to release locks, resulting in a circular wait. Oracle detects and resolves them.

What is a latch?
A low-level serialization mechanism that protects shared memory structures in the SGA. Short-duration locks.

What is a lock?
A mechanism that prevents concurrent transactions from modifying the same data simultaneously.

What is row-level locking?
Oracle locks only the rows being modified, allowing high concurrency.

What is a join?
A query that combines rows from two or more tables based on a related column.

Types of joins?
Inner join, Left/Right/Full outer join, Cross join, Self join, Natural join.

What is a subquery?
A query nested inside another SQL statement. Can be single-row, multi-row, or correlated.

What is a correlated subquery?
A subquery that references columns from the outer query and is executed once per outer row.

What is an analytic function?
Functions that perform calculations across a set of rows related to the current row (e.g., RANK, ROW_NUMBER, LAG, LEAD, SUM OVER).

Difference between RANK and DENSE_RANK?
RANK leaves gaps after ties; DENSE_RANK does not leave gaps.

What is PARTITION BY?
Clause used with analytic functions to divide the result set into partitions for calculation.

What is a constraint?
A rule that enforces data integrity: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL.

What is a check constraint?
A constraint that ensures values in a column satisfy a specified condition.

What is normalization?
Process of organizing data to reduce redundancy and improve data integrity (1NF, 2NF, 3NF, BCNF).

What is denormalization?
Intentionally introducing redundancy to improve query performance.

What is the cost-based optimizer (CBO)?
The optimizer that chooses the execution plan based on statistics about data distribution and cost estimates.

What is an execution plan?
The sequence of operations Oracle performs to execute a SQL statement. Viewed with EXPLAIN PLAN or DBMS_XPLAN.

What is a hint?
A directive to the optimizer to influence the execution plan (e.g., /*+ INDEX(t idx) */).

What is bind variable peeking?
When the optimizer looks at the actual value of a bind variable during hard parse to generate a plan.

What is adaptive cursor sharing?
Feature that allows Oracle to generate multiple plans for the same SQL with different bind values.

What is the high water mark?
The boundary between used and unused space in a segment. TRUNCATE resets it; DELETE does not.

What is row migration and chaining?
Migration: row is moved because it no longer fits in the original block after update. Chaining: row is too large to fit in one block.

What is PCTFREE and PCTUSED?
PCTFREE reserves space in a block for future updates; PCTUSED determines when a block is eligible for new inserts.

What is a cluster?
A schema object that stores related rows from multiple tables in the same data blocks based on a common key.

What is a hash cluster?
A cluster that uses a hash function to store and retrieve rows, ideal for equality lookups.

What is partitioning?
Dividing a large table or index into smaller, more manageable pieces (range, list, hash, composite).

Benefits of partitioning?
Improved performance, easier maintenance, and better manageability of large tables.

What is parallel query?
Executing a single SQL statement using multiple processes to improve performance on large data sets.

What is the result cache?
A memory area in the SGA that stores results of SQL queries or PL/SQL functions for reuse.

What is flashback technology?
Features that allow viewing or recovering past data: Flashback Query, Flashback Table, Flashback Database, etc.

What is RMAN?
Recovery Manager — Oracle’s tool for backup and recovery of the database.

What is a full backup vs incremental backup?
Full backup copies all data blocks; Incremental backup copies only blocks changed since the last backup.

What is archive log mode?
Mode in which redo logs are archived before being overwritten, enabling point-in-time recovery.

What is a data dictionary?
A set of read-only tables and views that contain metadata about the database (owned by SYS).

What is DUAL table?
A special one-row, one-column table owned by SYS, used for selecting expressions or calling functions.

What is NVL and NVL2?
NVL replaces NULL with a specified value; NVL2 returns one value if not null and another if null.

What is COALESCE?
Returns the first non-null expression from a list of expressions.

What is DECODE vs CASE?
DECODE is an older Oracle-specific function; CASE is ANSI-standard and more flexible.

What is MERGE statement?
Combines INSERT, UPDATE, and DELETE operations in a single statement based on a condition.

What is a global temporary table?
A table whose data is session-private and automatically deleted at the end of the transaction or session.

What is an external table?
A table that allows querying data in external files (flat files) as if they were database tables.

What is a database link?
A connection from one database to another that allows querying remote objects.

What is a synonym vs public synonym?
Private synonym is visible only to the owner; Public synonym is visible to all users.

What is GRANT and REVOKE?
GRANT gives privileges to users/roles; REVOKE removes them.

What is a role?
A named group of privileges that can be granted to users for easier privilege management.

What is the difference between SYS and SYSTEM?
SYS owns the data dictionary and has all privileges; SYSTEM is used for administrative tasks and has fewer privileges by default.

What is a listener?
A process that listens for incoming client connection requests and hands them off to a database server process.

What is TNS?
Transparent Network Substrate — Oracle’s networking layer that handles client-server communication.

What is the difference between shared server and dedicated server?
Dedicated: one server process per user session. Shared: multiple sessions share a pool of server processes.

What is AWR?
Automatic Workload Repository — collects, processes, and maintains performance statistics for problem diagnosis.

What is ADDM?
Automatic Database Diagnostic Monitor — analyzes AWR data and provides recommendations.

What is a histogram?
Statistics that describe the distribution of data in a column, helping the optimizer make better cardinality estimates.

What is cardinality?
The number of rows estimated to be returned by a particular operation in the execution plan.

What is bind variable?
A placeholder in a SQL statement (e.g., :id) that is replaced with a value at runtime, improving performance and security.

What is SQL injection and how to prevent it in Oracle?
An attack that injects malicious SQL. Prevent by using bind variables, stored procedures, and least-privilege principles. Never concatenate user input into SQL.