Oracle 26ai DBA Interview Questions: 25 Real Scenarios (2026)

Published Oct 2026 · 14 min read

Most "Oracle interview questions" lists online stop at 19c, or they are SQL definitions dressed up with a new year in the title. That is not what a 26ai interview feels like. Interviewers now ask what changed: how you would move a 19c estate onto 26ai, which new locking and security features you would actually switch on, and whether you can explain AI Vector Search to a project manager without hand-waving.

Below are 25 scenario questions of the kind asked of working DBAs in 2026, each with a model answer and, where it helps, the commands you would type. Syntax was checked against Oracle AI Database 26ai documentation and release 23.26. Read the answer, then try the commands on a practice database. That is how the answer becomes yours rather than memorised.

How to use this list

Each question starts with a situation, because that is how good interviewers ask. Answer in three beats: what you would check, what you would change, and what risk you would watch for. Short answers that show judgement beat long recitals of documentation. If you have never touched 26ai, set up the free edition first; our guide to installing Oracle 26ai Free on Windows 11 takes about twenty minutes.

Release, upgrade and licensing

1. "Is Oracle AI Database 26ai a new release, or just 23ai renamed?"

Both, in a sense. Oracle AI Database 26ai is the long-term support release that replaced Oracle Database 23ai in October 2025, and it is the same code line: it ships as Release Update 23.26.x. So version_full shows 23.26, not 26. For a database already on 23ai, moving to 26ai means applying a quarterly Release Update, not running a database upgrade. The interviewer wants to know you understand the numbers you will type into My Oracle Support and OPatch.

SELECT banner_full FROM v$version;
SELECT instance_name, version_full FROM v$instance;   -- e.g. 23.26.3.0.0

2. "You must move a 19c non-CDB to 26ai this quarter. What is your plan?"

The non-CDB architecture has been desupported since 21c, so the database has to become a PDB as part of the move. AutoUpgrade does the upgrade and the non-CDB-to-PDB conversion in one run when you give it a target CDB. Run analyze first and fix every precheck finding, then deploy in the change window. Take a full RMAN backup beforehand: once the database is plugged into the CDB, the guaranteed restore point no longer gives you a simple way back. We walk through the whole run in our 19c non-CDB to 26ai PDB upgrade lab.

# orcl.cfg
global.autoupg_log_dir=/u01/autoupgrade/logs
upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/26ai/dbhome_1
upg1.sid=ORCL19
upg1.target_cdb=CDB26
upg1.target_pdb_name=ORCLPDB

java -jar autoupgrade.jar -config orcl.cfg -mode analyze
java -jar autoupgrade.jar -config orcl.cfg -mode deploy

3. "Explain the version 23.26.3. How do you patch it?"

23 is the code base, 26 marks the 26ai release, and the last number is the quarterly Release Update: 23.26.3 was the July 2026 RU. Patch out of place: install a new home with the RU already applied, switch the database to it in a short window, then run datapatch. That keeps the old home as an instant fallback. Our RU patch-number reference lists the current patch IDs.

$ORACLE_HOME/OPatch/opatch lspatches
$ORACLE_HOME/OPatch/datapatch -verbose

SELECT patch_id, action, status, description
FROM   dba_registry_sqlpatch
ORDER  BY action_time DESC;

4. "How many PDBs can we create without buying the Multitenant option?"

Three user-created PDBs per CDB, in every edition, since 19c. CDB$ROOT and PDB$SEED do not count. If the business has not licensed Multitenant, set MAX_PDBS so nobody creates a fourth by accident. A good candidate also mentions that an application container counts as a PDB.

SHOW PDBS
ALTER SYSTEM SET max_pdbs = 3 SCOPE = BOTH;

5. "You are handed an unfamiliar 26ai database. What are your first five queries?"

Role, version and log mode first, then the PDBs and their open modes, then where the alert log lives, then the patch history, then the handful of new 26ai parameters that change behaviour. Five minutes of this saves hours of wrong assumptions.

SELECT i.instance_name, i.version_full, d.database_role, d.log_mode, d.open_mode
FROM   v$instance i, v$database d;

SELECT con_id, name, open_mode, restricted FROM v$pdbs;

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

SELECT patch_id, action, status, action_time
FROM   dba_registry_sqlpatch ORDER BY action_time DESC FETCH FIRST 3 ROWS ONLY;

SELECT name, value FROM v$parameter
WHERE  name IN ('max_idle_blocker_time','priority_txns_high_wait_target',
                'vector_memory_size','max_pdbs','max_columns');

Locking and concurrency

6. "A developer left a transaction open and went to lunch. Forty sessions are blocked. How does 26ai help?"

Today you find the blocker and kill it. To stop it happening again, set MAX_IDLE_BLOCKER_TIME, in minutes. Unlike MAX_IDLE_TIME, which disconnects any idle session, it only ends sessions that are idle and holding a lock that someone else is waiting for, so harmless idle connections survive. The full routine is in our 60-second blocking-session playbook.

SELECT sid, serial#, username, blocking_session, event, seconds_in_wait
FROM   v$session WHERE blocking_session IS NOT NULL;

ALTER SYSTEM SET max_idle_blocker_time = 10 SCOPE = BOTH;

7. "A low-priority batch job keeps blocking the payments service. What would you configure?"

Priority Transactions. Sessions get a TXN_PRIORITY of HIGH, MEDIUM or LOW, and when a HIGH transaction waits on a row lock longer than PRIORITY_TXNS_HIGH_WAIT_TARGET seconds, the database rolls back the lower-priority blocker. The feature only acts when both a priority and a wait target are set. Start in TRACK mode, which only counts in V$SYSSTAT what would have been rolled back, and switch to ROLLBACK once the numbers look sane.

ALTER SYSTEM SET priority_txns_high_wait_target = 15 SCOPE = BOTH;  -- seconds
ALTER SYSTEM SET priority_txns_mode = TRACK SCOPE = BOTH;           -- observe first

-- in the batch job's session (or a logon trigger for its service)
ALTER SESSION SET txn_priority = LOW;

8. "Five hundred sessions decrement stock on the same inventory row and serialise. What 26ai feature fixes that?"

Lock-free reservations. Mark the column RESERVABLE and protect it with a check constraint. Updates then record a reservation in a journal table instead of holding the row lock until commit, so concurrent decrements do not queue behind each other, and the constraint still stops stock going negative. The restrictions are worth knowing: the update may only add or subtract, and the WHERE clause must specify the primary key.

CREATE TABLE inventory (
  item_id  NUMBER PRIMARY KEY,
  qty      NUMBER RESERVABLE CONSTRAINT qty_not_negative CHECK (qty >= 0)
);

UPDATE inventory SET qty = qty - 1 WHERE item_id = 101;

-- pending reservations live in SYS_RESERVJRNL_<object_id>
SELECT object_name FROM user_objects WHERE object_name LIKE 'SYS_RESERVJRNL%';

Space management

9. "After a purge, a tablespace is 80% empty but the datafile is still 2 TB. Reclaim it."

In 26ai, DBMS_SPACE.SHRINK_TABLESPACE moves segments online and then resizes the file, which used to be a manual afternoon of moves and rebuilds. It works for bigfile tablespaces and, from 23.7, for smallfile ones too. Always run the analyze mode first: it reports which objects cannot move online and how much you will get back.

EXECUTE dbms_space.shrink_tablespace('APP_DATA', shrink_mode => dbms_space.ts_mode_analyze);

EXECUTE dbms_space.shrink_tablespace('APP_DATA', -
          shrink_mode => dbms_space.ts_mode_shrink, -
          target_size => dbms_space.ts_target_max_shrink);

10. "An old script fails on 26ai with ORA-32771 when it adds a datafile. Why?"

Since 23.4, new tablespaces are bigfile by default, and a bigfile tablespace has exactly one datafile, so ADD DATAFILE fails with ORA-32771. In CDB$ROOT, SYSTEM, SYSAUX, UNDOTBS1 and USERS are bigfile; in a PDB, USERS stays smallfile, and TEMP stays smallfile everywhere. Either resize the single file, or say SMALLFILE explicitly when the old layout really matters.

SELECT tablespace_name, bigfile FROM dba_tablespaces ORDER BY 1;

CREATE SMALLFILE TABLESPACE legacy_ts DATAFILE SIZE 1G AUTOEXTEND ON;

Security

11. "Auditors want the application account to run only the SQL it ran during testing. How?"

SQL Firewall. As a user with the SQL_FIREWALL_ADMIN role, enable the firewall, capture the application user's SQL during a full test cycle, generate an allow-list from the capture, then enforce it. Enforce first with block => FALSE so violations are only logged, review DBA_SQL_FIREWALL_VIOLATIONS for a week, then switch blocking on. Injected SQL simply is not on the list.

EXEC dbms_sql_firewall.enable;
EXEC dbms_sql_firewall.create_capture(username => 'APP_USER', top_level_only => TRUE, start_capture => TRUE);
-- ... run the full test cycle ...
EXEC dbms_sql_firewall.stop_capture('APP_USER');
EXEC dbms_sql_firewall.generate_allow_list('APP_USER');
EXEC dbms_sql_firewall.enable_allow_list(username => 'APP_USER', enforce => dbms_sql_firewall.enforce_all, block => FALSE);

SELECT * FROM dba_sql_firewall_violations;

12. "A reporting user needs read access to every HR table, including tables created next month. No ANY privileges."

Schema privileges, new in 23ai. One grant covers every current and future table in the schema, without the database-wide reach of SELECT ANY TABLE and without a script that re-grants object by object. Check the result in DBA_SCHEMA_PRIVS.

GRANT SELECT ANY TABLE ON SCHEMA hr TO report_user;
SELECT grantee, privilege, schema FROM dba_schema_privs WHERE grantee = 'REPORT_USER';

13. "What role do you give a new application developer?"

DB_DEVELOPER_ROLE. It bundles the privileges a developer needs to build and debug an application schema, so you stop granting DBA "just for now" or the old CONNECT and RESOURCE combination that never quite fit. The judgement point: grant it in development PDBs, not in production.

GRANT DB_DEVELOPER_ROLE TO dev_anita;

14. "An analyst account must never write data, even if someone grants it INSERT later. Can 26ai enforce that?"

Yes: read-only users, for local users in a PDB. A read-only user can query but any write fails with ORA-28194, whatever privileges it holds. It is a safety rail on top of least privilege, not a replacement for it.

ALTER USER analyst READ ONLY;
SELECT username, read_only FROM dba_users WHERE username = 'ANALYST';

15. "Create an audit policy for every change to HR.EMPLOYEES."

Use unified auditing; traditional auditing has been deprecated since 21c, so new policies should be unified. Create the policy, enable it, and query UNIFIED_AUDIT_TRAIL. In a CDB, a common policy created in the root with CONTAINER = ALL covers every PDB.

CREATE AUDIT POLICY hr_emp_changes ACTIONS INSERT, UPDATE, DELETE ON hr.employees;
AUDIT POLICY hr_emp_changes;

SELECT event_timestamp, dbusername, action_name, object_name
FROM   unified_audit_trail
WHERE  unified_audit_policies LIKE '%HR_EMP_CHANGES%'
ORDER  BY event_timestamp DESC;

Multitenant operations

16. "During maintenance you must change the schema as a DBA, while application users can still read but not write. How?"

Open the PDB in hybrid read-only mode. Common users see a read-write PDB and can run DDL and DML; local users see a read-only PDB and get ORA-16000 on any write. The application stays up for reporting while you work.

ALTER PLUGGABLE DATABASE salespdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE salespdb OPEN HYBRID READ ONLY;
SELECT name, open_mode FROM v$pdbs;

17. "Someone ran a bad update in one PDB at 14:00. Recover only that PDB."

PDB point-in-time recovery with RMAN, leaving the CDB and the other PDBs untouched. With local undo, the default since 12.2, no auxiliary instance is needed. Close the PDB, restore and recover it to just before the mistake, then open it with RESETLOGS. Mention flashback PDB to a restore point as the faster option when one exists.

ALTER PLUGGABLE DATABASE salespdb CLOSE IMMEDIATE;

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2026-10-07 13:58','YYYY-MM-DD HH24:MI')";
  RESTORE PLUGGABLE DATABASE salespdb;
  RECOVER PLUGGABLE DATABASE salespdb;
}
RMAN> ALTER PLUGGABLE DATABASE salespdb OPEN RESETLOGS;

Performance

18. "After last weekend's RU, one report runs ten times slower. Your move?"

Confirm it is a plan change before blaming the patch. AWR shows every plan it has captured for the SQL_ID; if a new plan hash value appeared after the RU, load the old good plan from AWR as a SQL plan baseline. That is a quick, reversible fix while you find out why the optimizer changed its mind. Our 10-step AWR reading method shows how to find the SQL in the first place.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('7h35uxf5uhmm1'));

DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_AWR(
         begin_snap   => 1200,
         end_snap     => 1210,
         basic_filter => q'[sql_id = '7h35uxf5uhmm1' AND plan_hash_value = 1234567890]');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

19. "A developer wants a table with 2,000 columns. Possible?"

Possible in 26ai: with MAX_COLUMNS set to EXTENDED and COMPATIBLE at 23 or higher, a table can have up to 4,096 columns. Then push back on the design. Very wide rows chain across blocks and hurt every full scan. Often JSON or a child table is the better answer. The interviewer is checking that you know the feature and the cost.

ALTER SYSTEM SET max_columns = EXTENDED SCOPE = SPFILE;   -- restart required

High availability

20. "What is True Cache, and when would you use it instead of an Active Data Guard standby?"

True Cache is a diskless, read-only, in-memory cache that sits in front of a primary database and stays consistent by applying the primary's redo. It offloads hot read traffic, and applications reach it through a dedicated service or the JDBC driver's read-only routing. It is not a disaster-recovery copy: it has no datafiles, and you cannot fail over to it. Active Data Guard is a full physical standby that also serves reads. Choose True Cache to scale reads cheaply; choose Data Guard to survive losing the primary. The primary must run in ARCHIVELOG mode.

21. "How do you prove a 26ai Data Guard configuration is ready for a switchover?"

Ask the broker rather than trusting the last switchover. Check the lag, validate the standby, and validate the static connect identifiers the broker uses to restart instances. Then mention Data Guard per Pluggable Database (DGPDB), which lets a single PDB move between two CDBs instead of the whole container.

DGMGRL> SHOW CONFIGURATION LAG;
DGMGRL> VALIDATE DATABASE 'cdb26_stby';
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR ALL;

AI and developer features a DBA must understand

22. "The business wants semantic search over product descriptions. What do you, the DBA, plan for?"

Storage, memory and indexes. Embeddings go in a VECTOR column. An HNSW vector index lives in memory, in the vector pool sized by VECTOR_MEMORY_SIZE, so that memory must be planned into the SGA. An IVF index is disk-based and suits larger data sets. Queries use VECTOR_DISTANCE with an approximate fetch. Then plan where embeddings are generated: inside the database with an imported ONNX model, or outside. We built one end to end in your first vector index in 15 minutes.

ALTER SYSTEM SET vector_memory_size = 1G SCOPE = SPFILE;

CREATE TABLE products (
  id     NUMBER PRIMARY KEY,
  descr  VARCHAR2(4000),
  emb    VECTOR(384, FLOAT32)
);

CREATE VECTOR INDEX products_hnsw ON products (emb)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE WITH TARGET ACCURACY 95;

SELECT id, descr
FROM   products
ORDER  BY VECTOR_DISTANCE(emb, :query_vec, COSINE)
FETCH  APPROX FIRST 5 ROWS ONLY;

23. "Developers want JSON documents; you want normalised tables. Who wins?"

Both, with a JSON Relational Duality view. Data stays in relational tables you can index, back up and tune as usual, while the application reads and writes whole JSON documents through the view. For the DBA, the base tables need primary keys, indexes on the join columns, and the normal statistics care.

CREATE JSON RELATIONAL DUALITY VIEW dept_dv AS
SELECT JSON {'_id'       : d.deptno,
             'name'      : d.dname,
             'employees' : [ SELECT JSON {'empno' : e.empno, 'ename' : e.ename}
                             FROM   emp e WITH INSERT UPDATE DELETE
                             WHERE  e.deptno = d.deptno ]}
FROM dept d WITH INSERT UPDATE DELETE;

24. "Name three SQL changes in 26ai that make DBA scripts simpler."

IF [NOT] EXISTS on DDL, so rerunnable deployment scripts stop failing on objects that already exist. A native BOOLEAN type instead of CHAR(1) flags. SELECT without FROM DUAL, and GROUP BY on a column alias. Small changes, but they remove a whole class of script errors.

CREATE TABLE IF NOT EXISTS app_flags (id NUMBER PRIMARY KEY, active BOOLEAN DEFAULT TRUE);
DROP TABLE IF EXISTS tmp_stage;
SELECT SYSDATE;
SELECT EXTRACT(YEAR FROM hire_date) AS yr, COUNT(*) FROM hr.employees GROUP BY yr;

25. "How did you practise 26ai, and what are the limits of that setup?"

Oracle AI Database 26ai Free is the honest answer for most candidates. Its limits: 2 CPUs for foreground processes, 2 GB of RAM for SGA and PGA combined, 12 GB of user data, and no patches or support. It is enough for every command on this page except multi-node features such as RAC. Interviewers like candidates who built a lab. Being able to say "I broke it and fixed it" counts for more than a certificate.

Common mistakes candidates make

• Saying "26ai is version 26". The binaries report 23.26.x. Getting this wrong suggests you have never logged in to one.
• Treating every new feature as a reason to switch it on. Priority Transactions, SQL Firewall and hybrid read-only all need a rollout plan: observe first, then enforce.
• Forgetting licensing. AWR needs the Diagnostics Pack; more than three PDBs needs Multitenant. A senior DBA mentions it unprompted.
• Answering upgrade questions without a fallback. Every upgrade answer should name the backup or restore point you would return to.

FAQ

Are 23ai interview questions still valid for 26ai?

Yes. 26ai continues the 23ai code line, so features introduced in 23ai, such as SQL Firewall, schema privileges, lock-free reservations and AI Vector Search, are all present in 26ai. Interviewers simply call the release by its current name.

Which 26ai topics should an experienced DBA prepare first?

The 19c-to-26ai upgrade with AutoUpgrade and the non-CDB-to-PDB conversion, Release Update patching, the new locking features (idle-blocker termination, Priority Transactions, lock-free reservations), the security features (SQL Firewall, schema privileges, read-only users), and enough AI Vector Search to plan memory and indexes for it.

Do I need the 1Z0-183 certification to get a 26ai job?

It is not a requirement, but it is a structured way to cover the syllabus and it helps a CV stand out. Our 1Z0-183 study guide maps each exam area to hands-on labs.

Can I practise all of these on a laptop?

Nearly all of them, using Oracle AI Database 26ai Free. Data Guard and True Cache need a second database, which you can run as a second container. RAC needs a proper multi-node lab.

Next step

Reading model answers is the first half; the second is doing each one on a real database until it feels routine. Our Oracle 26ai DBA training runs 45 live sessions with labs on all of these areas, from the 19c upgrade and RAC to SQL Firewall and AI Vector Search. If you prefer to learn at your own pace, the Oracle 26ai recorded course gives lifetime access to the same material. Oracle's official Oracle AI Database 26ai New Features Guide is the reference for every feature mentioned here.