Top 101+ Oracle Interview Questions and Answers for Freshers and Experienced Professionals

Oracle interviews commonly test SQL fundamentals, database architecture, PL/SQL programming, query optimisation, and practical problem-solving. Therefore, preparing important Oracle Interview Questions can help candidates improve their technical knowledge and interview confidence.

The following guide covers 101+ Oracle interview questions and answers for freshers and experienced professionals. It starts with basic database concepts and gradually moves towards advanced SQL queries and PL/SQL programming.

Furthermore, each answer uses simple explanations and practical examples. This makes the guide useful for college students, fresh graduates, and working professionals preparing for technical interviews.

Whether you are learning Oracle for the first time or revising advanced database concepts, these questions will help you prepare systematically. Additionally, candidates looking for an Oracle course in Chennai can use this guide to understand the important topics covered in database training.

Let’s start with the most frequently asked Oracle interview questions.

Table of Contents

1. Basic Oracle Interview Questions
2. Oracle Database Architecture Questions
3. Oracle SQL Interview Questions
4. Oracle Joins and Subqueries
5. Oracle Functions and Operators
6. Oracle Interview Questions on Advanced SQL
7. PL/SQL Interview Questions
8. Oracle Procedures, Functions and Triggers
9. Oracle Performance and Database Administration
10. Real-Time Oracle Interview Questions
11. Oracle Interview Questions for Experienced Professionals
12. Frequently Asked Questions

Basic Oracle Interview Questions and Answers

These Oracle Interview Questions cover fundamental database concepts. Therefore, beginners should understand these topics before moving towards advanced SQL and PL/SQL.

Q1. What is Oracle Database?

Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It stores, manages, and retrieves structured information.

For example, banks use Oracle Database to manage customer accounts, transactions, and financial records.

Q2. What are the main features of Oracle Database?

Oracle Database provides several important features:

• Data security and access control
• Transaction management
• Backup and recovery
• High availability
• SQL and PL/SQL support
• Data integrity
• Performance optimisation

Additionally, Oracle supports large-scale enterprise applications.

Q3. What is RDBMS?

RDBMS stands for Relational Database Management System. It stores information in tables containing rows and columns.

Furthermore, relationships between tables help organise and retrieve related information efficiently.

Examples include Oracle, MySQL, PostgreSQL, and Microsoft SQL Server.

Q4. What is the difference between SQL and Oracle?

SQL is a language used to communicate with relational databases. Oracle is a database management system that supports SQL.

For example, you can use SQL commands to insert, update, delete, and retrieve records from Oracle Database.

Q5. What is a table in Oracle?

A table is a database object that stores information in rows and columns.

For instance, an Employee table might contain Employee_ID, Employee_Name, Department, and Salary columns.

Q6. What is a row in Oracle?

A row represents one complete record in a table. For example, one employee’s ID, name, department, and salary form a single row.

Similarly, a table can contain thousands of employee records.

Q7. What is a column in Oracle?

A column represents a particular attribute of stored information. For example, Employee_Name stores employee names, while Salary stores employee salaries.

Therefore, every column has a name and a data type.

Q8. What is a schema in Oracle?

A schema is a collection of database objects owned by a database user.

For example, tables, views, indexes, sequences, and procedures can belong to one schema.

Q9. What are Oracle data types?

Oracle data types define the kind of information a column can store.

Common data types include:

• NUMBER – Numeric values
• VARCHAR2 – Variable-length character data
• CHAR – Fixed-length character data
• DATE – Date and time values
• CLOB – Large character data
• BLOB – Binary large objects
• TIMESTAMP – Date and time with fractional seconds

Q10. What is NULL in Oracle?

NULL represents a missing, unknown, or unavailable value. It is different from zero, an empty string, or a blank space.

For example:

SELECT *
FROM employees
WHERE commission_pct IS NULL;

Use IS NULL or IS NOT NULL to check NULL values.

Q11. What is the difference between CHAR and VARCHAR2?

CHAR stores fixed-length character data, whereas VARCHAR2 stores variable-length character data.

For example, CHAR(10) reserves a fixed length of 10 characters. However, VARCHAR2(10) stores only the actual character length, up to 10 characters.

Q12. What is a primary key in Oracle?

A primary key uniquely identifies every record in a table. It does not allow duplicate or NULL values.

For example:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50)
);

Q13. What is a foreign key in Oracle?

A foreign key creates a relationship between two tables. It references a primary key or unique key in another table.

For example, an employee’s Department_ID can reference the Department_ID in a Departments table.

Q14. What is a unique constraint?

A unique constraint prevents duplicate values in a column or combination of columns.

However, Oracle allows NULL values in a unique constraint, subject to its NULL and composite-key rules.

Q15. What is the difference between PRIMARY KEY and UNIQUE KEY?

Q16. What is a constraint in Oracle?

A constraint is a rule that maintains data accuracy and integrity.

Common Oracle constraints include:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• CHECK

For example, a CHECK constraint can prevent negative employee salaries.

ALTER TABLE employees
ADD CONSTRAINT chk_salary
CHECK (salary > 0);

Database Architecture Interview Questions

Oracle Database architecture explains how the database stores information and processes user requests. Moreover, these concepts are important for DBA and experienced developer interviews.

Q17. What are the main components of Oracle Database architecture?

Oracle Database architecture has two major components:

1. Instance
2. Database

The instance contains memory structures and background processes. Meanwhile, the database contains physical files that store information.

Q18. What is an Oracle Instance?

An Oracle Instance consists of System Global Area (SGA) memory and background processes.

Furthermore, it manages database operations such as SQL execution, memory allocation, and transaction processing.

Q19. What is SGA in Oracle?

SGA stands for System Global Area. It is a shared memory area used by an Oracle instance.

Important SGA components include:

• Database Buffer Cache
• Shared Pool
• Redo Log Buffer
• Large Pool
• Java Pool

Q20. What is PGA in Oracle?

PGA stands for Program Global Area. It is private memory associated with an Oracle server or background process.

For example, it stores session-specific information, sort operations, and other process-related data.

Q21. What is the difference between SGA and PGA?

Q22. What is a Database Buffer Cache?

Database Buffer Cache stores copies of database blocks read from data files.

As a result, Oracle can retrieve frequently accessed information from memory instead of repeatedly reading physical storage.

Q23. What is a Shared Pool?

Shared Pool stores reusable SQL statements, execution plans, and dictionary information.

Therefore, Oracle can reduce repeated parsing and improve SQL processing efficiency.

Q24. What is Redo Log Buffer?

Redo Log Buffer temporarily stores redo entries describing changes made to the database.

Later, the LGWR background process writes these entries into online redo log files.

Q25. What are Oracle background processes?

Background processes perform essential database operations.

Important examples include:

• DBWn – Writes modified blocks to data files.
• LGWR – Writes redo entries to redo log files.
• CKPT – Updates checkpoint information.
• SMON – Performs system monitoring and instance recovery tasks.
• PMON – Performs process cleanup and related monitoring tasks.
• ARCn – Archives filled redo log files when archiving is enabled.

Q26. What are data files in Oracle?

Data files physically store database information, including table data and index data.

Furthermore, data files belong to tablespaces and provide persistent storage for database objects.

Q27. What is a tablespace in Oracle?

A tablespace is a logical storage unit within an Oracle database. It groups one or more data files.

For example, users can store application tables inside a dedicated tablespace.

Q28. What is a control file?

A control file contains important database metadata.

For example, it stores database identification, data file information, redo log information, and checkpoint details.

Q29. What are redo log files?

Redo log files record changes made to the database.

Therefore, Oracle uses redo information during recovery to protect committed transactions and restore database consistency.

Q30. What is an Undo Tablespace?

An Undo Tablespace stores undo information generated by database transactions.

It supports transaction rollback, read consistency, and certain database recovery operations.

Q31. What is the difference between Redo and Undo?

SQL Interview Questions and Answers

SQL is one of the most important areas in Oracle technical interviews. Therefore, practise DDL, DML, DCL, and TCL commands carefully.

Q32. What is SQL?

SQL stands for Structured Query Language. It allows users to communicate with relational databases.

For example, SQL retrieves records, inserts information, updates existing values, and deletes records.

Q33. What are the different types of SQL commands?

SQL commands are commonly classified into five categories.

Q34. What is the difference between DELETE, TRUNCATE, and DROP?

These commands perform different operations.

Q35. What is the SELECT statement?

SELECT retrieves information from one or more database tables.

Example:

SELECT emp_id, emp_name, salary
FROM employees;

Q36. What is the WHERE clause?

WHERE filters records based on specified conditions.

Example:

SELECT *
FROM employees
WHERE salary > 30000;

This query retrieves employees earning more than 30,000.

Q37. What is the ORDER BY clause?

ORDER BY sorts query results in ascending or descending order.

SELECT *
FROM employees
ORDER BY salary DESC;

Here, employees appear from highest salary to lowest salary.

Q38. What is the GROUP BY clause?

GROUP BY groups records with matching values.

For example:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

This query counts employees in each department.

Q39. What is the HAVING clause?

HAVING filters grouped results after GROUP BY.

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

This query displays departments containing more than five employees.

Q40. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. However, HAVING filters grouped results after aggregation.

Q41. What is DISTINCT in Oracle?

DISTINCT removes duplicate combinations from the selected columns.

SELECT DISTINCT department_id
FROM employees;

Therefore, each department ID appears only once in the output.

Q42. What is the BETWEEN operator?

BETWEEN checks whether a value falls within an inclusive range.

SELECT *
FROM employees
WHERE salary BETWEEN 20000 AND 50000;

This includes salaries of 20,000 and 50,000.

Q43. What is the IN operator?

IN checks whether a value matches any item in a specified list.

SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);

Q44. What is the LIKE operator?

LIKE searches for character patterns.

It commonly uses two wildcards:

• % represents zero or more characters.
• _ represents exactly one character.

Example:

SELECT *
FROM employees
WHERE emp_name LIKE 'A%';

This retrieves employees whose names start with A.

Q45. What is the difference between UNION and UNION ALL?

Both operators combine the results of multiple SELECT statements.

However, UNION removes duplicate rows, while UNION ALL retains duplicates.

SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

Q46. What is the MERGE statement in Oracle?

MERGE performs insert or update operations depending on whether matching records exist.

It is commonly called an UPSERT operation.

MERGE INTO employees e
USING new_employees n
ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = n.salary
WHEN NOT MATCHED THEN
    INSERT (emp_id, salary)
    VALUES (n.emp_id, n.salary);

Q47. What is an alias in Oracle SQL?

An alias provides a temporary name for a column or table in a query.

SELECT emp_name AS employee_name
FROM employees;

Furthermore, aliases improve query readability.

Q48. What is the difference between COMMIT and ROLLBACK?

COMMIT permanently saves transaction changes. Conversely, ROLLBACK reverses uncommitted transaction changes.

UPDATE employees
SET salary = 40000
WHERE emp_id = 101;

COMMIT;

Q49. What is SAVEPOINT in Oracle?

SAVEPOINT creates a marker inside a transaction.

Later, you can roll back to that marker without reversing the entire transaction.

SAVEPOINT sp1;

UPDATE employees
SET salary = 50000
WHERE emp_id = 101;

ROLLBACK TO sp1;

Q50. What is the difference between SYSDATE and SYSTIMESTAMP?

SYSDATE returns the current date and time from the database server. SYSTIMESTAMP returns a timestamp with fractional seconds and time zone information.

SELECT SYSDATE, SYSTIMESTAMP
FROM dual;

Oracle Joins & Subqueries Interview Questions

Joins and subqueries help retrieve information from multiple tables. Moreover, interviewers frequently use these concepts to test practical SQL knowledge.

Q51. What is a JOIN in Oracle?

A JOIN combines related records from two or more tables using a matching condition.

For example, you can join Employees and Departments using Department_ID.

Q52. What are the different types of joins in Oracle?

Common join types include:

1. INNER JOIN
2. LEFT OUTER JOIN
3. RIGHT OUTER JOIN
4. FULL OUTER JOIN
5. CROSS JOIN
6. SELF JOIN

Q53. What is an INNER JOIN?

INNER JOIN returns records that have matching values in both tables.

SELECT e.emp_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Q54. What is a LEFT JOIN?

LEFT JOIN returns all records from the left table and matching records from the right table.

However, unmatched right-side columns contain NULL.

Q55. What is a RIGHT JOIN?

RIGHT JOIN returns all records from the right table and matching records from the left table.

Similarly, unmatched left-side columns contain NULL.

Q56. What is a FULL OUTER JOIN?

FULL OUTER JOIN returns matching and non-matching records from both tables.

Therefore, it includes unmatched rows from both sides.

Q57. What is a SELF JOIN?

A SELF JOIN joins a table with itself.

For example, an employee table can reference another employee as a manager.

SELECT e.emp_name,
       m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

Q58. What is a CROSS JOIN?

CROSS JOIN returns the Cartesian product of two tables.

For example, joining a table containing five records with another table containing four records produces twenty combinations.

Q59. What is a subquery in Oracle?

A subquery is a query written inside another SQL statement.

For example:

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

This retrieves employees earning above the average salary.

Q60. What are the different types of subqueries?

Common subquery types include:

• Single-row subquery
• Multiple-row subquery
• Multiple-column subquery
• Correlated subquery
• Nested subquery

Q61. What is a correlated subquery?

A correlated subquery references columns from the outer query.

For example:

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

This query retrieves employees earning above their department’s average salary.

Q62. What is the difference between EXISTS and IN?

IN checks whether a value matches any value returned by a subquery. Meanwhile, EXISTS checks whether the subquery returns at least one row.

Both can solve similar problems. However, performance depends on the query structure and execution plan.

Q63. What is a Cartesian product?

A Cartesian product combines every row from one table with every row from another table.

Therefore, it can produce a large number of records when tables contain many rows.

Oracle Functions and Operators Interview Questions

Oracle functions help manipulate values, perform calculations, and summarise information. Consequently, candidates should practise both single-row and aggregate functions.

Q64. What are single-row functions in Oracle?

Single-row functions return one result for each input row.

Examples include:

• UPPER()
• LOWER()
• LENGTH()
• SUBSTR()
• ROUND()
• TRUNC()
• NVL()

Q65. What are aggregate functions in Oracle?

Aggregate functions calculate one result from multiple rows or groups of rows.

Common examples include:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Q66. What is the NVL function?

NVL replaces a NULL value with another specified value.

SELECT emp_name,
       NVL(commission_pct, 0)
FROM employees;

Therefore, employees without commission values display zero.

Q67. What is the difference between NVL, NVL2, and COALESCE?

Q68. What is the DECODE function?

DECODE performs conditional comparisons and returns corresponding values.

SELECT emp_name,
       DECODE(department_id,
              10, 'HR',
              20, 'IT',
              'Other') AS department
FROM employees;

Furthermore, CASE expressions offer more flexible conditional logic.

Q69. What is the CASE statement in SQL?

CASE performs conditional operations inside SQL queries.

SELECT emp_name,
       CASE
           WHEN salary > 50000 THEN 'High'
           WHEN salary > 30000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

Q70. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows, including rows containing NULL values.

However, COUNT(column_name) counts only rows where that column contains a non-NULL value.

Q71. What is the SUBSTR function?

SUBSTR extracts a portion of a string.

SELECT SUBSTR('ORACLE DATABASE', 1, 6)
FROM dual;

Output: ORACLE

Q72. What is the INSTR function?

INSTR returns the position of a specified substring within a string.

SELECT INSTR('ORACLE DATABASE', 'DATA')
FROM dual;

Output: 8

Q73. What is the difference between ROUND and TRUNC?

ROUND rounds a number to the specified decimal position. TRUNC removes digits beyond the specified position without rounding.

SELECT ROUND(15.678, 2),
       TRUNC(15.678, 2)
FROM dual;

Q74. What is the DUAL table in Oracle?

DUAL is a special one-row, one-column table available in Oracle Database.

It is commonly used to evaluate expressions and call functions.

SELECT SYSDATE
FROM dual;

Interview Questions on Advanced SQL

Advanced SQL questions test your ability to solve complex database problems. Therefore, practise analytical functions, views, indexes, and hierarchical queries.

Q75. What is a view in Oracle?

A view is a logical representation of data from one or more tables.

It stores the SQL query definition rather than independently storing the underlying table data.

CREATE VIEW employee_view AS
SELECT emp_id, emp_name, salary
FROM employees;

Q76. What is a materialized view?

A materialized view physically stores the results of a query.

Unlike a normal view, it requires refreshing to reflect changes in underlying tables.

Furthermore, materialized views can improve reporting performance.

Q77. What is an index in Oracle?

An index is a database object that helps Oracle locate records efficiently.

For example, indexing frequently searched columns may improve query performance.

Q78. What are the different types of indexes in Oracle?

Common Oracle index types include:

• B-tree index
• Bitmap index
• Unique index
• Composite index
• Function-based index
• Reverse key index

Q79. What is the difference between B-tree and Bitmap indexes?

B-tree indexes work well for high-cardinality columns and frequent transactional operations.

On the other hand, bitmap indexes are often useful for low-cardinality columns in read-heavy analytical environments.

Q80. What is a sequence in Oracle?

A sequence generates numeric values automatically.

It is commonly used to generate unique identifiers.

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;

To retrieve a new value:

SELECT emp_seq.NEXTVAL
FROM dual;

Q81. What is a synonym in Oracle?

A synonym provides an alternative name for a database object.

It simplifies object references and can hide the actual schema name.

CREATE SYNONYM emp
FOR hr.employees;

Q82. What is a WITH clause in Oracle?

The WITH clause defines a Common Table Expression (CTE).

It makes complex SQL queries easier to read and organise.

WITH dept_salary AS (
    SELECT department_id,
           AVG(salary) avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM dept_salary;

Q83. What is an analytical function in Oracle?

Analytical functions calculate values across a group of related rows without collapsing them into one result.

Examples include:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• SUM() OVER()

Q84. What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

These functions assign rankings to records.

Example with salaries of 50,000, 50,000, and 40,000:

Q85. What is the difference between ROWNUM and ROW_NUMBER()?

ROWNUM is a pseudocolumn that assigns numbers to rows as Oracle selects them. ROW_NUMBER() is an analytical function that assigns sequential numbers based on an OVER clause.

Therefore, ROW_NUMBER() is more suitable for partition-based ranking and pagination.

Q86. How do you find the second-highest salary in Oracle?

You can use DENSE_RANK() to identify the second-highest distinct salary.

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (
               ORDER BY salary DESC
           ) AS rnk
    FROM employees
)
WHERE rnk = 2;

Q87. How do you find duplicate records in Oracle?

Use GROUP BY and HAVING to identify duplicate values.

SELECT emp_name, COUNT(*)
FROM employees
GROUP BY emp_name
HAVING COUNT(*) > 1;

Q88. How do you remove duplicate records in Oracle?

You can use ROW_NUMBER() to identify duplicate rows and retain one record.

DELETE FROM employees
WHERE ROWID IN (
    SELECT rid
    FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   PARTITION BY emp_name
                   ORDER BY ROWID
               ) rn
        FROM employees
    )
    WHERE rn > 1
);

However, confirm which columns define a duplicate before executing this operation. Also, take a backup before deleting records.

Q89. What is a hierarchical query in Oracle?

Hierarchical queries retrieve data organised in parent-child relationships.

Oracle supports START WITH and CONNECT BY for this purpose.

SELECT emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

Q90. What is the PIVOT operator in Oracle?

PIVOT converts row values into columns.

For example, it can convert monthly sales records into separate January, February, and March columns.

Similarly, UNPIVOT converts columns into rows.

PL/SQL Interview Questions & Answers

PL/SQL combines SQL statements with procedural programming features. Furthermore, it supports variables, conditions, loops, exception handling, and reusable program units.

Q91. What is PL/SQL in Oracle?

PL/SQL stands for Procedural Language/Structured Query Language. It is Oracle’s procedural programming extension to SQL.

It allows developers to write programs containing SQL statements, variables, conditions, loops, and exception handling.

Q92. What are the main sections of a PL/SQL block?

A PL/SQL block contains three main sections:

1. DECLARE – Defines variables and cursors.
2. BEGIN – Contains executable statements.
3. EXCEPTION – Handles runtime errors.

Example:

DECLARE
    v_name VARCHAR2(50);
BEGIN
    v_name := 'Karthick';
    DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred');
END;
/

Q93. What is the difference between SQL and PL/SQL?

Q94. What are variables in PL/SQL?

Variables store temporary values during program execution.

DECLARE
    v_salary NUMBER := 30000;
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_salary);
END;
/

Furthermore, variables can store numbers, characters, dates, and other supported data types.

Q95. What are conditional statements in PL/SQL?

Conditional statements execute code based on specified conditions.

Common conditional statements include:

• IF
• IF-ELSE
• IF-ELSIF-ELSE
• CASE

Q96. What are loops in PL/SQL?

Loops execute a group of statements repeatedly.

Oracle supports three common loop types:

• Simple LOOP
• WHILE LOOP
• FOR LOOP

Example:

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

Q97. What is a cursor in Oracle?

A cursor is a mechanism for processing query results.

It allows PL/SQL programs to retrieve and process rows individually when needed.

There are two main cursor types:

1. Implicit cursor
2. Explicit cursor

Q98. What is the difference between implicit and explicit cursors?

An implicit cursor is automatically managed by Oracle for SQL statements. However, developers explicitly declare and control explicit cursors.

Q99. What are cursor attributes in Oracle?

Cursor attributes provide information about cursor execution.

Important attributes include:

• %FOUND – Checks whether a row was affected or fetched.
• %NOTFOUND – Checks whether no row was affected or fetched.
• %ROWCOUNT – Returns the number of rows affected or fetched.
• %ISOPEN – Checks whether an explicit cursor remains open.

Q100. What is exception handling in PL/SQL?

Exception handling manages errors that occur during program execution.

For example:

BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE emp_id = 999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error');
END;
/

Common predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE.

Q101. What is the difference between predefined and user-defined exceptions?

Predefined exceptions are already defined by Oracle. In contrast, developers create user-defined exceptions for specific application conditions.

For example, you can create an exception when an employee’s salary falls below a business requirement.

Oracle Interview Questions on Procedures, Functions and Triggers

Procedures, functions, and triggers are important PL/SQL programming concepts. Therefore, developers should understand their differences and practical applications.

Q102. What is a stored procedure in Oracle?

A stored procedure is a named PL/SQL program stored inside the database.

It performs specific operations and can accept input and output parameters.

CREATE OR REPLACE PROCEDURE greet_employee
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome to Oracle');
END;
/

Q103. What is a function in Oracle?

A function is a PL/SQL program that returns a value.

CREATE OR REPLACE FUNCTION get_bonus
RETURN NUMBER
IS
BEGIN
    RETURN 5000;
END;
/

Q104. What is the difference between a procedure and a function?

Q105. What is a trigger in Oracle?

A trigger is a stored PL/SQL program that executes automatically when a specified database event occurs.

For example, a trigger can record changes made to employee salary information.

Q106. What are the different types of triggers in Oracle?

Common trigger classifications include:

• BEFORE triggers
• AFTER triggers
• INSTEAD OF triggers
• Row-level triggers
• Statement-level triggers
• DDL triggers
• Database event triggers

Q107. What is the difference between BEFORE and AFTER triggers?

BEFORE triggers execute before the triggering operation. Conversely, AFTER triggers execute after the operation.

For example, a BEFORE INSERT trigger can validate incoming employee information.

Q108. What is a package in Oracle?

A package is a database object that groups related procedures, functions, variables, cursors, and other PL/SQL elements.

It generally contains two parts:

1. Package Specification
2. Package Body

Furthermore, packages improve code organisation and reusability.

Q109. What is the difference between package specification and package body?

The package specification declares publicly accessible package elements. Meanwhile, the package body contains their implementation.

Therefore, users can understand the available package operations without examining the implementation.

Q110. What are IN, OUT, and IN OUT parameters in PL/SQL?

These parameters define how values move between calling programs and procedures.

• IN – Passes values into a procedure.
• OUT – Returns values from a procedure.
• IN OUT – Accepts an input value and returns an updated value.

Q111. What is dynamic SQL in Oracle?

Dynamic SQL allows programs to construct and execute SQL statements at runtime.

Oracle commonly uses EXECUTE IMMEDIATE for dynamic SQL execution.

BEGIN
    EXECUTE IMMEDIATE
        'UPDATE employees
         SET salary = 45000
         WHERE emp_id = 101';
END;
/

However, validate user input and use bind variables to reduce SQL injection risks.

Oracle Interview Questions on Performance and Database Administration

Database performance and administration questions are important for experienced candidates. Moreover, these concepts help developers write efficient queries and maintain reliable databases.

Q112. What is query optimisation in Oracle?

Query optimisation improves SQL execution efficiency.

Oracle’s Cost-Based Optimizer evaluates possible execution plans and chooses one based on available statistics and estimated costs.

Q113. What is an execution plan in Oracle?

An execution plan describes how Oracle intends to execute a SQL statement.

It shows operations such as table access, index scans, joins, and sorting.

You can inspect an execution plan using:

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE emp_id = 101;

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY()
);

Q114. What is the difference between INDEX SCAN and FULL TABLE SCAN?

An index scan accesses records through an index. A full table scan reads the table’s blocks.

However, a full table scan is not always slower. Oracle may choose it when many rows need retrieval.

Q115. What is database backup in Oracle?

A database backup creates a recoverable copy of database files and related information.

Oracle Recovery Manager (RMAN) is commonly used for backup and recovery operations.

Q116. What is RMAN in Oracle?

RMAN stands for Recovery Manager. It is an Oracle utility used to back up, restore, and recover databases.

Furthermore, RMAN supports incremental backups and backup validation.

Q117. What is database recovery?

Database recovery restores a database to a consistent state after a failure.

Oracle uses backups, redo information, and recovery mechanisms to restore database availability.

Q118. What is an Oracle listener?

An Oracle Listener is a network service that receives incoming database connection requests.

It helps establish communication between client applications and Oracle Database services.

Q119. What is database locking in Oracle?

Locking controls simultaneous access to database resources.

For example, when one transaction modifies a row, Oracle uses row-level locking to protect that modification from conflicting changes.

Q120. What is a deadlock in Oracle?

A deadlock occurs when two or more transactions wait for resources held by each other.

For example, Transaction A holds Row 1 and waits for Row 2, while Transaction B holds Row 2 and waits for Row 1.

Oracle detects the deadlock and rolls back one of the affected statements.

Questions Based on Real-Time SQL Scenarios

Scenario-based questions help interviewers understand how candidates solve practical database problems. Therefore, practise writing queries rather than simply memorising definitions.

Consider the following sample Employees table:

Q121. How do you find the highest salary in an employee table?

Use the MAX() aggregate function.

SELECT MAX(salary)
FROM employees;

Output: 60000.

Q122. How do you find the lowest salary?

Use the MIN() function.

SELECT MIN(salary)
FROM employees;

Output: 35000.

Q123. How do you calculate the average salary?

Use the AVG() function.

SELECT AVG(salary)
FROM employees;

Output: 47000.

Q124. How do you count employees in each department?

Use GROUP BY and COUNT().

SELECT department,
       COUNT(*) AS total_employees
FROM employees
GROUP BY department;

Q125. How do you find employees earning more than 40,000?

Use the WHERE condition.

SELECT *
FROM employees
WHERE salary > 40000;

Q126. How do you find the second-highest salary?

Use DENSE_RANK() to rank distinct salary values.

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (
               ORDER BY salary DESC
           ) rnk
    FROM employees
)
WHERE rnk = 2;

Output: 50000.

Q127. How do you find employees with the same salary?

Use GROUP BY and HAVING to identify repeated salary values.

SELECT salary,
       COUNT(*) AS total
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

Q128. How do you find the top three highest-paid employees?

Use ROW_NUMBER() or another ranking function.

SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (
               ORDER BY salary DESC
           ) rn
    FROM employees e
)
WHERE rn <= 3;

Q129. How do you retrieve employees whose names start with A?

Use the LIKE operator.

SELECT *
FROM employees
WHERE emp_name LIKE 'A%';

Q130. How do you retrieve the last five records from a table?

Use ROW_NUMBER() with an appropriate ordering column.

SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (
               ORDER BY emp_id DESC
           ) rn
    FROM employees e
)
WHERE rn <= 5;

This retrieves the five highest employee IDs. Note that relational tables have no inherent row order.

Q131. How do you find employees who joined in a particular year?

Use a date range for efficient filtering.

SELECT *
FROM employees
WHERE hire_date >= DATE '2025-01-01'
AND hire_date < DATE '2026-01-01';

Q132. How do you find employees who earn more than their department’s average salary?

Use a correlated subquery.

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Q133. How do you update employee salaries by 10%?

Use an UPDATE statement.

UPDATE employees
SET salary = salary * 1.10;

Q134. How do you retrieve employees whose salary is between 30,000 and 50,000?

Use BETWEEN.

SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Q135. How do you find employees without a department?

Use IS NULL.

SELECT *
FROM employees
WHERE department_id IS NULL;

Q136. How do you display department-wise maximum salary?

Use MAX() with GROUP BY.

SELECT department_id,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

Q137. How do you find the total salary paid by each department?

Use SUM() and GROUP BY.

SELECT department_id,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Q138. How do you retrieve the employees who joined most recently?

Use ORDER BY and FETCH FIRST.

SELECT *
FROM employees
ORDER BY hire_date DESC
FETCH FIRST 5 ROWS ONLY;

Q139. How do you find duplicate employee names?

Use GROUP BY and HAVING.

SELECT emp_name,
       COUNT(*)
FROM employees
GROUP BY emp_name
HAVING COUNT(*) > 1;

Q140. How do you retrieve the top two salaries in each department?

Use DENSE_RANK() with PARTITION BY.

SELECT *
FROM (
    SELECT e.*,
           DENSE_RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) rnk
    FROM employees e
)
WHERE rnk <= 2;

This includes employees with tied salaries at the second rank.

Questions for Experienced Professionals

Experienced professionals should understand advanced SQL, transaction processing, database performance, and PL/SQL optimisation. Additionally, they should be able to explain their real-time project experience.

Q141. What is an autonomous transaction in Oracle?

An autonomous transaction is an independent transaction that runs separately from its calling transaction.

For example, developers can use autonomous transactions to record error logs even when the main transaction rolls back.

Q142. What is bulk collect in PL/SQL?

BULK COLLECT retrieves multiple rows into PL/SQL collections in one operation.

Therefore, it can reduce context switching between the SQL and PL/SQL engines.

DECLARE
    TYPE emp_list IS TABLE OF employees.emp_name%TYPE;
    v_names emp_list;
BEGIN
    SELECT emp_name
    BULK COLLECT INTO v_names
    FROM employees;

    FOR i IN 1..v_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_names(i));
    END LOOP;
END;
/

Q143. What is FORALL in PL/SQL?

FORALL executes DML statements using collection values in batches.

It improves performance when processing multiple records through PL/SQL collections.

Q144. What is the difference between BULK COLLECT and FORALL?

BULK COLLECT retrieves multiple rows from SQL into collections. Conversely, FORALL sends multiple DML operations from collections to SQL.

Q145. What is partitioning in Oracle?

Partitioning divides large tables and indexes into smaller, manageable sections.

For example, a sales table can be partitioned by transaction date.

This approach can improve manageability and query performance when suitable partition pruning occurs.

Q146. What is partition pruning?

Partition pruning allows Oracle to access only the relevant partitions instead of scanning every partition.

Consequently, it can reduce the amount of data processed by a query.

Q147. What is database normalisation?

Normalisation organises database tables to reduce unnecessary duplication and improve data integrity.

Common normal forms include:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)

Q148. What is denormalisation?

Denormalisation intentionally introduces redundancy or combines data to improve certain read operations.

However, it can increase storage requirements and make data updates more complex.

Q149. What is ACID in database transactions?

ACID represents four important transaction properties.

• Atomicity – All operations succeed or the transaction rolls back.
• Consistency – Transactions maintain database rules.
• Isolation – Concurrent transactions follow defined isolation rules.
• Durability – Committed changes survive failures.

Q150. What is the difference between OLTP and OLAP?

Q151. How do you improve a slow SQL query in Oracle?

First, examine its execution plan. Next, check indexes, statistics, joins, and filtering conditions.

Additionally, avoid unnecessary columns, reduce repeated calculations, and check whether the query processes excessive data.

Q152. What is a database deadlock, and how do you prevent it?

A deadlock happens when transactions wait for resources held by each other.

You can reduce deadlocks by accessing tables and rows in a consistent order, keeping transactions short, and avoiding unnecessary locks.

Q153. What is Flashback Query in Oracle?

Flashback Query allows users to retrieve historical data from an earlier point in time, subject to available undo information and database configuration.

Example:

SELECT *
FROM employees
AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '10' MINUTE);

Q154. What is Data Pump in Oracle?

Oracle Data Pump is a utility for exporting and importing database objects and data.

It supports logical backup, migration, and data transfer operations.

Its main utilities are:

• expdp
• impdp

Q155. What is the difference between a database instance and a database?

An instance consists of memory structures and background processes. A database consists of physical storage files.

Therefore, an instance manages access to the database.

Frequently Asked Questions About Oracle Interviews

1. What are the most commonly asked Oracle Interview Questions for freshers?

Freshers commonly face questions about SQL commands, primary keys, foreign keys, joins, constraints, functions, and subqueries. Additionally, interviewers may ask basic PL/SQL concepts. Practising simple SQL queries helps beginners develop confidence and improve technical interview performance.

2. Is Oracle difficult to learn for beginners?

No, Oracle is manageable for beginners who understand database fundamentals. Start with SQL commands, tables, constraints, and joins. Furthermore, practise PL/SQL programming and real-time queries. Consistent practice and structured training make learning easier.

3. What is the difference between Oracle SQL and Oracle PL/SQL?

Oracle SQL retrieves and manipulates database information. However, PL/SQL adds programming features such as variables, loops, conditions, procedures, and exception handling. Both work together to develop database applications and automate database operations.

4. How much time does it take to learn Oracle?

Beginners can understand basic Oracle SQL within a few weeks. However, mastering advanced SQL, PL/SQL, procedures, triggers, and database administration requires additional practice. A structured learning programme and real-time projects can accelerate skill development.

5. What are the career opportunities after learning Oracle?

Oracle skills can support careers such as SQL Developer, Oracle PL/SQL Developer, Oracle Database Administrator, Database Support Engineer, and Database Developer. Furthermore, professionals with practical SQL and database administration experience can explore specialised enterprise database roles.

6. Which Oracle topics should experienced professionals prepare for?

Experienced professionals should focus on advanced SQL, analytical functions, execution plans, indexing, partitioning, PL/SQL optimisation, bulk processing, transactions, and database recovery. Additionally, they should prepare real-time project scenarios and explain their performance optimisation experience.

Learn Oracle Database with Practical Training at Infycle Technologies, Chennai

Learning Oracle requires more than memorising interview answers. You must understand SQL queries, database concepts, and practical programming techniques.

At Infycle Technologies, Chennai, learners can explore structured technical training designed to develop practical database skills.

Our Oracle training approach focuses on important industry-relevant topics.

What should an Oracle training programme cover?

• Oracle Database fundamentals
• SQL commands and queries
• Joins and subqueries
• Constraints and database objects
• SQL functions and operators
• Advanced SQL queries
• PL/SQL programming
• Procedures and functions
• Packages and triggers
• Real-time database projects
• Interview preparation

Furthermore, practical exercises help learners understand how databases work in real business applications.

Explore the Oracle course at Infycle Technologies and strengthen your database programming skills.

Conclusion

Preparing for Oracle interviews requires a strong understanding of SQL, PL/SQL, database architecture, and practical query writing.

These 155 Oracle interview questions and answers cover essential topics for freshers and experienced professionals. Furthermore, practising the examples can help you improve your technical knowledge and problem-solving ability.

Start with basic SQL commands. Next, practise joins, subqueries, and analytical functions. Finally, strengthen your PL/SQL knowledge through procedures, functions, triggers, and real-time projects.

If you are planning to build a career in database development, choosing the right training programme is an important step.

Start Your Oracle Learning Journey with Infycle Technologies!

Looking for an Oracle course in Chennai? Join Infycle Technologies to develop practical database skills through structured training and hands-on exercises.