Oracle Database Administration Practice Test

Different tablespace on different disks33

In Oracle's storage hierarchy: a data block is the smallest unit, an EXTENT is a contiguous collection of data blocks, a SEGMENT is a collection of extents (like a table or index), and a TABLESPACE contains segments. Therefore, extents are composed of contiguous database blocks (option B), not operating system blocks, table spaces, or segments.

ROLE privileges in Oracle CAN be dynamically modified using GRANT and REVOKE commands. When you grant or revoke privileges from a role, all users granted that role immediately gain or lose those privileges on their next session. This dynamic behavior is a key advantage of role-based security.

Hash-partitioned global indexes distribute index entries evenly across partitions, which prevents contention when many inserts happen at sequence-generated values. Range partitioning would create hot spots because sequential numbers map to adjacent ranges. Local indexes inherit the partitioning scheme of the table, so they don't solve the skew problem.

ASM (Automatic Storage Management) files are managed by Oracle ASM and cannot be accessed or copied using standard operating system commands. RMAN (Recovery Manager) is Oracle's dedicated tool for backup, restore, and copying ASM files, as it understands the ASM file system structure and can properly handle ASM-specific operations.

EXPLAIN PLAN is the Oracle tool used to analyze and display the execution plan SQL statements will use, helping identify performance bottlenecks like full table scans or inefficient joins. The other options (TUNEUP SYS, DB_TUNEUP) are not standard Oracle tools, and EXPLAIN SYS is not a valid tuning command.

Oracle does allow modifying decimal places in NUMBER columns under certain conditions: you can always increase decimal places, and you can decrease them if existing data fits without rounding errors. The statement claiming you cannot increase OR decrease decimal places is therefore false, making D the correct answer to the 'not true' question.

ORA-12533 indicates illegal ADDRESS parameters in the TNS connect string, meaning the protocol-specific settings (HOST, PORT, PROTOCOL) in the ADDRESS section are incorrect. The service name options (B, D) would produce different errors like ORA-12154 (TNS:could not resolve the connect identifier), while option A is too general.

Partitioned tables divide large datasets into smaller, more manageable pieces that can be queried or modified in parallel across multiple processes, improving performance and scalability. Regular tables (A) don't offer this parallelism, clustered tables (B) group related tables physically, and index-organized tables (D) store data within the index structure but don't inherently enable concurrent operations.

When Oracle detects a deadlock and terminates a session, it writes a trace file to the directory specified by USER_DUMP_DEST, which contains diagnostic information about the deadlock (sessions involved, resources waited on, wait-for graph). SQL_TRACE is a facility, not a destination; TRACE_DEST is not a valid parameter; CORE_DUMP_DEST is for core dumps from crashes.

DDL statements like DROP TABLE include an implicit COMMIT before execution and another COMMIT after execution, meaning the transaction is permanently committed before ROLLBACK can be issued. Once the DROP TABLE executes, the data is gone (unless using Flashback Drop in 10g+). Shutting down the database or terminating the session won't undo a committed DDL operation.

In NOARCHIVELOG mode, you cannot apply redo logs from a restored backup, so any changes after the last backup are lost. The only option is to restore the entire database from the last whole closed backup. You cannot selectively restore individual data files without opening with RESETLOGS, and even then, NOARCHIVELOG mode prevents point-in-time recovery.

A change to the DEFAULT value affects only subsequent insertions to the table.

MAXTRANS limits concurrent transaction access to a data block, while PCTUSED determines when a block returns to the free list for new inserts. Together, they control block contention and space reuse frequency. INITRANS sets the initial transaction slot reservation, and PCTFREE controls reserved space for updates.

SQL stands for Structured Query Language, the standard language for relational database management systems used to query, manipulate, and define data. The other options are fabrications: 'Strong Question Language' and 'Structured Question Language' incorrectly use 'Question' instead of 'Query', while 'Start up Language' has no relation to database terminology.

To add items corresponding to new columns to an existing manually-created data block, you select the block in the Object Navigator and invoke the Data Block Wizard, which can modify the block to include new items. The Layout Wizard only handles visual layout, not data block structure, and you cannot select individual items to invoke the Data Block Wizard.

The LOG_ARCHIVE_START parameter controls automatic archiving at instance startup. Setting it to TRUE in the initialization parameter file enables automatic archiving when the database starts up. Option A is incorrect because ARCHIVE LOG START is a runtime command, not a startup configuration. Option C places the database in ARCHIVELOG mode but does not enable automatic archiving at startup. Option D describes a manual archive command, not automatic startup archiving.

The GROUP BY clause organizes rows into groups based on semester_end, then MAX(gpa) finds the highest GPA within each group. The WHERE clause filters NULL values before grouping occurs. Options A and B are incorrect because they lack GROUP BY. Option D has incorrect syntax with WHERE after GROUP BY. Option E places WHERE after GROUP BY, which is invalid syntax - WHERE must precede GROUP BY.

The DEFAULT profile is a mandatory system profile that cannot be dropped from Oracle database - it must always exist. MILLER has full DBA privileges, so this is not a permission issue (option B is incorrect). Option C is incorrect because SYS can drop most profiles it creates, but DEFAULT is special. Option D is incorrect because CASCADE is used when dropping a profile assigned to users, but DEFAULT cannot be dropped regardless.

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.