Oracle Practice Test - 5

RMAN (Recovery Manager) requires the SYSDBA system privilege to connect to a target database and perform backup and recovery operations. SYSDBA is the highest administrative privilege that allows database startup, shutdown, and complete management. SYSOPER has fewer privileges and cannot perform all RMAN operations.

The control file is a small binary file that contains critical metadata about the database structure, including the names, locations, and status of all datafiles. Oracle reads the control file at startup to locate and open all datafiles. Redo logs contain transaction changes, parameter files have initialization settings, and password files store authentication data.

Sequence values CAN be cached. The CACHE clause in a sequence definition allows Oracle to pre-allocate sequence values in memory, which improves performance by reducing disk I/O. Without caching, each value must be written to disk. The default cache size is 20, and you can specify a larger value or use NOCACHE if needed.

After creating a procedure with compilation errors, SQL*Plus displays a warning but doesn't automatically show the errors. You must explicitly type SHOW ERRORS (or SHO ERR) to see the error messages with line numbers. The line numbers in error messages often don't match text editor line numbers because Oracle counts differently. The message 'NO ERRORS' is never displayed for successful compilation.

A cumulative export backs up only tables that have changed since the last cumulative or complete export. This is different from incremental export (since last incremental export of any type) and complete/full export (all tables regardless of changes). Cumulative provides a middle ground, capturing changes since the last major backup.

In SQLPlus, the hyphen (-) is used to continue a SQL statement on the next line. When you end a line with a hyphen, SQLPlus treats the next line as a continuation of the current statement. Other symbols have different meanings: * is for comments, / executes the SQL buffer, and @ runs a script file.

The correct query filters books purchased before Feb 21, 2002 with price outside the $500-$900 range (less than $500 OR greater than $900). The date condition uses < for 'before'. Results are sorted by purchase_date DESC to show most recent first. Option D has the correct logic with proper parentheses and DESC sorting. Note: there are minor typos but the logic is correct.

FormF can be invoked from FormE using CALL_FORM.

After the listener hands off the dispatcher address, the user process connects directly to that dispatcher. The dispatcher is already running and handles multiple user connections. The dispatcher then places requests in the request queue, and a shared server process picks it up. The user never connects directly to the shared server process in this architecture.

COUNT is the only aggregate function listed that works with all data types (numeric, character, date). AVG, SUM, and STDDEV are mathematical functions that only work with numeric data. COUNT(*) counts rows, while COUNT(expression) counts non-null values regardless of data type.

The query returns no rows. This is because ROWNUM is assigned BEFORE the WHERE clause filter is applied. When you filter with ROWNUM <= 2, Oracle tries to assign row numbers, but the WHERE clause eliminates rows before ROWNUM can be properly assigned. The condition ROWNUM <= 2 never allows any row to be returned. To limit rows, use ROWNUM in a subquery or use FETCH FIRST in modern Oracle.

Row migration occurs when an updated row no longer fits in its original block and must move to another block, leaving a pointer behind. Increasing PCTFREE reserves more space in each block for future updates, allowing rows to grow in place. This prevents the need for rows to migrate to new blocks, maintaining performance and reducing chaining.

When using Oracle replication (advanced replication), you cannot directly modify replicated table structures with DDL commands. You must use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT() procedure to ensure the change propagates correctly to all replication sites while maintaining replication consistency.

ROWNUM is assigned BEFORE the ORDER BY clause executes, so filtering with ROWNUM <= 10 in the same query level returns arbitrary 10 rows. To get the top 10 by marks, you must ORDER BY marks DESC in a subquery first, then apply ROWNUM <= 10 in the outer query. Option D correctly implements this pattern.

Shared Pool

The nested loops join

The ALTER DATABASE BACKUP CONTROL FILE TO 'filename' command creates a binary copy of the control file at the specified location. This is different from the TO TRACE option which generates a SQL script. The binary backup preserves all control file contents in their native format.

An inline view is a subquery that appears in the FROM clause and is treated like a table with an alias. Unlike a named view stored in the data dictionary, an inline view exists only for the duration of the query execution. It allows you to simplify complex queries and join derived result sets.

When an Oracle background process encounters an exception or error, it writes diagnostic information to trace files in the user_dump_dest or background_dump_dest directory. These trace files contain error details, stack traces, and context information useful for troubleshooting database issues.

In Oracle Forms, a radio group itself is just a container - it doesn't appear on the canvas until you add radio button items to it. The radio group holds the data value and logic, but each option must be implemented as a separate radio button item. Creating the radio group in Object Navigator is fine, but you must then create radio buttons for it to display.