Oracle Backend Developer Interview Questions
Backend work at a company built around databases and business applications for finance, human resources and supply chains is close to the data: query plans, locks, isolation, partitions and batch jobs that must finish before the working day starts. Prepare to talk through a slow query you tuned, a deadlock you untangled, a month-end report that must not see half-posted data, and a payroll run that failed at night. What to show here: deep respect for the database and for correct business data. Each question below has what the interviewer is checking, an answer frame and a sample spoken answer to adapt.
How Oracle hires, in its own words, and its questions for every job →
1. Tell me about a slow query you tuned in production. How did you read the execution plan, and what did you change?
- Describe the query and how slow it was for users.
- Explain what the plan showed.
- Show the change: index, rewrite or statistics.
- Share the result and how you confirmed it in production.
“An invoice search in our finance application took many seconds for large customers. I captured the real query with its bind values and looked at the plan. It showed a full scan of the invoice lines table joined by a nested loop to invoices, because the filter was on a function applied to the date column, which stopped the index being used. I rewrote the condition as a range on the plain column, and the plan switched to an index range scan. I tested with the largest customer's data before releasing. The result was your result, and I added a check for functions on indexed columns to our review list.”
Weak answers add an index without looking at the plan, or cannot say why the query was slow.
2. Describe a time two parts of a system locked each other and neither could go on. How did you find it, and how did you make it stop happening?
- Describe the symptom users saw.
- Explain how you found the two transactions and the rows they held.
- Show the cause, such as different lock order.
- Share the fix and any retry you added.
“Our order service started throwing deadlock errors a few times an hour. The database log named the two statements. One transaction updated the stock row and then the order row, while a new batch job updated the order first and then the stock. Each waited for the other. I changed both paths to lock rows in the same order, order first and then stock, and made the batch job commit in smaller groups so it held locks for less time. I kept a single retry for the rare case that remains. The errors stopped. The result was your result.”
Weak answers add a retry loop and call it fixed, without finding which locks collided.
3. A month-end finance report runs for several minutes while accountants keep posting entries. How do you make sure the report does not see a half-posted entry, without stopping the accountants?
- Explain the problem: a long read sees some new rows and not others.
- Describe a consistent read as of the start of the report.
- Explain how the database keeps older versions of rows for readers.
- Mention closing the period as the business-level answer.
“If the report reads table by table while postings continue, it can see the debit side of an entry and not the credit side, and the totals will not balance. I would run the report at an isolation level that gives a consistent view as of the moment it started. The database keeps older versions of changed rows, so readers see the version from their start time while writers continue, and nobody blocks anybody. There is a business answer too. Once a period is closed, no postings are allowed into it, so a report on a closed period is stable by definition.”
Weak answers lock the tables during the report, or do not know that readers can see old row versions.
4. Design the service that records entries in a general ledger for an accounting application. What rules would you enforce, and how would you store the entries?
- Enforce double entry: each entry has lines that sum to zero.
- Make entries append-only, with corrections as new entries.
- Post the entry in one transaction and validate against an open period.
- Keep balances in a separate table updated with the posting, and reconcile them.
“The core rule is that each entry has at least two lines and the debits equal the credits. The service checks that before it writes anything. Entries are never updated or deleted. A mistake is corrected by a reversing entry, so the history is complete. The write of the entry header and lines happens in one transaction, and it fails if the period is closed. For fast reports, I would keep a balances table by account and period, updated within the same transaction. A nightly job recomputes balances from the lines and alerts on any difference. Every entry records who posted it and when.”
Weak answers allow entries to be edited in place, or store a balance with no way to recompute it from the lines.
5. Application servers keep failing with errors that no database connection is available, though the database itself is not busy. How would you investigate and set the connection pool?
- Check for leaked connections that are opened and never returned.
- Look at how long each request holds a connection.
- Size the pool from the database's capacity, not the number of threads.
- Add timeouts and metrics on wait time.
“I would first check for a leak, where code opens a connection and does not return it on an error path. Pool metrics that show connections in use climbing and never falling point to this. Next I would look at how long requests hold a connection. A request that holds one while it calls another service ties up the pool. The pool should be sized from what the database can serve well, which is often smaller than people expect, and more application servers do not mean a bigger pool each. I would add a wait timeout so requests fail fast, and monitor wait time.”
Weak answers simply raise the pool size, which moves the problem to the database.
6. An invoices table has grown very large, and most queries only touch the last few months. How would you partition it, and what changes for queries and for archiving?
- Choose the partition key from the common filter, here the invoice date.
- Explain partition pruning for queries with a date range.
- Show how old partitions can be archived or dropped quickly.
- Note queries without the key and the effect on indexes.
“I would partition by invoice date, one partition per month, because most queries filter on a date range. The database then reads only the partitions in that range, which is much faster than scanning the whole table. Archiving becomes simple: an old month is one partition that can be moved to cheaper storage or dropped in one step, instead of a delete that runs for hours. The care point is queries that do not include the date, such as a lookup by invoice number. Those must search every partition unless there is a global index. I would test the important queries before and after.”
Weak answers partition by a column nobody filters on, or forget queries that lack the partition key.
7. The payroll batch job failed at three in the morning, halfway through the employees. The money must reach employees' bank accounts by the morning. What do you do?
- Find where it stopped and whether the finished part was committed.
- Read the error and fix the cause, or skip the one bad record.
- Rerun from the point of failure, making sure processed employees are not repeated.
- Verify totals before the bank file goes out, and inform the payroll team.
“First I would find exactly where it stopped and check what was committed, because a job that commits in batches leaves some employees done and others not. Then I would read the error. Often one employee's record has bad data, such as a missing bank account. If the fix is quick, I fix it, otherwise I set that one employee aside for manual handling. Before rerunning, I make sure the job skips employees already processed, using a status on each record. After the rerun, I compare the total count and amount with the payroll team's expected figures before the bank file is sent.”
Weak answers rerun the whole job from the start without checking for double payments.
8. Why do you want to build backend systems at a company whose business is the database itself and the applications that run on it?
- Say what part of backend work you enjoy most.
- Connect it to databases and business applications.
- Give an example from your experience.
- Say what you want to learn.
“The part of backend work I like most is the layer close to the data: transactions, locks, plans and the design of tables that will hold a company's records for years. Most application teams treat the database as a black box, and here it is the product. The applications matter to me too. Finance, payroll and supply chain systems are where correctness has real meaning. I have spent the last few years tuning queries and designing batch jobs for a finance system. I want to learn from people who build the engine I have been tuning.”
Weak answers talk about the company's size or history and show no interest in data-heavy work.
No figures on this page: pay depends on the level, the city and the offer in front of you. When the offer comes, the free salary negotiation script helps with that part.
The questions above are the prep. On the real call, ClapAssist helps with the ones you did not practice.
ClapAssist is an AI interview assistant for Mac and Windows. It listens to the interview on your computer and shows you what to say, in short lines you can read while you talk. Your live interview audio and screen are never stored. Your resume and notes are saved to your account so the app fills them in on any computer. It is hidden from screen share on every plan, including Free; only you can see it.