Oracle Database Interview Questions

Oracle Database Interview Questions

SQL & Core Concepts

Q: What is the difference between ROWNUM and ROW_NUMBER()?

-- ROWNUM — assigned before ORDER BY, unreliable for pagination
SELECT * FROM employees WHERE ROWNUM <= 10;  -- first 10 rows (unordered)

-- ROW_NUMBER() — window function, assigned after ORDER BY
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees e
) WHERE rn BETWEEN 11 AND 20;  -- page 2

---

Q: What is a DUAL table?

DUAL is a special one-row, one-column table used to evaluate expressions:

SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT SYS_GUID() FROM DUAL;
SELECT USER FROM DUAL;

---

Q: What is the difference between NVL, NVL2, NULLIF, and COALESCE?

-- NVL — replace NULL with a value
SELECT NVL(commission, 0) FROM employees;

-- NVL2 — if not null return expr1, else expr2
SELECT NVL2(commission, 'Has commission', 'No commission') FROM employees;

-- NULLIF — return NULL if two values are equal
SELECT NULLIF(salary, 0) FROM employees;  -- NULL if salary = 0

-- COALESCE — return first non-NULL (ANSI standard, preferred)
SELECT COALESCE(mobile, home_phone, work_phone, 'No phone') FROM contacts;

---

Q: What are Oracle sequences?

CREATE SEQUENCE emp_seq
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

-- Use in INSERT
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'Alice');

-- Get current value (must call NEXTVAL first in session)
SELECT emp_seq.CURRVAL FROM DUAL;

-- Oracle 12c+ identity column (simpler)
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(100)
);

---

PL/SQL

Q: What is PL/SQL and what are its key constructs?

PL/SQL is Oracle’s procedural extension to SQL. Key constructs:

DECLARE
  v_name    VARCHAR2(100);
  v_salary  NUMBER := 0;
  v_count   PLS_INTEGER;
BEGIN
  SELECT name, salary INTO v_name, v_salary
  FROM employees WHERE id = 1;

  IF v_salary > 100000 THEN
    DBMS_OUTPUT.PUT_LINE('High earner: ' || v_name);
  ELSIF v_salary > 50000 THEN
    DBMS_OUTPUT.PUT_LINE('Mid earner: ' || v_name);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Entry level: ' || v_name);
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

---

Q: What is the difference between a procedure and a function in PL/SQL?

-- Procedure — no return value, used for actions
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_amount IN NUMBER
) AS
BEGIN
  UPDATE employees SET salary = salary + p_amount WHERE id = p_emp_id;
  COMMIT;
END;

-- Function — returns a value, usable in SQL
CREATE OR REPLACE FUNCTION get_full_name(p_id IN NUMBER)
RETURN VARCHAR2 AS
  v_name VARCHAR2(200);
BEGIN
  SELECT first_name || ' ' || last_name INTO v_name
  FROM employees WHERE id = p_id;
  RETURN v_name;
END;

-- Use function in SQL
SELECT get_full_name(101) FROM DUAL;

---

Q: What are cursors in PL/SQL?

-- Implicit cursor (automatic for single-row queries)
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
END;

-- Explicit cursor
DECLARE
  CURSOR emp_cur IS
    SELECT id, name, salary FROM employees WHERE dept_id = 10;
  v_emp emp_cur%ROWTYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO v_emp;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.name || ': ' || v_emp.salary);
  END LOOP;
  CLOSE emp_cur;
END;

-- Cursor FOR loop (preferred — auto open/fetch/close)
BEGIN
  FOR emp IN (SELECT id, name FROM employees WHERE dept_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(emp.name);
  END LOOP;
END;

---

Q: What are triggers?

-- BEFORE INSERT trigger — auto-populate audit fields
CREATE OR REPLACE TRIGGER trg_employees_bi
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_at := SYSDATE;
  :NEW.created_by := USER;
  IF :NEW.id IS NULL THEN
    :NEW.id := emp_seq.NEXTVAL;
  END IF;
END;

-- AFTER UPDATE trigger — audit log
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
  VALUES (:OLD.id, :OLD.salary, :NEW.salary, SYSDATE);
END;

---

Indexes & Performance

Q: What types of indexes does Oracle support?

-- B-Tree (default) — equality and range queries
CREATE INDEX idx_emp_name ON employees(last_name, first_name);

-- Bitmap — low-cardinality columns (gender, status), data warehouses
CREATE BITMAP INDEX idx_emp_status ON employees(status);

-- Function-based — index on expression
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';  -- uses index

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Composite index — column order matters
CREATE INDEX idx_orders ON orders(customer_id, order_date, status);
-- Efficient for: customer_id alone, customer_id+order_date, all three
-- NOT efficient for: order_date alone

---

Q: What is partitioning?

Partitioning divides large tables into smaller, manageable pieces:

-- Range partitioning — by date range
CREATE TABLE orders (
  id NUMBER, order_date DATE, amount NUMBER
)
PARTITION BY RANGE (order_date) (
  PARTITION p2022 VALUES LESS THAN (DATE '2023-01-01'),
  PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- List partitioning — by discrete values
CREATE TABLE employees (id NUMBER, region VARCHAR2(20))
PARTITION BY LIST (region) (
  PARTITION p_north VALUES ('NORTH', 'NORTHEAST'),
  PARTITION p_south VALUES ('SOUTH', 'SOUTHEAST'),
  PARTITION p_other VALUES (DEFAULT)
);

Benefits: partition pruning (query only relevant partitions), faster maintenance.

---

Q: What is the difference between TRUNCATE and DELETE in Oracle?

	DELETE	TRUNCATE
Logging	Full redo log	Minimal
Rollback	Yes	No (DDL)
Triggers	Fires	Does not fire
WHERE clause	Yes	No
Flashback	Yes	No (by default)
Speed	Slower	Much faster

---

Q: What is the Oracle Optimizer and how do you influence it?

-- View execution plan
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Gather statistics (optimizer relies on these)
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- Hints — override optimizer decisions (use sparingly)
SELECT /*+ INDEX(e idx_emp_dept) */ * FROM employees e WHERE dept_id = 10;
SELECT /*+ FULL(e) */ * FROM employees e;
SELECT /*+ PARALLEL(e, 4) */ * FROM employees e;

---

Q: What is a materialized view?

-- Materialized view — stores query results physically
CREATE MATERIALIZED VIEW mv_dept_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

-- Refresh manually
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_SUMMARY');

-- Fast refresh (requires materialized view log)
CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID, SEQUENCE (dept_id, salary);

---

Q: What is Oracle Flashback?

-- Query data as it was at a point in time
SELECT * FROM employees AS OF TIMESTAMP (SYSDATE - INTERVAL '1' HOUR);

-- Flashback a row
SELECT * FROM employees AS OF SCN 1234567 WHERE id = 101;

-- Flashback entire table (requires FLASHBACK privilege)
FLASHBACK TABLE employees TO TIMESTAMP (SYSDATE - INTERVAL '30' MINUTE);

-- Flashback query using SCN
SELECT * FROM employees AS OF SCN DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER - 1000;