Oracle 1Z0-873 Online Practice Questions and Exam Preparation

Oracle 1Z0-873 Online Questions & Answers

• Question 41:

  Which of the following statements is true for mysqldump?

  A. It can dump data only on remote servers.
  B. It can dump data only on the local server.
  C. It can dump data on both local and remote servers.
  C. It can dump data on both local and remote servers.

  ---

  32.4.2. Making Text Backups with mysqldump

  mysqldump can back up local or remote servers, although the destination for the dump files depends on how you invoke it.

• Question 42:

  Where is the data stored for a table that is defined as using the FEDERATED Storage Engine?

  A. The data will always be stored on the local host.
  B. The data will always be stored on a remote host.
  C. The data can be stored on any host depending on the definition of the table.
  D. The data will always be stored on disk.
  E. The data will always be stored in memory.
  F. The data will be stored according to the storage engine of the referenced table.
  C. The data can be stored on any host depending on the definition of the table.

  ---

  29.6. The FEDERATED Engine

  "Remote" in the preceding discussion actually is not quite accurate: FEDERATED tables can be defined for accessing tables from other servers running on the same host, or even other tables from the same server.

• Question 43:

  Which of the following are some general capabilites of the mysql client program?

  A. Create and Drop databases.
  B. Ping the server.
  C. Create, Drop, and modify tables and indexes.
  D. Shutdown the server.
  E. Create users.
  F. Display replication server status.
  A. Create and Drop databases.
  C. Create, Drop, and modify tables and indexes.
  E. Create users.
  F. Display replication server status.

  ---

  25.3. mysql

  The mysql program is a general-purpose client that sends SQL statements to the server, and thus can perform any administrative operation that can be expressed using SQL. The following list describes some of its administrative capabilities:

• Question 44:

  Which of the following describes how deadlocks may occur and how InnoDB resolves them?

  A. Deadlocks happen anytime when more than one lock is placed in a table.
  B. Deadlocks happen when two transactions both have locks that the other is waiting for the release of the lock that the other holds.
  C. InnoDB resolves deadlocks by terminating and rolling back one of the deadlocking transactions.
  D. InnoDB resolves deadlocks by terminating and rolling back both of the deadlocking transactions.
  B. Deadlocks happen when two transactions both have locks that the other is waiting for the release of the lock that the other holds.
  C. InnoDB resolves deadlocks by terminating and rolling back one of the deadlocking transactions.

  ---

  29.4.4. InnoDB Locking Characteristics Deadlock can occur. Deadlock is a situation in which each of two transactions is waiting for the release of a lock that the other holds. For example, if two transactions each lock a different row, and then try to modify the row locked by the other, they can deadlock. Deadlock is possible because InnoDB does not acquire locks during a transaction until they are needed. When InnoDB detects a deadlock, it terminates and rolls back one of the deadlocking transactions. It tries to pick the transaction that has modified the smallest number of rows. If InnoDB does not detect deadlock, the deadlocked transactions eventually begin to time out and InnoDB rolls them back as they do.

• Question 45:

  Of the following mechanisms available to connect a MySQL client to a MySQL database server, which types of connections are only available on Windows based systems?

  A. TCP/IP
  B. Sockets
  C. Shared Memory
  D. Named Pipes
  C. Shared Memory
  D. Named Pipes

• Question 46:

  Will the following SELECT query list all of the tables in the INFORMATION_SCHEMA database? If not, why?

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' ORDER BY TABLE_NAME

  A. Yes.
  B. No; the INFORMATION_SCHEMA database does not contain information on itself.
  C. No; the WHERE clause is incorrect. The correct field name is TABLE_SCHEMATA.
  D. No; there is no table in the INFORMATION_SCHEMA database called TABLES.
  A. Yes.

  ---

  20.2. Using INFORMATION_SCHEMA to Obtain Metadata The INFORMATION_SCHEMA database serves as a central repository for database metadata. It is a "virtual database" in the sense that it is not stored on disk anywhere, but it contains tables like any other database, and the contents of its tables can be accessed using SELECT like any other tables. Furthermore, you can use SELECT to obtain information about INFORMATION_SCHEMA itself.

• Question 47:

  Which of following list the default order in which option files are read on UNIX?

  A. ~/.my.cnf, $MYSQL_HOME/my.cnf, and /etc/my.cnf
  B. /etc/my.cnf, $MYSQL_HOME/my.cnf, and ~/.my.cnf
  C. $MYSQL_HOME/my.cnf, /etc/my.cnf, and ~/.my.cnf
  D. ~/.my.cnf, /etc/my.cnf, and $MYSQL_HOME/my.cnf
  E. /etc/my.cnf, ~/.my.cnf, and $MYSQL_HOME/my.cnf
  B. /etc/my.cnf, $MYSQL_HOME/my.cnf, and ~/.my.cnf

  ---

  24.4. Runtime MySQL Configuration

  On Unix, the search order includes two general option files, /etc/my.cnf and $MYSQL_HOME/my.cnf. The second file is used only if the MYSQL_HOME environment variable is set. Typically, you set it to the MySQL installation directory. (The

  mysqld_safe script attempts to set MYSQL_HOME if it is not set before starting the server.)

  The Unix option file search order also includes ~/.my.cnf, that is, the .my.cnf file located in the home directory of the person running the program.

• Question 48:

  When installing a RPM based distribution, the data directory will be set to which of the following locations?

  A. /var/lib/mysql
  B. /usr/mysql/data
  C. /var/mysql/data
  D. /usr/local/mysql/data
  A. /var/lib/mysql

  ---

  The server RPM places data under the /var/lib/mysql directory. http://dev.mysql.com/doc/refman/5.1/en/ linux-installation-rpm.html

• Question 49:

  Which of the following steps should be taken to restore an InnoDB table to a consistent state without having to shutdown the server?

  A. Run the REPAIR TABLE command.
  B. Dump the table with mysqldump, drop the table then re-create from the dump file.
  C. Run the CHECK TABLE command.
  D. Dump the table with mysqlhotcopy, drop the table then re-create with the backup file.
  B. Dump the table with mysqldump, drop the table then re-create from the dump file.

  ---

  30.4. Repairing InnoDB Tables If a table check indicates that an InnoDB table has problems, you should be able to restore the table to a consistent state by dumping it with mysqldump, dropping it, and re-creating it from the dump file: shell> mysqldump db_name table_name > dump_file shell> mysql db_name < dump_file

• Question 50:

  When you set up per-table tablespace files for InnoDB...

  A. Shared tablespace files are not needed.
  B. Occasionally shared tablespace files are still needed.
  C. Shared tablespace files are still needed.
  D. Per-table tablespace files cannot be used with InnoDB.
  C. Shared tablespace files are still needed.

  ---

  InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there.

  The .ibd files are not sufficient for InnoDB to operate. http://dev.mysql.com/doc/refman/5.5/en/innodb- multiple-tablespaces.html