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