nario-Based Interview: Oracle Database Developer - Finance MNC (5-7 Years Experie

You are interviewing for an Oracle Database Developer role at a global finance MNC. The company
manages critical financial data
like transactions, risk metrics, and compliance reports. You will be designing and maintaining
high-performance Oracle databases,
writing optimized PL/SQL code, ensuring data accuracy, and supporting audit and compliance
systems.

The interview has three rounds (L1 -> L2 -> L3) focusing on practical problem-solving, SQL/PLSQL
mastery, and real-world financial data scenarios.
L1 Round - SQL, PL/SQL, and Core Database Concepts

Scenario 1: Daily Financial Transaction Report

A system generates a daily report of all financial transactions. The TRANSACTIONS table contains
50 million rows per day.

Questions:
1. How would you efficiently retrieve total transaction amounts by branch and product type?
2. How would you index this table for best performance?
3. Write a SQL query to find the top 5 customers by transaction value in the last 7 days.
4. If some transactions are missing, how would you verify data integrity?

Scenario 2: PL/SQL Business Rule Implementation

Rule: "If a customer's total transactions exceed $50,000 in a day, flag their account for review."

Questions:
1. Write a PL/SQL procedure to implement this rule.
2. How would you schedule this procedure daily?
3. How would you modularize the code for reuse across systems?
4. What audit information would you log to track flagged accounts?
L2 Round - Database Design, Optimization, and Real-Time Scenarios

Scenario 3: System Performance Degradation

End-of-month ETL jobs are slow. AWR reports show high I/O and long-running queries.

Questions:
1. How would you diagnose the issue using Oracle performance tools?
2. How would you rewrite heavy queries using analytic functions or materialized views?
3. For batch inserts of millions of rows, would you use BULK COLLECT/FORALL? Explain.
4. How would you handle deadlocks during concurrent ETL runs?

Scenario 4: Data Quality and Regulatory Compliance

You must retain 7 years of transaction history for audit compliance.

Questions:
1. How would you implement data auditing and tracking?
2. How would you ensure historical data is immutable?
3. How would you design partitions for quick retrieval of historical data?
4. How would you recover deleted rows using Oracle Flashback or RMAN?

L3 Round - System Design, Stakeholder Collaboration, and Problem Solving

Scenario 5: Designing a Credit Risk Database

The firm needs a Credit Risk System storing exposures, collaterals, limits, and ratings.

Questions:
1. Design the schema - key tables, relationships, and constraints.
2. How would you store historical risk rating changes?
3. How would you partition large risk tables?
4. How would you ensure consistency across modules (Risk, Collateral, Transactions)?
5. How would you scale as data grows 5x in 2 years?

Scenario 6: Real-Life Incident Handling

A reconciliation job fails with ORA-01555 (snapshot too old).
Questions:
1. What causes this error and how would you resolve it?
2. How can you prevent it through commit strategies or undo tablespace management?
3. How would you report and document the root cause?

Scenario 7: Collaboration and Delivery

Business teams complain about long delivery times for new data changes.

Questions:
1. How would you gather and validate requirements for new database features?
2. How would you manage change requests safely in production?
3. How would you ensure code is version-controlled and peer-reviewed?
4. Share an example where you optimized a legacy Oracle process and improved performance.

Evaluation Framework

| Category | Weight | Description |

|-----------|---------|-------------|
| SQL & PL/SQL Expertise | 30% | Ability to write, optimize, and debug Oracle queries and
procedures |
| Performance & Tuning Skills | 25% | Diagnose and optimize real-world bottlenecks |
| Design & Architecture | 20% | Model scalable, compliant financial systems |
| Business & Compliance Awareness | 15% | Understands audit, data quality, and regulatory
requirements |
| Communication & Collaboration | 10% | Works effectively across technical and business teams |

Your Task

For each scenario:

1. Describe your approach (SQL/PLSQL logic, tools, or design).
2. Include sample queries, procedure outlines, or recovery strategies.
3. Explain trade-offs between performance, maintainability, and compliance.
4. Reflect on how your approach ensures integrity, scalability, and audit readiness.