OCP Database Architecture & Configuration 3

Question 1

Which Oracle memory structure caches the parsed representations of SQL statements and PL/SQL code?

Accepted Answer

Library cache (within the shared pool)

Answer

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?

Accepted Answer

Set MEMORY_TARGET and MEMORY_MAX_TARGET; set SGA_TARGET and PGA_AGGREGATE_TARGET to 0

Answer

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?

Accepted Answer

Performs instance recovery at startup and coalesces free space in tablespaces

Answer

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?

Accepted Answer

TEMP tablespace

Answer

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?

Accepted Answer

A database object that consumes storage (e.g., a table, index, or undo segment)

Answer

A segment is any database object that occupies physical storage, such as a table segment, index segment, or rollback segment.

(OCP) Oracle Certified Professional Practice Test

(OCP) Oracle Certified Professional Practice Test

OCP Database Architecture & Configuration 3