Oracle Interview Questions

Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation. It's a leading enterprise database solution known for:

• High performance and scalability
• Advanced security features
• Comprehensive data management capabilities
• Support for ACID transactions
• PL/SQL procedural language extension
• Cloud and on-premises deployment options

Oracle Database is widely used in large enterprises for mission-critical applications.

Oracle Database consists of several key components:

1. Instance: Comprises memory structures (SGA) and background processes
2. Database: Physical files that store data (datafiles, control files, redo logs)
3. Tablespaces: Logical storage units that group related data
4. Schemas: Collections of database objects owned by a user
5. Data Dictionary: Metadata about the database structure
6. PL/SQL Engine: Processes PL/SQL program units
7. SQL Engine: Parses and executes SQL statements

Oracle offers several editions of its database:

1. Enterprise Edition: Full-featured version with all options
2. Standard Edition: Mid-range version with core features
3. Express Edition (XE): Free, lightweight version for learning
4. Personal Edition: Single-user version with Enterprise features
5. Oracle Cloud Services: Cloud-based database services

Each edition has different licensing costs and feature sets appropriate for different use cases.

To install Oracle Database:

1. Windows:
   • Download Oracle Database installer from Oracle website
   • Run setup.exe as administrator
   • Choose installation option (typical or advanced)
   • Specify Oracle home directory and database configuration
   • Set SYS and SYSTEM passwords
   • Complete the installation
2. Linux:
   • Download Oracle Database RPM or ZIP files
   • Install prerequisite packages
   • Create Oracle user and groups
   • Set kernel parameters
   • Run the installer with root privileges
   • Configure database using Database Configuration Assistant (DBCA)

After installation, verify it's working by connecting to the database using SQL*Plus.

Oracle provides several client tools for database administration and development:

• SQL*Plus: Command-line interface for SQL and PL/SQL
• SQL Developer: Free graphical tool for database development
• Enterprise Manager: Web-based administration console
• Data Pump: For data export/import operations
• RMAN: Recovery Manager for backups
• Net Manager: For network configuration

To create a new database in Oracle:

1. Use the Database Configuration Assistant (DBCA) GUI tool
2. Or manually create using CREATE DATABASE command in SQL*Plus

Steps for manual creation:

-- Create initialization parameter file (init.ora)
-- Create necessary directories
-- Set ORACLE_SID environment variable
-- Start SQL*Plus and connect as SYSDBA
SQL> STARTUP NOMOUNT;
SQL> CREATE DATABASE mydb
     USER SYS IDENTIFIED BY password
     USER SYSTEM IDENTIFIED BY password
     LOGFILE GROUP 1 ('/path/redo01.log') SIZE 100M,
             GROUP 2 ('/path/redo02.log') SIZE 100M
     MAXLOGFILES 5
     MAXLOGMEMBERS 5
     MAXLOGHISTORY 1
     MAXDATAFILES 100
     CHARACTER SET AL32UTF8
     NATIONAL CHARACTER SET AL16UTF16
     EXTENT MANAGEMENT LOCAL
     DATAFILE '/path/system01.dbf' SIZE 325M REUSE
     SYSAUX DATAFILE '/path/sysaux01.dbf' SIZE 325M REUSE
     DEFAULT TABLESPACE users
       DATAFILE '/path/users01.dbf' SIZE 500M REUSE
     DEFAULT TEMPORARY TABLESPACE temp
       TEMPFILE '/path/temp01.dbf' SIZE 20M REUSE
     UNDO TABLESPACE undotbs
       DATAFILE '/path/undotbs01.dbf' SIZE 200M REUSE;

After creation, run catalog.sql and catproc.sql scripts to build data dictionary views and PL/SQL packages.

Tablespaces are logical storage units in Oracle. To create one:

CREATE TABLESPACE mytablespace
DATAFILE '/path/mytablespace01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Key options:

• DATAFILE specifies the physical datafile
• AUTOEXTEND allows the file to grow automatically
• EXTENT MANAGEMENT LOCAL uses bitmaps for space management
• SEGMENT SPACE MANAGEMENT AUTO uses automatic segment space management

Basic table creation syntax in Oracle:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    department_id NUMBER,
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
) TABLESPACE users;

Oracle-specific features:

• VARCHAR2 data type (preferred over VARCHAR)
• NUMBER type for numeric values
• TABLESPACE clause to specify storage location
• Named constraints (emp_dept_fk)

Oracle supports various data types:

1. Character Types:
   • CHAR(n) - Fixed-length (padded with spaces)
   • VARCHAR2(n) - Variable-length (recommended)
   • NCHAR(n), NVARCHAR2(n) - Unicode versions
   • CLOB, NCLOB - Large character objects
2. Numeric Types:
   • NUMBER - Generic numeric type (NUMBER(p,s) for precision/scale)
   • BINARY_FLOAT, BINARY_DOUBLE - IEEE floating-point
3. Date/Time Types:
   • DATE - Date and time (to second)
   • TIMESTAMP - Higher precision (to fractions of a second)
   • INTERVAL - Time intervals
4. Binary Types:
   • BLOB - Binary large object
   • BFILE - External binary file reference
   • RAW(n) - Raw binary data
5. Special Types:
   • ROWID - Physical row identifier
   • UROWID - Universal row identifier
   • XMLType - XML data

Oracle supports standard SQL constraints with some extensions:

1. PRIMARY KEY - Enforces unique, non-null values

   CREATE TABLE table_name (
       id NUMBER CONSTRAINT pk_id PRIMARY KEY,
       ...
   );

2. FOREIGN KEY - Referential integrity with ON DELETE options

   CREATE TABLE orders (
       order_id NUMBER PRIMARY KEY,
       customer_id NUMBER,
       CONSTRAINT fk_customer 
           FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
           ON DELETE CASCADE
   );

3. CHECK - Complex validation conditions

   CREATE TABLE employees (
       salary NUMBER CHECK (salary > 0),
       gender CHAR(1) CHECK (gender IN ('M','F')),
       ...
   );

4. UNIQUE - Allows one NULL value (unlike other databases)

   CREATE TABLE table_name (
       email VARCHAR2(100) UNIQUE,
       ...
   );

5. NOT NULL - Column-level constraint

   CREATE TABLE table_name (
       name VARCHAR2(50) NOT NULL,
       ...
   );

The SELECT statement in Oracle follows standard SQL with some Oracle-specific extensions:

-- Basic syntax
SELECT column1, column2, ...
FROM table_name;

Oracle-specific examples:

-- Use DUAL table for calculations
SELECT SYSDATE FROM DUAL;

-- Hierarchical queries with CONNECT BY
SELECT employee_id, last_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Flashback queries (view past data)
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '60' MINUTE;

-- Pseudo-columns
SELECT ROWID, ROWNUM, employee_id FROM employees;

Oracle supports standard WHERE clauses with some extensions:

-- Standard filtering
SELECT * FROM employees WHERE department_id = 10;

-- Oracle date filtering
SELECT * FROM employees 
WHERE hire_date BETWEEN TO_DATE('01-JAN-2020', 'DD-MON-YYYY') 
                    AND TO_DATE('31-DEC-2020', 'DD-MON-YYYY');

-- REGEXP_LIKE for regular expressions
SELECT * FROM employees 
WHERE REGEXP_LIKE(last_name, '^S(m|n)ith$');

-- EXISTS with subquery
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e 
              WHERE e.department_id = d.department_id);

Oracle provides several ways to sort and limit results:

-- Standard ORDER BY
SELECT * FROM employees ORDER BY last_name, first_name;

-- ROWNUM for limiting rows (Oracle 11g and earlier)
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 5;

-- FETCH FIRST (Oracle 12c and later)
SELECT * FROM employees 
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

-- OFFSET-FETCH (Oracle 12c and later)
SELECT * FROM employees 
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle provides several ways to insert data:

-- Basic INSERT
INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (1001, 'John', 'Doe', SYSDATE);

-- Insert from another table
INSERT INTO managers (employee_id, name, department)
SELECT employee_id, first_name||' '||last_name, department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.job_id LIKE '%MANAGER%';

-- Multi-table INSERT (Oracle specific)
INSERT ALL
    INTO employees (employee_id, first_name, last_name) VALUES (emp_id, fname, lname)
    INTO employee_details (employee_id, hire_date, salary) VALUES (emp_id, hdate, sal)
SELECT 1001 emp_id, 'John' fname, 'Doe' lname, SYSDATE hdate, 50000 sal FROM DUAL;

-- RETURNING clause (Oracle specific)
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1001, 'John', 'Doe')
RETURNING employee_id INTO v_emp_id;

Oracle UPDATE statements can include:

-- Basic UPDATE
UPDATE employees 
SET salary = salary * 1.1
WHERE department_id = 10;

-- Correlated subquery update
UPDATE employees e
SET salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id
)
WHERE department_id = 10;

-- RETURNING clause
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 100
RETURNING salary INTO v_new_salary;

Oracle DELETE statements can include:

-- Basic DELETE
DELETE FROM employees
WHERE department_id = 10;

-- Using subquery
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

-- RETURNING clause
DELETE FROM employees
WHERE employee_id = 100
RETURNING first_name, last_name INTO v_fname, v_lname;

Note: Oracle also supports TRUNCATE TABLE for faster removal of all rows.

Oracle provides several regex functions:

-- REGEXP_LIKE: Pattern matching in WHERE
SELECT * FROM employees
WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$');

-- REGEXP_SUBSTR: Extract substring
SELECT REGEXP_SUBSTR(phone_number, '([0-9]{3})\.([0-9]{3})\.([0-9]{4})', 1, 1, 'i') 
FROM employees;

-- REGEXP_REPLACE: Replace patterns
SELECT REGEXP_REPLACE(phone_number, '\.', '-') 
FROM employees;

-- REGEXP_INSTR: Find pattern position
SELECT REGEXP_INSTR(street_address, '[0-9]+') 
FROM locations;

Oracle analytic functions perform calculations across sets of rows:

-- RANK: Rank with gaps
SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

-- DENSE_RANK: Rank without gaps
SELECT employee_id, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) as salary_dense_rank
FROM employees;

-- ROW_NUMBER: Unique sequential numbers
SELECT employee_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_salary_rank
FROM employees;

-- LEAD/LAG: Access adjacent rows
SELECT employee_id, hire_date,
       LAG(hire_date, 1) OVER (ORDER BY hire_date) as prev_hire_date,
       LEAD(hire_date, 1) OVER (ORDER BY hire_date) as next_hire_date
FROM employees;

Oracle's CONNECT BY syntax handles hierarchical data:

-- Basic hierarchy
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Formatting hierarchy
SELECT LPAD(' ', 2*(LEVEL-1)) || last_name as org_chart
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- SYS_CONNECT_BY_PATH
SELECT employee_id, SYS_CONNECT_BY_PATH(last_name, '/') as path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Oracle supports standard SQL joins with optimizations:

-- Hash join (large tables)
SELECT /*+ USE_HASH(e d) */ e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;

-- Nested loops join (small tables)
SELECT /*+ ORDERED USE_NL(d) */ e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Sort-merge join (pre-sorted data)
SELECT /*+ USE_MERGE(e d) */ e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;

-- Outer joins with (+) syntax (Oracle legacy)
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

Oracle supports standard set operations:

-- UNION (distinct rows from both)
SELECT employee_id FROM current_employees
UNION
SELECT employee_id FROM past_employees;

-- UNION ALL (all rows from both)
SELECT employee_id FROM current_employees
UNION ALL
SELECT employee_id FROM past_employees;

-- INTERSECT (rows in both)
SELECT employee_id FROM current_employees
INTERSECT
SELECT employee_id FROM past_employees;

-- MINUS (rows in first not in second)
SELECT employee_id FROM current_employees
MINUS
SELECT employee_id FROM past_employees;

Materialized views store query results for fast access:

-- Create materialized view
CREATE MATERIALIZED VIEW emp_dept_mv
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT e.department_id, d.department_name, 
       COUNT(*) as emp_count, AVG(e.salary) as avg_salary
FROM employees e JOIN departments d ON e.department_id = d.department_id
GROUP BY e.department_id, d.department_name;

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

-- Fast refresh (requires materialized view logs)
CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON departments WITH ROWID;

CREATE MATERIALIZED VIEW emp_dept_mv
REFRESH FAST ON COMMIT
AS SELECT e.rowid as e_rowid, d.rowid as d_rowid,
          e.department_id, d.department_name, e.salary
   FROM employees e, departments d
   WHERE e.department_id = d.department_id;

Oracle provides advanced grouping capabilities:

-- ROLLUP: Hierarchical subtotals
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);

-- CUBE: All possible subtotal combinations
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);

-- GROUPING SETS: Specify exact groupings
SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(
    (department_id, job_id),
    (job_id, manager_id),
    (department_id, manager_id)
);

-- GROUPING function identifies aggregated rows
SELECT 
    department_id, 
    job_id, 
    SUM(salary),
    GROUPING(department_id) as dept_grouping,
    GROUPING(job_id) as job_grouping
FROM employees
GROUP BY ROLLUP(department_id, job_id);

Oracle provides PIVOT and UNPIVOT for cross-tabulation:

-- PIVOT: Rows to columns
SELECT * FROM (
    SELECT department_id, job_id, salary
    FROM employees
)
PIVOT (
    SUM(salary) FOR job_id IN (
        'AD_PRES' as president,
        'AD_VP' as vp,
        'IT_PROG' as programmer
    )
);

-- UNPIVOT: Columns to rows
SELECT * FROM (
    SELECT department_id, 
           SUM(DECODE(job_id, 'AD_PRES', salary, 0)) as president_sal,
           SUM(DECODE(job_id, 'AD_VP', salary, 0)) as vp_sal,
           SUM(DECODE(job_id, 'IT_PROG', salary, 0)) as prog_sal
    FROM employees
    GROUP BY department_id
)
UNPIVOT (
    salary FOR job_title IN (
        president_sal as 'PRESIDENT',
        vp_sal as 'VICE PRESIDENT',
        prog_sal as 'PROGRAMMER'
    )
);

Oracle supports several subquery approaches:

-- Single-row subquery
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Multiple-row subquery
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments 
                       WHERE location_id = 1700);

-- Correlated subquery
SELECT e1.* FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2
                WHERE e2.department_id = e1.department_id);

-- EXISTS subquery
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e
              WHERE e.department_id = d.department_id);

-- WITH clause (Common Table Expression)
WITH dept_costs AS (
    SELECT department_id, SUM(salary) as total_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM dept_costs
WHERE total_salary > (SELECT AVG(total_salary) FROM dept_costs);

Oracle's flashback feature allows querying past data:

-- Query data as of specific time
SELECT * FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Query data from undo retention period
SELECT * FROM employees
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '60' MINUTE;

-- Flashback version query
SELECT versions_starttime, versions_endtime, salary
FROM employees VERSIONS BETWEEN TIMESTAMP 
    SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE employee_id = 100;

-- Flashback table (restore table to past state)
FLASHBACK TABLE employees TO TIMESTAMP 
    TO_TIMESTAMP('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

Oracle views can include advanced features:

-- Basic view
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;

-- Read-only view
CREATE OR REPLACE VIEW emp_readonly AS
SELECT * FROM employees
WITH READ ONLY;

-- View with constraints
CREATE OR REPLACE VIEW emp_sal_view (
    employee_id, last_name, salary,
    CONSTRAINT sal_positive CHECK (salary > 0) DISABLE NOVALIDATE
) AS SELECT employee_id, last_name, salary FROM employees;

-- Object view (for object-relational features)
CREATE OR REPLACE VIEW emp_obj_view OF employee_type
WITH OBJECT IDENTIFIER (employee_id) AS
SELECT employee_id, last_name, salary FROM employees;

Oracle supports several index types:

1. B-tree: Standard balanced tree index

   CREATE INDEX emp_name_idx ON employees(last_name);

2. Bitmap: For low-cardinality columns

   CREATE BITMAP INDEX emp_gender_idx ON employees(gender);

3. Function-based: On expressions

   CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name));

4. Reverse key: For reducing contention

   CREATE INDEX emp_id_rev_idx ON employees(employee_id) REVERSE;

5. Domain: For specialized data types
6. Partitioned: Aligned with table partitions
7. Cluster: For clustered tables

Oracle provides several index management features:

-- Monitor index usage
ALTER INDEX emp_name_idx MONITORING USAGE;

-- Check usage
SELECT * FROM v$object_usage WHERE index_name = 'EMP_NAME_IDX';

-- Rebuild index
ALTER INDEX emp_name_idx REBUILD;

-- Make index invisible (optimizer ignores)
ALTER INDEX emp_name_idx INVISIBLE;

-- Make index visible again
ALTER INDEX emp_name_idx VISIBLE;

-- Collect statistics
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_NAME_IDX');

-- Drop index
DROP INDEX emp_name_idx;

Oracle provides extensive string functions:

-- Concatenation
SELECT first_name || ' ' || last_name as full_name FROM employees;

-- Substring
SELECT SUBSTR('Oracle Database', 8, 8) FROM dual; -- 'Database'

-- Case conversion
SELECT UPPER(last_name), LOWER(first_name), INITCAP(job_id) FROM employees;

-- Padding
SELECT LPAD(salary, 10, '*'), RPAD(last_name, 20, '.') FROM employees;

-- Trimming
SELECT TRIM(LEADING '0' FROM phone_number) FROM employees;

-- Regular expressions
SELECT REGEXP_REPLACE(phone_number, '([0-9]{3})\.([0-9]{3})\.([0-9]{4})', '(\1) \2-\3') 
FROM employees;

-- Soundex (phonetic matching)
SELECT last_name FROM employees 
WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');

Oracle has comprehensive date handling:

-- Current date/time
SELECT SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP FROM dual;

-- Date arithmetic
SELECT hire_date, hire_date + 30 as after_30_days,
       hire_date + INTERVAL '1' MONTH as after_1_month,
       hire_date + INTERVAL '1-3' YEAR TO MONTH as after_1yr3mo
FROM employees;

-- Date extraction
SELECT EXTRACT(YEAR FROM hire_date) as hire_year FROM employees;

-- Date formatting
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') as formatted_date
FROM employees;

-- Date conversion
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') FROM dual;

-- Date difference
SELECT MONTHS_BETWEEN(SYSDATE, hire_date)/12 as years_employed
FROM employees;

Oracle provides mathematical functions:

-- Rounding
SELECT ROUND(123.456, 2), ROUND(123.456, -1), TRUNC(123.456, 2) FROM dual;

-- Modulus
SELECT MOD(10, 3) FROM dual; -- 1

-- Sign
SELECT SIGN(-10), SIGN(0), SIGN(10) FROM dual;

-- Power
SELECT POWER(2, 10) FROM dual; -- 1024

-- Logarithm
SELECT LN(10), LOG(10, 100) FROM dual;

-- Trigonometric
SELECT SIN(3.14159), COS(0), TAN(0.785398) FROM dual;

-- Random
SELECT DBMS_RANDOM.VALUE(1, 100) FROM dual;

Oracle transactions follow ACID principles:

-- Start transaction (implicit in Oracle)
UPDATE accounts SET balance = balance - 100 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 202;

-- Commit transaction
COMMIT;

-- Or rollback
ROLLBACK;

-- Savepoints
UPDATE accounts SET balance = balance - 100 WHERE account_id = 101;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 202;
-- If error occurs:
ROLLBACK TO SAVEPOINT after_debit;

Oracle supports read consistency - queries see a consistent snapshot of data as of the query start time.

Oracle supports these isolation levels:

1. READ COMMITTED: Default level, prevents dirty reads
2. SERIALIZABLE: Transactions appear to execute serially
3. READ ONLY: Transaction sees data as of transaction start

Oracle doesn't support READ UNCOMMITTED level.

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Read-only transaction
SET TRANSACTION READ ONLY;

Oracle uses multi-version concurrency control with these lock types:

1. Row locks (TX): For DML operations
2. Table locks (TM): For DDL operations
3. Enqueue locks: For serialization

Locking examples:

-- Explicit row lock
SELECT * FROM employees 
WHERE employee_id = 100
FOR UPDATE;

-- NOWAIT option
SELECT * FROM employees 
WHERE employee_id = 100
FOR UPDATE NOWAIT;

-- WAIT with timeout
SELECT * FROM employees 
WHERE employee_id = 100
FOR UPDATE WAIT 5; -- seconds

-- Share mode
SELECT * FROM departments 
WHERE department_id = 10
FOR UPDATE OF location_id;

PL/SQL is Oracle's procedural language extension to SQL:

• Block-structured language with procedures, functions, packages
• Supports variables, conditions, loops, exceptions
• Tightly integrated with SQL
• Stored in database for efficient execution

Basic PL/SQL block structure:

DECLARE
    -- Variable declarations
    v_employee_id NUMBER := 100;
    v_salary employees.salary%TYPE;
BEGIN
    -- Executable statements
    SELECT salary INTO v_salary 
    FROM employees 
    WHERE employee_id = v_employee_id;
    
    -- Conditional logic
    IF v_salary < 5000 THEN
        UPDATE employees SET salary = salary * 1.1
        WHERE employee_id = v_employee_id;
    END IF;
    
    -- Exception handling
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

Stored procedures are created with CREATE PROCEDURE:

CREATE OR REPLACE PROCEDURE increase_salary (
    p_employee_id IN NUMBER,
    p_percentage IN NUMBER,
    p_result OUT VARCHAR2
) AS
    v_current_salary NUMBER;
    v_department_id NUMBER;
BEGIN
    -- Get current salary
    SELECT salary, department_id 
    INTO v_current_salary, v_department_id
    FROM employees 
    WHERE employee_id = p_employee_id;
    
    -- Check department budget
    IF check_department_budget(v_department_id, 
                              v_current_salary * p_percentage/100) THEN
        -- Update salary
        UPDATE employees 
        SET salary = salary * (1 + p_percentage/100)
        WHERE employee_id = p_employee_id;
        
        p_result := 'Salary increased successfully';
        COMMIT;
    ELSE
        p_result := 'Department budget exceeded';
    END IF;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_result := 'Employee not found';
    WHEN OTHERS THEN
        p_result := 'Error: ' || SQLERRM;
        ROLLBACK;
END increase_salary;

Packages group related PL/SQL types, variables, and subprograms:

-- Package specification (interface)
CREATE OR REPLACE PACKAGE employee_mgmt AS
    -- Public type
    TYPE emp_rec IS RECORD (
        id employees.employee_id%TYPE,
        name VARCHAR2(100),
        salary employees.salary%TYPE
    );
    
    -- Public constant
    g_max_salary CONSTANT NUMBER := 100000;
    
    -- Public procedures
    PROCEDURE hire_employee(
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_salary IN NUMBER,
        p_dept_id IN NUMBER
    );
    
    PROCEDURE fire_employee(p_emp_id IN NUMBER);
    
    -- Public function
    FUNCTION get_employee(p_emp_id IN NUMBER) RETURN emp_rec;
END employee_mgmt;

-- Package body (implementation)
CREATE OR REPLACE PACKAGE BODY employee_mgmt AS
    -- Private variable
    v_employee_count NUMBER := 0;
    
    -- Private procedure
    PROCEDURE log_action(p_action IN VARCHAR2) IS
    BEGIN
        INSERT INTO employee_log(action_date, action)
        VALUES (SYSDATE, p_action);
    END log_action;
    
    -- Implement public procedures
    PROCEDURE hire_employee(
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_salary IN NUMBER,
        p_dept_id IN NUMBER
    ) IS
    BEGIN
        INSERT INTO employees(
            employee_id, first_name, last_name, 
            hire_date, salary, department_id
        ) VALUES (
            employees_seq.NEXTVAL, p_first_name, p_last_name,
            SYSDATE, p_salary, p_dept_id
        );
        
        v_employee_count := v_employee_count + 1;
        log_action('Hired ' || p_first_name || ' ' || p_last_name);
    END hire_employee;
    
    PROCEDURE fire_employee(p_emp_id IN NUMBER) IS
    BEGIN
        DELETE FROM employees WHERE employee_id = p_emp_id;
        v_employee_count := v_employee_count - 1;
        log_action('Fired employee ' || p_emp_id);
    END fire_employee;
    
    FUNCTION get_employee(p_emp_id IN NUMBER) RETURN emp_rec IS
        v_result emp_rec;
    BEGIN
        SELECT employee_id, first_name||' '||last_name, salary
        INTO v_result.id, v_result.name, v_result.salary
        FROM employees
        WHERE employee_id = p_emp_id;
        
        RETURN v_result;
    END get_employee;
END employee_mgmt;

Oracle SQL tuning techniques:

1. Execution Plans:

   -- Explain plan
   EXPLAIN PLAN FOR 
   SELECT * FROM employees WHERE department_id = 10;
   
   -- View plan
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   
   -- Gather statistics
   EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

2. Hints:

   SELECT /*+ INDEX(e emp_name_idx) */ * 
   FROM employees e 
   WHERE last_name = 'Smith';

3. SQL Tuning Advisor:

   -- Create tuning task
   DECLARE
       v_task VARCHAR2(100);
       v_sql CLOB := 'SELECT * FROM employees WHERE department_id = 10';
   BEGIN
       v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
           sql_text => v_sql,
           user_name => 'HR',
           scope => 'COMPREHENSIVE',
           time_limit => 60,
           task_name => 'emp_dept_tuning'
       );
       DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task);
   END;
   
   -- View recommendations
   SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('emp_dept_tuning') FROM dual;

4. SQL Access Advisor: Recommends indexes and materialized views
5. SQL Profile: Stores auxiliary statistics for better plans

Key performance views in Oracle:

• V$SQL: Shared SQL area statistics
• V$SQLAREA: Shared SQL area summary
• V$SESSION: Current sessions
• V$SESSION_WAIT: Session wait events
• V$SYSTEM_EVENT: System-wide wait events
• V$LOCK: Lock information
• V$DATAFILE: Datafile information
• V$TABLESPACE: Tablespace information

Example queries:

-- Top SQL by buffer gets
SELECT sql_id, executions, buffer_gets, 
       buffer_gets/executions as gets_per_exec,
       sql_text
FROM v$sqlarea
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

-- Current waits
SELECT s.sid, s.serial#, s.username, 
       sw.event, sw.wait_time, sw.seconds_in_wait
FROM v$session s
JOIN v$session_wait sw ON s.sid = sw.sid
WHERE s.status = 'ACTIVE';

Automatic Workload Repository (AWR) collects performance data:

-- Generate AWR report
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid => 1234, -- begin snapshot ID
    l_eid => 1235  -- end snapshot ID
));

-- Compare periods
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(
    l_dbid => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid1 => 1234, l_eid1 => 1235, -- period 1
    l_bid2 => 1236, l_eid2 => 1237  -- period 2
));

-- Key AWR views
SELECT * FROM dba_hist_snapshot; -- snapshot list
SELECT * FROM dba_hist_sqlstat; -- SQL statistics
SELECT * FROM dba_hist_system_event; -- wait events
SELECT * FROM dba_hist_osstat; -- OS statistics

= is not the SQL test for nullity. Any comparison with NULL yields UNKNOWN (not TRUE), so the row is filtered out. Use IS NULL / IS NOT NULL, or NVL/COALESCE if you must compare to a sentinel value.

-- Wrong (always UNKNOWN, never TRUE)
SELECT * FROM t WHERE col = NULL;
-- Correct
SELECT * FROM t WHERE col IS NULL;

NVL evaluates b even when a is not null (side effects matter). NVL2 returns b if a is not null, else c. COALESCE returns the first non-null argument and stops—later arguments are not evaluated—so it matches ANSI semantics and avoids extra expression evaluation.

Oracle treats each NULL as distinct from other NULLs in unique constraints (pre-12c behavior for single-column; multi-column rules still follow documentation). Interviewers probe whether you know UNIQUE does not mean “no duplicates” for NULL keys—design partial uniqueness with a function-based index or a virtual column if you need “at most one unknown.”

ROWNUM is assigned as rows are produced before a final ORDER BY is applied in a simple query shape, so you can get “any” 10 rows, then sort those—not the top 10 salaries. Fix with an inline view or CTE: sort first, then filter ROWNUM, or use ROW_NUMBER() OVER (...) / FETCH FIRST (12c+).

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

If the subquery can still return zero rows, outer joins or scalar subquery contexts behave differently. For “exactly one row expected,” use aggregation (MAX/MIN), FETCH FIRST 1 ROW ONLY, or handle no-data with EXCEPTION in PL/SQL—interviewers care that you separate “pick one” from “must exist.”

Queries read a consistent SCN image using undo; writers create new versions. Readers are not blocked by row-level data locks for normal DML, but they can wait on DDL, library cache, or rare ITL slot exhaustion. Long-running queries can force undo retention pressure and ORA-1555 if undo is too small—an operational “trick” question.

Each session sees a read-consistent snapshot as of its query start SCN (or current SCN for serializable expectations). Another session’s committed DML after your query began is invisible until you re-execute. Flashback query (AS OF TIMESTAMP) makes the snapshot explicit—good follow-up for architects.

MERGE routes rows to WHEN MATCHED / WHEN NOT MATCHED; with DELETE WHERE on matched rows, you can remove rows in the same statement. Triggers fire per phase; compound triggers (11g+) help consolidate logic. Candidates stumble explaining ordering and that referential actions still apply.

ORA-04091 happens when a row trigger reads the mutating table (or touches rows affected by the same statement). Fixes: use COMPOUND TRIGGER, defer work to AFTER STATEMENT, use autonomous transactions (sparingly), or redesign with foreign keys and constraints instead of trigger-maintained aggregates.

No. CACHE, instance failover, rollback, and NOCYCLE exhaustion all create gaps. Interviewers check you don’t use sequences for audit “no missing ID” proofs—use a dedicated ledger or strict business rules.

GENERATED AS IDENTITY binds generation to the column metadata, supports GENERATED ALWAYS vs BY DEFAULT, and aligns with ANSI. You still don’t get gap-free IDs; migration from sequences needs care around bulk load and direct-path inserts.

Adaptive cursor sharing, bind-aware/peeking behavior, cardinality feedback, and automatic reoptimization can change plans after the engine observes row counts. Statistics snapshots and histogram boundaries matter for skewed columns—good answer mentions checking V$SQL, V$SQL_CS_*, and AWR for plan flips.

Applying a function or implicit conversion on the indexed column prevents normal B-tree range scans (unless you have a matching function-based index). Examples: WHERE TO_CHAR(order_date,'YYYYMM') = '202401' instead of range on DATE; WHERE amount + 0 = :n on a string column. Fix the expression or the data type.

No—GROUP BY only emits groups present in the filtered row set. To include “zero count” departments you must start from a departments driving table and LEFT JOIN employees, then COUNT(e.id), or use a partitioned outer join pattern. Classic trick for report queries.

COUNT(*) counts rows including nulls; COUNT(expr) ignores rows where expr is null. With COUNT(DISTINCT expr), multiple NULLs are typically treated as one unknown key in SQL semantics—know your Oracle version’s documented behavior for DISTINCT and NULLs in analytics.