Oracle Consultant Interview Questions

A nightly batch procedure that processes 500,000 insurance claims suddenly takes 4 hours instead of 45 minutes after a schema change was deployed last Friday. No indexes were dropped. Walk me through your diagnostic approach and the fix you would implement.

Your team inherited a 12-year-old ERP customization: a 3,000-line PL/SQL package that mixes business logic, direct DML, and UTL_FILE writes into a single monolithic procedure. The package now needs to support multi-currency (5 new currencies) without a full rewrite. How do you approach this safely?

A developer accidentally ran DROP TABLE orders PURGE on the production Oracle 19c database at a retail company during peak trading. The table had 8 million rows and no recent export. RMAN backups exist. What is your exact recovery sequence?

You are managing a 4-node Oracle RAC 19c cluster for a global bank. One node fails at 2 AM due to a storage I/O timeout, triggering node eviction. The other three nodes continue serving traffic but performance degrades 35%. Describe your triage and remediation over the next 4 hours.

A report query that joins five tables across a 200 GB order management schema runs in 8 seconds in dev but takes 6 minutes in production, which has 10x the data. Both environments are Oracle 19c. Explain your investigation and the likely fixes.

An Oracle 19c OLTP system at a logistics company handles 2,000 TPS. After migrating from HDD to NVMe storage, average transaction latency dropped from 4 ms to 2 ms, but p99 latency spiked from 40 ms to 280 ms. How do you diagnose the p99 regression?

A client running Oracle EBS R12.2 is seeing intermittent 'APP-FND-01564: ORACLE error 1555 has occurred' errors during a large GL period-close process. The DBA says the UNDO tablespace is sized at 20 GB. What is happening and how do you fix it?

A manufacturing client on Oracle EBS R12.2.11 wants to customize the Purchase Order Approval workflow to route orders above $500K to a CFO approval step, but the IT policy prohibits modifying Oracle-seeded workflow definitions. How do you implement this without violating policy?

A finance team at a mid-size retailer has just gone live on Oracle Fusion Cloud Financials. They report that the AutoPost program for journal entries is completing but many journals remain in 'Incomplete' status instead of posting. No error message is shown. Where do you look first?

A global manufacturing company is implementing Oracle Fusion Cloud Procurement across 14 legal entities in 6 countries with different tax regimes, approval hierarchies, and currency requirements. The project is 6 weeks from go-live and the CFO just asked whether a single procurement BU structure will work. How do you advise?

A developer submits a SQL query for code review that uses NOT IN with a subquery against a nullable column to find customers without any orders in the last 90 days. The query returns zero rows unexpectedly. What is wrong and how do you fix it?

A data warehouse query on Oracle 19c aggregating 2 billion sales rows across 5 years runs in 22 minutes. The business wants it under 3 minutes for a daily dashboard. Partitioning exists on SALE_DATE by month. What is your optimization strategy?

You are designing the data model for an Oracle database that will store a product catalog for an e-commerce platform. Products can belong to multiple categories, have variable attributes (electronics have voltage; clothing has size/color), and prices vary by region. Walk me through your design decisions.

A telecom company needs to migrate their Oracle billing schema from a single-company design to a multi-tenant design supporting 20 independent MVNOs, each with complete data isolation. The existing schema has 180 tables with no tenant column. How do you approach this migration?

You are setting up RMAN backup strategy for a new Oracle 19c production database (2 TB, RPO 1 hour, RTO 4 hours). The backup target is an NFS mount with 8 TB available. Design the RMAN configuration and schedule.

A production Oracle 19c database serving a financial services firm experiences a silent data corruption on a critical ACCOUNTS table. The corruption was introduced 18 days ago during a botched application deployment. RMAN backups have a 14-day retention window. How do you recover the data?

A REST API integration between Oracle Fusion Cloud and a third-party CRM platform needs to sync customer account updates in near-real-time (under 5 minutes). The CRM team proposes a polling mechanism that hits Oracle's REST API every 2 minutes. Assess this approach and suggest alternatives.

You are lead architect for integrating Oracle EBS R12.2 GL data with a cloud data warehouse (Snowflake) for a financial reporting platform. Data must be available in Snowflake within 15 minutes of period-close in EBS. Design the end-to-end architecture.

A finance user complains that a BI Publisher report in Oracle EBS runs for 45 minutes and then times out. The report pulls 3 years of invoice data with a complex multi-join query. Where do you start to fix this?

A multinational company needs a single BI Publisher report that renders with different layouts, currencies, and legal disclosure text depending on which of 12 countries the requesting user is in. The report runs from Oracle Fusion Cloud. How do you design this without maintaining 12 separate report definitions?

An Oracle Forms 11g application at a manufacturing company is throwing FRM-41839 (cannot allocate memory for form) when a user opens a specific form with a large multi-row block displaying 5,000 product records. What causes this and how do you fix it?

A client wants to replace their Oracle Forms 12c custom application with a modern web UI while keeping Oracle Database and all existing PL/SQL business logic. The Forms application has 200 forms, 500 PL/SQL triggers, and 150 LOVs. Describe your migration strategy.

You are upgrading an Oracle 11gR2 database to 19c using the DBUA (Database Upgrade Assistant). During the pre-upgrade checks, you find 58 invalid objects and 3 deprecated parameters in init.ora. How do you handle these before proceeding?

A retail bank is upgrading Oracle EBS from R12.1.3 to R12.2.11 — a major in-place upgrade that takes the system from Oracle 11gR2 to 19c and introduces online patching (adop). The bank has 400 customizations. How do you plan and execute this upgrade with minimal downtime?

You are implementing Oracle Fusion Cloud Projects (PPM) for a professional services firm. During conference room pilots, the project managers complain that budget versus actuals reporting shows a 24-hour lag. The CFO needs same-day visibility. How do you resolve this?

You are the Oracle Cloud ERP implementation lead for a 3,000-employee manufacturing company go-live in 90 days. Three weeks before go-live, data migration testing reveals that 40,000 of 180,000 open purchase orders failed validation due to supplier site mismatches. How do you manage this crisis?

An end user in accounts payable reports that they can no longer access the 'Enter Invoices' form in Oracle EBS after a role change approved by the security team last week. The user has the same responsibility they had before. How do you troubleshoot this?

Your team manages Oracle EBS for a healthcare company with 10,000 users. On a Monday morning, 200 concurrent users report the application is extremely slow — page loads taking 30–45 seconds. The database team says the database is fine. Where is the bottleneck and how do you find it fast?

An autonomous transaction in a PL/SQL package at a payments company is being used to log every failed transaction to an audit table, even when the main transaction rolls back. After a code review, a DBA points out the log records are missing after a database crash mid-session. Why, and how do you make the audit trail crash-consistent?

A high-frequency trading firm's Oracle 19c database shows 'cursor: pin S wait on X' wait events dominating V$SESSION_WAIT during peak market hours. Parsing time spikes from 0.2 ms to 120 ms per query. Explain the root cause and your optimization strategy.

During Oracle EBS R12.2 patching using adop, the prepare phase completes but the apply phase fails with 'edition object conflict' errors on 3 custom packages. The patch window is 2 hours and half that time is already gone. What do you do?

A developer shows you this query: SELECT * FROM orders WHERE UPPER(customer_name) = 'ACME CORP'. It runs in 8 seconds on a table with 10 million rows. There is an index on customer_name. Why is the index not being used and how do you fix it?

A large energy company is consolidating two separately implemented Oracle Fusion Cloud instances (one for upstream, one for downstream) into a single instance with a unified chart of accounts. Finance insists on zero data loss and complete historical reporting continuity. What is your migration architecture?

You are designing an Oracle database schema to support a hospital's patient records system handling 500 admissions per day. The system must enforce strict HIPAA data isolation between departments, support fast discharge summaries, and be auditable for every access to patient records. How do you design it?

An Oracle 19c Data Guard standby database at a media company has fallen 48 hours behind the primary due to a network outage. The gap is too large for standard log apply to catch up within the business RTO of 2 hours. How do you recover without a full rebuild of the standby?

A retail company uses Oracle GoldenGate to replicate data from Oracle 19c to a downstream analytics Oracle database in real time. After a primary database failover to the standby (Data Guard role transition), the GoldenGate extract process stops and replication falls behind by 6 hours. Describe how GoldenGate should be configured to handle this automatically.

A BI Publisher scheduled report bursts to 800 individual PDF files and emails them to recipients every Friday. Last Friday the burst job completed but only 200 recipients received their PDFs. The other 600 received no email and no error. How do you investigate?

You inherit a PL/SQL procedure that opens a cursor, loops through results using FETCH, and updates each row individually. It processes 50,000 rows nightly and takes 25 minutes. You need to get this under 3 minutes. What changes do you make?

An Oracle Reports 11g report fails with REP-1401 and ORA-12154 when run from the Oracle Reports Server, even though the same database connection works fine when tested from SQL*Plus on the same server. What is the likely cause?

A major Oracle EBS production outage occurs at 11 PM: the entire application is inaccessible. The error in the alert log is 'ORA-00257: archiver error, connect internal only, until freed'. Your on-call DBA is unavailable. Walk me through resolving this as an Oracle Consultant.

A government agency must migrate an Oracle 12.1 on-premises database (3 TB, 15 years of historical data) to Oracle Autonomous Data Warehouse (ADW) on OCI, with a 6-hour downtime window and zero data loss. Design the migration approach.

A financial services firm is implementing Oracle Fusion Cloud Cash Management for the first time. During SIT, the bank statement auto-reconciliation rate is only 40% — far below the 95% target. Go-live is in 6 weeks. What do you do?

An Oracle 19c RAC database for an airline reservation system shows intermittent 'gc buffer busy acquire' and 'gc cr block 2-way' wait events spiking during flight search queries. Average wait time for these events is 12 ms, up from 2 ms last month. What changed and how do you fix it?

A payroll manager reports that the Oracle EBS Payroll processing completes successfully but the resulting journal entries in GL show incorrect costing for 200 employees — costs are posted to the wrong cost center. The costing setup has not changed. What do you investigate?

You are migrating an Oracle 12c database from an on-premises Linux server to a new Oracle 19c Linux server using Data Pump. After the import completes, a developer reports that sequences in the application are generating IDs that conflict with existing data imported from the old system. Why is this happening?

A procurement analyst at a consumer goods company notices that approved purchase orders in Oracle Fusion Procurement are not creating accounting entries in the subledger. The Encumbrance Accounting is enabled. The PO status shows 'Approved' but V$SUBLEDGER shows no events. What is happening?

A SELECT query joining three tables runs in 2 seconds in isolation, but when 50 users run it simultaneously on Oracle 19c, average response time climbs to 40 seconds. The query uses bind variables and its execution plan is stable. What causes the concurrency degradation and how do you address it?

You are configuring Oracle Fusion Cloud Payables for a new legal entity in India. The client's finance team wants three-way matching on all invoices, but the AP team says this is causing most invoices to stall in the 'Pending Approval' queue. Only 20% of invoices are auto-approved. What is likely wrong with the configuration?

A query on an Oracle 19c data warehouse uses a WITH clause (CTE) with five nested CTEs, each referencing the previous one. The final CTE computes a 90-day rolling average on 500 million rows. The query runs in 18 minutes. The DBA says CTEs are being materialized. Explain what is happening and the best fix.

A developer proposes storing audit history for an Oracle customer table using a trigger that inserts into an audit table on every UPDATE. The customer table receives 10,000 updates per hour. You have concerns. What are they, and what do you propose instead?

A nightly batch job in a logistics company processes 2 million shipment records using a PL/SQL cursor FOR loop. It has been running 6 hours but business now needs it under 45 minutes. The DBA says there are no missing indexes. How do you approach this re-architecture?

You inherit a PL/SQL package in a healthcare ISV's system where a stored procedure returns different row counts depending on whether it is called from SQL*Plus, a Java JDBC client, or a scheduled DBMS_SCHEDULER job. The business logic seems identical. What is causing this and how do you diagnose it?

A retail bank's Oracle 19c RAC cluster starts throwing ORA-04031 errors during end-of-month batch processing. The batch was running fine last quarter. The DBA team has already increased the shared pool to 8 GB. Errors persist. Walk through your full diagnosis.

You are managing an Oracle 19c SE2 instance for a mid-sized manufacturing company. Developers are complaining that running ALTER TABLE to add a NOT NULL column with a default value locks the table for 30 minutes and blocks the application. How do you resolve this?

An insurance company's Oracle 19c reporting database suddenly degrades to 10x slower query times every weekday at 2 PM. AWR snapshots show the wait event 'log file sync' spiking to 80% of total wait time during that window. Storage team says disk latency is nominal. What is your analysis?

A developer hands you a SQL query that joins six tables for a product catalog search in an e-commerce platform. On a 50-row test dataset it runs in 20ms. In production with 10 million rows it takes 4 minutes. EXPLAIN PLAN shows a HASH JOIN but no parallel operations. What is your step-by-step tuning approach?

A manufacturing client on Oracle EBS 12.2.10 reports that their month-end close is failing in General Ledger with FND-01564 errors referencing a concurrent program that posted fine last month. No code changes were deployed. The DBA says the instance was patched last weekend. What is your investigation path?

A client on EBS R12.1.3 asks you to add a new custom field to the Purchase Order header form that populates from a lookup code and must be stored in a custom extension table. Outline your approach end-to-end including how you handle future patching risk.

A global retail chain on Oracle Fusion Cloud Financials wants to integrate their legacy on-premise warehouse management system with Oracle Inventory Management Cloud. The WMS sends 500K inventory transactions daily via flat files. Describe your integration architecture and key failure-handling strategy.

Your client just went live on Oracle Fusion Procurement Cloud. Users report that approvals for purchase requisitions above $50,000 are routing to the wrong approver group. Approval chains were configured before go-live. What do you check first?

A telecom company's billing query joins a 500-million-row CDR table to a 2-million-row customer table. The query runs in 12 seconds using a nested loop, which was acceptable two years ago but now the CDR table has grown and the query takes 4 minutes. No hint changes the plan. Why and what do you do?

A developer reports that a query returning order summaries runs in 2 seconds when they write it as a correlated subquery but in 15 seconds when rewritten as a JOIN. The DBA says this is backwards from what they expected. Explain why this can happen and what you would verify.

A healthcare provider wants to model patient clinical events in an Oracle data warehouse. Events include lab results, diagnoses, medications, and procedures—each with different attributes. The team proposes a single generic EAV (Entity-Attribute-Value) table. You disagree. Make your case and propose an alternative.

You are designing the product catalog schema for an Oracle-backed multi-tenant e-commerce SaaS platform. Each tenant can have different product attributes (electronics vs. clothing vs. food). How do you handle the varying attribute problem without EAV and without separate schemas per tenant?

A financial services firm's Oracle 19c primary database has an MTD=0 (zero data loss) requirement. Their current RMAN strategy is a nightly full backup to tape with 30-minute archived log backups. The RPO is 30 minutes. Leadership now demands RPO of 5 seconds. How do you redesign the backup and availability architecture?

You are asked to restore a single 200GB tablespace from RMAN backup on a production Oracle 19c instance without taking the entire database offline. The tablespace contains read/write tables that have had transactions since the last backup was taken 6 hours ago. Walk through your recovery procedure.

A utility company's Oracle EBS AP module needs to receive invoices from 120 supplier EDI systems in real time. Current volume is 8,000 invoices per day. They use Oracle SOA Suite 12c as the middleware layer. Describe how you design the integration including error quarantine and audit trail.

You need to expose Oracle EBS R12 Purchase Order data to a new microservices application in real time. The microservices team wants a REST API. EBS has no native REST layer. What are your options and which do you recommend for a team with Oracle Integration Cloud available?

A finance director at a consumer goods company complains that a BI Publisher report for monthly P&L takes 45 minutes to generate and occasionally times out. The report runs against Oracle EBS GL. The data model queries three complex views. Describe your tuning strategy.

A client wants a BI Publisher report in Oracle EBS that conditionally shows different sections based on the user's responsibility. For example, CFO responsibility shows salary data; AP Clerk responsibility shows only invoice totals. How do you implement this without building separate reports?

An Oracle EBS Forms-based purchasing workflow was customized four years ago using a WHEN-NEW-ITEM-INSTANCE trigger to auto-populate a vendor field. After upgrading to EBS 12.2 and applying the latest RUP, the trigger fires but the vendor field is blank. No errors in the log. How do you troubleshoot this?

A client on Oracle EBS 12.1 needs an Oracle Reports 10g-based output that was designed for A4 paper to now produce output in both A4 and Letter size based on a runtime parameter. The report uses hard-coded margins. What is your approach?

A pharmaceutical company wants to migrate from Oracle EBS 11i to Oracle Fusion Cloud ERP. They have 15 years of transactional history (40TB), 120 customizations, and a hard go-live deadline in 18 months. How do you structure the program and what are the highest-risk elements?

You are leading the technical migration of an Oracle 11g (11.2.0.4) database to Oracle 19c on new hardware. The database is 2TB with 50 application schemas and no downtime window longer than 4 hours. What is your migration strategy?

A food manufacturing company is implementing Oracle Fusion Cloud Procurement for the first time. During CRP1, business users discover that the approval workflow in their current system allows delegation chains of up to 7 levels, but Fusion's standard approval configuration only supports 3 levels before requiring escalation. How do you resolve this gap?

During a Fusion Financials implementation, your client's finance team insists on keeping their existing Excel-based period-close checklist outside of Oracle rather than adopting Close Monitor in Fusion. How do you handle this pushback and what risks do you flag?

An Oracle EBS production instance experiences intermittent ORA-04061 errors (existing state of package has been discarded) that affect random users throughout the business day. The DBA cannot reproduce it consistently. It has been escalated to Oracle Support who asked for more information. What diagnostic data do you collect and what is your hypothesis?

A user calls you reporting that their Oracle EBS expense report is stuck in the 'In Process' status for 3 days and has not reached their manager for approval. The workflow appears frozen. How do you diagnose and resolve this?

An energy company's Oracle 19c PL/SQL package uses autonomous transactions inside a loop to write audit records. After a production incident, auditors discover that audit records were written for transactions that were ultimately rolled back. Explain the root cause and how you redesign this.

You are reviewing a PL/SQL procedure in a banking application that processes loan calculations. The procedure uses a nested function that is declared inside another function's body. A junior DBA says this is not valid in Oracle. How do you respond and what are the implications?

A media streaming company runs Oracle 19c on AWS EC2. During peak streaming events they see a dramatic spike in 'library cache: mutex X' wait events. The application team says they have not changed any code. The spike correlates with a 3x traffic increase. Explain the root cause and remediation.

A government agency's Oracle 19c reporting database runs a query that joins GL, AP, and HR tables for a monthly payroll audit report. When run by Analyst A, it completes in 90 seconds. When run by Analyst B with identical filters, it takes 22 minutes. Both users query the same view. What do you investigate?

A retail company on Oracle EBS R12.2.7 needs to implement a multi-org structure to support their acquisition of a smaller company. The acquired company has different fiscal calendars, currencies, and legal entities. How do you approach the multi-org configuration without disrupting existing operations?

During a Fusion Cloud Financials implementation, your client's controller realizes that Oracle's standard period-end accrual process does not support their industry-specific revenue recognition methodology (percentage-of-completion for long-term contracts). This is discovered in CRP2. How do you handle this?

A data engineering team at a logistics company runs a daily ETL query that aggregates 200 million rows of GPS tracking events using GROUP BY on vehicle_id and day. The query runs in 2 hours. A colleague suggests converting it to a materialized view with ON COMMIT FAST REFRESH. Will this work, and is it the right solution?

A supply chain analytics team needs a dimensional model for Oracle Analytics Cloud. Their source system is Oracle EBS R12. They want to track supplier performance over time—including changes to supplier category, tier, and payment terms—with full historical accuracy. How do you model this?

An RMAN backup job that previously completed in 3 hours now takes 9 hours on the same Oracle 19c instance. The DBA confirms no new tablespaces or significant data growth. Storage team says I/O bandwidth is unchanged. What are the most likely causes?

A bank needs to synchronize customer master data between Oracle Fusion Customer Experience Cloud and their legacy core banking system in real time, with strict 2-second SLA. Conflicts can occur when both systems update the same customer record simultaneously. How do you design the conflict resolution strategy?

A client asks you to build a BI Publisher report that merges two separate Oracle EBS data sources: one from GL and one from a custom XXCUSTOM schema table. The report must reconcile balances between both. How do you structure the data model and handle the potential for mismatched periods?

A logistics company is two weeks from go-live on Oracle Fusion Procurement Cloud. During final UAT, business users discover they cannot create blanket purchase agreements because the purchasing configuration for their primary business unit was set to 'Standard PO Only'. It was configured this way by accident. What is the risk of changing this now and how do you proceed?

A critical Oracle EBS concurrent program hangs every night at 2:30 AM after running for exactly 47 minutes. The program processes payroll data for 15,000 employees. The DBA kills the session manually each time and the program succeeds when re-run. No errors appear in the log. How do you systematically diagnose this?

You are leading an Oracle EBS 12.2 Online Patching (AD Online Patching or ADOP) cycle for a 24x7 manufacturing client that cannot take the application offline. Midway through the patch cycle, ADOP fails in the 'apply' phase with error 'adop phase=apply failed: worker process aborted'. Describe your recovery procedure.

An Oracle EBS R12.1 Forms-based AR invoice entry form is running slowly for users in the Singapore office (200ms local, 8 seconds Singapore). Network team says latency between Singapore and the US data center is 220ms round-trip. Describe your analysis and recommended solution.

Users of an Oracle EBS 12.2 instance report that Oracle Forms-based screens are extremely slow to load after the DBA applied an operating system patch to the middle-tier server last night. The DBA says they only patched the OS and did not touch any Oracle software. How do you investigate?

A developer on a financial trading application reports that their CONNECT BY hierarchical query for an account tree runs in 3 seconds with 10,000 accounts but causes ORA-01436 (CONNECT BY loop in user data) with 10,500 accounts. How do you investigate the data issue?

Your client on Oracle Fusion Cloud HCM has 500 employees whose grade steps need to be mass-updated as part of a compensation cycle. The HR team wants to do this in bulk without entering each record individually. What are their options and what do you recommend?

A business analyst writes a query using multiple correlated subqueries in the SELECT clause to compute different aggregates for each order row, for example separate subqueries for total_lines, total_amount, and last_shipment_date. The query scans the order lines table three times. How do you refactor this?

A global bank is implementing Oracle Fusion Financials Cloud across 40 countries with 12 functional currency ledgers. The COA design phase is taking longer than planned. The project sponsor asks you whether it is safe to start configuring Payables and Receivables before the COA is finalized. How do you respond?

You are migrating data from Oracle EBS 11i AP to Oracle Fusion Payables Cloud. Source data includes 3 years of invoices, suppliers, and payment history. Oracle's recommended migration tool is FBDI (File-Based Data Import). Describe how you validate data completeness after migration.

A manufacturing company uses Oracle EBS for financials and SAP for plant operations. They need purchase orders created in SAP to automatically create corresponding POs in EBS without manual re-entry. What integration pattern do you recommend and what are the key data mapping challenges?

An HR director wants a BI Publisher report showing headcount by department that always reflects data as of last Friday, regardless of when the report is run during the week. Oracle Fusion HCM is the data source. How do you implement the 'as-of-last-Friday' date logic?

A nightly batch job at a telecom company processes 4 million CDRs using a PL/SQL procedure. It ran in 45 minutes six months ago but now takes over 5 hours. The underlying table has grown from 200 million to 1.8 billion rows. How do you diagnose and fix this regression?

You are reviewing a PL/SQL package in a retail banking system where a developer used EXECUTE IMMEDIATE inside a FOR loop to insert audit records. Code review flagged it. Explain what is wrong and how you would refactor it.

A healthcare ISV ships a PL/SQL API package used by 12 client databases. A client reports that calling pkg_patient.get_summary returns ORA-04068 (existing state of packages has been discarded) intermittently, causing their application to crash. How do you root-cause and resolve this?

An e-commerce platform running Oracle 19c on Exadata reports that undo tablespace grew from 8 GB to 340 GB overnight during a promotion event. The on-call DBA extended it, but the underlying cause is unknown. Walk through your investigation.

A junior DBA accidentally dropped a non-system tablespace named APPDATA in a production Oracle 19c database with no current RMAN backup available, but archivelog mode is enabled. What recovery options exist?

You are the DBA for a financial institution running Oracle RAC with three nodes. Node 2 is evicted repeatedly from the cluster every 72–96 hours, always between 2 AM and 4 AM. The hardware team finds no faults. How do you investigate?

A logistics company's Oracle 12c warehouse has a critical report that joins five fact tables and six dimension tables. It runs in 3 minutes during off-peak but degrades to 45 minutes during the 8 AM business peak. Explain your systematic tuning approach.

A developer reports that a SQL query in a manufacturing ERP returns results in 200ms when run directly in SQL Developer but takes 8 seconds when called from the Java application. The query and parameters are identical. What do you investigate?

After an Oracle 19c upgrade at a media streaming company, a content catalog search query that used an index range scan now performs a full table scan on a 500-million-row table. The index exists and was not rebuilt. What are the possible causes?

A manufacturing company running Oracle EBS R12.2 reports that the concurrent manager is backing up 800 jobs in the queue during month-end close. Finance wants the GL posting and MRP planning runs to complete within a 4-hour window. What is your approach?

You are implementing Oracle EBS Accounts Payable for a retail chain. The client wants to match purchase orders, receipts, and invoices in a 3-way match, but 20% of invoices have quantity or price discrepancies. How do you configure the tolerance and exception workflow?

After applying a critical RUP patch to Oracle EBS R12.2, users report that the Sales Order form throws FRM-40735 when saving. The patch touched OE_ORDER_PUB. Describe your debugging and rollback strategy.

A global manufacturing firm migrating from Oracle EBS to Oracle Fusion Cloud ERP wants to maintain their 47 custom reports built in EBS BI Publisher. The project manager assumes they can be migrated as-is. How do you handle this expectation gap?

A Fusion Cloud Financials client complains that their period close checklist shows the AP trial balance and GL trial balance are out of sync by $23,000 at month-end. The discrepancy appeared after a recent journal import. What do you investigate?

A retail bank is implementing Oracle Fusion Cloud HCM and Payroll simultaneously. Three months before go-live, the payroll consultant discovers that the bank's 14 pay groups have different overtime rules due to historical union agreements, and the standard payroll elements don't support them. How do you handle this?

A reporting query in a government ERP system uses NOT IN with a subquery against a table with 2 million rows. The subquery can return NULL values. The query returns unexpected results and runs for 20 minutes. Explain the issue and fix it.

An analytics team at an insurance company runs a weekly risk aggregation query that takes 14 hours. It uses multiple WITH clause (CTE) references, window functions, and scans a 6-billion-row claims table. How do you approach this optimization?

A developer submits a SQL query for code review at a fintech company. It uses a correlated subquery in the SELECT list to compute a running balance for each transaction. The query works but takes 40 seconds on 500,000 rows. Rewrite it and explain your reasoning.

You are designing the data model for a multi-tenant SaaS Oracle database serving 500 enterprise clients. Each client has isolated data, with some clients requiring different custom attributes on the core 'Account' entity. How do you model this without creating 500 schema variations?

A startup building a healthcare platform on Oracle asks you to review their initial schema. The 'Patient' table has a 'current_medications' column stored as a comma-separated string. Explain the problems and propose a corrected model.

A bank's Oracle data warehouse tracks customer account balances. The current model stores only the current balance. Auditors now require a complete balance history going back 7 years, including who changed what and when. How do you redesign the model?

A critical Oracle 19c OLTP database at an energy company has a 4-hour RTO and 30-minute RPO SLA. The current backup strategy is a nightly full RMAN backup to local disk. A storage controller failure corrupts the primary datafiles at 2 PM. Walk through your recovery execution.

You are the DBA at a mid-sized logistics company. During an RMAN backup job, you receive ORA-19809: limit exceeded for recovery files, and the backup fails. The flash recovery area is 80% full. What are your immediate and long-term actions?

A data warehouse at a media company uses Oracle 19c. Someone executed DELETE FROM FACT_SALES WHERE 1=1 at 10:15 AM without a WHERE clause and committed. The table had 180 million rows. The earliest RMAN backup is from 10 PM last night. How do you recover?

A telecommunications company needs to integrate Oracle EBS with a third-party CRM in real time. Whenever a customer account is created or updated in EBS, the CRM must be notified within 30 seconds. How do you architect this integration?

An Oracle Integration Cloud (OIC) integration between a Fusion Cloud HCM and a payroll provider is failing for 3% of employees with error 'Employee record not found in target system.' Transfers and new hires are failing. How do you debug this?

A bank is decommissioning a legacy mainframe general ledger and needs to migrate 22 years of transaction history to Oracle. The mainframe exports EBCDIC flat files nightly. The Oracle GL requires IFRS-compliant chart of accounts mapping. Describe your migration architecture.

A CFO complains that the Oracle BI Publisher aging report for Accounts Receivable shows different totals than the SQL query the AR team runs manually. Both query the same Oracle EBS database. The difference is $1.2 million. How do you investigate?

A BI Publisher report template in Oracle EBS is rendering Arabic text as garbled characters in the PDF output. The data in the database is stored correctly in UTF-8. What are the likely causes and fixes?

A BI Publisher operational report runs every 30 minutes and queries the Oracle EBS production OLTP database. Users complain it impacts transaction performance during business hours. How do you resolve this without losing report freshness?

Users of an Oracle Forms 12c application report that a specific form intermittently freezes when they try to open the LOV (List of Values) on the Item Code field. The LOV query joins three tables and has no WHERE clause. What do you do?

An organization is running Oracle Reports 11g in character mode on an old Solaris server. The server is being decommissioned. Leadership wants to move reports to a modern stack but has a 6-month timeline with no Forms/Reports rewrite budget. What is your migration strategy?

A pharmaceutical company needs to upgrade Oracle EBS R12.1.3 to R12.2.10 while remaining on Oracle Database 12c. The system has 200+ custom objects (packages, triggers, views) and 15 custom integrations. What is your upgrade methodology?

You are migrating a small business's Oracle 11g database (non-CDB, 500 GB) to Oracle 19c as a PDB inside an existing CDB. The 11g database uses UTL_FILE directories and Database Links. What are the migration steps and the gotchas?

A government agency is migrating from Oracle EBS on-premises to Oracle Fusion Cloud ERP. They have 8 years of transaction history and a legal requirement to retain it for 25 years. The Fusion Cloud instance will only store 5 years of active data. How do you handle the historical data?

A retail company is implementing Oracle Fusion Cloud Procurement for the first time. During the conference room pilot, the procurement team insists that all purchase orders must go through a 5-level approval hierarchy based on spend amount and commodity type. The project timeline is 6 months. How do you advise?

During the data migration phase of an Oracle Fusion Cloud Financials implementation, the migration team discovers that 30% of the legacy supplier records have missing tax registration numbers, which Fusion requires for AP invoice processing. Go-live is in 4 weeks. What do you do?

A manufacturing client implementing Oracle Fusion Cloud SCM discovers that their bill of materials (BOM) has 14,000 components with complex phantom subassembly structures. The standard FBDI template for BOM import doesn't support phantom items natively. How do you handle this?

An Oracle EBS production system at a hospital is throwing ORA-04031 (unable to allocate shared memory) intermittently during peak hours, causing user sessions to be killed. The system has been running for 3 years without this error. What changed and how do you fix it?

An Oracle EBS user reports that their concurrent program 'Generate Customer Statements' has been running for 6 hours and is still in 'Running' status. No error is visible. A manager is demanding answers. How do you investigate?

Oracle Support has issued a critical security patch (CPU) that patches a vulnerability in Oracle Database 19c's listener. Your Oracle EBS system depends on this listener. You must apply the patch within 72 hours per security policy. How do you plan and execute this under time pressure?

A developer accidentally granted DBA role to an application user account in a production Oracle 19c database during a maintenance task that was meant for dev. The account is connected to a live ERP system. How do you remediate this without disrupting the application?

A financial services company uses a PL/SQL function that computes compound interest. During testing, a QA analyst finds that for large principal amounts (above 100 million), the function returns NULL with no error. The function uses NUMBER(12,2) for intermediate calculations. What is happening?

You are asked to create a read-only reporting user in Oracle 19c who needs to query all tables in the FINANCE schema but should not be able to see any tables in the HR schema. How do you set this up correctly?

A developer reports that enabling the RESULT_CACHE hint on a frequently called PL/SQL function in a high-throughput order management system caused performance to degrade rather than improve. How do you explain and resolve this?

A manufacturing company running Oracle Fusion Cloud SCM discovers that their MRP planning run is generating purchase recommendations for items with existing stock on hand exceeding safety stock. The planning engine seems to be ignoring on-hand balances. What do you investigate?

A developer's Oracle SQL query uses a function in the WHERE clause: WHERE UPPER(customer_name) = 'ACME CORP'. It was fast last year but now takes 45 seconds on a table that grew from 500K to 5 million rows. The column has a B-tree index. What is wrong and how do you fix it?

An organization's RMAN backup job has been completing successfully for months, but a test restore attempt reveals that 3 archived log files referenced in the backup catalog are missing from the tape library. What process improvements do you implement?

An Oracle Database Link from EBS PROD to an Oracle Financials Cloud database is failing with ORA-12170 (Connect timeout) after a recent network firewall change. The DBLink was working before the change. How do you diagnose and restore it?

A newly hired Oracle EBS functional consultant notices that GL journal lines are being posted to the wrong accounting period even though users are entering transactions in the correct period. Journals seem to be using the system date instead of the transaction date. What do you investigate?

A BI Publisher scheduled report emails a 45 MB Excel file to 80 users every Monday morning. The email server is rejecting the attachment due to size limits. Suggest practical solutions without eliminating the report.

A startup asks you to design the database schema for a subscription billing system on Oracle. Customers can have multiple subscriptions, each with a different billing cycle (monthly, quarterly, annual), and prices can change during the subscription term. What are the key tables?

During UAT for a new Oracle Fusion Cloud Accounts Receivable implementation, the client's AR manager reports that AutoInvoice is rejecting 15% of invoice lines with 'Invalid transaction type.' The source system sends transaction type codes that were valid in their legacy AR system. What is causing this?

A university is migrating its Oracle 10g student information database (1.2 TB) to Oracle 19c. The database uses deprecated features including CONNECT BY PRIOR without a START WITH clause and 10g-specific outer join syntax (+). How do you assess and handle the SQL compatibility issues?

A large retail chain's Oracle 19c database hits an ORA-00600 (internal error code) during an online backup operation every Sunday at 3 AM. The error is always the same internal code: [kdt_bseg_srch_cbk]. It has occurred 4 times in 4 weeks. How do you approach this?

An organization's Oracle Forms 10g application has a complex multi-block master-detail form used for order entry. Users report that the 'List Items' button in the detail block returns 0 results when it should return thousands of items. The same LOV query returns rows when run in SQL*Plus. What are the potential causes?

A nightly batch in a logistics company processes 2 million shipment records using a row-by-row PL/SQL loop. The job now takes 6 hours and is missing the SLA window. You are asked to redesign it without changing the business logic. What is your approach?

You inherit a package where a procedure raises ORA-04068 intermittently in production when a deployment is pushed. Users report that re-running the procedure once fixes it. Explain what is happening and how you would prevent it.

A 10 TB Oracle 19c database serving a global retail chain reports that redo log switches happen every 90 seconds during peak hours, causing periodic 'log file switch' wait events. How do you diagnose and resolve this without taking the system offline?

A developer asks you to create a new tablespace for a reporting schema in production. They want AUTOEXTEND ON with no maximum size. Why is this dangerous and what alternative do you propose?

An insurance company's end-of-month report on Oracle 19c takes 4 hours. The query joins 8 tables and includes GROUP BY and window functions across 300 million rows. AWR shows 'direct path read' waits dominating. What is your tuning plan?

A developer reports that their query ran in 2 seconds yesterday but takes 45 seconds today. They did not change the SQL. The table had a batch load last night that added 10 million rows. What do you investigate first?

A manufacturing client on Oracle EBS R12.2 reports that concurrent request jobs in their Order Management module have been failing silently for 48 hours. No error appears in the request log, but output files are empty. How do you investigate?

A finance team running Oracle EBS 12.1.3 reports that journal imports from a feeder system are creating duplicate batches. The interface table GL_INTERFACE contains no apparent duplicates. How do you trace the root cause?

A global pharmaceutical company wants to consolidate 5 legal entities across 3 countries into Oracle Fusion Cloud Financials. Each entity has different fiscal calendars. How do you design the chart of accounts and ledger structure?

A business analyst reports that approved purchase orders in Oracle Fusion Procurement are not triggering budgetary control reservations. The budget is configured and periods are open. What do you check?

A telecom company runs a daily query joining a 500-million-row CDR fact table to three dimension tables. The query uses an outer join and a CASE expression in the WHERE clause. Execution time has degraded from 8 minutes to 55 minutes after a data migration. Describe your optimization process end-to-end.

A developer shows you a query that returns order totals for the last 30 days. It runs fine on the dev database with 50,000 rows but takes 3 minutes on production with 20 million rows. The WHERE clause filters on ORDER_DATE. What do you look for?

A healthcare system asks you to design the data model for a patient appointment scheduling system in Oracle. It must support multi-specialty hospitals, recurring appointments, cancellations with audit history, and real-time slot availability checks at scale. Walk through your design.

You are reviewing a data model for an e-commerce order system. The ORDERS table has 40 columns including 10 nullable flag columns like IS_GIFT, IS_EXPRESS, IS_CORPORATE. The architect says this is fine for flexibility. What concerns do you raise?

At 2 AM you receive an alert: a critical Oracle 19c OLTP database had its SYSTEM tablespace datafile accidentally deleted by a sysadmin running a disk cleanup script. The database is still up. What are your exact recovery steps?

Your RMAN backup job succeeds every night, but during a DR drill the restore fails with RMAN-06172: no AUTOBACKUP found. The controlfile was not backed up. Why does this happen and how do you fix it?

A bank wants to integrate Oracle Fusion ERP with a core banking system that exposes only file-based batch feeds (fixed-width flat files, daily). The bank's compliance team requires end-to-end message traceability and error alerting. Design the integration architecture.

You have configured a SOAP web service call from Oracle EBS to an external HR system using Oracle SOA Suite. The call works in UAT but returns a connection timeout in production. The network team says there is no firewall block. What do you investigate?

A procurement department requires a BI Publisher report that merges purchase order data from Oracle EBS with real-time supplier credit ratings fetched from an external REST API. The report must run on demand with response under 30 seconds. How do you architect this?

A BI Publisher report used by 200 finance users is correct in UAT but shows blank columns for certain fields when run in production. The data source query returns values in production SQL*Plus. What is your diagnostic approach?

An Oracle EBS 12.2 upgrade project reveals that a heavily customised Oracle Form (custom .fmb) with 200 program units is breaking after the upgrade due to deprecated API calls and changed base-table column names. You have two weeks to remediate. What is your strategy?

A legacy Oracle Report (RDF file) generates a Payroll Summary that is correct on an older server but shows incorrect totals on a new server running Oracle Reports 11g. Same database, same data. What do you investigate?

A retail bank is migrating from Oracle Database 11g to 19c in a lift-and-shift, with zero downtime tolerance — the core banking system cannot have more than a 15-minute planned outage. Walk through the migration architecture.

You are asked to migrate an Oracle 12c database to Oracle 19c using DBUA. After the upgrade completes, several Java stored procedures throw ORA-29521. What is the likely cause and how do you resolve it?

A global manufacturing company is implementing Oracle Fusion Cloud ERP across 12 countries simultaneously. Six months in, the project is 3 months behind schedule and the steering committee is asking you, as the lead consultant, to explain the root causes and present a recovery plan.

During the SIT phase of an Oracle Fusion Payroll implementation, testers find that net pay calculations are incorrect for employees with multiple assignments. The configuration was reviewed and appears correct. What is your investigation approach?

A P1 incident is raised at 3 AM: an Oracle EBS 12.2 AP invoice validation program has been running for 6 hours with no output and is holding 47 other concurrent requests in the Normal queue. The database alert log shows no errors. What is your war-room process?

A user reports that they cannot open a specific Oracle EBS responsibility. The error message is 'FND_FUNCTION_NOT_FOUND'. Other users can open the same responsibility without issues. What is your troubleshooting checklist?

A financial services firm's end-of-day interest calculation package is running correctly but causes a deadlock (ORA-00060) approximately twice a week when run concurrently with a repayment posting batch. Both packages touch the LOAN_ACCOUNTS table. How do you resolve this without serializing all of their executions?

After enabling Oracle Transparent Data Encryption (TDE) on a 5 TB production database, the DBA team notices a 15% CPU increase and 10% storage increase. The CISO says this is acceptable, but the application team is complaining about insert latency doubling on the most active table. What do you investigate?

A media company's Oracle 19c database shows 'gc buffer busy acquire' wait events dominating in RAC during peak publishing windows. One node handles 80% of the workload while the other sits largely idle. What is the architectural problem and how do you fix it?

A retail company running Oracle EBS 12.2 on-premises wants to enable online patching (edition-based redefinition) for their customisations. They have 150 custom database objects referencing standard EBS base tables. What is the remediation process?

A professional services firm has gone live on Oracle Fusion Project Portfolio Management. Six weeks post-go-live, project managers report that resource actual hours are not flowing through to project financial plans, causing budget variance reports to be misleading. Diagnose and fix.

An energy trading company runs a position calculation query that joins 12 tables and takes 18 minutes. The trading desk needs it under 2 minutes for intraday use. The query cannot be restructured because it is owned by a vendor application. What options do you have?

A government agency needs to model a land registration system in Oracle. Properties can be co-owned, ownership can transfer partially or fully, and the system must support point-in-time queries: show ownership as it was on any given date. How do you design this?

A financial institution has a Recovery Time Objective of 30 minutes and a Recovery Point Objective of 5 minutes for their Oracle 19c trading database. Their current RMAN strategy is a nightly full backup with no archived log backups during the day. How do you redesign the strategy?

A healthcare insurer wants to move from a custom PL/SQL ETL that populates a reporting datamart from Oracle EBS to an Oracle Data Integrator (ODI) based solution. The current ETL has 200 transformation rules embedded in stored procedures. How do you approach the migration?

A pharmaceutical company's regulatory submission report generated by BI Publisher must be pixel-perfect to FDA specifications: fixed column widths, specific fonts, and page-break logic that varies by section. The report is currently drifting on certain printers. How do you address this?

A client's Oracle Form has a custom LOV (List of Values) that performs a query against a 10-million-row table. The LOV takes 8 seconds to open, frustrating users. You cannot change the underlying table. What are your options?

A telecommunications company is upgrading Oracle EBS from 12.1.3 to 12.2.10 with 300 custom objects including 80 custom database triggers on standard EBS tables. The project manager wants a 4-day go-live weekend. Is this feasible and what are the critical path items?

During the CRP2 (Conference Room Pilot 2) of an Oracle Fusion Cloud Financials implementation for a private equity firm, the revenue recognition rules for complex financial instruments are not producing correct journal entries. The FP&A team insists the configuration is correct. How do you resolve the conflict?

A major bank escalates a P1 at year-end: Oracle Fusion GL Period Close is hanging at the 'Sweep Transactions' step for 4 hours. The close was successful last quarter. What changed and how do you resolve it?

A developer has written a function that queries a table inside a loop, calling it once per row of a parent cursor. In testing with 500 rows it performs fine. In production with 500,000 rows it runs for hours. The developer insists the function itself is fast. What is the problem?