Multi-Database Administration Quiz

MYISAM was indeed the default storage engine in MySQL versions prior to 5.5. From MySQL 5.5 onwards, InnoDB became the default. For the era when this question was written, the answer 'True' is correct. Note that the question uses 'search engine' which is imprecise terminology - it should be 'storage engine'.

mysqldump is the standard MySQL command-line tool for creating backups. It dumps database contents or structures as SQL statements. The 'mysql' command is for executing SQL queries or importing data, not backups. 'mysqlbackup' is a separate commercial tool from Oracle, and 'mydump' doesn't exist as a standard MySQL utility.

MySQL's configuration file on Unix/Linux systems is named 'my.cnf' and is typically located in /etc/. The file contains server settings including port, data directory, buffer sizes, and other operational parameters. Options A and C have incorrect filenames ('my.conf', 'mysq.conf'), while D adds 'mysql' prefix unnecessarily.

SET NOCOUNT ON prevents SQL Server from sending the number of rows affected message to the client for each statement. This reduces network traffic and can improve performance for stored procedures with multiple statements. Options B and D relate to ARITHABORT which controls query behavior on arithmetic overflows, and Option C is the opposite of what's needed.

SET NOCOUNT ON disables the message showing the number of rows affected by T-SQL statements. This is the same question as 154415, testing the same concept. Option A (OFF) would enable the messages, Option B/D relate to XACT_ABORT which controls transaction behavior on errors, not row count messages.

When SET ANSI_NULLS is OFF, the SQL standard behavior is overridden, and comparison operators like = against NULL evaluate to true for rows containing nulls. Thus, column_name = NULL successfully retrieves rows with null values, instead of returning empty results or throwing errors.

When SET QUOTED_IDENTIFIER is ON, double quotation marks are used to delimit identifiers (such as table or column names), whereas single quotation marks must be used to delimit literal strings. Other options incorrectly describe double quotes for literals or restrict identifier delimiting.

In SQL Server, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON for query execution behavior. The option stating they do not affect each other, are the same, or set it to OFF are incorrect.

Mandatory background processes in Oracle9i include PMON, SMON, DBWR, LGWR, and CKPT. MMON (Memory Monitor) was introduced later in Oracle 10g to support automatic workload repository features.

An Oracle Database can have up to 8 control files. These are multiplexed copies for redundancy. The question asks for the maximum number, and 8 is the correct answer.

ADDM is an Oracle tool that automatically diagnoses database performance problems. The acronym expands to Automatic Database Diagnostic Monitor, making option A correct. Option B incorrectly swaps 'Database' and 'Diagnostic', while option C uses 'Data' instead of 'Database'.

In Oracle databases, multiplexing controlfiles means maintaining multiple copies of the controlfile on different disks. This provides redundancy - if one controlfile is damaged, the database can use another copy. The statement is true, though the question should more completely state what multiplexing means rather than leaving it as an incomplete sentence.

AWR (Automatic Workload Repository) automatically collects performance statistics in Oracle 10g. Statspack is the older statistics collection tool, still available. TKProf is used to format and analyze SQL trace files, providing execution statistics. 'Utlb/Utle' in option D appears to be nonsensical or a typo.

The Pctused parameter in Oracle determines the minimum percentage of a data block that must be used before it becomes available for new inserts. The default value is 40. This storage parameter works with Pctfree to manage space utilization within data blocks.

AWR (Automatic Workload Repository) data is stored in the SYSAUX tablespace in Oracle 10g and later. SYSAUX was introduced specifically to relieve the SYSTEM tablespace of auxiliary data like AWR statistics, making option C correct. SYSTEM is for core dictionary data, USERS for user objects.

When a PRIMARY KEY constraint is defined in Oracle, it automatically creates a unique index to enforce uniqueness. This is default behavior - no manual index creation is required. The index is created automatically and maintained by the database to support the constraint.

SCOTT retains the SELECT privilege because it is still granted via the role r1 which is assigned to him. Revoking the direct SELECT privilege from SCOTT does not affect the privilege inherited through the active role, and it does not alter the role itself.