An Oracle technical interview cannot be cracked merely by memorizing the syntax, but also requires an understanding of the architecture of relational database management systems (RDBMS), memory structures such as the System Global Area (SGA), and database structures. Be it a fresher learning about the difference between SQL and PL/SQL or a database administrator working on execution plans, proper preparation will play an important role in making you stand out.
This article consists of the commonly asked Oracle interview questions along with accurate answers to help you prepare for Oracle interviews. For learning the fundamentals of RDBMS and understanding every complex topic in detail, download our Oracle course syllabus.
Oracle Interview Questions and Answers for Freshers
1. What is an Oracle Database?
Oracle database is a Multi-Model Relational Database Management System developed by Oracle corporation that uses structured tables, schemas, and SQL to provide a secure environment for storage, management and manipulation of huge amounts of information in enterprise applications.
2. What is the difference between SQL and PL/SQL?
SQL is a declarative language that is used to perform individual data manipulations such as queries and updates. PL/SQL is an extension of Oracle SQL that adds loops, variables and error-handling.
3. What are DDL, DML, and DCL commands?
- DDL (Data Definition Language): Creates structure (CREATE, ALTER, DROP).
- DML (Data Manipulation Language): Manipulates rows (INSERT, UPDATE, DELETE).
- DCL (Data Control Language): Provides permissions management (GRANT, REVOKE).
4. What is the Primary Key and how does it differ from a Unique Key?
A Primary Key uniquely identifies each row in a table and strictly forbids null values. A Unique Key also ensures data uniqueness across a column but allows a single NULL value within that column.
5. State the difference between DELETE, TRUNCATE, and DROP.
DELETE is a DML operation that deletes selected rows through a WHERE clause. Truncate is a faster DDL operation that deletes all rows without the log of each delete process, while DROP will destroy the complete structure of the table from memory.
6. State what a cursor is in Oracle and state its types.
Cursor is a memory work area created by the Oracle database to execute the SQL statement. The two types are Implicit cursor and explicit cursor, where Implicit cursor is automatically created for the execution of single DML operations, while explicit cursors are manually defined by the programmer.
7. State what Trigger is in Oracle?
A trigger is a stored PL/SQL block that will automatically trigger off when there is a particular event. These events can be DML operations and system events on the table.
8. State the difference between a function and a procedure.
A function is an operation that always returns a single value to the environment and can be called inside the SQL statement, while a procedure performs some operations and returns one or more than one values but cannot be called inside the SQL statement.
9. What is a View? Why do we use it?
A View is a logical table formed through the execution of a pre-configured SQL query and it does not hold any physical data but helps in providing data security by limiting the access of users to only particular columns and complex joins.
10. Explain Oracle sequence.
Oracle sequence is a database object that provides automatically incremented, unique sequential integers. They are commonly used for the generation of unique auto-incremented primary keys at the time of insertion.
Learn from scratch with our Oracle tutorial for beginners.
11. Explain the foreign key constraint and what it enforces.
A foreign key is a constraint that references the primary key in another table. It enforces the rule of referential integrity, making sure that there are corresponding records in the child tables with respect to the parent table.
12. Explain the use of NVL and NVL2 functions.
The NVL(expr1, expr2) function replaces a NULL value in expr1 with the specified string expr2. NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not null, otherwise returning expr3.
13. What is the meaning of Index in Oracle?
An Index is a performance-tuning database object associated with tables. It creates a pointer for fast look-up, which allows the Oracle Engine to access the required rows without doing time-consuming table scans.
14. Describe GROUP BY and HAVING statements.
The GROUP BY clause groups rows sharing identical data values into single summary rows. The HAVING clause filters those summarized group results based on aggregate conditions, acting like a WHERE clause for groups.
15. What are Joins in Oracle? Name the types.
Joins combine columns from multiple tables based on related logical columns. Main types include Inner Join (matching data), Left/Right Outer Join (all data from one side), and Full Outer Join (all records).
Oracle Interview Questions and Answers for Experienced Candidates
1. Describe the architecture of the Oracle System Global Area (SGA). What should you do to troubleshoot “shared pool latch contention”?
SGA stands for a shared memory area that consists of the Database Buffer Cache, Redo Log Buffer, and Shared Pool, which includes Library Cache and Data Dictionary Cache.
Shared pool latch contention happens because the database does hard parsing due to SQL code that cannot be reused. Troubleshoot this problem by analyzing the AWR report and looking for high latch: shared pool wait events. You need to change the way queries in the application are written, replacing literals with bind variables.
2. Compare the physical and logical storage organization of an Oracle database. How do the tablespaces relate to datablocks, extents, and segments?
Oracle organizes its data through a structured logical hierarchy that keeps database operations physically isolated from host storage:
| Logical Structure | Physical Mapping | Description |
| Data Block | Maps to specific OS bytes. | The smallest unit of Oracle storage where raw data rows reside. |
| Extent | A grouping of contiguous data blocks. | Allocated when database objects grow. |
| Segment | A set of extents allocated for a specific database object. | e.g., a specific table or index segment. |
| Tablespace | Formed by one or more physical Datafiles. | The highest logical storage container. |
3. What is the distinction between Automated Segment Space Management (ASSM) and Manual Segment Space Management (MSSM)?
In MSSM, manual management of parameters such as PCTFREE and PCTUSED is necessary, with monitoring of the block space using very complicated freelists that lead to block contentions when there are write operations occurring simultaneously.
In ASSM, freelists are replaced by bitmap indexes that allow the monitoring of the block space in real-time. Such optimization decreases block waiting time internally and makes the segment space allocation much more efficient.
4. How would you review Oracle’s Execution Plan with the help of DBMS_XPLAN and determine a Cartesian Product or a poorly designed Join?
Use EXPLAIN PLAN FOR to get the live execution plan and use SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); to retrieve the results.
During the plan review, take note of the following red flags: the MERGE JOIN CARTESIAN clause implies the lack of join criteria, whereas the TABLE ACCESS FULL clause on a table containing millions of records implies no index at all or an invisible one. Large mismatches between estimates and actual row counts mean outdated optimizer stats.
5. Discuss the implementation of and trade-off between Nested Loops Join, Hash Join, and Sort Merge Join.
The Oracle Cost-Based Optimizer (CBO) will choose a type of join based on the size of your data and how you indexed it:
- Nested Loops Join: Iterates through a driving table and fetches matching rows from the inner table based on an index. Suitable for small amounts of data.
- Hash Join: Loads the smaller data into a hash table in memory and then scans the larger data against that hash table. Very effective for large, unindexed data.
- Sort Merge Join: First sorts each data set on the joining column and then joins them. Most effective when inequalities are used in join conditions (e.g., < or >).
Explore our Oracle Challenges and Solutions to learn more.
6. How would you go about partitioning an Oracle table to increase efficiency for large tables measured in terabytes, and what is the advantage of using local index vs. global indexes?
Partitioning divides a very large table into smaller, easily handled partitions using a certain key such as Range, Hash, or List partitioning. This helps achieve Partition Pruning, where scanning is performed only in selected partitions.
— Range partitioned transactions table
CREATE TABLE tx_history (tx_id NUMBER, tx_date DATE)
PARTITION BY RANGE (tx_date) (
PARTITION p2026_q1 VALUES LESS THAN (TO_DATE(’01-APR-2026′,’DD-MON-YYYY’)),
PARTITION p2026_q2 VALUES LESS THAN (TO_DATE(’01-JUL-2026′,’DD-MON-YYYY’))
);
Partitioning in local indexes is done directly by the base table, thus maintaining partitioning becomes easy. Global indexes cover all partitions; thus, they provide faster search on non-partitioned keys, although maintenance becomes difficult.
7. How do you avoid context switch overhead during large collection loops in between SQL Engine and PL/SQL Engine?
A regular FOR loop where SQL statements are executed for each row will lead to many context switches between the SQL and PL/SQL runtime engines, which in turn will impact the performance adversely.
This can be avoided by implementing array processing where rows will be fetched using the BULK COLLECT clause, and the rows thus fetched will be processed by passing them to the database engine using FORALL.
DECLARE
TYPE t_emp IS TABLE OF employees%ROWTYPE;
l_emp t_emp;
BEGIN
SELECT * BULK COLLECT INTO l_emp FROM employees WHERE dept_id = 10;
FORALL i IN 1..l_emp.COUNT — Processes updates in a single unified block
UPDATE emp_history SET salary = l_emp(i).salary * 1.1 WHERE emp_id = l_emp(i).emp_id;
END;
8. Describe the MVCC Model used by Oracle. How is Statement-Level Read Consistency maintained without using shared locks in the database?
The MVCC model used by Oracle uses the Undo Segments in order to maintain the fact that “readers never block writers, and writers never block readers.”
At the start of a query, Oracle takes note of the System Change Number (SCN). If during the process of the query, any transaction changes a row, then Oracle uses the blocks stored in the Undo Segment in order to create a consistent view of the data at that SCN.
9. What is the “Snapshot Too Old” exception (ORA-01555)? Which changes to the database help prevent this exception from occurring in production environments?
The ORA-01555 exception occurs because a query that takes too much time to complete requires some historical blocks stored in the Undo Segments to ensure the consistency of the reading process, yet these blocks were already overwritten by other transactions.
To solve the problem, you need to increase the UNDO_RETENTION setting of your database, allocate more space for the physical storage of your UNDOTABLESPACES, or optimize the query itself.
10. How will you handle ORA-00060 application deadlock condition and foreign key lock contention without indexing foreign keys?
In the case of deadlock, Oracle finds the circular lock, breaks it by performing a rollback of the locking query, and logs the details in the trace file.
On the other hand, foreign keys create another problem since updating a parent table’s primary key creates a full-table share lock on the child table. To handle this, one should create an index on the foreign key columns in the child table.
11. Differentiate Autonomous Transactions from normal nested PL/SQL blocks. In which scenario would you use the PRAGMA AUTONOMOUS_TRANSACTION with a procedure?
A normal nested block has the same scope of commit as its enclosing transaction. On the other hand, an Autonomous Transaction is a transaction block that operates independently and isolates its operations to commit or roll back transactions on its own.
Use the PRAGMA AUTONOMOUS_TRANSACTION directive for standalone tasks like writing to an error logging table, where you want to save log entries even if the main transaction fails and rolls back:
CREATE OR REPLACE PROCEDURE log_error (p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; — Decouples commit scope from parent
BEGIN
INSERT INTO error_logs VALUES (p_msg, SYSDATE);
COMMIT; — Commits safely without affecting the main application transaction
END;
12. Describe the process of setting up Oracle Fine-Grained Auditing (FGA) compared to database auditing. What is involved in setting up a policy for row-level security?
Database auditing simply audits the actions performed on the table, such as SELECT or UPDATE, while ignoring the data. Fine-Grained Auditing (FGA) lets you audit based on particular columns and their values.
DBMS_FGA.ADD_POLICY is the package used to create a policy where there will be no audit entry made in case the sensitive data is not accessed, for instance, when a user selects salary values exceeding 50,000 from the salary column.
13. How does the Oracle Recovery Manager (RMAN) manage backup metadata, and how does the procedure of Media Recovery differ from that of Instance Recovery?
Oracle Recovery Manager (RMAN) manages its metadata by storing it either in the control file of the target database or in the external Recovery Catalog database.
Instance Recovery occurs automatically during the startup process of a database in case of a crash, where the online redo log files are used to roll forward the transactions not yet committed. The Media Recovery process is initiated manually following the physical failure of a disk, which involves restoring the backup data using the archive logs.
14. What is an Oracle Data Guard, and what are the structural differences between a Physical Standby, Logical Standby, and Active Data Guard?
An Oracle Data Guard creates a duplicate database as a standby database to increase availability and ensure continuity in case of a disaster.
- Physical Standby: A bit-for-bit replica of the primary database that keeps synchronized using redo log information.
- Logical Standby: Redo logs are converted to SQL statements and executed locally, and therefore, it is possible to have another index and table organization.
- Active Data Guard: An improved physical standby database that is always available for performing read-only queries and backups.
15. How do you set up an Oracle Flashback Query to help diagnose or fix a data corruption problem in your application without restoring the database from its backups?
Oracle Flashback works by utilizing the information stored in the Undo Segments to perform a Flashback Query on a table as it appeared at a particular point in time or SCN. As such, it is straightforward to recover from accidental modifications using Oracle Flashback by recovering rows that existed historically:
— Querying historical data from 2 hours ago
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘2’ HOUR)
WHERE employee_id = 101; — Retrieves the row state before corruption
Accelerate your career with our Oracle course in Chennai.
Conclusion
Passing an Oracle technology interview demands that you demonstrate knowledge in database management as well as complex PL/SQL coding skills. Being able to control layers such as the SGA, generate the best execution plan through DBMS_XPLAN, and take advantage of features like BULK COLLECT shows your competency to handle secure relational databases.
Are you looking for ways to enhance your database knowledge and gain Oracle certification? At SLA, which is the best-known IT training center in Chennai, you will get specialized training in Oracle from working database administrators.