Database Management Systems

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.

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.

Snapshot replication is ideal for this scenario because it distributes a complete copy of the publication at scheduled intervals (daily 5 a.m.). The publication is not large, and while there are many changes during the day, snapshot replication completely refreshes subscriber data each time. Transactional and merge replication are more complex and unnecessary for this daily refresh requirement.

SQL Server provides multiple tools for data import and export: BULK INSERT is a T-SQL command for bulk data loading, BCP (Bulk Copy Program) is a command-line utility, and DTS (Data Transformation Services) is a graphical tool for data transfer and transformation. All three are valid options.

The generic two-level data warehouse architecture consists of a central data warehouse that extracts and consolidates data from multiple internal and external sources. Data marts (A) are derived from the warehouse, not part of the core architecture definition, and near real-time updates (C) are not a required feature of the basic architecture.

When a user creates an object in Oracle without specifying a TABLESPACE clause, Oracle stores the segment in that user's default tablespace. This is a user-specific default, not the system tablespace. Each user can be assigned a specific default tablespace for their objects.

DBA_SYS_PRIVS provides information about all system privileges granted in the database. The other options are more specific: DBA_COL_PRIVS for column-level privileges, DBA_OBJ_PRIVS for object privileges, and DBA_TAB_PRIVS for table privileges. System privileges are the broadest category of grants.