Oracle Practice Test - 3

In Oracle Shared Server (formerly MTS), client processes connect to dispatchers, which then route requests to shared server processes. The listener initially hands off the connection to a dispatcher, not directly to a shared server. The service is a logical entity registered with the listener. This architecture allows many clients to share fewer server processes, reducing memory overhead.

The DBWR (Database Writer) process writes dirty buffers to datafiles, uses LRU algorithm for buffer management, and can defer writes for I/O optimization. Writing to redo log files is the responsibility of LGWR (Log Writer), not DBWR. LGWR writes redo entries as users COMMIT data. This is a fundamental separation of duties in Oracle's process architecture.

When roles are granted to other roles, access cascades through the role hierarchy. HR is granted DIRECTOR, which is granted MANAGER, which is granted CLERK. Therefore HR has access to all three roles: DIRECTOR (directly), MANAGER (via DIRECTOR), and CLERK (via MANAGER). This hierarchical role granting is a key feature of Oracle's role-based access control.

When creating a user, Oracle allows specifying default tablespace, profile, and default roles. The idle_time parameter is a resource limit that belongs in a PROFILE, not a direct user creation parameter. You must create or modify a profile with idle_time settings, then assign that profile to the user. Resource limits are profile-level settings, not user-level.

CHAR is a fixed-length data type that pads values with space characters to match the declared length. VARCHAR and VARCHAR2 are variable-length and don't pad. CHAR2 doesn't exist in Oracle. This padding behavior is important for string comparison and storage considerations - 'abc' in CHAR(10) becomes 'abc ' (seven spaces added).

Materialized views can be set to continuously refresh even while the database is open and active through the ON COMMIT refresh option. Dynamic performance views (V$ views) are updated continuously as well, but the question specifies 'views' in the context of stored data objects. 'Oracle view' is not a standard category name. Option A is correct as materialized views are designed to maintain pre-computed results with ongoing refresh capabilities.

When data files are lost but control files are intact, the database starts in NOMOUNT state to read control files, then RESTORE DATABASE recovers the data files from backup, RECOVER DATABASE applies archived and online redo logs, and ALTER DATABASE OPEN opens normally without RESETLOGS since no control file creation occurred. Option B is incorrect because NOMOUNT is required when instance is down. Option C and D incorrectly use RESETLOGS unnecessarily.

Materialized Views pre-compute and store complex query results, especially aggregations from multiple tables. For a DSS (Decision Support System) reporting query joining 7 tables with GROUP BY operations, a Materialized View eliminates the need to re-execute the expensive join and aggregation on each report run.

A Query Record Group is specifically defined by its association with a query. This is its fundamental characteristic - it's created based on a SQL query and populated with the query's result set. Other record group types exist (static, non-query), but the defining feature of a Query Record Group is this query association.

Control files must be copied when the database is completely shut down. Copying a control file while the database is open results in an inconsistent copy because the control file is being actively updated. When you try to mount using this inconsistent copy, Oracle detects the mismatch and fails the mount operation.

A stacked canvas (overlay canvas) requires defining its display area using Set_View_Property, which sets the viewport size and position. This determines where and how large the overlay appears on the content canvas beneath it. Set_Canvas_Property affects canvas properties, not the viewport display area.

ALTER SYSTEM commands only affect the running instance's memory (SGA). They do not modify the PFILE (text parameter file) on disk. When you restart with the same PFILE, it still contains LOG_ARCHIVE_START=FALSE, so the Archiver remains disabled. To make the change persistent, you need to update the PFILE manually or use SPFILE.

The two LOVs require different sorting - one by event_date (for the Date item) and one by event_name (for the Event_Name item). Since record groups contain the query including the ORDER BY clause, each LOV needs its own record group with the appropriate ORDER BY. They cannot share a record group because the sorting requirements are different.

The shared pool stores the most recently executed SQL statements and the most recently accessed data definitions.