Oracle Interview Questions and Answers

Answer

An instance is memory plus processes: the SGA (buffer cache, shared pool, log buffer, large pool, and optionally the In-Memory area) together with the background processes. A database is the set of files on disk: datafiles, control files and online redo log files. The instance is transient and dies when you shut down; the database persists.

One instance normally mounts one database, but in Real Application Clusters many instances on different nodes mount the same database, which is exactly why the distinction matters in interviews. Session memory lives outside the SGA in the PGA, sized by PGA_AGGREGATE_TARGET with a hard ceiling from PGA_AGGREGATE_LIMIT, and that is where sorts and hash joins spill to the temporary tablespace when they do not fit. The background processes worth naming are DBWn (writes dirty buffers to datafiles), LGWR (flushes the redo log buffer at commit), CKPT (advances the checkpoint and updates file headers), SMON (instance recovery and space coalescing), PMON with the CLMN and CLnn cleanup helpers in recent versions (process cleanup), ARCn (archives filled redo logs), MMON (AWR snapshots) and LREG (listener registration). On a hung database the first three things to check are the alert log under the diagnostic destination, whether the archiver is stuck because the fast recovery area is full (ORA-00257), and V$SESSION joined to V$SESSION_WAIT for a wall of log file sync or free buffer waits, which point at LGWR and DBWn respectively.

-- Is this instance open, and against which database?
SELECT instance_name, host_name, status, database_status FROM v$instance;
SELECT name, db_unique_name, open_mode, database_role, log_mode FROM v$database;

-- Which background processes are running right now?
SELECT name, description FROM v$bgprocess WHERE paddr <> '00';

-- Where is the alert log and the diag trace directory?
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- Top wait events for active sessions (first stop on a hang)
SELECT event, COUNT(*)
  FROM v$session
 WHERE status = 'ACTIVE' AND wait_class <> 'Idle'
 GROUP BY event
 ORDER BY 2 DESC;

Answer

A container database (CDB) holds one CDB$ROOT container with the Oracle-supplied metadata and code, a PDB$SEED template used to clone new pluggable databases, and any number of pluggable databases (PDBs), each of which behaves like a self-contained database with its own data dictionary, tablespaces, users and services. The instance, SGA and background processes are shared across all PDBs, which is the whole point: consolidating fifty small application schemas as fifty PDBs on one instance costs far less memory than fifty separate instances. Common users are created in the root with a C## prefix and exist in every container; local users exist only in their PDB.

You move between containers with ALTER SESSION SET CONTAINER, and you connect directly to a PDB using its service name, never its SID. The legacy non-CDB architecture was deprecated in 12c and desupported from 21c onward, so on 19c you can still create a non-CDB but you are on a dead-end path, and on 23ai every database is a CDB. Oracle allows three user-created PDBs per CDB without the Multitenant option licence, which is how most Indian mid-size shops use it. Practical gotchas: DBA_ views only show the current container, so use CDB_ views from the root to see everything; V$ views in a PDB show instance-wide data with a CON_ID column; and a plugged-in PDB carrying a different character set or time zone file version will refuse to open until you reconcile it.

-- Where am I?
SHOW con_name
SELECT sys_context('USERENV','CON_NAME') AS container FROM dual;

-- List all PDBs from the root
SELECT con_id, name, open_mode, restricted FROM v$pdbs;

-- Create a PDB from the seed and open it
CREATE PLUGGABLE DATABASE billing_pdb
  ADMIN USER pdbadmin IDENTIFIED BY "Str0ng#Pass"
  FILE_NAME_CONVERT = ('/pdbseed/', '/billing_pdb/');

ALTER PLUGGABLE DATABASE billing_pdb OPEN;
ALTER PLUGGABLE DATABASE billing_pdb SAVE STATE;  -- reopen automatically on restart

-- Switch containers
ALTER SESSION SET CONTAINER = billing_pdb;

Answer

The hierarchy runs tablespace to segment to extent to block. A tablespace is a logical container mapped to one or more datafiles. A segment is one object that consumes space, a table, an index, a LOB, or one partition of a partitioned table.

An extent is a contiguous run of blocks allocated to a segment in one go. A block is the smallest unit of I/O, usually 8 KB, set by DB_BLOCK_SIZE at database creation and effectively unchangeable afterwards. Modern databases use locally managed tablespaces with bitmaps in the datafile header rather than the old dictionary-managed model, and Automatic Segment Space Management for freelist handling, so you rarely tune PCTUSED any more.

ORA-01653 means Oracle could not extend a table segment in a tablespace: either every datafile has hit MAXSIZE, autoextend is off, or the filesystem or ASM diskgroup is full. The lazy fix is to add space; the right response is to look at why. Common real causes in Indian production estates are an unpurged audit or interface staging table, a runaway job inserting without a commit boundary, LOB segments in the wrong tablespace, and index rebuilds that need roughly double the segment size temporarily. Check DBA_FREE_SPACE against DBA_SEGMENTS before adding a datafile, and prefer enabling autoextend with a sane MAXSIZE over unbounded growth, because an unbounded datafile will happily consume the mount point that also holds your archive logs and take the database down with ORA-00257.

-- Free vs used space per tablespace
SELECT df.tablespace_name,
       ROUND(SUM(df.bytes)/1024/1024) AS mb_alloc,
       ROUND(NVL(fs.free_mb,0))       AS mb_free
  FROM dba_data_files df
  LEFT JOIN (SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb
               FROM dba_free_space GROUP BY tablespace_name) fs
    ON fs.tablespace_name = df.tablespace_name
 GROUP BY df.tablespace_name, fs.free_mb
 ORDER BY mb_free;

-- Top 10 growing segments
SELECT segment_name, segment_type, ROUND(bytes/1024/1024) mb
  FROM dba_segments WHERE tablespace_name = 'APP_DATA'
 ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

-- Add space with a ceiling, not unbounded
ALTER DATABASE DATAFILE '/u02/oradata/app_data_04.dbf'
  AUTOEXTEND ON NEXT 256M MAXSIZE 32G;

Answer

Redo records how to redo a change; undo records how to reverse it. When you update a row, Oracle writes the new value into the buffer cache, writes a redo record describing that change into the redo log buffer, and writes the old value into an undo segment in the undo tablespace. Crucially, the change to the undo block is itself protected by redo, so redo covers both.

On COMMIT, LGWR flushes the redo log buffer to the online redo log and the commit returns only after that write completes: this is the log file sync wait, and it is why commit latency is dominated by redo log I/O, not by datafile I/O. The dirty data blocks are still in the buffer cache and get written later by DBWn, so a commit does not force a datafile write. On ROLLBACK, Oracle reads the undo records and applies them in reverse to restore the old values, which is real work and is why rolling back a large batch can take longer than the batch itself took.

Undo is not just for rollback: it also serves read consistency, so a query started at 10:00 keeps reading the pre-change image from undo even after another session commits, and it serves instance recovery, where SMON first rolls forward all redo and then rolls back uncommitted transactions. The two classic exam follow-ups are that a nologging direct-path load generates minimal redo but still needs a backup afterwards, and that ORA-01555 is an undo problem, never a redo problem.

-- Undo usage and retention right now
SELECT tablespace_name, status, ROUND(SUM(bytes)/1024/1024) mb
  FROM dba_undo_extents GROUP BY tablespace_name, status;

SHOW PARAMETER undo_retention
SELECT retention FROM dba_tablespaces WHERE contents = 'UNDO';

-- Redo generated by the current session
SELECT n.name, s.value
  FROM v$statname n JOIN v$mystat s ON s.statistic# = n.statistic#
 WHERE n.name IN ('redo size','undo change vector size');

-- Commit latency: average log file sync in milliseconds
SELECT event, total_waits, ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) avg_ms
  FROM v$system_event WHERE event = 'log file sync';

Answer

Use VARCHAR2 for essentially all character data. CHAR is blank-padded to its declared length, so a CHAR(10) holding 'ABC' stores seven trailing spaces and comparisons against a VARCHAR2 then behave inconsistently: comparing CHAR to CHAR uses blank-padded semantics, but comparing CHAR to VARCHAR2 uses non-padded semantics, which is a genuine source of production bugs where a join silently returns no rows. NVARCHAR2 stores data in the national character set (usually AL16UTF16) and is only worth it on a legacy database whose main character set cannot hold the scripts you need.

On any modern database created with AL32UTF8, plain VARCHAR2 handles Devanagari, Tamil and emoji fine, so NVARCHAR2 is legacy baggage. Declare length semantics explicitly: VARCHAR2(50 CHAR) means fifty characters, VARCHAR2(50) means fifty bytes unless NLS_LENGTH_SEMANTICS says otherwise, and in UTF-8 a Hindi character can take three bytes, so a byte-semantics column will throw ORA-12899 on perfectly valid input. The maximum is 4000 bytes by default and 32767 when MAX_STRING_SIZE is set to EXTENDED, though extended columns above 4000 bytes are stored out of line like a LOB.

NUMBER is a base-100 variable-length decimal type, not a binary float, so it does exact decimal arithmetic and is the correct choice for money. NUMBER(10,2) allows ten significant digits with two after the decimal point, and the scale rounds rather than truncates, so 12.567 stored into NUMBER(10,2) becomes 12.57. Use BINARY_DOUBLE only for scientific workloads where IEEE semantics and speed matter more than exactness.

CREATE TABLE payments (
  payment_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payer_name   VARCHAR2(120 CHAR) NOT NULL,
  ifsc_code    CHAR(11),                 -- genuinely fixed width, fine here
  amount_inr   NUMBER(14,2) NOT NULL,    -- exact decimal money
  paid_at      TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP
);

-- The CHAR trap
SELECT CASE WHEN CAST('ABC' AS CHAR(10)) = CAST('ABC' AS VARCHAR2(10))
            THEN 'equal' ELSE 'NOT equal' END AS result FROM dual;
-- returns NOT equal: non-padded comparison semantics

-- Scale rounds, it does not truncate
SELECT CAST(12.567 AS NUMBER(10,2)) FROM dual;  -- 12.57

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Answer

Oracle is the odd one out among major databases: assigning an empty string to a VARCHAR2 column stores NULL, and comparing a column to '' is therefore never true. This is documented behaviour retained for backward compatibility, and Oracle explicitly warns it may change, but in practice you must code around it. The consequences are everywhere: a NOT NULL constraint rejects an empty string insert with ORA-01400, LENGTH('') returns NULL rather than zero, and concatenation is the one operator that ignores NULL, so 'a' || NULL || 'b' returns 'ab' rather than NULL.

Never write WHERE col = NULL or col <> NULL; use IS NULL and IS NOT NULL. Also remember that a normal B-tree index does not store entirely NULL keys, so WHERE col IS NULL will not use a single-column index on that column, which surprises people who wonder why their index is being ignored. As for the functions: NVL(a, b) returns b when a is NULL and always evaluates both arguments, which matters if b is an expensive function call or a sequence NEXTVAL.

NVL2(a, b, c) returns b when a is not null and c when it is. COALESCE takes any number of arguments, returns the first non-NULL one, and short-circuits, so it is generally the better choice. NULLIF(a, b) returns NULL when the two are equal, which is the standard trick for avoiding divide-by-zero: amount / NULLIF(qty, 0) yields NULL instead of ORA-01476. Aggregates ignore NULL, so COUNT(col) and COUNT(*) differ, and AVG over a column with NULLs divides by the non-null count.

SELECT CASE WHEN '' IS NULL THEN 'empty string IS null' END AS quirk FROM dual;

SELECT NVL(NULL, 'fallback')            AS nvl_result,
       NVL2('x', 'has value', 'is null') AS nvl2_result,
       COALESCE(NULL, NULL, 'third')     AS coalesce_result,
       NULLIF(10, 10)                    AS nullif_result
  FROM dual;

-- Safe division
SELECT total_amount / NULLIF(item_count, 0) AS avg_per_item FROM orders;

-- COUNT(*) vs COUNT(col): the classic interview trap
SELECT COUNT(*) AS all_rows, COUNT(discount_pct) AS non_null_discounts FROM orders;

-- Index the NULLs if you must search for them
CREATE INDEX ix_orders_closed_null ON orders (closed_at, 1);

Answer

DUAL is a one-row, one-column table owned by SYS with a column named DUMMY containing the value 'X'. It exists because Oracle SQL historically required a FROM clause on every SELECT, so any expression evaluation, function call or sequence fetch needed a table to select from, and DUAL guarantees exactly one row so the result set does not multiply. It is not a normal table in practice: the optimizer recognises it and uses a FAST DUAL access path that does not touch a block at all, so the overhead is negligible.

Two things get asked. First, why not create your own one-row table: you can, but you would have to guarantee it always has exactly one row, and you would lose the FAST DUAL optimisation. Second, and this is the 2026 update, Oracle Database 23ai finally allows SELECT without a FROM clause for expressions, matching the ANSI behaviour that MySQL and PostgreSQL have had for years.

So SELECT SYSDATE works directly on 23ai. On 19c, which is still what most Indian enterprise estates run, you must write SELECT SYSDATE FROM dual. 23ai also added the table value constructor, so VALUES (1,'a'),(2,'b') can be used inline as a row source, and boolean expressions can appear in SQL because 23ai introduced a real BOOLEAN data type in SQL rather than PL/SQL only. If you claim 23ai experience in an interview, expect exactly this question as a cheap authenticity check, because a candidate who has only read about the release will not know that DUAL still exists for backward compatibility.

-- Works on every release
SELECT SYSDATE, USER, sys_context('USERENV','SID') FROM dual;
SELECT order_seq.NEXTVAL FROM dual;

-- Oracle Database 23ai only: FROM clause is optional
SELECT SYSDATE;
SELECT 2 + 2 AS answer;

-- 23ai table value constructor as an inline row source
SELECT * FROM (VALUES (1,'UPI'), (2,'CARD'), (3,'NETBANKING')) AS t(id, mode);

-- What DUAL actually contains
DESC dual
SELECT dummy FROM dual;  -- X

Answer

ROWNUM is a pseudocolumn assigned as rows are produced by the query, after the WHERE clause filters them and before ORDER BY sorts them. That ordering of operations explains every gotcha. The first candidate row that passes the predicate gets ROWNUM 1, the second gets 2, and so on.

So WHERE ROWNUM > 1 can never be true: the first candidate row is offered ROWNUM 1, fails the predicate, is discarded, and the next row is again offered ROWNUM 1, forever. The same reasoning makes WHERE ROWNUM = 1 work but WHERE ROWNUM = 2 always empty. The second trap is that ROWNUM is applied before ORDER BY, so SELECT * FROM emp WHERE ROWNUM <= 5 ORDER BY salary DESC gives you five arbitrary employees sorted, not the top five earners.

The correct legacy pattern is to sort in an inline view and filter ROWNUM on the outside, and for offset pagination you need two levels of nesting because you must materialise ROWNUM as a real column before you can filter a range on it. Since 12c the row limiting clause makes all of this unnecessary: ORDER BY salary DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. Use FETCH FIRST n ROWS WITH TIES when you want to include rows tied on the sort key.

Two production caveats interviewers like: deep offset pagination still forces Oracle to produce and discard all the skipped rows, so page 5000 is slow no matter which syntax you use, and keyset pagination on an indexed sort key is the scalable answer. Also do not confuse ROWNUM with ROWID, which is a physical address, not a counter.

-- WRONG: five arbitrary rows, then sorted
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

-- Legacy correct pattern: sort inside, limit outside
SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;

-- Legacy offset pagination needs two levels
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (SELECT * FROM employees ORDER BY salary DESC) t
   WHERE ROWNUM <= 30
) WHERE rn > 20;

-- Modern (12c and later)
SELECT employee_id, salary FROM employees
 ORDER BY salary DESC
 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT employee_id, salary FROM employees
 ORDER BY salary DESC FETCH FIRST 5 ROWS WITH TIES;

Answer

A sequence is a standalone object that hands out numbers with NEXTVAL and lets you re-read the last value in your session with CURRVAL. An IDENTITY column, added in 12c, is syntactic sugar: Oracle creates a hidden sequence and wires it into the column default. GENERATED ALWAYS AS IDENTITY blocks explicit inserts into the column with ORA-32795, which is what you want for a surrogate key; GENERATED BY DEFAULT allows them, and GENERATED BY DEFAULT ON NULL uses the sequence only when the supplied value is NULL, which is handy for data migration.

Since 12c you can also just put seq.NEXTVAL in a column DEFAULT clause, which is more flexible than IDENTITY because several tables can share one sequence. Sequences are guaranteed unique, never guaranteed gapless. Gaps come from three places: a rolled-back transaction still consumed the number, a cached range is lost when the instance shuts down or is flushed from the shared pool, and in RAC each instance caches its own range so values interleave.

Anyone who needs a strictly gapless invoice number for a statutory requirement, which comes up constantly with GST invoice series in Indian systems, must implement it with a control table row locked with SELECT FOR UPDATE, and must accept the serialisation that causes. On the CACHE question: the default is 20, which is far too small for a hot table. Set CACHE to 1000 or more and leave NOORDER on, because ORDER in RAC forces cross-instance coordination through the row cache and produces dc_sequences contention that shows up as row cache lock waits.

NOCACHE is worse still: every NEXTVAL updates the data dictionary and serialises. Scalable sequences (18c) add an instance and session prefix to spread inserts across index leaf blocks.

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 CACHE 1000 NOORDER NOCYCLE;

-- 12c+ identity column
CREATE TABLE orders (
  order_id  NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE 1000) PRIMARY KEY,
  amount    NUMBER(12,2)
);

-- Shared sequence via DEFAULT (more flexible than IDENTITY)
CREATE TABLE order_events (
  event_id NUMBER DEFAULT order_seq.NEXTVAL PRIMARY KEY,
  payload  CLOB
);

-- 18c scalable sequence: reduces right-hand index leaf block contention
ALTER SEQUENCE order_seq SCALE EXTEND;

-- Truly gapless numbering: serialise deliberately
UPDATE invoice_counter SET last_no = last_no + 1
 WHERE series = 'GST-FY26' RETURNING last_no INTO :new_no;

Answer

PRIMARY KEY and UNIQUE constraints are enforced by an index, so Oracle creates one automatically unless a usable index already leads with those columns, in which case it reuses it. This matters: if you create the index yourself first, dropping the constraint later leaves your index in place, whereas an index Oracle created implicitly is dropped with the constraint unless you say KEEP INDEX. NOT NULL is implemented as a check constraint and creates nothing.

CHECK constraints create nothing. FOREIGN KEY constraints create nothing either, and that is the important one. An unindexed foreign key hurts in two ways.

First, every delete or primary-key update on the parent must scan the child table to verify no orphans, which turns a single-row parent delete into a full scan of a hundred-million-row child. Second, Oracle takes a share lock on the child table for the duration of that check, so concurrent DML on the child blocks, and this is a classic mystery-blocking incident where the blocked sessions are all on the child and nobody realises the culprit is a delete on a tiny parent lookup table. The fix is simply to index the foreign key columns.

Beyond that, know DEFERRABLE INITIALLY DEFERRED, which postpones validation to commit time and is essential for circular references or for bulk loads that temporarily violate ordering, and ENABLE NOVALIDATE, which enforces the rule on new rows without validating history, the standard technique for adding a constraint to a dirty legacy table without a long outage. RELY with NOVALIDATE lets the optimizer trust an unenforced constraint for query rewrite in a warehouse, which is powerful and dangerous in equal measure.

-- Find every unindexed foreign key in your schema
SELECT c.table_name, c.constraint_name, cc.column_name
  FROM user_constraints c
  JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
 WHERE c.constraint_type = 'R'
   AND NOT EXISTS (
         SELECT 1 FROM user_ind_columns ic
          WHERE ic.table_name = cc.table_name
            AND ic.column_name = cc.column_name
            AND ic.column_position = cc.position)
 ORDER BY c.table_name;

-- Add a constraint to a dirty legacy table without validating history
ALTER TABLE invoices ADD CONSTRAINT ck_inv_amount
  CHECK (amount_inr >= 0) ENABLE NOVALIDATE;

-- Deferred FK for a bulk load with out-of-order inserts
ALTER TABLE order_items ADD CONSTRAINT fk_oi_order
  FOREIGN KEY (order_id) REFERENCES orders (order_id)
  DEFERRABLE INITIALLY DEFERRED;

Answer

Oracle starts a transaction implicitly at the first DML statement and ends it only at COMMIT, ROLLBACK, or a disconnect. There is no autocommit at the server; SQL*Plus and SQLcl have a client-side SET AUTOCOMMIT setting, and JDBC defaults to autocommit true, which is why a Java developer often does not realise a transaction is open at all. Every DDL statement issues an implicit commit before and after itself, so CREATE TABLE, ALTER TABLE, TRUNCATE, DROP, GRANT and REVOKE will silently commit whatever uncommitted DML you had pending.

That is the single most-asked version of this question: TRUNCATE is DDL, so it commits and cannot be rolled back, while DELETE is DML and can. Connecting or disconnecting normally also commits; an abnormal client termination rolls back. SAVEPOINT marks a point inside the transaction, and ROLLBACK TO SAVEPOINT undoes work back to that mark while keeping the transaction and its locks alive, so it does not release row locks acquired before the savepoint.

This is the standard pattern for a batch loop that should skip a bad record and continue rather than abandon the whole run. Separately, Oracle applies statement-level atomicity: if a single UPDATE fails halfway through, for example on row 5000 of 10000 because of a constraint violation, Oracle rolls back that entire statement using an implicit savepoint but leaves the rest of your transaction intact and still open. Candidates often assume the whole transaction rolls back; it does not, and the session is still holding locks, which is exactly the state that produces mysterious long-lived blockers when the application layer swallows the exception and never calls rollback.

BEGIN
  FOR r IN (SELECT * FROM staging_invoices) LOOP
    SAVEPOINT before_row;
    BEGIN
      INSERT INTO invoices (invoice_no, amount_inr)
      VALUES (r.invoice_no, r.amount_inr);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK TO before_row;   -- skip this row, keep the transaction
        INSERT INTO load_errors (invoice_no, reason)
        VALUES (r.invoice_no, 'duplicate');
    END;
  END LOOP;
  COMMIT;
END;
/

-- Find sessions holding an open transaction with no activity
SELECT s.sid, s.username, s.status, s.last_call_et AS idle_secs, t.used_urec
  FROM v$transaction t JOIN v$session s ON s.saddr = t.ses_addr
 ORDER BY s.last_call_et DESC;

Answer

The static data dictionary describes what exists on disk and comes in four prefixes. USER_ views show objects you own, and they have no OWNER column because it is implied. ALL_ views show objects you own plus objects you have been granted access to.

DBA_ views show everything in the current container and need SELECT ANY DICTIONARY or the SELECT_CATALOG_ROLE. CDB_ views, available from the root of a multitenant database, show the DBA_ content across every PDB with an extra CON_ID column. So DBA_TABLES in a PDB shows that PDB only, and CDB_TABLES from CDB$ROOT shows all of them, which trips up people writing container-wide scripts.

The dynamic performance views, prefixed V$ and their global RAC counterparts prefixed GV$ with an INST_ID column, are a completely different animal: they are memory structures exposed as views, they reset when the instance restarts, and they describe what is happening now rather than what is defined. V$SESSION, V$SQL, V$SQL_PLAN, V$LOCK, V$ACTIVE_SESSION_HISTORY, V$SYSTEM_EVENT and V$PARAMETER are the ones you should be able to write from memory. Note that V$ views are actually synonyms for V_$ views over X$ fixed tables, which is why grants behave oddly on them.

Two practical points interviewers like. First, historical equivalents live in DBA_HIST_ views populated by AWR snapshots, so V$ACTIVE_SESSION_HISTORY holds roughly the last hour in memory while DBA_HIST_ACTIVE_SESS_HISTORY holds sampled history for the retention period, and AWR requires a Diagnostics Pack licence. Second, querying V$SESSION in a tight polling loop from a monitoring tool is itself a load source, because those reads take latches on shared memory structures.

-- Static: what exists
SELECT table_name, num_rows, last_analyzed FROM user_tables ORDER BY num_rows DESC;
SELECT owner, object_name, object_type, status FROM dba_objects WHERE status = 'INVALID';

-- Container-wide, run from CDB$ROOT
SELECT con_id, owner, COUNT(*) FROM cdb_tables GROUP BY con_id, owner;

-- Dynamic: what is happening now
SELECT sid, serial#, username, sql_id, event, seconds_in_wait, blocking_session
  FROM v$session WHERE status = 'ACTIVE' AND username IS NOT NULL;

SELECT sql_id, executions, ROUND(elapsed_time/1e6,2) elapsed_s,
       ROUND(elapsed_time/NULLIF(executions,0)/1000,2) ms_per_exec
  FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- Which parameters have been changed from the default?
SELECT name, value FROM v$parameter WHERE isdefault = 'FALSE' ORDER BY name;

Answer

Oracle DATE is a fixed seven-byte type that always stores century, year, month, day, hour, minute and second. It is not a date-only type despite the name, which is why comparing a DATE column to TRUNC(SYSDATE) misses everything after midnight. TIMESTAMP adds fractional seconds, default six digits of precision.

TIMESTAMP WITH TIME ZONE stores the offset or region name alongside the value, so it round-trips correctly across regions. TIMESTAMP WITH LOCAL TIME ZONE normalises to the database time zone on write and converts back to the session time zone on read, which is usually what a multi-region SaaS actually wants. Subtracting two DATEs gives a NUMBER of days, so you multiply by 24 for hours, while subtracting two TIMESTAMPs gives an INTERVAL DAY TO SECOND, and forgetting that difference breaks reports.

The real production hazard is implicit conversion. TO_DATE('01-02-2026') with no format mask uses the session NLS_DATE_FORMAT, so the same code returns 1 February in one session and throws ORA-01843 in another when the client sets a different NLS. Even worse, a WHERE clause comparing a DATE column to a string literal forces Oracle to apply TO_DATE implicitly, and if the conversion lands on the column side instead, your index is not used.

Always pass an explicit format mask, and for anything crossing a client boundary use the unambiguous ANSI literals DATE '2026-02-01' and TIMESTAMP '2026-02-01 10:30:00'. SYSDATE returns the database server time, CURRENT_DATE returns the session time zone time, and SYSTIMESTAMP returns the server time with offset, which is the one you want for audit columns.

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT SYSDATE, CURRENT_DATE, SYSTIMESTAMP, DBTIMEZONE, SESSIONTIMEZONE FROM dual;

-- Always give a format mask
SELECT TO_DATE('01-02-2026', 'DD-MM-YYYY') FROM dual;
SELECT DATE '2026-02-01', TIMESTAMP '2026-02-01 10:30:00 Asia/Calcutta' FROM dual;

-- Index-friendly range predicate on a DATE column
SELECT * FROM orders
 WHERE created_at >= DATE '2026-02-01'
   AND created_at <  DATE '2026-03-01';

-- NOT index friendly: the function is on the column
-- SELECT * FROM orders WHERE TRUNC(created_at) = DATE '2026-02-01';

SELECT (SYSTIMESTAMP - CAST(SYSDATE AS TIMESTAMP)) AS an_interval FROM dual;

Answer

The optimizer skips an index for several distinct reasons and you should be able to separate them. First, a function or implicit conversion applied to the indexed column kills index access: WHERE UPPER(email) = 'X' cannot use an index on email, and WHERE account_no = 12345 on a VARCHAR2 column forces TO_NUMBER(account_no) and does the same. Second, a leading wildcard in LIKE '%foo' cannot use a B-tree because the traversal needs a known prefix.

Third, the leading-column rule: an index on (a, b, c) supports predicates on a, on a and b, and on all three, but a predicate on b alone can only use it through an index skip scan, which is only efficient when a has very few distinct values. Fourth, a plain single-column index does not store all-NULL keys, so IS NULL cannot use it. Fifth, and most commonly, the index is usable but the optimizer decides a full scan is cheaper because the predicate is not selective enough or the statistics are wrong: fetching 30 percent of a table by index means a table access by rowid per row, and multi-block sequential reads win.

The clustering factor is the hidden variable here, an index whose row order matches the physical table order is far cheaper to range scan than one that jumps around. A function-based index solves the first category by indexing the expression itself, for example CREATE INDEX ix_emp_upper ON employees (UPPER(email)), after which WHERE UPPER(email) = :x becomes an index range scan. Gather statistics on it afterwards, because the optimizer needs statistics on the hidden virtual column that Oracle creates behind the scenes.

CREATE INDEX ix_emp_upper_email ON employees (UPPER(email));
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', cascade => TRUE);

SELECT * FROM employees WHERE UPPER(email) = 'RAVI@EXAMPLE.COM';  -- range scan now

-- Partial index effect: index only the rows you query
CREATE INDEX ix_orders_open ON orders (CASE WHEN status = 'OPEN' THEN order_id END);

-- Diagnose why an index is ignored
SELECT index_name, num_rows, distinct_keys, clustering_factor, last_analyzed
  FROM user_indexes WHERE table_name = 'ORDERS';

SELECT index_name, column_position, column_name
  FROM user_ind_columns WHERE table_name = 'ORDERS' ORDER BY index_name, column_position;

Answer

A PL/SQL block has an optional DECLARE section, a mandatory BEGIN to END executable section, and an optional EXCEPTION section immediately before END. An anonymous block has no name and is compiled every time it is submitted; a named block is a procedure, function, package or trigger stored compiled in the database. PL/SQL is a separate engine from SQL, and every SQL statement inside PL/SQL causes a context switch between the two, which is the root cause of slow row-by-row code and the reason BULK COLLECT and FORALL exist. %TYPE anchors a variable to a column or another variable's data type, so v_salary employees.salary%TYPE automatically follows a later ALTER TABLE that widens the column, and your code does not silently truncate. %ROWTYPE does the same for an entire row, giving you a record whose fields match the table or cursor.

Using them consistently is the cheapest way to keep PL/SQL resilient to schema drift, and interviewers treat hard-coded VARCHAR2(30) declarations as a smell. DBMS_OUTPUT.PUT_LINE writes into a session buffer that is only flushed to the client when the call finishes and only if the client has enabled SERVEROUTPUT. That makes it useless for production logging: a batch job running for two hours shows nothing until it ends, a job invoked from an application server never displays anything at all, and the buffer can overflow with ORA-20000 on older releases. Real logging writes to a table from an autonomous transaction so the rows survive a rollback, or uses DBMS_APPLICATION_INFO to set MODULE and ACTION so the work is visible in V$SESSION and in ASH while it is still running.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
  v_emp   employees%ROWTYPE;
  v_bonus employees.salary%TYPE;
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('PAYROLL', 'BONUS_CALC');

  SELECT * INTO v_emp FROM employees WHERE employee_id = 101;
  v_bonus := v_emp.salary * 0.10;

  DBMS_OUTPUT.PUT_LINE('Bonus for ' || v_emp.last_name || ': ' || v_bonus);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee 101 not found');
END;
/

-- Watch a long-running job from another session
SELECT sid, module, action, sql_id, event FROM v$session WHERE module = 'PAYROLL';

Answer

Analytic functions compute a value per row over a window of related rows without collapsing the result set the way GROUP BY does. The syntax is function() OVER (PARTITION BY ... ORDER BY ... windowing_clause).

PARTITION BY resets the calculation per group, ORDER BY defines the sequence within the partition, and the windowing clause defines which rows around the current one participate. ROW_NUMBER, RANK and DENSE_RANK differ only in how they treat ties: with salaries 100, 90, 90, 80, ROW_NUMBER gives 1, 2, 3, 4 arbitrarily breaking the tie, RANK gives 1, 2, 2, 4 leaving a gap, and DENSE_RANK gives 1, 2, 2, 3 with no gap. Interviewers use the classic top-N-per-group problem to test this: get the highest-paid employee per department, where ROW_NUMBER gives exactly one row per department and RANK returns all tied winners.

The critical windowing detail that catches candidates is the default frame. When you write SUM(x) OVER (ORDER BY d), the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total, but RANGE ties on the ORDER BY value, so duplicate dates get the same cumulative value. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want a strict row-by-row running total.

Omit the ORDER BY entirely and the frame becomes the whole partition, which is how you compute a partition total for a percentage-of-total column. LAG and LEAD read the previous and next row, which is the idiomatic way to do month-over-month deltas without a self join, and both accept a default value to avoid NULL on the boundary rows.

-- Highest paid employee per department
SELECT * FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
    FROM employees e)
 WHERE rn = 1;

-- Tie behaviour side by side
SELECT last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
  FROM employees;

-- Running total and share of total
SELECT sale_date, amount_inr,
       SUM(amount_inr) OVER (ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       ROUND(100 * amount_inr / SUM(amount_inr) OVER (), 2) AS pct_of_total,
       LAG(amount_inr, 1, 0) OVER (ORDER BY sale_date) AS prev_day
  FROM daily_sales;

Answer

MERGE performs an upsert in one statement: it joins a target table to a source query on an ON condition, then applies WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT. It is a single pass over the source, which makes it dramatically faster than the alternative of trying an update, checking SQL%ROWCOUNT and inserting if zero, especially for ETL loads of millions of rows. You can add a DELETE clause inside the WHEN MATCHED branch, which only deletes rows the update touched, a subtlety candidates often get wrong.

You can also add WHERE clauses to each branch to skip unnecessary updates, which is worth doing because an update that writes identical values still generates redo, undo and index maintenance. ORA-30926, unable to get a stable set of rows in the source tables, means your source produced more than one row matching the same target row, so Oracle cannot decide which value wins. The fix is to deduplicate the source, typically with a ROW_NUMBER filter or a GROUP BY, and you should assume every interviewer asking about MERGE is waiting for this answer.

ORA-38104 means you tried to update a column that appears in the ON clause; the join key is immutable inside MERGE and you must handle key changes as a delete plus insert. Two more production notes: MERGE takes the same row locks as the underlying DML, so two concurrent merges on overlapping keys will block and can deadlock if they process rows in different orders, and using an APPEND hint with MERGE gives direct-path insert for the not-matched branch, which bypasses the buffer cache and is much faster for bulk loads but takes an exclusive table lock.

MERGE INTO customer_dim d
USING (
  SELECT customer_id, name, city, updated_at
    FROM (SELECT s.*,
                 ROW_NUMBER() OVER (PARTITION BY customer_id
                                    ORDER BY updated_at DESC) rn
            FROM customer_stage s)
   WHERE rn = 1                      -- dedupe: prevents ORA-30926
) s
ON (d.customer_id = s.customer_id)   -- do not UPDATE customer_id: ORA-38104
WHEN MATCHED THEN UPDATE SET d.name = s.name, d.city = s.city
  WHERE DECODE(d.name, s.name, 0, 1) = 1     -- skip no-op updates
     OR DECODE(d.city, s.city, 0, 1) = 1
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, city) VALUES (s.customer_id, s.name, s.city)
LOG ERRORS INTO merge_err ('cust_load') REJECT LIMIT UNLIMITED;

Answer

A view is a stored query with no data of its own. Every reference to it is merged into the calling statement by the optimizer, so a view over a slow query is still a slow query, and a view with an ORDER BY inside it does not guarantee output order once it is joined to something else. Views can be updatable if they are key preserved, meaning the optimizer can prove each row of the view maps to exactly one row of one base table; otherwise you need an INSTEAD OF trigger.

WITH CHECK OPTION prevents DML through the view from creating rows the view itself could not see, and WITH READ ONLY is the safe default for reporting views. A materialized view stores the result physically, so it is a table plus a refresh mechanism plus optional query rewrite. Refresh can be COMPLETE, which truncates and repopulates, FAST, which applies only the changes recorded in materialized view logs on the base tables, ON COMMIT, which pushes the refresh cost into every base transaction, or ON DEMAND via DBMS_MVIEW.REFRESH.

Query rewrite is the real payoff in a warehouse: with QUERY REWRITE enabled and adequate statistics, a user query against base tables is silently redirected to the aggregate. Materialized views are the wrong answer when the data must be transactionally current and the base tables are write-heavy, because ON COMMIT refresh serialises transactions on the aggregate rows and turns a fast OLTP insert into a hotspot. They are also wrong when a properly indexed query or a partitioned base table would solve the problem, and when nobody owns the operational burden: a FAST refresh silently downgrades to COMPLETE if the materialized view log is missing a required column or a ROWID clause, and the first sign is a nightly job that suddenly takes four hours.

CREATE MATERIALIZED VIEW LOG ON sales
  WITH ROWID, SEQUENCE (product_id, sale_date, amount_inr)
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_sales_daily
  BUILD IMMEDIATE
  REFRESH FAST ON DEMAND
  ENABLE QUERY REWRITE
AS
SELECT product_id, TRUNC(sale_date) AS d,
       SUM(amount_inr) AS total_inr, COUNT(*) AS cnt
  FROM sales
 GROUP BY product_id, TRUNC(sale_date);

-- Will FAST refresh actually work? Ask Oracle, do not guess
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_SALES_DAILY');
SELECT capability_name, possible, msgtxt FROM mv_capabilities_table
 WHERE capability_name LIKE 'REFRESH_FAST%';

EXEC DBMS_MVIEW.REFRESH('MV_SALES_DAILY', method => 'F');

Answer

Oracle uses multiversion read consistency built on undo. Every change stamps the block with a System Change Number. When your query starts, it records the current SCN, and for every block it reads it compares the block SCN to the query SCN.

If the block has been changed since your query began, Oracle clones the buffer and rolls it back using undo records until it represents the state at your SCN, producing a consistent read block. That is why readers never block writers and writers never block readers, and why Oracle has no equivalent of the shared read locks that dominate older SQL Server behaviour. The default isolation level is READ COMMITTED, applied per statement, so each statement in a transaction gets its own snapshot.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE gives a transaction-level snapshot and can fail with ORA-08177 on write conflicts; READ ONLY gives the same snapshot without allowing DML, which is the correct setting for a long reporting query that must see one consistent point in time. ORA-01555, snapshot too old, happens when the undo needed to reconstruct that older image has already been overwritten by other transactions. It is not caused by your query being big, it is caused by your query being long-running while other sessions churn undo faster than UNDO_RETENTION protects it.

The classic trigger is a cursor open across a loop that commits inside itself, the so-called fetch across commit, because your own commits release the undo you still need. Fixes in order of preference: stop committing inside the fetch loop, size the undo tablespace properly and set RETENTION GUARANTEE if the reporting window matters more than DML, and shorten the query. Increasing UNDO_RETENTION alone does nothing if the undo tablespace is too small to honour it.

-- Reporting session that must see one consistent point in time
SET TRANSACTION READ ONLY;
-- ... long report ...
COMMIT;

-- Undo sizing reality check
SELECT MAX(maxquerylen) AS longest_query_secs,
       MAX(tuned_undoretention) AS tuned_retention_secs
  FROM v$undostat;

SELECT tablespace_name, retention FROM dba_tablespaces WHERE contents = 'UNDO';
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- Anti-pattern that causes ORA-01555
-- FOR r IN (SELECT * FROM big_table) LOOP
--   UPDATE big_table SET flag='Y' WHERE ROWID = r.ROWID;
--   COMMIT;              -- committing inside the open cursor's fetch loop
-- END LOOP;

SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%ORA-01555%';

Answer

Oracle locks rows, not pages, and it stores the lock in the data block itself in an Interested Transaction List entry rather than in a memory lock manager, which is why there is no lock escalation and no practical limit on the number of row locks. A session waiting for a row held by another waits on the TX enqueue with event enq: TX - row lock contention. TM locks are table-level locks taken by DML to stop concurrent DDL, which is why an uncommitted insert blocks an ALTER TABLE.

Other TX flavours matter: mode 4 usually means an index unique or bitmap conflict, or an ITL shortage in the block, rather than the same row. SELECT FOR UPDATE takes the row locks explicitly, and FOR UPDATE NOWAIT fails immediately with ORA-00054 while FOR UPDATE SKIP LOCKED silently ignores locked rows, which is the standard pattern for a queue table with multiple worker processes. To find the blocker, V$SESSION.BLOCKING_SESSION points directly at the holder, and V$LOCK plus DBA_BLOCKERS give the full chain.

Deadlocks raise ORA-00060 and Oracle resolves them automatically by rolling back one statement, not the whole transaction, so the victim session is still alive and still holding its other locks. Oracle writes a trace file to the diagnostic destination containing a deadlock graph with the resource names, the two sessions, and the SQL each was running. Read the graph to identify the two objects and the access order.

The overwhelmingly common causes are two code paths updating the same set of rows in different orders, and unindexed foreign keys causing unexpected child-table share locks. The fix is almost always to impose a consistent ordering, for example always locking parent before child and always processing IDs ascending.

-- Who is blocking whom, right now
SELECT s.sid, s.serial#, s.username, s.event, s.seconds_in_wait,
       s.blocking_session, s.sql_id,
       o.object_name
  FROM v$session s
  LEFT JOIN dba_objects o ON o.object_id = s.row_wait_obj#
 WHERE s.blocking_session IS NOT NULL;

-- Full lock picture
SELECT sid, type, id1, id2, lmode, request, block FROM v$lock
 WHERE type IN ('TX','TM') ORDER BY block DESC, sid;

-- Queue table consumed by many workers without blocking
SELECT job_id, payload FROM job_queue
 WHERE status = 'PENDING'
 ORDER BY created_at
 FOR UPDATE SKIP LOCKED;

-- Fail fast instead of hanging
SELECT * FROM accounts WHERE account_id = :id FOR UPDATE NOWAIT;  -- ORA-00054
SELECT * FROM accounts WHERE account_id = :id FOR UPDATE WAIT 5;

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

Answer

When a statement arrives, Oracle hashes its text to a SQL_ID and looks in the library cache inside the shared pool. If an identical, reusable cursor exists, it is a soft parse and Oracle skips optimisation entirely. If not, it is a hard parse: syntax and semantic checks, permission checks, and a full optimisation pass that considers access paths, join orders and join methods, all while holding library cache mutexes.

Hard parsing is expensive in CPU and, worse, it serialises. An application that concatenates literals into SQL generates a distinct SQL_ID for every value, so a payments API doing five thousand transactions per second hard parses five thousand times per second, floods the shared pool with single-use cursors, and produces library cache mutex X waits and eventually ORA-04031 when the pool fragments. Bind variables fix this by making the text constant.

In PL/SQL, static SQL is automatically bound, so the problem is almost always in Java, .NET or Python code building strings, and the fix is parameterised statements plus a properly configured connection pool that reuses PreparedStatements. Binds are also the primary defence against SQL injection. The trade-off interviewers want you to name is bind peeking: on the first hard parse Oracle peeks at the actual bind value and optimises for it, so a skewed column can get a plan that suits the rare value and is terrible for the common one.

Adaptive Cursor Sharing mitigates this by creating multiple child cursors for bind-sensitive predicates. CURSOR_SHARING=FORCE makes Oracle rewrite literals into system-generated binds; treat it as a temporary tourniquet for a third-party application you cannot change, not a design choice, because it defeats literal-specific plans and interacts badly with function-based indexes and histograms.

-- Find literal SQL: many SQL_IDs sharing one FORCE_MATCHING_SIGNATURE
SELECT force_matching_signature, COUNT(DISTINCT sql_id) AS variants,
       SUM(executions) AS execs, MIN(sql_text) AS sample
  FROM v$sql
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(DISTINCT sql_id) > 50
 ORDER BY variants DESC;

-- Parse ratio: soft parses should dominate
SELECT name, value FROM v$sysstat
 WHERE name IN ('parse count (total)','parse count (hard)','execute count');

-- Bind-sensitive and bind-aware child cursors (Adaptive Cursor Sharing)
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, executions
  FROM v$sql WHERE sql_id = '&sql_id';

-- PL/SQL static SQL is bound automatically
SELECT amount_inr INTO v_amt FROM payments WHERE payment_id = p_id;

-- Dynamic SQL: bind with USING, never concatenate
EXECUTE IMMEDIATE 'SELECT amount_inr FROM payments WHERE payment_id = :1'
  INTO v_amt USING p_id;

Answer

Dynamic SQL comes in two forms. Native Dynamic SQL uses EXECUTE IMMEDIATE for single statements and OPEN cursor FOR when you need to fetch a variable result set, and it is the fast, readable default. DBMS_SQL is the older API and remains necessary when you do not know the number or type of columns at compile time, for example a generic export routine, and it is also how you handle a variable number of bind variables through DBMS_SQL.BIND_VARIABLE calls.

The security rule is absolute: values must be bound with USING, never concatenated. EXECUTE IMMEDIATE 'SELECT ... WHERE user_id = ' || p_id is injectable and also generates a new SQL_ID per call, so it fails on both security and performance grounds.

The complication is that bind variables cannot be used for identifiers: you cannot bind a table name, a column name, a schema name, or the direction of an ORDER BY. Those must be concatenated, which is exactly the injection surface. DBMS_ASSERT is the sanctioned way to validate them.

SIMPLE_SQL_NAME verifies that a string is a legal unqualified SQL name, SQL_OBJECT_NAME verifies the object actually exists and is accessible, SCHEMA_NAME checks it is a real schema, and ENQUOTE_LITERAL and ENQUOTE_NAME wrap a value in quotes while escaping embedded quotes. The second half of the answer is privilege model. A stored procedure runs with definer's rights by default, so it executes with the owner's privileges, and an injection there escalates to the owner. AUTHID CURRENT_USER switches to invoker's rights so the caller's own privileges apply, and in recent versions you should also consider granting INHERIT PRIVILEGES carefully and using an ACCESSIBLE BY clause on package specifications to restrict who can call sensitive code.

CREATE OR REPLACE PROCEDURE archive_rows (
  p_table IN VARCHAR2,
  p_cutoff IN DATE,
  p_deleted OUT NUMBER
) AUTHID CURRENT_USER AS
  l_table VARCHAR2(128);
  l_sql   VARCHAR2(4000);
BEGIN
  -- identifier cannot be bound, so validate it
  l_table := DBMS_ASSERT.SQL_OBJECT_NAME(DBMS_ASSERT.SIMPLE_SQL_NAME(p_table));

  l_sql := 'DELETE FROM ' || l_table || ' WHERE created_at < :cutoff';
  EXECUTE IMMEDIATE l_sql USING p_cutoff;   -- value IS bound
  p_deleted := SQL%ROWCOUNT;
END archive_rows;
/

-- Variable result set with a ref cursor
DECLARE
  c SYS_REFCURSOR;
  v_name VARCHAR2(100);
BEGIN
  OPEN c FOR 'SELECT last_name FROM employees WHERE department_id = :d' USING 60;
  LOOP
    FETCH c INTO v_name; EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/

Answer

Every SQL statement executed from PL/SQL crosses from the PL/SQL engine to the SQL engine and back. A cursor loop doing a hundred thousand single-row updates pays that context switch two hundred thousand times, and that overhead usually dominates the actual work. BULK COLLECT fetches many rows into a collection in one round trip, and FORALL sends many DML statements to the SQL engine in one call.

Typical speedups on real batch code are five to twenty times, which is why this is the single highest-value PL/SQL optimisation and appears in nearly every PL/SQL developer interview. The critical detail is LIMIT. An unbounded BULK COLLECT INTO on a ten-million-row table builds the entire collection in the session PGA, and since PGA memory is per process, a handful of concurrent jobs will hit PGA_AGGREGATE_LIMIT and Oracle will kill sessions with ORA-04036.

Always fetch in chunks, commonly one thousand to ten thousand rows, using a loop with FETCH ... BULK COLLECT INTO ... LIMIT and EXIT WHEN collection.COUNT = 0, not EXIT WHEN cursor%NOTFOUND, because the last partial batch sets NOTFOUND while still containing rows.

FORALL is not a loop: the index variable can only be used to subscript collections, you cannot put arbitrary statements inside it, and it issues one DML statement per element but with a single context switch. By default the first failing element aborts the whole FORALL and rolls back all its changes. SAVE EXCEPTIONS changes that: the remaining elements still execute, and afterwards Oracle raises ORA-24381 which you catch and inspect through SQL%BULK_EXCEPTIONS, giving you the ERROR_INDEX and ERROR_CODE of every failure. That is how you build a batch loader that reports every bad row in one pass instead of failing on the first.

DECLARE
  CURSOR c IS SELECT invoice_id, amount_inr FROM staging_invoices;
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows  t_rows;
  l_errs  NUMBER;
  bulk_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_error, -24381);
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 5000;
    EXIT WHEN l_rows.COUNT = 0;          -- not c%NOTFOUND

    BEGIN
      FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
        INSERT INTO invoices (invoice_id, amount_inr)
        VALUES (l_rows(i).invoice_id, l_rows(i).amount_inr);
    EXCEPTION
      WHEN bulk_error THEN
        l_errs := SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1 .. l_errs LOOP
          INSERT INTO load_errors (invoice_id, err_code)
          VALUES (l_rows(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).invoice_id,
                  SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
        END LOOP;
    END;
    COMMIT;
  END LOOP;
  CLOSE c;
END;
/

Answer

An implicit cursor is what Oracle creates for a SELECT INTO or any DML statement you write without declaring a cursor. It is the cleanest option for single-row fetches, and it deliberately raises NO_DATA_FOUND when nothing matches and TOO_MANY_ROWS when more than one row matches, which is a feature: it makes a broken assumption loud rather than silent. After DML you read its attributes through SQL%ROWCOUNT, SQL%FOUND and SQL%NOTFOUND.

An explicit cursor is declared with a name, then opened, fetched and closed, and you control the lifecycle. The cursor FOR loop is the pragmatic middle ground because Oracle handles OPEN, FETCH, EXIT and CLOSE for you and silently array-fetches one hundred rows at a time, so a simple FOR r IN (SELECT ...) LOOP is far better than a hand-written fetch-one-row loop.

Explicit cursors matter when you need FOR UPDATE with WHERE CURRENT OF, or a parameterised cursor reused with different arguments. A ref cursor is a pointer to a result set rather than a fixed query. Declared as SYS_REFCURSOR or a strongly typed REF CURSOR RETURN rowtype, it can be assigned different queries at runtime and, critically, it can be passed out of PL/SQL to a client.

That is the standard Oracle pattern for returning a result set from a stored procedure to Java, .NET or Python, where the client receives it as a JDBC ResultSet or an equivalent. Strong ref cursors are type checked at compile time; weak ones are flexible but errors surface at runtime. Two gotchas: a ref cursor left open leaks a cursor and eventually causes ORA-01000 maximum open cursors exceeded, and you cannot BULK COLLECT from a ref cursor into a collection and also return it, since fetching consumes it.

CREATE OR REPLACE PACKAGE reporting AS
  TYPE t_emp_cur IS REF CURSOR RETURN employees%ROWTYPE;  -- strong
  FUNCTION dept_employees (p_dept IN NUMBER) RETURN SYS_REFCURSOR;  -- weak
END reporting;
/

CREATE OR REPLACE PACKAGE BODY reporting AS
  FUNCTION dept_employees (p_dept IN NUMBER) RETURN SYS_REFCURSOR IS
    c SYS_REFCURSOR;
  BEGIN
    OPEN c FOR SELECT employee_id, last_name, salary
                 FROM employees WHERE department_id = p_dept;
    RETURN c;   -- the caller must close it
  END;
END reporting;
/

-- Explicit cursor with WHERE CURRENT OF
DECLARE
  CURSOR c IS SELECT salary FROM employees
               WHERE department_id = 50 FOR UPDATE;
BEGIN
  FOR r IN c LOOP
    UPDATE employees SET salary = r.salary * 1.05 WHERE CURRENT OF c;
  END LOOP;
  COMMIT;
END;
/

SELECT value FROM v$parameter WHERE name = 'open_cursors';

Answer

PL/SQL has three exception categories. Predefined exceptions such as NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, ZERO_DIVIDE, INVALID_CURSOR and VALUE_ERROR are named for you. Non-predefined Oracle errors have a number but no name, and you attach one with PRAGMA EXCEPTION_INIT, for example binding ORA-02291 to a foreign_key_missing exception so your handler reads as business logic instead of magic numbers.

User-defined exceptions are declared as EXCEPTION variables and raised with RAISE, and if they must reach a client you raise them with RAISE_APPLICATION_ERROR using an error number between -20000 and -20999, which is the only range Oracle reserves for applications. RAISE_APPLICATION_ERROR takes a third argument, keep_errors, and passing TRUE preserves the existing error stack instead of replacing it, which most code forgets. The mistake that ruins production debugging is WHEN OTHERS THEN NULL, or logging only SQLERRM.

SQLERRM tells you what went wrong but not where, so a five-hundred-line package reports ORA-01403 with no line number. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE returns the exact program unit and line where the error was originally raised, and it must be called in the handler before any other statement re-raises or overwrites the stack. DBMS_UTILITY.FORMAT_ERROR_STACK gives the nested error chain.

Since 12c, UTL_CALL_STACK provides the same information in structured form with ERROR_DEPTH, BACKTRACE_LINE and BACKTRACE_UNIT so you can log fields rather than parse text. The professional pattern is a WHEN OTHERS handler that captures SQLCODE, SQLERRM and the backtrace, writes them to an error table from an autonomous transaction so the row survives the rollback, and then re-raises with a bare RAISE so the caller still sees the failure.

CREATE OR REPLACE PROCEDURE post_payment (p_id NUMBER) IS
  parent_missing EXCEPTION;
  PRAGMA EXCEPTION_INIT(parent_missing, -2291);   -- ORA-02291 integrity constraint
  dup_key        EXCEPTION;
  PRAGMA EXCEPTION_INIT(dup_key, -1);             -- ORA-00001 unique violation
BEGIN
  INSERT INTO ledger (payment_id) VALUES (p_id);
EXCEPTION
  WHEN parent_missing THEN
    RAISE_APPLICATION_ERROR(-20010, 'Payment ' || p_id || ' has no order', TRUE);
  WHEN dup_key THEN
    NULL;  -- idempotent replay, deliberately ignored
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;  -- never swallow
END post_payment;
/

-- Structured backtrace (12c and later)
BEGIN
  NULL;
EXCEPTION WHEN OTHERS THEN
  FOR i IN 1 .. UTL_CALL_STACK.BACKTRACE_DEPTH LOOP
    DBMS_OUTPUT.PUT_LINE(UTL_CALL_STACK.BACKTRACE_UNIT(i) || ' line ' ||
                         UTL_CALL_STACK.BACKTRACE_LINE(i));
  END LOOP;
END;
/

Answer

PRAGMA AUTONOMOUS_TRANSACTION declares that a procedure, function, trigger or top-level anonymous block runs in its own independent transaction. Oracle suspends the calling transaction, gives the autonomous unit its own undo, locks and commit scope, and resumes the caller afterwards. Anything the autonomous unit commits is durable even if the caller subsequently rolls back.

That makes it the correct and essentially only tool for two jobs: audit or error logging that must survive a rollback, and consuming a sequence or writing a monitoring heartbeat from inside a read-only context. Everything else is usually a misuse. The dangers are real.

An autonomous transaction cannot see the uncommitted changes of its parent, because it is a separate transaction with its own read-consistent view, so a logging routine that tries to read the row the caller just inserted finds nothing. Worse, it can deadlock against its own parent: if the parent holds a row lock and the autonomous unit tries to update the same row, nobody can proceed and you get ORA-00060 with a deadlock graph naming a single session, which is a genuinely confusing incident to debug at 2 a.m. ORA-06519 means an active autonomous transaction was detected and rolled back, which happens when the autonomous block exits without an explicit COMMIT or ROLLBACK.

Unlike a normal top-level transaction, an autonomous block must terminate its transaction before returning, and Oracle refuses to let it leak. So every exit path, including every exception handler, needs a COMMIT or a ROLLBACK. Keep autonomous units tiny, touching only their own logging tables, and never call business logic from inside one.

CREATE OR REPLACE PROCEDURE log_error (
  p_code NUMBER, p_msg VARCHAR2, p_backtrace VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_error_log (logged_at, err_code, err_msg, backtrace, sid)
  VALUES (SYSTIMESTAMP, p_code, p_msg, p_backtrace,
          SYS_CONTEXT('USERENV','SID'));
  COMMIT;                       -- mandatory, or ORA-06519
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;                   -- every exit path must end the transaction
END log_error;
/

-- The row survives even when the caller rolls back
BEGIN
  INSERT INTO invoices (invoice_id) VALUES (1);
  log_error(-1, 'test', 'none');
  ROLLBACK;   -- invoice gone, log row stays
END;
/

Answer

A table is mutating while a statement is modifying it. Inside a row-level trigger on that table, Oracle cannot give you a read-consistent view of the table, because the statement is halfway through changing it and the result would depend on the arbitrary order rows are processed. So a SELECT against the triggering table from a FOR EACH ROW trigger raises ORA-04091.

The same restriction extends to tables related by an unindexed foreign key in some constraint scenarios. The classic scenario is a business rule such as total salary in a department must not exceed a cap: the row trigger wants to sum the department after the change, and cannot. Two things are legal: the trigger can read and modify the current row through :NEW and :OLD, and a statement-level trigger can query the table freely because the statement has finished.

The historical workaround was a package with a collection: the row trigger stashes the affected keys in a package-level collection, and an AFTER STATEMENT trigger reads the collection and does the aggregate check. That works but spreads the logic across three objects and breaks if the package state is reset. The compound trigger, added in 11g, packages all four timing points into one object with a shared declaration section whose variables persist for the duration of the statement: BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT.

You collect keys in AFTER EACH ROW and validate in AFTER STATEMENT, all in one readable unit, and Oracle resets the state automatically per statement. Always reinitialise the collection in BEFORE STATEMENT, because a single call can execute the statement more than once, for example under a restart. The honest senior answer is that most mutating-table problems are a sign the rule belongs in a constraint, a materialized view with a check, or the application service layer rather than in a trigger at all.

CREATE OR REPLACE TRIGGER trg_dept_salary_cap
  FOR INSERT OR UPDATE OF salary ON employees
  COMPOUND TRIGGER

  TYPE t_depts IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  g_depts t_depts;

  BEFORE STATEMENT IS
  BEGIN
    g_depts.DELETE;                 -- reset per statement
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    g_depts(:NEW.department_id) := :NEW.department_id;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    l_total NUMBER;
    l_idx   PLS_INTEGER := g_depts.FIRST;
  BEGIN
    WHILE l_idx IS NOT NULL LOOP
      SELECT SUM(salary) INTO l_total     -- legal here, statement is done
        FROM employees WHERE department_id = g_depts(l_idx);
      IF l_total > 5000000 THEN
        RAISE_APPLICATION_ERROR(-20020,
          'Department ' || g_depts(l_idx) || ' exceeds salary budget');
      END IF;
      l_idx := g_depts.NEXT(l_idx);
    END LOOP;
  END AFTER STATEMENT;

END trg_dept_salary_cap;
/

Answer

A package has a specification, which is the public contract, and a body, which holds the implementation plus anything private. That separation is the main benefit: you can recompile the body without invalidating anything that depends on the spec, so dependent code does not go INVALID and does not need recompilation. Standalone procedures have no such shield, and changing one cascades invalidation across everything that calls it.

Packages also give you overloading, private helper routines, package-level constants and types shared by the whole application, forward declarations for mutual recursion, and the initialisation block at the end of the body that runs once per session on first reference. Performance matters too: the entire package is loaded into the shared pool on first call, so subsequent calls to any of its procedures avoid extra loads, and you can pin hot packages with DBMS_SHARED_POOL.KEEP. Package state is any variable, constant, cursor or collection declared at package level rather than inside a procedure.

It lives in the session UGA and persists for the whole session, which makes it useful for caching lookup data and dangerous for anything else, because in a connection-pooled application the next request gets a different user on a session that still holds the previous user's cached values. ORA-04068, existing state of packages has been discarded, is raised when a package with state is recompiled while a session holds instantiated state: Oracle throws away the state and raises the error on the next call. The following call succeeds, which is why people patch it with a retry, but the real fixes are to deploy DDL in a maintenance window, keep public state out of packages, or declare PRAGMA SERIALLY_REUSABLE so the state is discarded after each call rather than kept for the session.

CREATE OR REPLACE PACKAGE tax_util AS
  g_fy CONSTANT VARCHAR2(7) := '2026-27';
  FUNCTION gst_amount (p_base NUMBER, p_rate NUMBER DEFAULT 18) RETURN NUMBER;
  PROCEDURE recalc (p_invoice_id NUMBER);
END tax_util;
/

CREATE OR REPLACE PACKAGE BODY tax_util AS
  g_cache_loaded BOOLEAN := FALSE;   -- package state: lives for the session

  FUNCTION gst_amount (p_base NUMBER, p_rate NUMBER DEFAULT 18) RETURN NUMBER IS
  BEGIN
    RETURN ROUND(p_base * p_rate / 100, 2);
  END;

  PROCEDURE recalc (p_invoice_id NUMBER) IS BEGIN NULL; END;

BEGIN
  g_cache_loaded := TRUE;            -- initialisation block, runs once per session
END tax_util;
/

-- What is invalid after a deployment, and why
SELECT object_name, object_type, status FROM user_objects WHERE status = 'INVALID';
EXEC DBMS_UTILITY.COMPILE_SCHEMA(USER, compile_all => FALSE);

-- Pin a hot package in the shared pool
EXEC DBMS_SHARED_POOL.KEEP('APP.TAX_UTIL', 'P');

Answer

Partitioning splits one logical table into physically separate segments, which buys three things: partition pruning so a query touches only relevant partitions, partition-wise operations such as dropping a month of history in seconds instead of a long DELETE, and parallel operations that scale across partitions. Range partitioning on a date is the default for time-series data. Interval partitioning extends it so Oracle creates the next partition automatically on first insert, which removes the maintenance job that everyone forgets until it fails with ORA-14400 inserted partition key does not map to any partition.

List partitioning suits a discrete set such as region or state, with automatic list partitioning creating new values on demand. Hash partitioning distributes evenly when there is no natural key and is mostly used to spread contention or enable partition-wise joins. Composite schemes such as range-hash combine both.

Pruning only happens when the partition key appears in the predicate in a form the optimizer can evaluate, so a function applied to the key, an implicit type conversion, or a bind variable whose value is unknown at parse time can degrade you from static pruning to dynamic pruning or none at all. Check the PSTART and PSTOP columns in the plan to confirm. On indexes: a local index is partitioned the same way as the table, so each partition has its own index segment, maintenance is per partition, and dropping or truncating a partition does not invalidate anything.

A global index spans all partitions and is the right choice for a unique key that does not include the partition key, or for OLTP lookups by a non-key column, but any partition maintenance operation marks it UNUSABLE unless you add UPDATE INDEXES, which makes the drop much slower. The rule of thumb is local for warehouse and rolling-window tables, global for primary keys and hot OLTP lookups. Partitioning is a separately licensed Enterprise Edition option, which matters commercially in Indian deals.

CREATE TABLE sales (
  sale_id     NUMBER,
  sale_date   DATE NOT NULL,
  region      VARCHAR2(20),
  amount_inr  NUMBER(14,2)
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
( PARTITION p_start VALUES LESS THAN (DATE '2026-01-01') );

CREATE INDEX ix_sales_region ON sales (region) LOCAL;
CREATE UNIQUE INDEX ix_sales_pk ON sales (sale_id) GLOBAL;

-- Pruning: PSTART/PSTOP should show a narrow range, not 1 to 1048575
EXPLAIN PLAN FOR
SELECT SUM(amount_inr) FROM sales
 WHERE sale_date >= DATE '2026-03-01' AND sale_date < DATE '2026-04-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PARTITION'));

-- Drop a month in seconds, keeping global indexes valid
ALTER TABLE sales DROP PARTITION FOR (DATE '2026-01-15') UPDATE INDEXES;

SELECT partition_name, high_value, num_rows FROM user_tab_partitions
 WHERE table_name = 'SALES' ORDER BY partition_position;

Answer

The cost-based optimizer chooses a plan from statistics, so statistics quality determines plan quality. DBMS_STATS.GATHER_TABLE_STATS collects row counts, block counts, average row length, and per-column distinct values, low and high values, null counts and density; CASCADE => TRUE includes indexes. Leave ESTIMATE_PERCENT at DBMS_STATS.AUTO_SAMPLE_SIZE, which uses a hash-based algorithm that is close to a full scan in accuracy at a fraction of the cost, and leave METHOD_OPT at FOR ALL COLUMNS SIZE AUTO unless you have a reason.

The automatic optimizer statistics task runs in the maintenance windows and refreshes objects whose rows have changed by more than STALE_PERCENT, ten percent by default, tracked in DBA_TAB_MODIFICATIONS. Histograms describe skew. Without one, the optimizer assumes uniform distribution, so a status column with values ACTIVE at ninety-nine percent and FAILED at one percent is estimated at fifty percent each and the FAILED lookup gets a full scan instead of an index.

Frequency histograms handle up to 254 distinct values exactly, top frequency histograms cover the dominant values, hybrid histograms combine height balanced and frequency approaches for high-cardinality skew. Histograms are only created when the column is skewed and has actually been used in a predicate, which Oracle knows from column usage tracking, so gathering stats on a fresh table often creates none. Statistics gathering makes things slower in three recognisable ways.

New statistics can flip a plan that was accidentally good, which is what SQL plan baselines exist to prevent. Bind peeking plus a new histogram can produce a plan tuned to an unrepresentative first bind value. And the gather itself invalidates dependent cursors, causing a hard parse storm at the exact moment your batch window ends. Use NO_INVALIDATE and pending statistics to test before publishing, and use incremental statistics on partitioned tables so a new partition does not force a full table scan.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'SALES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    granularity      => 'AUTO',
    cascade          => TRUE,
    degree           => 4,
    no_invalidate    => FALSE);
END;
/

-- Incremental stats: a new partition should not force a full table scan
EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'SALES', 'INCREMENTAL', 'TRUE');

-- Test before you publish
EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'SALES', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES');
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;   -- try the new plan
EXEC DBMS_STATS.PUBLISH_PENDING_STATS(USER, 'SALES');        -- or DELETE_PENDING_STATS

-- Which columns actually have histograms?
SELECT column_name, num_distinct, histogram, num_buckets
  FROM user_tab_col_statistics WHERE table_name = 'SALES';

-- How stale is it?
SELECT table_name, inserts, updates, deletes, truncated
  FROM user_tab_modifications WHERE table_name = 'SALES';

Answer

EXPLAIN PLAN gives you the plan the optimizer would probably choose, not the plan that actually ran. It does not peek at bind values, it can differ because of adaptive plans, and it never tells you how many rows each step really produced. For real tuning you need the plan from the cursor cache.

Run the statement with the GATHER_PLAN_STATISTICS hint, or set STATISTICS_LEVEL to ALL for the session, then call DBMS_XPLAN.DISPLAY_CURSOR with format ALLSTATS LAST. That gives you E-Rows, the estimate, alongside A-Rows, the actual, plus A-Time, Buffers and the number of Starts for each row source. Buffers is the metric to optimise: logical reads are the currency of SQL work, and a plan that halves buffers will almost always be faster regardless of what the cost column says.

Read the plan from the most indented leaf outward, and compare E-Rows to A-Rows at every step. A misestimate at the deepest level cascades: if the optimizer thinks a scan returns ten rows and it returns a million, it will pick a nested loop that executes the inner side a million times, and the plan is doomed no matter what happens above. So find the first step where the estimate diverges by an order of magnitude and fix that, do not start at the top.

The usual causes of a bad estimate are stale or missing statistics, a missing histogram on a skewed column, correlated predicates the optimizer assumes are independent, a function on a column that forces a default five percent guess, and implicit type conversion. Fixes in order: gather statistics, add a histogram or extended statistics on the correlated column group, rewrite the predicate, and only then consider a hint or a SQL plan baseline. Also check the Note section at the bottom, which reports dynamic sampling, cardinality feedback and adaptive plan switches.

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ GATHER_PLAN_STATISTICS find_me */ o.order_id, c.name
  FROM orders o JOIN customers c ON c.customer_id = o.customer_id
 WHERE o.status = 'FAILED' AND o.created_at >= DATE '2026-02-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST +PEEKED_BINDS'));

-- Or fetch a plan for a SQL_ID already in the cursor cache
SELECT sql_id, child_number, plan_hash_value, executions
  FROM v$sql WHERE sql_text LIKE '%find_me%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'ALLSTATS LAST'));

-- Historical plans from AWR (Diagnostics Pack)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

-- Correlated predicates: teach the optimizer they are not independent
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'ORDERS', '(status, channel)') FROM dual;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS', method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Answer

A bitmap index stores, for each distinct key value, a compressed bitmap with one bit per row. That makes it tiny on low-cardinality columns and extremely fast for combining predicates, because Oracle can AND and OR the bitmaps directly before touching the table, which is why star transformation in a data warehouse depends on bitmap indexes over the fact table's foreign keys. Bitmap indexes also index NULLs, unlike single-column B-trees, so COUNT(*) and IS NULL predicates can be answered from the index.

The danger in OLTP is locking granularity. Updating one row's indexed value locks the entire bitmap segment covering a range of rowids, which can be hundreds or thousands of rows, so two concurrent transactions updating unrelated rows that share a bitmap piece will block each other. On a busy transactional table this converts into enq: TX waits and effectively serialises DML.

The rule is bitmap for read-mostly warehouse tables, never on a table with concurrent single-row DML. Bitmap join indexes go further and index a column of a dimension against the fact table's rowids, eliminating the join entirely for filtered queries. Invisible indexes, added in 11g, are maintained by DML but ignored by the optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES is set or a hint names them.

They solve two real problems: testing whether a new index helps without exposing every session to a plan change, and safely retiring a suspected unused index, since you can make it invisible for a week and instantly make it visible again if something regresses, which beats dropping and rebuilding a two-hundred-gigabyte index. An unusable index is different: it is not maintained at all, DML skips it, and queries cannot use it, which is the standard trick for a bulk load where you mark indexes unusable, load with direct path, then rebuild. A unique index marked unusable will make inserts fail, so mind that distinction.

-- Warehouse: bitmap on low-cardinality dimensions of a fact table
CREATE BITMAP INDEX bx_sales_region  ON sales (region);
CREATE BITMAP INDEX bx_sales_channel ON sales (channel);

-- Bitmap join index removes the join for filtered queries
CREATE BITMAP INDEX bx_sales_cust_city
  ON sales (c.city) FROM sales s, customers c
 WHERE s.customer_id = c.customer_id;

-- Test a candidate index without changing anyone else's plan
CREATE INDEX ix_orders_status ON orders (status, created_at) INVISIBLE;
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
-- happy? then
ALTER INDEX ix_orders_status VISIBLE;

-- Bulk load pattern
ALTER INDEX ix_sales_region UNUSABLE;
INSERT /*+ APPEND */ INTO sales SELECT * FROM sales_stage;
COMMIT;
ALTER INDEX ix_sales_region REBUILD ONLINE PARALLEL 8;
ALTER INDEX ix_sales_region NOPARALLEL;

-- Is anything actually using this index? (12.2+ tracking)
SELECT name, total_access_count, last_used FROM dba_index_usage
 WHERE name = 'IX_ORDERS_STATUS';

Answer

Flashback is several distinct features that share a name. Flashback Query, using AS OF TIMESTAMP or AS OF SCN, reads a table as it was at a past point in time, reconstructing blocks from undo. It needs nothing but sufficient undo retention, which is exactly its limitation: go back further than your undo covers and you get ORA-01555 or ORA-08180.

Flashback Version Query, VERSIONS BETWEEN TIMESTAMP ... AND ..., returns every committed version of a row along with pseudocolumns VERSIONS_STARTTIME, VERSIONS_OPERATION and VERSIONS_XID, which is how you answer who changed this row and when without an audit trail, and you can feed the XID into Flashback Transaction Query to see the undo SQL. Flashback Table rewinds an entire table to a past time.

It needs ALTER TABLE ... ENABLE ROW MOVEMENT because rows get new rowids, it is a DML operation so it holds locks and can be rolled back, and it also needs undo to reach that far. Flashback Drop is different again: it does not use undo.

DROP TABLE without PURGE renames the segment into the recycle bin, so FLASHBACK TABLE ... TO BEFORE DROP restores it, but the recycle bin is only a rename, so the space is reclaimed the moment the tablespace comes under pressure, and dependent foreign keys are not restored. Flashback Database rewinds the whole database and needs real preparation: ARCHIVELOG mode, a fast recovery area, and FLASHBACK ON so flashback logs are written, with DB_FLASHBACK_RETENTION_TARGET controlling how far back you can go.

It is the standard rollback plan for a risky release and for reverting a snapshot standby after testing. Flashback Data Archive, sometimes called Total Recall, stores history in dedicated tablespaces for years, and unlike everything else here it is independent of undo, which makes it the right answer for regulatory retention rather than for oops-recovery.

-- What did this row look like an hour ago?
SELECT * FROM accounts AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
 WHERE account_id = 4711;

-- Who changed it, and with which transaction?
SELECT versions_starttime, versions_operation, versions_xid, balance
  FROM accounts VERSIONS BETWEEN TIMESTAMP
         SYSTIMESTAMP - INTERVAL '2' HOUR AND SYSTIMESTAMP
 WHERE account_id = 4711;

SELECT undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('0A001C00D8210000');

-- Rewind a table
ALTER TABLE accounts ENABLE ROW MOVEMENT;
FLASHBACK TABLE accounts TO TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE;

-- Recover a dropped table
SELECT object_name, original_name, droptime FROM user_recyclebin;
FLASHBACK TABLE staging_invoices TO BEFORE DROP RENAME TO staging_invoices_old;

-- Whole database rewind (prepared in advance)
SELECT flashback_on FROM v$database;
SELECT oldest_flashback_time FROM v$flashback_database_log;

Answer

RMAN is the only backup tool that understands Oracle block structure, validates blocks as it reads them, skips never-used blocks, and can restore individual blocks. A standard strategy is a weekly level 0 incremental, which is a full backup usable as the base of an incremental chain, plus daily level 1 incrementals, plus frequent archive log backups, with a retention policy expressed as RECOVERY WINDOW OF n DAYS rather than a redundancy count, because a window maps directly to a recovery point objective. Always enable CONFIGURE CONTROLFILE AUTOBACKUP ON, because without a control file you cannot even start a restore.

Put backups in the fast recovery area or on separate storage from the datafiles, and if you are on Enterprise Edition use BACKUP AS COMPRESSED BACKUPSET, checking the licensing tier for the algorithm you pick. Block change tracking is the feature that makes incrementals cheap. Without it, a level 1 incremental still reads every block of every datafile to decide what changed, so the backup takes almost as long as a full one.

With ALTER DATABASE ENABLE BLOCK CHANGE TRACKING, Oracle maintains a bitmap file of changed blocks and RMAN reads only those, typically cutting incremental time by an order of magnitude on a large database. An incrementally updated backup takes this further: you keep an image copy and roll the daily level 1 into it, so restore is instant because the copy is already current. Proving restorability is the part candidates skip.

RESTORE DATABASE VALIDATE checks that the required backups exist and are readable without writing anything. BACKUP VALIDATE CHECK LOGICAL DATABASE reads every block and detects logical corruption, recording it in V$DATABASE_BLOCK_CORRUPTION. RMAN also runs against a recovery catalog for long-term metadata, and the only real proof is a scheduled test restore to a separate host, which is exactly what auditors at Indian banks ask to see evidence of.

-- One-time configuration
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
       USING FILE '/u03/oradata/bct.chg';
SQL> SELECT status, filename, bytes FROM v$block_change_tracking;

-- Weekly base, daily incremental
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG DELETE INPUT;

-- Prove it without touching production files
RMAN> RESTORE DATABASE VALIDATE;
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
SQL> SELECT * FROM v$database_block_corruption;

RMAN> CROSSCHECK BACKUP; DELETE NOPROMPT OBSOLETE;
RMAN> LIST BACKUP SUMMARY;
RMAN> REPORT NEED BACKUP;

-- Point in time recovery
RMAN> RUN { SET UNTIL TIME "TO_DATE('2026-02-14 09:15:00','YYYY-MM-DD HH24:MI:SS')";
            RESTORE DATABASE; RECOVER DATABASE; }
SQL> ALTER DATABASE OPEN RESETLOGS;

Answer

Data Pump, invoked as expdp and impdp, is Oracle's own logical export and import. It is server-side, so it writes to a DIRECTORY object on the database host rather than to your client machine, it supports PARALLEL, it can filter by schema, table, tablespace or a query predicate, and it can remap schemas, tablespaces and datafile paths on import. It is the tool for moving Oracle data between Oracle databases: migrations, refreshing a UAT environment from production with a subset, or extracting metadata only with CONTENT=METADATA_ONLY to compare DDL.

NETWORK_LINK lets you import directly over a database link without an intermediate dump file, and FLASHBACK_TIME gives you a consistent export across tables, which people forget and then wonder why referential integrity broke. It cannot read a CSV. SQL*Loader reads external text files into tables using a control file, supports conventional and direct path, handles fixed-width and delimited formats, and has been the workhorse for flat-file loading for decades.

Direct path bypasses the buffer cache and SQL processing and writes formatted blocks above the high water mark, which is very fast but takes an exclusive table lock and does not fire triggers or enforce most constraints during the load. External tables expose the same flat file as if it were a table, using the ORACLE_LOADER access driver for text or ORACLE_DATAPUMP for dump files. For a nightly partner CSV feed, external tables are usually the better answer: you get the full power of SQL over the file, so you can validate, join to reference data, transform and load with a single INSERT ...

SELECT or MERGE, you can run it in parallel, and you avoid a separate control file and a separate operating system step. Use SQL*Loader instead when the file layout is genuinely awkward, when you need per-record error handling with a bad file, or when the file cannot be placed on the database server. Both benefit from a LOG ERRORS clause and a DBMS_ERRLOG error table so one bad row does not abort ten million good ones.

CREATE DIRECTORY feed_dir AS '/u01/feeds';
GRANT READ, WRITE ON DIRECTORY feed_dir TO app_owner;

CREATE TABLE partner_feed_ext (
  txn_ref     VARCHAR2(40),
  txn_date    DATE,
  amount_inr  NUMBER(14,2)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER DEFAULT DIRECTORY feed_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE SKIP 1
    BADFILE feed_dir:'partner.bad' LOGFILE feed_dir:'partner.log'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    ( txn_ref, txn_date CHAR DATE_FORMAT DATE MASK 'YYYY-MM-DD', amount_inr )
  )
  LOCATION ('partner_20260214.csv')
) REJECT LIMIT UNLIMITED;

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('TRANSACTIONS', 'ERR$_TRANSACTIONS');

INSERT /*+ APPEND PARALLEL(4) */ INTO transactions (txn_ref, txn_date, amount_inr)
SELECT txn_ref, txn_date, amount_inr FROM partner_feed_ext
 WHERE amount_inr > 0
  LOG ERRORS INTO err$_transactions ('nightly') REJECT LIMIT UNLIMITED;

-- Consistent Oracle to Oracle extract
-- expdp app/pw DIRECTORY=feed_dir DUMPFILE=app_%U.dmp PARALLEL=4 \
--   SCHEMAS=app FLASHBACK_TIME=SYSTIMESTAMP

Answer

A global temporary table is a permanent definition with session-private data. The DDL exists once in the data dictionary, but each session sees only its own rows, and the data lives in the temporary tablespace rather than in a permanent one. You choose the lifetime with ON COMMIT DELETE ROWS, which empties the table at every commit and is the default, or ON COMMIT PRESERVE ROWS, which keeps the data for the whole session.

GTTs generate very little redo because the temporary segments are not protected by redo, though the undo they generate is protected, which is why 12c added TEMP_UNDO_ENABLED to push that undo into temp as well and cut redo further, useful on an Active Data Guard standby where you could not otherwise write at all. Since 12c a GTT can carry its own session-private optimizer statistics, controlled by GLOBAL_TEMP_TABLE_STATS, so one session's five rows do not give another session's five million rows a nested loop plan. Private temporary tables, added in 18c, go further: even the definition is private and lives only in memory for the session or transaction.

Their names must start with the value of PRIVATE_TEMP_TABLE_PREFIX, which defaults to ORA$PTT_, and they are created with CREATE PRIVATE TEMPORARY TABLE. They suit dynamically generated reporting steps where creating a permanent GTT definition would pollute the schema, and they avoid the DDL contention of creating and dropping real tables. ORA-01652, unable to extend temp segment, means the temporary tablespace is exhausted.

The usual causes are a large sort or hash join spilling to disk, a runaway Cartesian join, a global temporary table filling with far more rows than expected, or an index rebuild on a huge segment. Diagnose it from V$TEMPSEG_USAGE and V$SORT_USAGE joined to V$SESSION to find the offending SQL_ID; adding a tempfile is the last step, not the first.

CREATE GLOBAL TEMPORARY TABLE gtt_reconcile (
  txn_ref    VARCHAR2(40),
  amount_inr NUMBER(14,2),
  status     VARCHAR2(10)
) ON COMMIT PRESERVE ROWS;

EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'GTT_RECONCILE', 'GLOBAL_TEMP_TABLE_STATS', 'SESSION');

-- 18c private temporary table: definition disappears with the session
CREATE PRIVATE TEMPORARY TABLE ora$ptt_daily_slice
  ON COMMIT PRESERVE DEFINITION
AS SELECT * FROM sales WHERE sale_date = DATE '2026-02-14';

SHOW PARAMETER private_temp_table_prefix

-- Who is eating the temp tablespace?
SELECT s.sid, s.username, s.sql_id, u.tablespace,
       ROUND(u.blocks * 8192 / 1024 / 1024) AS mb
  FROM v$tempseg_usage u JOIN v$session s ON s.saddr = u.session_addr
 ORDER BY u.blocks DESC;

SELECT tablespace_name, ROUND(bytes_used/1024/1024) used_mb,
       ROUND(bytes_free/1024/1024) free_mb
  FROM v$temp_space_header;

Answer

In Real Application Clusters several instances mount one database, so a block modified in one instance's buffer cache may be needed by another. Cache Fusion ships that block across the private interconnect instead of forcing a write to disk and a re-read. Global Cache Service tracks block mastership and current versions, Global Enqueue Service handles cluster-wide locks and enqueues, and LMS processes serve blocks to remote instances.

A consistent-read request produces a gc cr block 2-way or 3-way transfer depending on whether the master and holder are the same node; a current-mode request for modification produces gc current block transfers. Normal transfer times are a fraction of a millisecond over a proper private interconnect. The events that indicate trouble are gc buffer busy acquire and gc buffer busy release, which mean many sessions across nodes are fighting for the same hot block, and gc cr block busy or high gc cr block receive time, which usually points at interconnect saturation or an LMS process starved of CPU.

The classic hot-block cases are a right-growing index on a sequence-generated primary key, where every node inserts into the same rightmost leaf block, a small frequently updated status table, and a sequence with ORDER set, which forces cross-instance coordination and shows as row cache lock on dc_sequences. Fixes are structural: hash-partition the index or use a scalable sequence to spread inserts, set CACHE high and NOORDER, and use application partitioning so related work lands on one node. That last point is what services deliver.

A service is a named workload you connect to rather than an instance, it can be preferred on some nodes and available on others, and it lets you route the batch job to node three while OLTP stays on nodes one and two. Combined with Fast Application Notification and Transparent Application Continuity, a service lets a driver replay an in-flight transaction on a surviving node after a failure instead of surfacing an error to the user.

-- Cluster-wide waits (note GV$ and INST_ID)
SELECT inst_id, event, total_waits,
       ROUND(time_waited_micro/NULLIF(total_waits,0)/1000, 3) AS avg_ms
  FROM gv$system_event
 WHERE event LIKE 'gc %'
 ORDER BY time_waited_micro DESC FETCH FIRST 12 ROWS ONLY;

-- Which segments are hot across the cluster?
SELECT object_name, statistic_name, SUM(value) v
  FROM gv$segment_statistics
 WHERE statistic_name IN ('gc buffer busy','gc cr blocks received',
                          'gc current blocks received')
 GROUP BY object_name, statistic_name ORDER BY v DESC FETCH FIRST 10 ROWS ONLY;

-- Spread a right-growing primary key index
CREATE UNIQUE INDEX ix_orders_pk ON orders (order_id)
  GLOBAL PARTITION BY HASH (order_id) PARTITIONS 32;
ALTER SEQUENCE order_seq CACHE 5000 NOORDER;

-- Application partitioning by service
-- srvctl add service -db PRODCDB -pdb BILLING_PDB -service batch_svc \
--   -preferred inst3 -available inst1,inst2 -failovertype AUTO -commit_outcome TRUE
SELECT inst_id, name, network_name FROM gv$active_services ORDER BY 1;

Answer

A physical standby is a block-for-block copy maintained by Redo Apply through the Managed Recovery Process; it is identical to the primary and is the default choice for disaster recovery. A logical standby uses SQL Apply: it mines redo, reconstructs SQL and executes it, so the standby can carry extra indexes and materialized views and can be open read-write for objects it does not maintain, at the cost of unsupported data type restrictions and more moving parts. A snapshot standby is a physical standby temporarily opened read-write for testing using a guaranteed restore point, then flashed back and resynchronised, which is how teams get a realistic test environment without extra storage.

The three protection modes are the heart of this question. Maximum Performance uses LGWR ASYNC, so the primary never waits for the standby and you accept a small data loss window; this is the default and what most Indian enterprises run for a remote DR site. Maximum Availability uses SYNC AFFIRM, so a commit waits for the standby to write redo, giving zero data loss, but if the standby becomes unreachable the primary degrades to asynchronous rather than stopping.

Maximum Protection is the same synchronous guarantee with no degradation: if the last standby is unreachable, the primary shuts down. Almost nobody runs Maximum Protection with a single standby, for obvious reasons. Fast-Start Failover automates the switch.

The Data Guard broker plus an observer process running on a third host monitors the primary, and if it is unreachable for FastStartFailoverThreshold seconds the observer promotes the standby automatically and later reinstates the old primary. Because it needs a genuinely independent vantage point, the observer must not run on either database host. Active Data Guard, a separately licensed option, keeps the physical standby open read-only while Redo Apply continues, which offloads reporting; DML redirection, added in 19c, lets an occasional write issued on that read-only standby be transparently shipped to the primary and applied back, so read-mostly applications with a stray update no longer need two connection pools.

-- Broker: state and health
DGMGRL> SHOW CONFIGURATION VERBOSE;
DGMGRL> VALIDATE DATABASE 'stby_mum';
DGMGRL> SHOW DATABASE 'stby_mum' 'TransportLagThreshold';

-- Protection mode change (needs matching LogXptMode)
DGMGRL> EDIT DATABASE 'stby_mum' SET PROPERTY LogXptMode = 'SYNC';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

-- Fast-Start Failover with an observer on a third host
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 30;
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> START OBSERVER;

-- Apply and transport lag on the standby
SELECT name, value, time_computed FROM v$dataguard_stats
 WHERE name IN ('transport lag','apply lag','apply finish time');

SELECT process, status, thread#, sequence#, block# FROM v$managed_standby;
SELECT database_role, open_mode, protection_mode, switchover_status FROM v$database;

-- Active Data Guard: allow occasional writes to be redirected (19c)
ALTER SESSION ENABLE ADG_REDIRECT_DML;

Answer

Log file sync is the time a session waits after issuing COMMIT until LGWR confirms the redo is on disk. Forty milliseconds is roughly two orders of magnitude worse than a healthy system, so this is a real problem, and the diagnosis is a fork. Compare log file sync against log file parallel write, which is LGWR's own write time.

If log file parallel write is also high, the redo log I/O path is slow: the online redo logs are on the wrong storage, they share spindles with datafiles, the storage array is saturated, or on a virtualised host the write cache is disabled. Fix the storage, move redo to its own low-latency devices, and check redo log member count and sizing so log switches are not happening every minute, visible as log file switch (checkpoint incomplete) in the same report. If log file parallel write is low but log file sync is high, LGWR is not the bottleneck, the post-wait handoff is: too many sessions committing far too often, LGWR starved of CPU on a saturated host, or in a virtual machine, CPU scheduling delay.

That second case is overwhelmingly caused by application code committing per row inside a loop, so the fix is in the code, not the storage. Look at user commits in the load profile against executes per second: if you are doing tens of thousands of commits per second on a modest workload, batch them. In a Data Guard configuration running SYNC, log file sync also includes the round trip to the standby, so a network hop or a distant DR site directly inflates it, and this is a very common surprise after a DR site is added.

Confirm the whole picture in ASH by looking at when the waits occurred rather than at the aggregate: a 40 ms average across an hour can be a smooth 40 ms or five minutes of 400 ms during a batch job, and those need different fixes. Remember that AWR and ASH require the Diagnostics Pack licence; Statspack is the free fallback.

-- The fork: compare the two events
SELECT event, total_waits, ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) avg_ms
  FROM v$system_event
 WHERE event IN ('log file sync','log file parallel write',
                 'log file switch (checkpoint incomplete)','SYNC Remote Write');

-- Commit rate: is the application committing per row?
SELECT name, value FROM v$sysstat
 WHERE name IN ('user commits','user rollbacks','redo size','execute count');

-- Time distribution, not the average
SELECT TRUNC(sample_time,'MI') AS minute, COUNT(*) AS sessions_waiting
  FROM v$active_session_history
 WHERE event = 'log file sync' AND sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
 GROUP BY TRUNC(sample_time,'MI') ORDER BY 1;

-- Redo log sizing and switch frequency
SELECT group#, thread#, ROUND(bytes/1024/1024) mb, members, status FROM v$log;
SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') hr, COUNT(*) switches
  FROM v$log_history GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')
 ORDER BY 1 DESC FETCH FIRST 24 ROWS ONLY;

Answer

Oracle Database 23ai added a native VECTOR data type so embeddings live in the same table as the relational data they describe, which removes the usual architecture of a separate vector store kept in sync with the transactional database. You declare a column as VECTOR, optionally fixing the dimension count and format, for example VECTOR(768, FLOAT32), or leave both flexible. Similarity is computed with VECTOR_DISTANCE using COSINE, EUCLIDEAN, DOT or MANHATTAN, and the shorthand operators exist too.

You can generate embeddings outside the database and insert them, or load an ONNX model into the database with DBMS_VECTOR.LOAD_ONNX_MODEL and call VECTOR_EMBEDDING so text never leaves the database, which is a genuine advantage for Indian banking and government workloads under data residency rules. DBMS_VECTOR_CHAIN provides chunking and utility functions for building retrieval pipelines. The key operational point is that an exact search over millions of vectors scans everything, so you create a vector index and switch to approximate search with the APPROX keyword in the row limiting clause, optionally with a target accuracy.

There are two index families and the choice matters. HNSW, declared as ORGANIZATION INMEMORY NEIGHBOR GRAPH, builds a hierarchical navigable small world graph in memory. It gives the best recall-to-latency ratio and is the right default for read-heavy search, but it is memory resident, sized by the VECTOR_MEMORY_SIZE parameter, and rebuilding is expensive.

IVF, declared as ORGANIZATION NEIGHBOR PARTITIONS, clusters vectors into partitions and probes only the nearest ones. It lives on disk, handles much larger datasets than fit in memory, and tolerates ongoing DML better, at somewhat lower recall for the same latency. The real reason teams evaluate this in 2026 is not raw vector speed, where dedicated engines still compete, it is that you can combine a vector predicate with ordinary SQL filters, joins and row-level security in one transactionally consistent statement.

CREATE TABLE job_posts (
  post_id    NUMBER PRIMARY KEY,
  city       VARCHAR2(40),
  posted_on  DATE,
  jd_text    CLOB,
  jd_vec     VECTOR(768, FLOAT32)
);

-- In-memory graph index for low-latency search
CREATE VECTOR INDEX vx_jd_hnsw ON job_posts (jd_vec)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

-- On-disk partitioned index for very large, changing corpora
-- CREATE VECTOR INDEX vx_jd_ivf ON job_posts (jd_vec)
--   ORGANIZATION NEIGHBOR PARTITIONS DISTANCE COSINE;

-- Hybrid query: vector similarity AND ordinary relational filters
SELECT post_id, city,
       VECTOR_DISTANCE(jd_vec, :query_vec, COSINE) AS dist
  FROM job_posts
 WHERE city = 'Bengaluru'
   AND posted_on >= DATE '2026-01-01'
 ORDER BY dist
 FETCH APPROX FIRST 20 ROWS ONLY WITH TARGET ACCURACY 90;

-- Embed inside the database using an imported ONNX model
SELECT VECTOR_EMBEDDING(minilm_model USING 'senior java developer' AS data) FROM dual;

SHOW PARAMETER vector_memory_size

Answer

The long-standing trade-off is that document databases give developers a convenient object-shaped API while relational databases give normalised storage, constraints and joins. Teams usually pick one and pay for the other, either duplicating data into MongoDB and keeping it in sync, or hand-writing mapping layers. A JSON Relational Duality View in Oracle Database 23ai defines a JSON document shape over normalised tables and makes it fully updatable in both directions.

The tables remain the single source of truth with their primary keys, foreign keys and check constraints intact, while applications can read and write whole JSON documents through SQL, REST via ORDS, or the MongoDB-compatible API. There is no duplicated storage and no synchronisation job, which is the entire point. You define the view either in a GraphQL-like syntax or in SQL using JSON object notation, and you annotate each nested table with the operations it allows: insert, update, delete, or nothing, so you can expose a customer document that permits updating the customer's own fields but treats the joined product catalogue as read-only.

Concurrency is handled with an ETAG. Every document carries a hash in its _metadata field, and when an application posts a modified document back, Oracle recomputes the ETAG and rejects the write if the underlying rows changed since the read. That is optimistic concurrency at document granularity, which is exactly the semantics a REST client expects, and it avoids holding a database lock across a user think time. The practical caveats are worth naming in an interview: a duality view is not a free performance win, because a document assembled from six joined tables still costs six joins, so you design the document shape around the access pattern; and the same row can appear in several documents, so an update through one document is visible in all of them, which is correct but surprises developers used to independent document copies.

CREATE JSON RELATIONAL DUALITY VIEW candidate_dv AS
  SELECT JSON {
    '_id'   : c.candidate_id,
    'name'  : c.full_name,
    'city'  : c.city,
    'skills': [ SELECT JSON { 'skill': s.skill_name, 'years': s.years }
                  FROM candidate_skills s WITH INSERT UPDATE DELETE
                 WHERE s.candidate_id = c.candidate_id ],
    'source': ( SELECT JSON { 'channel': ch.channel_name }
                  FROM channels ch WITH NOUPDATE NOINSERT NODELETE
                 WHERE ch.channel_id = c.channel_id )
  }
  FROM candidates c WITH INSERT UPDATE;

-- Read a whole document
SELECT json_serialize(data PRETTY) FROM candidate_dv WHERE json_value(data, '$._id') = 101;

-- Write it back; the ETAG in _metadata guards against a lost update
UPDATE candidate_dv
   SET data = JSON('{"_id":101,"name":"Priya S","city":"Pune",
                     "skills":[{"skill":"PL/SQL","years":6}],
                     "_metadata":{"etag":"9F2C..."}}')
 WHERE json_value(data, '$._id') = 101;

SELECT view_name FROM user_json_duality_views;

Answer

Plan regressions without code changes come from a small set of causes: new statistics, a new histogram, a bind value change interacting with bind peeking, an index becoming unusable, a parameter change, or an adaptive feature reacting to runtime data. Start by confirming the regression is a plan change rather than a data volume change: query DBA_HIST_SQLSTAT for the SQL_ID and compare PLAN_HASH_VALUE and elapsed time per execution across snapshots. If the hash changed, you have a plan flip.

Adaptive plans, introduced in 12c, let the optimizer defer a decision to runtime: a statistics collector sits in the plan and switches a nested loop to a hash join once actual row counts exceed a threshold, and DBMS_XPLAN shows this with the is_resolved_adaptive_plan note. Statistics feedback and SQL plan directives take that further by persisting what was learned so later parses estimate better. These features usually help, but they add variance, which is why 12.2 split the controls into OPTIMIZER_ADAPTIVE_PLANS, generally left on, and OPTIMIZER_ADAPTIVE_STATISTICS, generally left off in OLTP.

The proper stabilisation tool is SQL Plan Management. A SQL plan baseline is an accepted set of plans for a statement: the optimizer may only use an accepted plan, and when it finds a new and possibly better one it stores it as unaccepted until it is verified to be faster, either by the automatic evolve task or manually with DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. To fix an incident, load the known-good plan from AWR or from the cursor cache into a baseline and mark the bad one disabled, which pins behaviour without touching application SQL.

Contrast this with a SQL profile, which supplies correction factors to the optimizer's estimates rather than fixing the plan and comes from SQL Tuning Advisor under a Tuning Pack licence, and with a hint, which is brittle because it depends on object names surviving refactoring. Oracle Database 23ai adds Real-Time SQL Plan Management, which detects a regression and creates the baseline automatically instead of waiting for someone to notice.

-- Did the plan change, or did the data grow?
SELECT snap_id, plan_hash_value, executions_delta,
       ROUND(elapsed_time_delta/NULLIF(executions_delta,0)/1000,2) ms_per_exec
  FROM dba_hist_sqlstat WHERE sql_id = '&sql_id' ORDER BY snap_id;

-- Pin the good plan from AWR into a baseline
DECLARE n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_AWR(
         begin_snap => 41200, end_snap => 41260,
         basic_filter => q'[sql_id = 'a1b2c3d4e5f6g']');
END;
/

-- Or from the cursor cache while the good plan is still live
DECLARE n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id => 'a1b2c3d4e5f6g', plan_hash_value => 2417328611);
END;
/

-- Disable the regressed plan
DECLARE n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle => 'SQL_1a2b3c', plan_name => 'SQL_PLAN_bad_0001',
         attribute_name => 'enabled', attribute_value => 'NO');
END;
/

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
  FROM dba_sql_plan_baselines WHERE sql_text LIKE '%orders%';

SHOW PARAMETER optimizer_adaptive

Answer

Adding a nullable column with no default is instant because it is a dictionary-only change, and since 11g adding a NOT NULL column with a default is also metadata only, so those cases need no special technique. The hard case is restructuring: converting a heap table to partitioned, changing a data type, or reordering storage. DBMS_REDEFINITION is the classic answer.

You create an interim table with the target structure, call CAN_REDEF_TABLE to validate, START_REDEF_TABLE to seed it and set up the materialized-view-log machinery that captures ongoing changes, COPY_TABLE_DEPENDENTS to bring across indexes, constraints, triggers and grants, optionally SYNC_INTERIM_TABLE to catch up before the final step, and FINISH_REDEF_TABLE, which takes a brief exclusive lock and swaps names. Redefine by primary key when you can, which is faster than by rowid and does not require row movement. Two things bite in practice: the intermediate table needs roughly as much space as the original, and COPY_TABLE_DEPENDENTS failures are reported in a count that people ignore, so check the num_errors output and DBA_REDEFINITION_ERRORS.

Since 12.2 many single-step operations are online natively, including ALTER TABLE ... MOVE ONLINE, moving a partition online, and converting a non-partitioned table to partitioned with ALTER TABLE ... MODIFY PARTITION BY ...

ONLINE, which makes DBMS_REDEFINITION unnecessary for a large share of cases. Partition exchange solves the load side: you build a standalone table with identical structure and indexes, load and index it offline at full speed, then EXCHANGE PARTITION INCLUDING INDEXES WITHOUT VALIDATION, which is a dictionary swap taking a fraction of a second. Edition-based redefinition addresses a different axis, the application code rather than the data.

You create a new edition, compile the new package versions and editioning views in it, use crossedition triggers to keep old and new data representations in sync, then move sessions to the new edition and retire the old one. It is the only true zero-downtime path for a change that alters PL/SQL behaviour and schema together, and its cost is real complexity, so it is worth it for a core banking system and overkill for most applications.

-- 12.2+: convert to partitioned in one online statement
ALTER TABLE sales MODIFY
  PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
  ( PARTITION p0 VALUES LESS THAN (DATE '2026-01-01') ) ONLINE
  UPDATE INDEXES;

-- Classic online redefinition when you need full control
DECLARE
  l_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'SALES',
    DBMS_REDEFINITION.CONS_USE_PK);

  DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'SALES', 'SALES_INTERIM',
    col_mapping => NULL, options_flag => DBMS_REDEFINITION.CONS_USE_PK);

  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(USER, 'SALES', 'SALES_INTERIM',
    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE,
    ignore_errors => FALSE, num_errors => l_errors);

  IF l_errors > 0 THEN RAISE_APPLICATION_ERROR(-20030, 'dependents failed'); END IF;

  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'SALES', 'SALES_INTERIM');
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'SALES', 'SALES_INTERIM');
END;
/

-- Load offline, publish instantly
ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2026-02-15')
  WITH TABLE sales_feb_stage INCLUDING INDEXES WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;

-- Edition-based redefinition
CREATE EDITION rel_2026_02 AS CHILD OF ora$base;
ALTER SESSION SET EDITION = rel_2026_02;

Answer

ORA-04031, unable to allocate n bytes of shared memory, means Oracle could not find a contiguous chunk in the shared pool. Note contiguous: the pool is often not full, it is fragmented into thousands of small pieces because single-use cursors were loaded and aged out repeatedly. The error message names the allocation area, and values like sql area, KGLH0 or SQLA point straight at cursor churn, while a large pool allocation failure usually points at parallel query or RMAN buffers instead.

Library cache mutex X and cursor: pin S wait on X are the concurrency symptom of the same disease: hard parsing takes mutexes on library cache objects, and at high concurrency thousands of sessions serialise behind them, so CPU looks busy while throughput collapses. The root cause in nearly every real incident is literal SQL from an application that concatenates values, and the diagnosis is to group V$SQL by FORCE_MATCHING_SIGNATURE and look for signatures with thousands of distinct SQL_IDs. Secondary causes worth naming: an unnecessarily large number of child cursors for one SQL_ID, visible in V$SQL_SHARED_CURSOR with a reason column telling you exactly why they could not be shared, often bind mismatch, optimizer mismatch or a NLS setting difference from a badly behaved connection pool; excessive invalidation from repeated DDL or statistics gathering during peak hours; and PL/SQL packages being aged out and reloaded, which DBMS_SHARED_POOL.KEEP prevents.

The fixes in order are: parameterise the application, which is the only permanent answer; if you cannot change the application, set CURSOR_SHARING to FORCE as a tourniquet; increase SHARED_POOL_SIZE and set a floor even under automatic memory management so the pool cannot be shrunk by the auto-tuning algorithm; pin large hot packages; stop running DDL and stats gathers in peak windows; and check SESSION_CACHED_CURSORS and the application's statement cache so soft parses become softer still. Flushing the shared pool clears the symptom for minutes and is not a fix.

-- Is the pool fragmented?
SELECT pool, name, ROUND(bytes/1024/1024,1) mb FROM v$sgastat
 WHERE pool = 'shared pool' ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

SELECT ksmchcls, COUNT(*), ROUND(SUM(ksmchsiz)/1024/1024,1) mb
  FROM x$ksmsp GROUP BY ksmchcls;

-- Prove literal SQL
SELECT force_matching_signature, COUNT(DISTINCT sql_id) variants,
       ROUND(SUM(sharable_mem)/1024/1024,1) mb, MIN(sql_text) sample
  FROM v$sql WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(DISTINCT sql_id) > 100 ORDER BY variants DESC;

-- Why did these child cursors not share?
SELECT sql_id, child_number, bind_mismatch, optimizer_mismatch,
       language_mismatch, auth_check_mismatch
  FROM v$sql_shared_cursor WHERE sql_id = '&sql_id';

SELECT sql_id, COUNT(*) children FROM v$sql
 GROUP BY sql_id HAVING COUNT(*) > 50 ORDER BY 2 DESC;

-- Mitigations
ALTER SYSTEM SET shared_pool_size = 4G SCOPE = BOTH;   -- floor under AMM
ALTER SYSTEM SET session_cached_cursors = 200 SCOPE = SPFILE;
EXEC DBMS_SHARED_POOL.KEEP('APP.BILLING_PKG', 'P');

Answer

Database In-Memory is a separately licensed Enterprise Edition option that keeps a columnar copy of chosen tables, partitions or columns in the In-Memory Column Store, sized by INMEMORY_SIZE, alongside the normal row-format buffer cache. The dual format is the point: OLTP keeps reading rows from the buffer cache while analytic queries scan the columnar copy with SIMD vector instructions, in-memory storage indexes that skip whole chunks by min and max, and aggressive compression. It pays off for scan-heavy aggregate queries over wide tables where you touch few columns of many rows, and it is genuinely transformative there, often an order of magnitude.

It does not help point lookups by primary key, it does not help write throughput, it costs memory that could otherwise go to the buffer cache, and populating a large table takes time and CPU at startup unless you use priority levels and In-Memory FastStart. So the honest answer is: use it to retire a separate reporting copy of the data, not to speed up an OLTP transaction. The server result cache is a different mechanism entirely and needs no extra licence.

It caches the final result set of a query in the shared pool, keyed by the statement and its binds, and serves subsequent identical executions without any work, invalidating automatically when any dependent object changes. That last property is what makes it useful for small, expensive, rarely-changing lookups such as a reference aggregate, and useless for a query over a table that changes constantly, because every DML invalidates the entry. It is sized by RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MAX_RESULT, and over-using it produces contention on the Result Cache: RC Latch, which serialises everything.

The PL/SQL function result cache is the same idea applied to a function: mark it RESULT_CACHE and Oracle caches the return value per parameter combination, tracking dependencies automatically since 11.2 so the old RELIES_ON clause is no longer needed. It is the right tool for a deterministic lookup function called thousands of times per statement, and it must never be used for a function with side effects or one that depends on session state such as NLS or a package variable.

-- In-Memory: choose objects deliberately, not the whole database
SHOW PARAMETER inmemory_size
ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY HIGH;
ALTER TABLE sales MODIFY PARTITION p_2026_01 NO INMEMORY;   -- cold data out
ALTER TABLE sales INMEMORY NO INMEMORY (jd_text);           -- exclude a wide CLOB

SELECT segment_name, populate_status, ROUND(inmemory_size/1024/1024) mb,
       ROUND(bytes/NULLIF(inmemory_size,0),1) AS compression_ratio
  FROM v$im_segments;

-- Server result cache for a small, expensive, stable aggregate
SELECT /*+ RESULT_CACHE */ region, SUM(amount_inr)
  FROM sales WHERE sale_date < DATE '2026-01-01' GROUP BY region;

SELECT name, value FROM v$result_cache_statistics;
SELECT type, status, name, scan_count FROM v$result_cache_objects
 WHERE type = 'Result' ORDER BY scan_count DESC FETCH FIRST 10 ROWS ONLY;

-- PL/SQL function result cache: deterministic lookups only
CREATE OR REPLACE FUNCTION gst_rate (p_hsn VARCHAR2) RETURN NUMBER
  RESULT_CACHE IS
  v_rate NUMBER;
BEGIN
  SELECT rate_pct INTO v_rate FROM hsn_rates WHERE hsn_code = p_hsn;
  RETURN v_rate;
END gst_rate;
/