Oracle 1Z0-873 Online Practice Questions and Exam Preparation

Oracle 1Z0-873 Online Questions & Answers

• Question 21:

  Assume you created a backup of the world database by the following statement.

  shell> mysqldump --opt world > dump.sql

  How can you import the data from this dumped file into the test database?

  A. shell> mysql test < dump.sql
  B. shell> mysqladmin recover test dump.sql
  C. mysql> USE test;mysql> SOURCE dump.sql;
  D. mysql> RECOVER test dump.sql;
  A. shell> mysql test < dump.sql
  C. mysql> USE test;mysql> SOURCE dump.sql;

  ---

  32.8.1. Reloading mysqldump Output

  To reload an SQL-format dump file produced by mysqldump, process it with mysql. For example, you might have made a copy of the Country table in the world database with this command:

  shell> mysqldump world Country > dump.sql To reload the file later, use mysql: shell> mysql world < dump.sql

  2.5. Using Script Files with mysql

  One way to process a script file is by executing it with a SOURCE command from within mysql:

  mysql> SOURCE input_file;

• Question 22:

  Which of the following best describes the processing model for how the MySQL server handles queries?

  A. The server uses a one-tier processing model in which each storage engine optimizes and processes each query issued against it.
  B. The server uses a two-tier processing model: a SQL/optimizer tier and a storage engine tier.
  C. The server uses a three-tier processing model: a SQL/optimizer tier, a formatting tier and a storage engine tier.
  B. The server uses a two-tier processing model: a SQL/optimizer tier and a storage engine tier.

  ---

  23.3. The SQL Parser and Storage Engine Tiers The upper tier includes the SQL parser and optimizer. The lower tier comprises a set of storage engines.

• Question 23:

  Which of the following list the difference between mysqld, mysqld_safe and mysql.server respectively?

  A. mysqld is the server; mysqld_safe is a shell script that invokes mysqld; mysql.server is a wrapper for mysqld_safe.
  B. mysqld is a shell script that starts mysql.server; mysqld_safe is a shell script that invokes the server in safe mode; mysql.server is the server.
  C. mysqld is the server; mysqld_safe is a shell script that invokes mysqld in safe mode, and mysql.server is a wrapper for mysqld_safe.
  D. Nothing. They are symbloic links to the same file.
  A. mysqld is the server; mysqld_safe is a shell script that invokes mysqld; mysql.server is a wrapper for mysqld_safe.

  ---

  24.3.2. Choosing a Server Startup Method on Unix You can invoke mysqld manually. This is usually not done except for debugging purposes. If you invoke the server this way, error messages go to the terminal by default rather than to the error log. mysqld_safe is a shell script that invokes mysqld. The script sets up the error log, and then launches mysqld and monitors it. If mysqld terminates abnormally, mysqld_safe restarts it. mysql.server is a shell script that invokes mysqld_safe. It's used as a wrapper around mysqld_safe for systems such as Linux and Solaris that use System V run-level directories. Typically, this script is renamed to mysql when it is installed in a run-level directory.

• Question 24:

  Which of the following correctly describes the differences between explicitly and implicitly set locks?

  A. Implicitly set locks are locks set and released on behalf of the client, by the server.
  B. Explicitly set locks are locks set and released on behalf of the client, by the server.
  C. Implicitly set locks are locks acquired and released by the client.
  D. Explicitly set locks are locks acquired and released by the client.
  A. Implicitly set locks are locks set and released on behalf of the client, by the server.
  D. Explicitly set locks are locks acquired and released by the client.

  ---

  28.1. Locking Concepts

  For a client that does nothing special to acquire locks, the MySQL server implicitly acquires locks as necessary to process the client's statements safely.

  If implicit locking is insufficient for a client's purposes, it can manage locks explicitly byacquiring them with LOCK TABLES and releasing them with UNLOCK TABLES. Explicit locking may be necessary when a client needs to perform an

  operation that spans multiple statements and that must not be interrupted by other clients.

• Question 25:

  When working with InnoDB, which of the following is true when trying to create foreign key references?

  A. Only the child column must be indexed.
  B. Only the parent column must be indexed.
  C. Both the parent and child columns must be indexed.
  D. InnoDB will not create an index automatically.
  E. InnoDB may create an index automatically.
  C. Both the parent and child columns must be indexed.
  E. InnoDB may create an index automatically.

  ---

  29.4.6. Using Foreign Keys

  In a foreign key relationship, the referring column and the referenced column should have the same data type, and both must be indexed. (If the referring column has no index, InnoDB creates an index on it automatically.)

• Question 26:

  When backing up a replication slave, which of the following replication files should also be backed up?

  A. The master.info file
  B. Any relay logs
  C. The relay index
  D. The world db file
  E. The replication world file
  F. None of the above
  A. The master.info file
  B. Any relay logs

  ---

  32.5. Backing Up Log and Status Files

  Replication slave servers create a master.info file that contains information needed for connecting to the master server, and a relay-log.info file that indicates the current progress in processing the relay logs.

• Question 27:

  After a server crash, some InnoDB tables need to be repaired. However, when the server is restarted, auto-recover failed. How would you repair the InnoDB tables?

  A. Restart the server with the --innodb_force_recovery option.
  B. Execute the REPAIR TABLE command.
  C. Execute the OPTIMIZE TABLE command.
  A. Restart the server with the --innodb_force_recovery option.

  ---

  30.4. Repairing InnoDB Tables In rare cases, the server might not start up due to failure of InnoDB auto-recovery. If that happens, use the following procedure: Restart the server with the --innodb_force_recovery option set to a value in the range from 1 to 6. These values indicate increasing levels of caution in avoiding a crash, and increasing levels of tolerance for possible inconsistency in the recovered tables. A good value to start with is 4. (...)

• Question 28:

  What are some advantages of using the SHOW command rather than using the INFORMATION_SCHEMA?

  A. It is available for releases older than MySQL 5.0.
  B. It returns results quicker than using the INFORMATION_SCHEMA.
  C. Using SHOW can provide more concise information.
  D. SHOW is a feature of standard SQL, and INFORMATION_SCHEMA is a MySQL specific command.
  A. It is available for releases older than MySQL 5.0.
  C. Using SHOW can provide more concise information.

  ---

  20.1. Overview of Metadata Access Methods

  SHOW and mysqlshow have been available since very early releases of MySQL. As of MySQL 5, metadata access is enhanced through two additions:

  The INFORMATION_SCHEMA database is implemented. This provides better compliance with standard SQL because INFORMATION_SCHEMA is standard, not a MySQL-specific extension like SHOW. SHOW statement syntax is extended

  to support a WHERE clause for describing which rows to display. (Some SHOW statements support a LIKE clause for applying a pattern match to the rows of the result, but WHERE is more flexible.)

• Question 29:

  which of the following are some general capabilities of the mysqladmin client program?

  A. Ping the server
  B. Shutdown the server.
  C. Create users.
  D. Create and drop databases.
  E. Dump the contents of a table.
  F. Set passwords.
  A. Ping the server
  B. Shutdown the server.
  D. Create and drop databases.
  F. Set passwords.

  ---

  25.4. mysqladmin

  The mysqladmin command-line client is designed specifically for administrative operations. Its capabilities include those in the following list:

• Question 30:

  You have created a dump using mysqldump with the --extended-insert option. Will you be able to use this dump on any MySQL server instance?

  A. Always
  B. Yes, provided that --max-allowed-packet is set correctly
  C. Yes, provided that binary logging is turned off during the re-reading of the dump.
  D. No, the new server instance must be the same version as the one which created the dump for everything to work
  B. Yes, provided that --max-allowed-packet is set correctly

  ---

  32.8.1. Reloading mysqldump Output If a dump file contains very long INSERT statements, they might exceed the default size of the communications buffer (1MB). You can increase the buffer size for both mysqldump and mysql with the -- max-allowed-packet option. The option value may be given in bytes or followed by K, M, or G to indicate a size in kilobytes, megabytes, or gigabytes. For example, --max-allowed-packet=32M specifies a size of 32MB. The server also must be run with a --max-allowed-packet value that increases its own communications buffer to be large enough.