Oracle 1Z0-873 Online Practice Questions and Exam Preparation

Oracle 1Z0-873 Online Questions & Answers

• Question 11:

  Which of the following actions are performed during an RPM installation of the MySQL server package?

  A. Setup a mysql user
  B. Setup a mysql group
  C. Initialize the data directory
  D. Setup passwords for all default accounts.
  A. Setup a mysql user
  B. Setup a mysql group

  ---

  During RPM installation, a user named mysql and a group named mysql are created on the system. This is done using the useradd, groupadd, and usermod commands. Those commands require appropriate administrative privileges, which is required for locally managed users and groups (as listed in the /etc/ passwdand /etc/group files) by the RPM installation process being run by root. http://dev.mysql.com/doc/ refman/5.1/en/linux-installation-rpm.html

• Question 12:

  Which of the following statements are true with regard to savepoints in transactions?

  A. Within a transaction, you can define multiple savepoints
  B. All savepoints must have unique identifiers
  C. You can rollback to any previous savepoint.
  D. You can rollback to only the latest savepoint.
  E. If you rollback to a previous savepoint, all statements after this savepoint are rolled back and cannot be reissued.
  F. If you rollback to a previous savepoint, you still can replay the statements after this savepoint.
  A. Within a transaction, you can define multiple savepoints
  B. All savepoints must have unique identifiers
  C. You can rollback to any previous savepoint.
  E. If you rollback to a previous savepoint, all statements after this savepoint are rolled back and cannot be reissued.

  ---

  29.4.3. The InnoDB Transaction Model

  If you want to roll back only part of a transaction, you can set a savepoint by using the SAVEPOINT statement:

  SAVEPOINT savepoint_name;

  Multiple savepoints can be set within a transaction.

  The SAVEPOINT statement sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set. The ROLLBACK TO SAVEPOINT

  statement rolls back a transaction to the named savepoint without terminating the transaction.

  Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint.

  http://dev.mysql.com/doc/refman/5.0/en/savepoint.html

• Question 13:

  Which of the following is true of the mysqldump client program?

  A. It can only backup tables from MyISAM.
  B. It can only backup tables from InnoDB.
  C. It can only backup tables from MyISAM and InnoDB.
  D. It can backup tables from any of the available storage engines.
  D. It can backup tables from any of the available storage engines.

  ---

  32.4.2. Making Text Backups with mysqldump It works for tables created by any storage engine.

• Question 14:

  Which of the following are some general properties of the mysqlimport client program?

  A. It loads data files into tables.
  B. It can load files only on the server host.
  C. It provides a command-line interface to the LOAD DATA INFILE.
  D. It can load files both on the client and server host.
  E. It bypasses the server and writes directly to the corresponding data files.
  A. It loads data files into tables.
  C. It provides a command-line interface to the LOAD DATA INFILE.
  D. It can load files both on the client and server host.

  ---

  25.5. mysqlimport The mysqlimport client program loads data files into tables. It provides a command-line interface to the LOAD DATA INFILE statement. That is, mysqlimport examines the options given on the command line. It then connects to the server and, for each input file named in the command, issues a LOAD DATA INFILE statement that loads the file into the appropriate table.

• Question 15:

  Consider the available command line options of mysqlbinlog. Which of the following statements are true?

  A. --start-position will read the binlog entries starting at the specified position
  B. --end-position will read the binlog entries up to the specified position
  C. --start-datetime will read the binlog entries starting at the specified date and time
  D. --end-datetime will read the binlog entries up to the specified date and time
  E. --start-file will read the binlog entries starting with the specified filename
  F. --end-file will read the binlog entries ending after the specified filename
  A. --start-position will read the binlog entries starting at the specified position
  B. --end-position will read the binlog entries up to the specified position
  C. --start-datetime will read the binlog entries starting at the specified date and time
  D. --end-datetime will read the binlog entries up to the specified date and time

  ---

  32.8.3. Processing Binary Log Contents

  To handle partial-file extraction, mysqlbinlog supports options that enable you to specify the time or log position at which to begin extracting log contents:

• Question 16:

  You want to lock the three tables a, b and c, and issue the following statements:

  mysql> LOCK TABLES a READ; mysql> LOCK TABLES b READ; mysql> LOCK TABLES c READ; Which tables are now locked?

  A. Tables a, b and c
  B. Table a only
  C. Table c only
  D. None of the tables are locked
  C. Table c only

  ---

  28.2. Explicit Table Locking If you need to use multiple tables while holding an explicit lock, you must lock all of them at the same time because you cannot use any unlocked tables while you hold explicit locks. Also, you must lock all the tables with a single LOCK TABLES statement. LOCK TABLES releases any locks that you already hold, so you cannot issue it multiple times to acquire multiple locks.

• Question 17:

  Which of the following should be done before upgrading the mysql server to a newer version?

  A. Read and understand the appropriate "Upgrading MySQL" section of the online manual.
  B. Read through the online change notes and understand the changes made between the versions.
  C. Prepare the datadir to be read in by a new version by running the mysqlupgrade --prepare command on it.
  D. Nothing; MySQL data files are binary compatible between all versions and the server will automatically perform any conversions needed.
  A. Read and understand the appropriate "Upgrading MySQL" section of the online manual.
  B. Read through the online change notes and understand the changes made between the versions.

  ---

  24.9. Upgrading MySQL Check the MySQL Reference Manual before performing any upgrade: Always consult the section on upgrading to see whether there are any notes pertaining to the type of upgrade you're performing. If so, follow the recommended procedures described there. Check the change note sections for versions more recent than your current version to make sure that you're aware of what has changed since your original install. Note particularly any changes that are not backward compatible with your current version because they may require modifications to your existing applications if you upgrade.

• Question 18:

  Index analysis and optimization using ANALYZE and OPTIMIZE statements should...

  A. generally never be run manually
  B. be run once the table reaches 100,000 rows or above
  C. be run when more than 5% of the rows are changed by a single statement
  D. be run when EXPLAIN SELECT shows that an inordinate amount of rows is expected to be read during query execution
  E. be run when you suspect that a table is heavily fragmented
  D. be run when EXPLAIN SELECT shows that an inordinate amount of rows is expected to be read during query execution

  ---

  30.2. SQL Statements for Table Maintenance The ANALYZE TABLE statement updates a table with information about the distribution of key values in the table. This information is used by the optimizer to make better choices about query execution plans. This statement works for MyISAM and InnoDB tables. The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaiming unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics.

• Question 19:

  When working with the InnoDB engine, which of the following correctly defines the READ UNCOMMITTED isolation level?

  A. It allows a transaction to only see its uncommitted changes.
  B. It allows a transaction to see committed changes made by other transactions.
  C. It allows a transaction to see uncommitted changes made by other transactions.
  D. It allows a transaction to see both committed/uncommitted changes made by other transactions.
  C. It allows a transaction to see uncommitted changes made by other transactions.

  ---

  29.4.5. InnoDB Isolation Levels, Multi-Versioning, and Concurrency READ UNCOMMITTED allows a transaction to see uncommitted changes made by other transactions. This isolation level allows dirty reads, non-repeatable reads, and phantoms to occur.

• Question 20:

  Which of the following are requirements for MyISAM binary portability?

  A. Both machines must use the same operating system.
  B. Database and table names must use lowercase format.
  C. Both machines must use IEEE floating-point format or contain no floating-point columns.
  D. Both machines must use two's-complement integer arithmetic.
  B. Database and table names must use lowercase format.
  C. Both machines must use IEEE floating-point format or contain no floating-point columns.
  D. Both machines must use two's-complement integer arithmetic.

  ---

  32.3.4. Conditions for Binary Portability

  MyISAM tables and InnoDB tablespaces are binary portable from one host to another if two conditions are met:

  Both machines must use two's-complement integer arithmetic. Both machines must use IEEE floating- point format, or else the tables must contain no floating-point columns (FLOAT or DOUBLE). In practice, those two conditions pose little

  restriction. Two's-complement integer arithmetic and IEEE floatingpoint format are the norm on modern hardware.

  A third condition for InnoDB binary portability is that you should use lowercase names for databases and tables. This is because InnoDB stores these names internally (in its data dictionary) in lowercase on Windows.

  Using lowercase names allows binary portability between Windows and Unix. To force the use of lowercase names, you can put the following lines in an option file:

  [mysqld]

  lower_case_table_names=1