Oracle Database runs many banking, telecom and ERP systems, so Oracle skills are in demand for DBA, developer and support roles. These Oracle interview questions and answers cover architecture, SQL, PL/SQL, performance and backup, with short examples.
Architecture
1. What is the difference between an instance and a database?
The database is the set of physical files on disk: data files, control files and redo log files. The instance is the memory structures (SGA) and background processes that access those files. One instance usually opens one database, although Oracle RAC runs several instances against one database.
2. Describe the SGA and PGA.
The System Global Area (SGA) is shared memory, including the buffer cache, shared pool and redo log buffer. The Program Global Area (PGA), by contrast, is private memory for each server process, used for sorting and session data.
3. Name some key background processes.
- DBWn: writes changed blocks from the buffer cache to data files.
- LGWR: writes redo entries to the online redo logs.
- CKPT: records checkpoints.
- SMON: performs instance recovery.
- PMON: cleans up after failed processes.
- ARCn: archives filled redo logs.
4. What is a tablespace?
A tablespace is a logical storage container made of one or more data files. Tables and indexes live in tablespaces. Common examples are SYSTEM, SYSAUX, UNDO, TEMP and USERS.
5. Explain the multitenant architecture.
Since 12c, a container database (CDB) can hold several pluggable databases (PDBs). Each PDB looks like a separate database to applications, which simplifies consolidation and upgrades. In recent releases, multitenant is the only supported architecture.
SQL
6. NVL vs NVL2 vs COALESCE?
NVL(a, b)returns b when a is NULL.NVL2(a, b, c)returns b when a is not NULL, and c when it is NULL.COALESCE(a, b, c)returns the first non-NULL value, and it is ANSI standard.
7. DELETE vs TRUNCATE vs DROP?
| Command | What it removes | Rollback? |
|---|---|---|
| DELETE | Selected rows (with WHERE) | Yes, it is DML |
| TRUNCATE | All rows, quickly | No, it is DDL |
| DROP | The whole table and its structure | No (unless the recycle bin is used) |
8. How do you get the first 10 rows?
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;Older versions used ROWNUM in a subquery.
9. What is the difference between UNION and UNION ALL?
UNION removes duplicates, which needs a sort. UNION ALL keeps all rows, so it is faster.
10. What is an index, and when can it hurt?
An index speeds up lookups, usually with a B-tree structure. However, too many indexes slow down inserts and updates, and use storage. Bitmap indexes suit low-cardinality columns in data warehouses, not busy OLTP tables.
PL/SQL
11. What is PL/SQL?
PL/SQL is Oracle’s procedural extension to SQL. It adds variables, loops, conditions and exception handling, and it runs inside the database.
12. Procedure vs function?
A function must return a value and can be used in SQL statements. A procedure performs actions and returns values only through OUT parameters.
CREATE OR REPLACE PROCEDURE raise_salary(p_empid IN NUMBER, p_amt IN NUMBER) AS
BEGIN
UPDATE employees SET salary = salary + p_amt WHERE employee_id = p_empid;
END;
/13. What is a trigger?
A trigger runs automatically when an event happens, such as an INSERT on a table. Use triggers sparingly, because hidden logic makes systems harder to maintain.
14. What is a cursor?
A cursor points to the result set of a query. Implicit cursors handle single statements, while explicit cursors let you loop through rows. For bulk work, BULK COLLECT and FORALL are much faster.
Performance, backup and security
15. How do you analyse a slow query?
Check the execution plan with EXPLAIN PLAN or DBMS_XPLAN, confirm statistics are current, and look at AWR reports where licensed.
16. How do you back up an Oracle database?
Use RMAN (Recovery Manager) for full and incremental backups, run the database in ARCHIVELOG mode for point-in-time recovery, and test restores regularly. Data Pump (expdp/impdp) handles logical exports, but it is not a backup strategy on its own.
17. Name some basic security practices.
Lock or remove default accounts, grant least privilege through roles, enable unified auditing, encrypt sensitive data with TDE where licensed, and patch with the quarterly Release Updates.
For more database preparation, see our PostgreSQL and MySQL interview questions.
Keep preparing
Practise each answer out loud, and run the commands in a lab rather than memorising them. Interviewers often ask follow-up questions, so make sure you understand why each answer is true.
Sources
Preparing for an interview? Browse more questions in our interview preparation hub. For businesses, we also offer security services such as VAPT, server hardening and ISO 27001 readiness.
