Oracle Practice Test - 14

The System Global Area (SGA) is allocated when the Oracle instance starts up, not when the database is created or mounted. The instance consists of memory structures (SGA) and background processes. Database creation creates physical files, mounting reads the control file, but neither allocates the SGA. User processes connect to an already-running instance with an existing SGA.

Oracle Forms supports three types of record groups: query record groups (populated by SQL queries), non-query record groups (created programmatically), and static record groups (created at design time). Option D correctly lists all three types. Each type serves different purposes - query for database data, non-query for dynamic data, static for fixed values.

A single Oracle Net listener can listen for multiple protocols simultaneously. The listener configuration file (listener.ora) can specify multiple protocol addresses, allowing the listener to accept connection requests for different protocols like TCP/IP, IPC, and others. This provides flexibility in client connectivity configurations.

LGWR waits when redo log groups are not available due to incomplete checkpoints or archiving. Adding more redo log groups ensures that at least one group is always available for LGWR to write to, preventing these waits. Option B (increasing log buffer) doesn't address log group availability. Option C (decreasing buffer) would worsen performance. Option D (decreasing groups) would make the problem worse by reducing availability.

CREATE USER  Joy
IDENTIFIED BY  Joy18;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO Joy

The five server stack components in a direct client-server connection are: Oracle database (data storage), TTC (Two Task Common layer for data representation), Oracle Net (network communication layer), OPS (Oracle Program Interface for OCI calls), and Protocol (network protocol like TCP/IP). Options B, C, and D have incorrect component orders or missing components.

For free navigation between windows without restriction, both must be document and modeless. Document windows can contain multiple data blocks and modeless windows don't block interaction with other windows. Dialog windows are modal by definition, preventing free navigation. Modal windows require user response before allowing navigation elsewhere.

The CONTENT parameter controls what to unload: DATA_ONLY unloads only table data without metadata, METADATA_ONLY unloads only DDL, and ALL (default) unloads both. Options A (EXCLUDE) and B (INCLUDE) filter specific objects within whatever CONTENT is specified. Option D (DATA_ONLY) is not a valid parameter name - the correct parameter is CONTENT with value DATA_ONLY.

Option D is correct because UPDATE statements use comma-separated column assignments in the SET clause. Options A and B incorrectly use multiple SET keywords. Option C incorrectly uses AND instead of a comma - AND is a logical operator for WHERE clauses, not for separating column assignments in SET. The correct syntax is SET column1 = value1, column2 = value2.

When a tablespace is made read-only with ALTER TABLESPACE, Oracle performs a checkpoint and updates the control file to mark the data files as read-only for recovery purposes. This prevents the database from attempting to apply redo logs to these files during recovery. The checkpoint numbers in the data file headers are frozen at this point, so they don't need to stay synchronized with the control file's checkpoint number.

In Oracle Multithreaded Server (MTS) architecture, also called Shared Server, dispatcher processes route multiple user sessions to a pool of shared server processes. The server processes themselves are shared among users, while client processes and sessions remain distinct. This allows many users to be served with fewer server processes, reducing memory usage.

Performance tuning always starts with monitoring to identify bottlenecks. Without measurement, tuning efforts are wasted on areas that aren't limiting performance. The proper sequence is: monitor first, then tune the design if needed, then database I/O patterns, and finally OS-level parameters as a last resort.

Oracle's logical storage hierarchy builds from smallest to largest: data blocks are the smallest unit (typically 2-32KB), extents are collections of contiguous data blocks, and segments are collections of extents that store a specific object type. Therefore the correct sequence is: data block → extent → segment.

SORT_AREA_SIZE determines how much memory is allocated for each sort operation. Increasing this parameter allows more sorts to complete entirely in memory, reducing the need to spill to disk (which is much slower). SORT_AREA_RETAINED_SIZE controls memory retained after sort completion and doesn't affect disk sort frequency.

To share a Record Group across multiple forms in Oracle Forms, use CREATE_GROUP_FROM_QUERY with GLOBAL_SCOPE. This creates the group at the application level rather than form-level scope, making it accessible to Department, Customer, and Orders forms. FORM_SCOPE would restrict it to the creating form, while CREATE_GROUP without a query doesn't populate data.

SQL was originally called SEQUEL (Structured English Query Language), emphasizing that it was designed to be readable like English. The 'E' stands for English. When the name was shortened to SQL due to trademark issues, the pronunciation 'sequel' persisted in common usage alongside the letter-by-letter 'S-Q-L'.

The SQL-92 standard explicitly prohibits ORDER BY in view definitions because views represent unordered sets. However, Oracle extends this by allowing ORDER BY in conjunction with top-N queries or specific restrictions. Option A correctly states that Oracle views cannot use ORDER BY in standard view definitions without special syntax.

The storage hierarchy from largest to smallest is: tablespace (logical container for segments), segment (collection of extents storing an object like a table or index), extent (contiguous data blocks), and data block (smallest unit of I/O, typically 2-32KB). This hierarchy organizes physical storage into manageable logical units.

MTTR (Mean Time To Recovery) measures how quickly the system can be restored after a failure. When management wants to minimize downtime, MTTR is the business requirement to optimize. MTBF (Mean Time Between Failures) measures reliability/frequency of failures, not recovery speed. RMAN is a tool, not a business requirement.