Oracle Practice Test - 36

USER_SEGMENTS provides segment-level information including initial extent size (from INITIAL_EXTENT column) and can be joined with other views for extent counts. The view contains storage parameters for segments owned by the user. Option A (USER_EXTENTS) shows individual extents but not the initial extent allocation. Option C (USER_OBJECT_SIZE) does not exist as a standard view. Option D combines non-existent and insufficient views.

DBA_INDEXES contains all index information including index names, types (NORMAL, BITMAP, FUNCTION-BASED, etc.), status, and other properties. DBA_IND_COLUMNS shows which columns comprise each index but doesn't contain the index type information. DBA_USERS contains user account information, not index metadata.

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.

ALTER SYSTEM commands only affect the running instance's memory (SGA). They do not modify the PFILE (text parameter file) on disk. When you restart with the same PFILE, it still contains LOG_ARCHIVE_START=FALSE, so the Archiver remains disabled. To make the change persistent, you need to update the PFILE manually or use SPFILE.

The SQL Tuning Advisor is an Oracle tool that analyzes SQL statements and provides recommendations for performance improvement, including automatically collecting missing optimizer statistics when needed. While it can recommend statistics collection (option B), it can also proactively collect them. Option C is incorrect because the advisor heavily relies on optimizer statistics for analysis. Option D is incomplete because the advisor will collect missing statistics rather than work with incomplete data.

Option B is correct because it specifies COPY LEVEL 0 to create a level 0 image copy suitable for incremental backups, allocates a DISK channel (c1), and omits CHECK LOGICAL as requested. Option A includes CHECK LOGICAL, which performs logical block corruption detection - exactly what Mary wants to avoid. Option C is missing the LEVEL 0 specification required for incremental backup compatibility. Option D allocates an sbt_tape channel instead of the required DISK channel.

RMAN (Recovery Manager) requires the SYSDBA privilege to connect to a target database and perform backup and recovery operations. This is because RMAN needs to read and write critical database files, including data files, control files, and redo logs - operations that demand database administrator level privileges. SYSOPER (option B) provides more limited privileges. Option C is incorrect - privileges are mandatory. Option D is false because users other than SYS can connect if granted SYSDBA.

When a PL/SQL procedure has compilation errors, SQLPlus displays a warning message but does not automatically show the errors. The DBA or developer must explicitly execute the 'SHOW ERRORS' command to view the compilation error details, including line numbers and error messages. Option A is incorrect because SQLPlus renumbers lines starting from 1, not matching your editor. Option B is false because errors aren't shown automatically. Option D is incorrect - successful compilation produces no warning message.

Datafiles are the Oracle files that contain actual user data and system data stored in tables, indexes, and other database segments. Control files (option A) contain metadata about the database structure but not the actual data. Online and offline redo log files (options C and D) contain transaction records for recovery purposes, not user or system data themselves. When users insert, update, or query data, they are interacting with data stored in datafiles.

Oracle Connection Manager is specifically designed to handle large numbers of concurrent client connections through connection pooling and multiplexing. It acts as an intermediary that can efficiently manage hundreds or thousands of client sessions to a single server instance. Dedicated Server (option A) would create one server process per user - resource-intensive for hundreds of users. Directory Naming (option B) is for name resolution, not connection scaling. Heterogeneous Services (option D) is for non-Oracle data source integration.

In SQLPlus, a semicolon followed by the Enter key signals that the SQL statement is complete and ready for execution. The semicolon acts as a statement terminator in SQLPlus. Option A is incorrect - a forward slash (/) executes statements in the buffer but doesn't terminate statement entry. Option B's colon is used for bind variables, not statement termination. Option D's period has no special meaning in SQL*Plus statement termination.

In PL/SQL, any variable that is declared but not initialized automatically has a NULL value, regardless of its datatype. This is a fundamental PL/SQL behavior - uninitialized variables are NULL, not 0 or any default value. Option A is incorrect because 0 is not assigned automatically. Option C is wrong because the initial value is always NULL regardless of scale/precision. Option D is false - uninitialized variables can be used, though they will have NULL values which may affect logic.

When OC4J (Oracle Containers for Java) starts, it opens in a separate command console window. This window must remain open because closing it terminates the OC4J instance, which would interrupt the form execution. The window displays OC4J startup and runtime messages. Options A and C are incorrect because OC4J runs independently, not within Forms Builder. Option B is wrong because while it is a separate window, it should NOT be closed.

RMAN determines which archived redo logs to back up by querying the target database's control file, which contains a complete record of all archived logs. When a recovery catalog is used, RMAN can also access that repository for this information. RMAN doesn't use text files (option A), require manual specification (option B), or simply backup everything in ARCHIVE_LOG_DEST without checking (option D) - it intelligently tracks which logs are needed based on the control file's repository.

The FAST_START_PARALLEL_ROLLBACK parameter in Oracle can only be set to FALSE, LOW, or HIGH - not a numeric value. Setting it to 5 is invalid syntax, so Oracle ignores this setting and uses the default behavior. The numeric options A and B are incorrect because numeric values are not valid for this parameter. Option D is incorrect because RECOVERY_PARALLELISM is a separate parameter unrelated to rollback behavior.

ALTER TABLE is the standard SQL command for modifying the structure of an existing table. This includes adding, dropping, or modifying columns, constraints, and other table properties. UPDATE TABLE is used for modifying data within a table, not its structure. MODIFY TABLE and CHANGE TABLE are not valid SQL commands in any major database system.

Password History is a security feature that tracks previously used passwords and prevents users from reusing them within a specified time period or number of password changes. This prevents users from cycling through a few favorite passwords and increases overall password security. Account Locking prevents login attempts after failed attempts. Password Verification refers to validating password strength. Password Expiration forces users to change passwords periodically.

SQLPlus and Oracle database identifiers are case-insensitive by default. This means 'SELECT', 'select', and 'Select' are treated identically, and table/column names like 'Employee' and 'employee' refer to the same object. This case-insensitivity applies unless quoted identifiers are used, in which case they become case-sensitive. The default behavior makes SQLPlus more user-friendly and less prone to typing errors.