Oracle Practice Test - 10

Materialized views are ideal for DSS applications as they pre-compute and store aggregate results, dramatically improving query performance for complex, multi-table joins and grouping operations. The data is already summarized and stored physically, so queries run against the pre-aggregated data rather than recomputing each time. IOTs and clusters are better suited for OLTP workloads with high single-row access patterns.

Oracle's initialization parameters control critical database behaviors including memory allocation (SGA/PGA), optimizer modes, parallel execution settings, I/O subsystem configuration, and many other performance-related aspects. Proper tuning of these parameters is a fundamental part of database performance optimization and can significantly impact throughput and response times.

TRIM with the 'W' FROM clause removes the leading 'W' character, leaving 'ELCOME'. The LOWER function then converts the remaining string to lowercase, producing 'elcome'. Option A uses SUBSTR incorrectly and doesn't achieve the transformation, options B and C have syntax errors in their function calls, and option D has incorrect syntax.

In a LOCALLY MANAGED tablespace with UNIFORM SIZE specification, each extent allocated within the tablespace will be exactly the uniform size specified (1 MB in this example). Segments are composed of multiple extents, Oracle blocks are smaller logical units within extents, and operating system blocks are the physical I/O units. The UNIFORM SIZE clause directly controls extent size allocation.

In Oracle PL/SQL, functions use the IS or AS keyword to begin the declaration section, not DECLARE. The DECLARE keyword is used only in anonymous PL/SQL blocks, not in named program units like functions or procedures. The correct syntax for a function uses IS/AS after the parameter list to begin the declaration section.

Option A correctly uses SELECT DISTINCT to retrieve unique combinations of position and manager values. The DISTINCT keyword applies to the entire row combination, not just the column immediately following it. Option B incorrectly places DISTINCT after only one column name, option C returns all rows including duplicates, and option D has invalid syntax with DISTINCT placed incorrectly.

The correct answer is B because Oracle blocks are the smallest unit of logical storage allocation, while data files are physical storage structures. The logical hierarchy flows from tablespaces (largest) down to Oracle blocks (smallest): tablespaces contain segments, segments contain extents, and extents contain Oracle blocks. Option A incorrectly includes data files which are physical, not logical. Options C and D incorrectly include 'database' as a component within the hierarchy.

The correct answer is D because indexes are created using the CREATE INDEX command, not ALTER TABLE. Options A, B, and C are all true statements about indexes: they can enforce uniqueness (A), enable fast retrieval by column values (B), and columns frequently used in WHERE clauses are indeed good candidates for indexing (C).

The correct answer is D because the UPDATE GLOBAL INDEXES clause maintains global indexes during partition DDL operations, allowing concurrent DML operations to continue using them without interruption. Options A and B are incorrect because the clause doesn't automatically rebuild indexes - it maintains them. Option C is incorrect because global indexes are updated, not left completely unchanged.

The correct answer is C because control files contain the physical structure of an Oracle database, including the database name, names and locations of data files and redo log files, and the current log sequence number. Archive log files (A) store redo entries, initialization files (B) contain parameter settings, and SGA definition files (D) are not a standard Oracle file type.

The correct answer is C because online control file backups can only be performed when the database is in ARCHIVELOG mode. This mode is necessary to ensure that all redo logs are archived before the control file backup occurs. Options A, B, and D are not valid database operating modes for this purpose.

The correct answer is D because in SQL, NULL is not a value but a marker for unknown/missing data. Any comparison operation involving NULL returns NULL (unknown), not TRUE or FALSE. Therefore, comparing two NULL values returns NULL, not TRUE, FALSE, or undefined. This is fundamental three-valued logic in SQL.

The correct answer is C because REPORT OBSOLETE is the correct RMAN command to list backups that are no longer needed based on the retention policy. Option A (LIST NEED BACKUP) is not a valid RMAN command. Option B (REPORT EXPIRED BACKUPS) refers to backups that have exceeded their retention period, not those that are simply no longer needed. Option D (LIST OBSOLETE BACKUPS) is not the correct syntax.

The correct answer is C because Oracle Connection Manager provides connection pooling and multiplexing capabilities that allow hundreds of users to efficiently share network connections to the Oracle Server. Dedicated Server (A) assigns one server process per user, which doesn't scale well. Directory Naming (B) is for name resolution, not connection management. Heterogeneous Services (D) is for accessing non-Oracle data sources.

The correct answer is D because LOG_ARCHIVE_START is the initialization parameter that enables automatic archiving when set to TRUE. Option A is incorrect because ALTER DATABASE SET LOG_ARCHIVE_START is not valid syntax. Option B is incorrect because automatic archiving is not the default. Option C is incorrect because LOG_ARCHIVE_MAX_PROCESSES controls the number of archiver processes, not whether archiving is enabled.

Create a default temporary tablespace

The correct answer is C because ASM disk groups provide redundancy through mirroring - they can maintain multiple copies of data (normal/high redundancy) to protect against disk failures. Option A is incorrect because striping is for performance, not redundancy. Option B is incorrect because mirroring provides redundancy, not performance. Option D is incorrect because striping improves performance, not redundancy.

The correct answer is A because the expression evaluates step-by-step: MOD(1600,10) returns 0, TRUNC(0,-1) truncates to -1 decimal place giving 0, and ROUND(0,2) rounds to 2 decimal places still giving 0. Option B (1) is incorrect because the mod is 0, not 1600. Option C (0.00) is incorrect because ROUND(0,2) returns 0, not 0.00. Option D is incorrect because the syntax is valid.

The correct answer is A because the control file is automatically updated to reflect the new data file location when ALTER DATABASE RENAME FILE is executed. The control file maintains the database's physical structure. Options B (Alert log), C (Registry), and D (Parameter file) are not automatically updated by this operation.

When a non-base table item is renamed and changed to display a database column, you must explicitly set its Database Item property to Yes. This tells Forms that the item should map to a database column during query operations. The name case (uppercase STATUS) is not required for proper mapping.

DBA_INDEXES is the data dictionary view that contains both index names (INDEX_NAME column) and index types (INDEX_TYPE or TYPE column). DBA_IND_COLUMNS only contains column-level information about indexes. DBA_USERS contains user information, not index details.