Basic Level Oracle DBA Interview Questions
1. What are the main responsibilities of a DBA in day-to-day operations?
Situation: As a DBA, I manage critical production databases that support business applications.
Task: My responsibility is to ensure high availability, security, and optimal performance of these databases.
Action: I monitor system health using tools like OEM and AWR reports, manage backups through RMAN, apply patches, optimize queries, and handle user access. I also proactively troubleshoot issues to prevent downtime.
Result: These actions ensure 99.9% uptime, compliance with security standards, and smooth business operations without performance bottlenecks.
2. How do you monitor and resolve performance degradation in a running database?
Situation: During a peak business cycle, users reported slow response times on critical applications.
Task: I needed to identify the root cause and restore performance quickly without impacting ongoing transactions.
Action: I analyzed AWR reports, checked v$session and v$sql for high wait events, and found inefficient queries causing contention. I tuned SQL statements, added missing indexes, and adjusted memory parameters.
Result: Query execution time dropped by 70%, and overall system performance stabilized within an hour, ensuring uninterrupted business operations.
3. Describe a situation where you improved a process or database performance.
Situation: Reporting queries were taking several minutes to execute, impacting decision-making.
Task: My goal was to optimize query performance without redesigning the application.
Action: I reviewed execution plans, introduced composite indexes, and implemented table partitioning for large datasets.
Result: Query execution time reduced from 5 minutes to under 10 seconds, improving reporting efficiency and user satisfaction.
4. How did you handle a disagreement with a team member on a DBA approach?
Situation: A colleague preferred full backups for simplicity, while I recommended incremental backups for efficiency.
Task: I needed to resolve the disagreement and choose a strategy that met compliance and performance needs.
Action: I presented recovery time objectives and compliance requirements, showing how incremental backups reduce backup windows and storage costs. We agreed on a hybrid approach—weekly full backups and daily incremental backups.
Result: This solution improved backup efficiency by 40% while meeting compliance standards, and strengthened team collaboration.
5. A production database slows down during peak hours – what steps would you take to identify and fix the issue?
Situation: A critical production database experienced severe slowdowns during peak usage.
Task: My objective was to diagnose and resolve the issue quickly to minimize business impact.
Action: I checked active sessions and wait events, analyzed execution plans, and reviewed system metrics for CPU and I/O bottlenecks. I tuned queries, added indexes, and implemented connection pooling. For long-term stability, I recommended partitioning and workload balancing.
Result: Performance improved immediately, reducing response times by 60%, and the implemented changes prevented future peak-hour slowdowns
1. What is an Oracle database instance?
An Oracle instance is a combination of memory structures (SGA) and background processes that run on a server. It interacts with physical database files to perform operations like reading, writing, and managing data.
2. Difference between an Oracle database and an Oracle instance?
- Database: Physical storage components such as data files, redo logs, and control files.
- Instance: Memory structures and background processes that manage the database.
In RAC environments, one database can have multiple instances.
3. What is the purpose of an Oracle index?
Indexes speed up data retrieval by reducing full table scans. They can be unique or non-unique, depending on the requirement.
4. What is a synonym in Oracle and why use it?
A synonym is an alias for a database object (table, view, sequence, etc.). It simplifies object access, especially across schemas, and is useful in large multi-user environments.
5. What is the purpose of a password file?
The password file stores credentials for users with SYSDBA or SYSOPER privileges, enabling authentication even when the database is down.
6. Main physical components of an Oracle database:
- Data files: Store user and system data.
- Redo log files: Record all changes for recovery.
- Control files: Maintain metadata like database name, log history, and checkpoints.
7. How do you recover a database if all control files are lost?
- Restore control files from backup using RMAN:
RESTORE CONTROLFILE FROM 'backup_location'; - Mount and recover the database:
RECOVER DATABASE; ALTER DATABASE OPEN; - If no backup exists, recreate control files using
CREATE CONTROLFILE, then recover and open withRESETLOGS.
Intermediate Level Oracle DBA Interview Questions
1. How does Oracle process an SQL statement from parsing to execution?
- Syntax and semantic check
- Search shared pool for existing execution plan
- If not found, create a new plan
- Bind variables, execute, and return results
2. Difference between hot backup and cold backup?
- Hot backup: Taken while the database is open in ARCHIVELOG mode; users can continue working.
- Cold backup: Taken when the database is shut down; simpler but causes downtime.
3. What are bind variables and why use them?
Bind variables hold values in SQL statements, enabling plan reuse, reducing parsing overhead, and improving performance.
4. What are deadlocks and when do they occur?
Deadlocks occur when two sessions hold locks and wait for each other. Oracle resolves this by terminating one session automatically.
5. What is row migration and its impact?
Row migration happens when an updated row no longer fits in its original block and moves to another block. This increases I/O and slows queries.
6. Difference between shared server and dedicated server configurations?
- Dedicated: Each user gets a dedicated server process.
- Shared: Multiple users share server processes via dispatchers; saves memory but may reduce performance under heavy load.
Advanced Level Oracle DBA Interview Questions
1. Difference between clustered and non-clustered indexes?
- Clustered: Table data is physically sorted by the index key; only one per table.
- Non-clustered: Stores pointers to rows; multiple allowed per table.
2. How to switch from NOARCHIVELOG to ARCHIVELOG mode?
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
3. Key views for performance monitoring:
v$session– Active sessionsv$system_event– Wait eventsv$sysstat– Global statsv$sql– SQL performancev$buffer_pool_statistics– Buffer usage
4. What is a sequence and how to set its values?
A sequence generates numeric values, often for primary keys. Use START WITH for initial value and INCREMENT BY for step size.
5. Difference between DELETE and TRUNCATE:
- DELETE: DML, removes rows individually, supports WHERE, logs each row, can be rolled back.
- TRUNCATE: DDL, removes all rows instantly, minimal logging, cannot be rolled back in most cases.
Experienced Level Oracle DBA Interview Questions
1. Explain ACID properties:
- Atomicity: All-or-nothing transactions
- Consistency: Maintains valid state
- Isolation: Transactions run independently
- Durability: Data persists after commit
2. How to manage user sessions and orphaned sessions?
Monitor v$session and v$process. Kill orphaned sessions using:ALTER SYSTEM KILL SESSION 'sid,serial#';
Use profiles and RESOURCE_LIMIT for idle timeouts.
3. Tools for performance tuning:
AWR, ADDM, v$sql, wait events (v$session_wait), execution plans, and session tracing.
4. Explain RMAN backup and recovery:
RMAN automates backups (full/incremental), validates integrity, and restores missing files during recovery. Supports point-in-time recovery.
5. Methods to secure Oracle database:
- Apply least privilege principle
- Use roles for system privileges
- Audit sensitive actions
- Revoke unnecessary PUBLIC access
- Enforce password policies via profiles
