Database Administration and Concepts

Hash partitioning is a standard table partitioning method where data is distributed across partitions using a hash function on partition key values. Key-based is not a standard partitioning method name. Freelist relates to space management within segments, not partitioning. Index is a separate access structure, not a partitioning method. Common partitioning methods include range, list, hash, and composite.

DB_CREATE_FILE_DEST is the primary parameter used to enable Oracle Managed Files (OMF) by specifying the default directory for datafiles. Other parameters like USER_DUMP_DEST and LOG_ARCHIVE_DEST serve administrative purposes and do not initialize OMF for database files.

In database mirroring, the mirror database remains in a restoring/standby state and cannot be directly read or written to by users, representing idle capacity. Identical hardware is not strictly mandatory, automatic failover is supported, and a witness server is optional.

The Database Maintenance Plan Wizard is specifically designed to help DBAs create comprehensive maintenance plans including backup strategies. Database Engine Tuning Advisor is for performance tuning, SQL Server Configuration Manager is for server configuration, and the design surface requires more advanced knowledge.

The Read Uncommitted isolation level allows dirty reads because it doesn't lock shared data and can read uncommitted modifications from other transactions. Since the requirement is to avoid dirty reads while accepting reduced accuracy, Read Uncommitted is the isolation level to avoid.

Activity Monitor in SQL Server Management Studio provides a graphical interface to monitor and manage running processes, including the ability to kill blocking processes. System Monitor, SQL Profiler, and SSRS don't have direct 'Kill Process' functionality for blocking processes.

Navigational database languages require programmers to write code that manually navigates data relationships, which demands precise knowledge of physical tables and index paths. In contrast, declarative languages like SQL let users specify what data they want without knowing the underlying structure.

The sys.dm_exec_query_stats DMV returns performance statistics for cached query plans, including a 'compilation_count' column that tracks how many times each query has been compiled. This allows identifying queries with the highest recompilation counts. The other DMVs focus on current requests, active sessions, and optimizer information respectively.

Data warehouses are designed around major subject areas (like customers, sales, products) rather than business applications. They contain historical data in a consistent format and are read-optimized for analysis. They are not updated by end users, use consistent naming conventions, and contain historical data rather than just current data.

Operational systems support day-to-day business operations in real time using current data. They capture and process transactions as they occur. Decision support systems (analytical systems) use historical data for analysis. The key distinction is operational = real-time current data, analytical = historical data for decision making.

Sync points (syncpoints) in CICS transactions occur at programmer-defined locations using commands like SYNCPOINT. They allow explicit control over transaction boundaries rather than being fixed at the beginning or end. They don't automatically occur after each READ FOR UPDATE.