DBA course and interview preparation, 1‑on‑1 with a working database administrator.

A clustered index sets the physical order of the rows in a table, so a table can have only one. A non-clustered index is a separate structure that points to the rows, and a table can have many. Oracle handles this differently, with index-organised tables, so say which database you mean.

Atomicity, consistency, isolation and durability: a transaction completes fully or not at all, keeps the data valid, isn’t affected by other transactions running at the same time, and survives a crash once committed.

DELETE removes selected rows and can be rolled back. TRUNCATE removes all rows quickly with minimal logging, and in most databases can’t be rolled back the same way. DROP removes the table itself, structure and all.

Normalisation organises data to reduce duplication and update problems. You denormalise on purpose, usually for reporting or read-heavy workloads, when the speed you gain is worth the work of keeping copies consistent.

Check what changed: data volume, statistics, the execution plan, indexes, parameters or blocking. Compare the current plan with a known good one. Stale statistics, a plan change or a missing index are common causes. Interviewers want your steps in order, not a list of guesses.

Use the database’s views or monitoring tools to see which session is blocking which, and what each is running. For deadlocks, read the deadlock graph or trace, then fix the cause: access tables in a consistent order, keep transactions short and index properly.

Start from the business’s recovery point and recovery time objectives. Typically full backups plus differential or incremental backups and frequent log backups, stored away from the server, encrypted, and above all restored regularly as a test.

They control what one transaction can see of another’s changes, from read uncommitted up to serializable. Higher levels prevent dirty reads, non-repeatable reads and phantom reads, at the cost of more locking or versioning overhead.

Start from the recovery objectives and budget: synchronous replication or clustering for automatic failover, a standby in another location for disasters, and application connections that cope with failover. Test failover regularly; untested failover often fails when it matters.

Test on a copy of production, check deprecated features and application compatibility, benchmark performance, then use replication or a standby to cut over in a short window. Always have a rollback plan.

Find out what’s growing and why: tables, indexes, logs or temporary space. Then act: archive or partition old data, fix runaway logging, compress, and set up capacity monitoring with alerts.

Assess size, downtime tolerance and compatibility, choose between a one-off copy and ongoing replication, run a test migration, validate data and performance, then cut over with a rollback plan. Interviewers probe what went wrong in migrations you’ve done.