Skip to content

Oracle Database Interview Questions

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)
);

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;

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;