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