SQL and PLSQL Interview Questions - 09 Sep 2025

Enhancements in Oracle 12c vs 11g

1. Multitenant Architecture (CDB & PDBs)
2. In-Memory Column Store
3. Automatic Data Optimization & Heat Map
4. Advanced Partitioning Enhancements
5. Improved Security (Data Redaction, Unified Auditing)
6. Enhanced PL/SQL Features
7. Online Operations (reduced downtime)
8. Sharding (12cR2)
9. Adaptive Query Optimization, SQL pattern matching, better RMAN features

Summary: 11g had standalone DBs; 12c introduced multitenancy, in-memory, ILM, enhanced security, and adaptive optimization.

JSON Enhancements in 12c

- Native storage in VARCHAR2/CLOB/BLOB with IS JSON constraint.
- Functions: JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_TABLE.
- Indexing support for JSON attributes.
- PL/SQL APIs: JSON_OBJECT_T, JSON_ARRAY_T, etc.
- Integration with REST/Big Data.
11g: No JSON support; 12c: introduced JSON features.

Advantage of JSON Data Type

- Efficient binary storage (less space, faster parsing).
- Faster query performance (no repeated text parsing).
- Automatic validation of JSON data.
- Better indexing (native JSON indexes).
- Seamless integration with modern apps & REST APIs.
- Future-proof (aligned with PostgreSQL/MySQL/MongoDB).

JSON Evolution: 11g vs 12c vs 21c

11g: No JSON support.
12c: JSON stored in VARCHAR2/CLOB/BLOB + IS JSON constraint, slower.
21c+: Native JSON data type, binary format, faster, validated, better indexing.

VARCHAR2 JSON vs JSON Datatype

- VARCHAR2: Flexible, works in 12c, but slower, more storage, manual validation.
- JSON datatype: 21c+, faster, compact, validated automatically, better indexing, but less flexible and requires newer version.

Pluggable Database (PDB)

- PDB is a portable collection of schemas & objects inside a Container Database (CDB).
- CDB has root (CDB$ROOT), seed (PDB$SEED), and multiple PDBs.
Advantages: Consolidation, easier management, portability, rapid provisioning, isolation.
Introduced in Oracle 12c.

User vs Schema

- User: Database account with authentication, owns objects.
- Schema: Collection of objects owned by a user.
- Creating a user automatically creates a schema with same name.
Example: CREATE USER HR; -> Creates HR schema where HR's tables reside.

Advantage of Materialized View vs Table

- Performance boost (precomputed joins/aggregations).
- Automatic refresh (ON COMMIT/ON DEMAND).
- Query rewrite for faster execution (transparent to apps).
- Less manual effort than summary tables.
Disadvantage: Data may not be real-time; overhead for refresh logs.

Temporary Table & Types

- Temporary table stores session/transaction-specific data in TEMP tablespace.
Types:
1. ON COMMIT DELETE ROWS: Data cleared after transaction.
2. ON COMMIT PRESERVE ROWS: Data persists till session ends.
Definition is permanent, data is temporary.

GTT vs Normal Table

- GTT definition permanent, data temporary & session-specific.
- Stored in TEMP tablespace, auto-cleared.
- Less redo/undo overhead.
- Normal table stores permanent data, visible to all users, in permanent tablespace.

Sharing Temporary Table Data

- Definition of GTT is global, shared across sessions.
- Data inside GTT is private per session.
- You cannot see my temporary table data, and I cannot see yours, even in the same database.

Click here for detailed Answers

ODI Interview Question and Answers 04 September 2025

Q: Can you tell me the architecture of ODI?

A: ODI follows a modular architecture with components:
- ODI Studio (design-time client for mappings, load plans, scenarios).
- Repositories (Master & Work) to store metadata.
- ODI Agent (Standalone/Java EE) to execute jobs.
- Topology for defining physical/logical data servers.
- Scenarios & Load Plans for execution in different contexts.

Q: Suppose you are migrating from 11g to 12c. What challenges do you face and how to handle that?

A: Migration challenges include:
- Repository upgrade (Master/Work migration scripts).
- Topology & security changes.
- ODI 12c introduces declarative mappings instead of interfaces.
Approach: Backup repos, run ODI upgrade assistant, validate mappings, test load plans, resolve deprecated KM usage.

Q: Suppose one load plan got failed in production. How to find out the issue and fix that?

A: Steps:
- Check ODI Operator logs for error details.
- Verify agent logs.
- Check source/target connectivity.
- Rerun failed step from restartability point.
- Fix root cause (data issue, connection, KM error) before re-execution.

Q: How to integrate ODI with Oracle Cloud or any third-party API?

A: Approaches:
- Use ODI Technology: RESTful Web Services / SOAP.
- Leverage ODI Procedure with Groovy/Java for API calls.
- Use Staging tables for JSON/XML parsing.
- For Oracle Cloud, configure topology with Oracle DBaaS/Autonomous DB endpoints.

Q: How to track the DML operation in SQL?

A: Methods:
- Triggers to log INSERT/UPDATE/DELETE in audit table.
- Use ORA_ROWSCN pseudocolumn.
- Use Flashback Data Archive for history.
- LogMiner for redo log mining.

Q: What is the substitute of API in ODI?

A: In ODI, Knowledge Modules (KMs) act as reusable templates like APIs.
Also, ODI SDK (Java/Groovy) provides programmatic API for automation.

Q: What is load balancing?

A: Load balancing distributes workloads across multiple servers/agents to avoid bottlenecks and optimize performance.

Q: What is data quality control?

A: Data Quality Control ensures data correctness, completeness, consistency.
- Implemented via ODI CKM (Check Knowledge Module).
- Example: Constraint checks, duplicate checks, referential integrity.

Q: How to load valid record in one table and invalid record in another table?

A: Use CKM for constraint validation.
- Mapping flow → valid rows to Target_Valid, invalid rows (errors) to Target_Error.
- Can also implement filter conditions in mapping.

Q: Suppose we have 3 sources (API, JSON, CSV) and want to load into a single DB table. What is the approach?

A: Steps:
- Extract API → staging table.
- Load JSON using ODI JSON driver → staging.
- Load CSV via File technology → staging.
- Union all staging data → final target table.

Q: What are the views?

A: Views are logical representations of queries stored in DB.
- Types: Simple, Complex, Materialized.
- Used for abstraction, security, performance (materialized).

Q: What is contextual agent in ODI?

A: A logical agent linked with context.
- ODI resolves to physical agent based on execution context (Dev/Test/Prod).
- Useful for portability of scenarios.

Q: What are the different types of facts?

A: Types:
- Additive facts (summable across all dimensions).
- Semi-additive (summable across some dimensions).
- Non-additive (not summable, e.g., ratios).
- Derived/Calculated facts.

Q: What are the different types of dimensions?

A: Types:
- Conformed, Junk, Degenerate, Role-playing, Slowly Changing Dimension (SCD), Static, Time.

Q: What are the different ways of prompting?

A: Ways include:
- ODI Variables (prompt at runtime).
- User input parameters in Load Plans.
- BI/Reporting tool prompts.

Q: What is SCD?

A: Slowly Changing Dimension — handles changes in dimension attributes.
- Type 1: Overwrite.
- Type 2: History tracking with versioning.
- Type 3: Limited history with previous/current fields.

Q: How to load multiple files into a DB table?

A: Methods:
- ODI File technology with wildcards.
- ODI procedure using UTL_FILE/OS command to iterate files.
- Use staging and append to target.

Q: How to handle incremental data load?

A: Methods:
- ODI CDC (Change Data Capture).
- Using LAST_UPDATE_DATE column.
- Using ODI variables to capture max date/key.

Q: What about lookups and joins?

A: In ODI mappings:
- Lookups are defined as lookup components for reference data.
- Joins can be inner/outer, done via mapping join components.
- Pushdown optimization executes joins at source DB.

Q: How to load data from XML to relational DB table?

A: Use ODI XML technology:
- Define XML model via XSD.
- Reverse engineer.
- Map XML elements to target relational table.
- Load via XML driver/KM.

Q: What are different types of Oracle Data Warehouses and their architecture?

A: Types:
- Enterprise Data Warehouse (EDW).
- Data Mart (department-level).
- Operational Data Store (ODS).
- Architecture: Source → Staging → DW (fact/dim) → Data Marts → BI Layer.

Q: What are partition and its types?

A: Partitioning splits large tables for performance.
Types:
- Range, List, Hash, Composite (Range-Hash, Range-List).
- Interval (auto range).
- Reference (child follows parent partition).
- System, Virtual column.

Q: Can you tell me the cursor?

A: A cursor is a pointer to the result set of a query in PL/SQL.
- Types: Implicit, Explicit, Cursor FOR loop, Parameterized.
- Attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
- Used to fetch/process rows one by one in PL/SQL.

Click here for detailed answers

More Question and Answers