Oracle Practice Test - 31

Oracle SCN (System Change Number) is a logical timestamp that increments whenever database changes are made. It's used for data consistency, recovery, and tracking the order of transactions. It is NOT related to dirty reads, deadlocks, or explicit locks.

In Oracle Forms, client-side validation requires setting an Implementation Class property on the item. This allows custom validation logic to execute on the client side without server round-trips. Option B is incorrect as middle-tier configuration may be needed. Option D describes triggers, not the configuration requirement.

Columns appearing in WHERE clauses are prime candidates for indexing because indexes speed up data retrieval. Not every queried column needs an index (indexes have overhead), and not every updated column should be indexed (indexes slow down updates). Data warehouses do use indexes, just different types.

The EMPNO_SEQ.CURRVAL returns the current value of the sequence named EMPNO_SEQ. Sequences in Oracle are database objects that generate unique numeric values. CURRVAL specifically returns the last value obtained from that sequence in the current session.

CURSOR_SPACE_FOR_TIME=TRUE keeps cursors in memory to reduce reparsing. Before setting it, confirm V$LIBRARYCACHE shows RELOADS at or near zero, indicating cursors aren't being reloaded. This prevents wasting memory if the cache is already ineffective.

The Oracle Database Configuration Assistant (DBCA) is a GUI tool that automates database creation with wizards and templates. It handles all complex setup steps including parameter files, password files, and initialization parameters. Manual methods require deep expertise and are error-prone. DBCA is Oracle's recommended approach for most production and development environments.

The NEXT_DAY function returns the first occurrence of a specified day that is strictly later than the input date. Since today is Monday July 10, the NEXT Monday is July 17 (7 days later). The TO_CHAR format 'DD-MON-RR' produces '17-JUL-00'. Note that NEXT_DAY always looks forward, never returning the current day even if it matches the specified weekday.

The PRAGMA EXCEPTION_INIT directive associates a named exception with an Oracle error code but doesn't stop transactions. RAISE_APPLICATION_ERROR is designed to halt execution by raising a user-defined error with a custom message, which automatically rolls back the current transaction unless explicitly handled. This is the standard mechanism for intentional transaction termination in PL/SQL.

The <> (not equal) operator is a single-row comparison operator in SQL, meaning the subquery must return exactly one value. When a subquery returns multiple rows with comparison operators like =, <>, >, <, Oracle raises error ORA-01427 'single-row subquery returns more than one row'. For multiple values, you must use IN, ANY, or ALL operators instead.

The shared pool is a memory structure in the SGA that caches parsed SQL statements, PL/SQL code, and data dictionary definitions. It consists of the library cache and dictionary cache, NOT the buffer cache (which stores actual data blocks). This caching eliminates the need to re-parse frequently executed statements, significantly improving performance.

Creating an Oracle database requires SYSDBA privilege, which is the highest administrative privilege. DBA is a role that grants database administration permissions but does NOT include database creation authority. SYSOPER provides startup/shutdown and basic operations but cannot create databases. RESOURCE is a schema-level role for object creation with no database creation rights.

The control file contains the database's physical structure metadata including all data file names and locations. When you execute ALTER DATABASE RENAME FILE, Oracle automatically updates the control file to reflect the new path. Alert log entries are chronological records, parameter files are manual configs, and Registry stores Oracle software metadata - none auto-update from rename operations.

The server parameter file (SPFILE) is a binary file maintained by the Oracle server, providing persistent storage and automatic synchronization of initialization parameters. While ALTER SYSTEM can update both memory and SPFILE, and changes persist across restarts, option A captures the core advantage - Oracle actively manages the SPFILE, preventing human edit errors and ensuring consistency.

Index segments store sorted copies of indexed columns, allowing Oracle to locate rows without scanning entire tables. This dramatically improves query performance, especially for large tables with selective WHERE clauses. Tables store actual data, temporary segments hold intermediate query results, and bootstrap segments contain internal metadata - none exist primarily for query performance enhancement.

The query selects employees with either a 50% commission rate (commission_pct = 0.5) OR a salary exceeding 23,000. The OR condition means employees meeting EITHER criterion are returned. Option A incorrectly interprets it as a percentage calculation, and Option C misreads the comparison direction. This is a straightforward OR condition combining two independent filters.

ROWID access is the fastest retrieval method because ROWID is the direct physical address of a row in the data file. When Oracle knows the ROWID, it can access the row in a single I/O operation without traversing indexes or scanning tables. Primary key and unique index lookups require index traversal first, then ROWID access. Full table scans read all blocks, which is the slowest method.

The DELAY attribute in LOG_ARCHIVE_DEST_n parameter on the PRIMARY database causes archived logs to be held for the specified minutes before being applied on the standby. Setting 'SERVICE=PRODS DELAY 20' on PROD ensures PRODS waits 20 minutes before applying shipped logs. This prevents corrupted or erroneous data from being immediately propagated to the standby, providing a recovery window.

In PL/SQL, any scalar variable (NUMBER, VARCHAR2, DATE, etc.) that is declared but not initialized automatically gets NULL as its initial value. Option A is incorrect because 0 would need explicit initialization. Option C is wrong because NULL behavior is consistent across scale/precision. Option D is false because uninitialized variables can be used - they'll just be NULL until assigned.

USER_COL_PRIVS 

After creating the recovery catalog tablespace, user, schema, and granting privileges, then creating the catalog with RMAN, the mandatory next step is registering the target database in the catalog. Option A (setting incarnation) happens later if needed. Option B (synchronizing) occurs during registration and subsequent updates. Option D (resetting incarnation) is only required for advanced recovery scenarios like flashback or point-in-time recovery to a different incarnation, not during initial setup.