Oracle Database Administration and Development - Practice Test 8

FAST_START_MTTR_TARGET specifies the mean time to recover (MTTR) in seconds, which directly influences checkpoint frequency. Oracle automatically adjusts checkpoint intervals to meet the specified recovery time goal, affecting the roll forward phase during crash recovery.

The NVL function wraps the EMPNO column, which prevents the database from using the index on EMPNO. When a column is modified by a function in the WHERE clause, the optimizer cannot perform an index range scan on that column because the indexed values don't match the function-transformed values. The other options (B, C, D) all use direct equality comparisons on EMPNO, which can leverage the index.

TRUNCATE is a DDL command that removes all rows from a table while preserving the table structure. Unlike DELETE (which is DML and can be rolled back), TRUNCATE cannot be rolled back and is faster because it doesn't generate redo logs for individual rows. It does not remove the table itself (option A), does not limit rows to 10 (option C), and does not affect column structure (option D).

The DEFAULT profile in Oracle is a special system profile that cannot be dropped. Oracle requires this profile to exist as a fallback for users who are not explicitly assigned other profiles. Even a user with FULL DBA privileges cannot drop it because it's a protected system object. The error message ORA-00950 indicates an invalid DROP option for this reason. Option B is incorrect because DBA privileges already include all necessary permissions. Option C is incorrect because SYS-created objects can generally be dropped unless they're protected like DEFAULT. Option D is incorrect because CASCADE is used for profiles with assigned users, not for DEFAULT itself.

The correct order for creating an RMAN recovery catalog is: 5) Create tablespace first, 4) Create user/schema owner, 1) Grant necessary privileges (CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER), 3) Create the catalog using RMAN, 6) Connect to target database with SYSDBA, 2) Register target database in catalog. This sequence ensures prerequisites are met before dependent steps - the tablespace must exist before creating the user, the user must exist before granting privileges, and privileges must be granted before creating the catalog.

When a database is switched to ARCHIVELOG mode using ALTER DATABASE ARCHIVELOG, this mode change is recorded in the control file. The control file stores critical database information including the database name, checkpoint information, and archiving mode. SPFILE (option A) stores initialization parameters. Archive logs (option B) are the output of archiving, not where the mode is stored. The System tablespace (option D) stores data dictionary objects, not database state information.

The CREATE INDEX syntax shown creates a standard B-tree index, which is Oracle's default index type. B-tree indexes are suitable for high-cardinality columns like last_name where there are many distinct values. Bitmap indexes (option A) are for low-cardinality columns. The syntax shows no partitioning clause (option C) or REVERSE keyword (option D), so it's a regular B-tree index.

Use the UNIFORM clause for allocating new extents and set a very high extent size.

In the trigger code shown, line C ("IS") is on its own line before the variable declarations. This is incorrect syntax because the declaration section cannot begin immediately after "IS" without proper context. The correct order should be the trigger header (lines A-B), then "IS" on the same line or followed by declarations, but not as a standalone line. Lines A and B correctly define the trigger. Line D (variable declaration) and line E (BEGIN) are syntactically correct in their positions.

The DELETE statement fails because it incorrectly lists column names in the DELETE clause. The correct DELETE syntax is DELETE FROM table_name WHERE condition - you don't specify columns to delete in DELETE. The DELETE operation removes entire rows, not specific columns. If the goal was to delete rows from employees where dept_id = 40, the correct syntax would be DELETE FROM employees WHERE dept_id = 40. Option A is irrelevant (wrong dept_id anyway). Option B is incorrect because DELETE doesn't have column-level constraints. Option D is incorrect because deleting rows doesn't affect the primary key definition.

When a replication site contains subsets of another site's data, this is called snapshot replication (also known as materialized view replication). A snapshot site stores and maintains copies of data from a master site, allowing for read-only access and periodic refresh. This is different from master-site replication (option A) which maintains full copies. Option C is incorrect because the question specifically describes a subset scenario, which characterizes snapshot sites, not a combination of both types.

When the Data Length Semantics property is set to BYTE, Oracle measures column length in bytes rather than characters. For multi-byte character sets (like UTF-8 or AL32UTF8), a single character might require multiple bytes. If Maximum Length is set based on character count but BYTE semantics are used, the actual byte capacity may be insufficient for the expected number of characters, requiring manual adjustment. Option A is false because display issues depend on item width, not Maximum Length. Option B is false because Initial Value must respect validation range constraints. Option C is false because Required property at the form level is independent of database constraints.

CALL_FORM is the correct built-in for invoking the Orders form from Customers form because it can be issued while the calling form is in Enter-Query mode, and it can be constrained to query-only mode. This is important when you want to maintain query context in the calling form. CALL_FORM opens the second form in a modal state (not modeless as option C suggests), blocking navigation to the calling form until the called form closes. Option A incorrectly claims NEW_FORM is invalid for web deployment (it works but behaves differently). Option D is incorrect because NEW_FORM replaces the calling form entirely, preventing navigation between both.

The Export/Import direct-path feature extracts data faster than conventional-path export because it bypasses the SQL evaluation layer and reads data directly from disk blocks, formatting it as Oracle export format. This reduces CPU overhead and processing time. Option B is incorrect because Export cannot directly export tablespaces or data files (those are physical storage structures, not logical database objects). Option C is incorrect because FULL export mode only exports objects in schemas that the user has privileges to access, not all objects regardless of schema. Option D is incorrect because transportable tablespaces (which copy data files) are much faster than export/import for moving large volumes of table data.

Clicking Step Over to step over the execution of the subprogram

Top-N queries require sorting data and then limiting results, which needs ORDER BY for sorting, an inline view to perform the sorting, and an outer query to restrict the rows returned. Option A is missing the outer query. Option B incorrectly suggests GROUP BY and lacks the outer query. The standard pattern is SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM <= N.

Oracle instance startup progresses through three main states: NOMOUNT (reads parameter file and starts instance), MOUNT (associates instance with control file and database), and OPEN (makes database available for users). Option D lists all three valid stages. Options A, B, and C include invalid or non-existent commands like STARTUP CLOSE, START MOUNTALL, STARTUP EXIT, and STARTUP START.

The /*+APPEND */ hint directs Oracle to perform a Direct-Load insert, which bypasses the buffer cache and writes data directly to datafiles, significantly improving performance for large inserts. This is different from Conventional insert (uses buffer cache) and Parallel Direct-Load (requires parallel execution). The hint makes Oracle treat the insert as a direct-path operation.

PCTFREE and PCTUSED are storage parameters that control block space utilization and are specified at the segment level (table or index), not at the tablespace level or data dictionary. They determine how much free space to leave in blocks for updates (PCTFREE) and when blocks become available for new inserts (PCTUSED). Option A correctly states this.

Online control file backups require the database to be in ARCHIVELOG mode, which enables continuous archiving of redo logs. In NOARCHIVELOG mode, the database must be shut down for consistent backups. FAILOVER and FASTBACK are not valid database modes. ALTER DATABASE with backup options requires ARCHIVELOG mode for online operation.