OCP Database Architecture & Configuration 3 — Questions and Answers
Question 1: Which Oracle memory structure caches the parsed representations of SQL statements and PL/SQL code?
- Database buffer cache
- Large pool
- Library cache (within the shared pool) (Correct answer)
- Java pool
Correct answer: Library cache (within the shared pool)
The library cache, a component of the shared pool, stores parsed SQL cursors and execution plans to enable soft parses.
Question 2: When using Automatic Memory Management (AMM), which two parameters must be set and which must be zeroed out?
- Set MEMORY_TARGET and MEMORY_MAX_TARGET; set SGA_TARGET and PGA_AGGREGATE_TARGET to 0 (Correct answer)
- Set SGA_TARGET; set MEMORY_TARGET to 0
- Set PGA_AGGREGATE_TARGET; set MEMORY_MAX_TARGET to 0
- Set DB_CACHE_SIZE; set SGA_TARGET to 0
Correct answer: Set MEMORY_TARGET and MEMORY_MAX_TARGET; set SGA_TARGET and PGA_AGGREGATE_TARGET to 0
AMM requires MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to be set while SGA_TARGET and PGA_AGGREGATE_TARGET must be 0 to allow full automatic management.
Question 3: What is the role of the SMON background process in Oracle Database?
- Monitors server processes and cleans up failed processes
- Performs instance recovery at startup and coalesces free space in tablespaces (Correct answer)
- Writes redo records to online redo logs
- Manages the dynamic registration of services
Correct answer: Performs instance recovery at startup and coalesces free space in tablespaces
SMON performs crash/instance recovery during startup and reclaims temporary segments and coalesces free dictionary-managed tablespace extents.
Question 4: Which type of Oracle tablespace is used to store temporary segments for operations like sorting and hashing?
- UNDO tablespace
- SYSTEM tablespace
- TEMP tablespace (Correct answer)
- SYSAUX tablespace
Correct answer: TEMP tablespace
A TEMP (temporary) tablespace stores transient work segments for SQL operations that exceed PGA memory, such as large sorts.
Question 5: In Oracle, what is a 'segment' in storage architecture terms?
- A set of contiguous OS blocks
- A database object that consumes storage (e.g., a table, index, or undo segment) (Correct answer)
- A portion of the shared pool
- A single Oracle block
Correct answer: A database object that consumes storage (e.g., a table, index, or undo segment)
A segment is any database object that occupies physical storage, such as a table segment, index segment, or rollback segment.
Question 6: What does the DB_BLOCK_SIZE parameter define, and when must it be set?
- Size of the redo log buffer; set at any time
- Size of the standard Oracle database block; set at database creation and cannot be changed (Correct answer)
- Size of the shared pool; set during instance startup
- Size of the PGA; set when a session begins
Correct answer: Size of the standard Oracle database block; set at database creation and cannot be changed
DB_BLOCK_SIZE defines the standard block size for the database and is fixed at creation time — changing it requires recreating the database.
Question 7: Which Oracle file contains the database name, DBID, and locations of all datafiles and redo logs?
- Password file
- Parameter file (SPFILE)
- Control file (Correct answer)
- Alert log
Correct answer: Control file
The control file stores critical database structural information including the database name, DBID, datafile/redo log locations, and checkpoint information.
OCP Database Architecture & Configuration 3
Which Oracle memory structure caches the parsed representations of SQL statements and PL/SQL code?