Oracle 1Z0-873 Online Practice Questions and Exam Preparation

Oracle 1Z0-873 Online Questions & Answers

• Question 131:

  Which of the following are some general characteristics of the MEMORY storage engine?

  A. Each table is represented on disk as a .frm file.
  B. Each table has a corresponding .MYI and .MYD file.
  C. They can support foreign keys.
  D. They can not contain TEXT or BLOB columns
  E. Table contents will not be saved if the server is restarted.
  F. They can support transactions.
  A. Each table is represented on disk as a .frm file.
  D. They can not contain TEXT or BLOB columns
  E. Table contents will not be saved if the server is restarted.

  ---

  29.5. The MEMORY Engine

  The MEMORY storage engine manages tables that have the following characteristics:

  Each MEMORY table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.

  In-memory storage results in very fast performance.

  MEMORY table contents do not survive a restart of the server. The table structure itself survives, but the table contains zero data rows after a restart.

  MEMORY tables use up memory (obviously), so they should not be used for large tables. MySQL manages query contention for MEMORY tables using table-level locking. Deadlock cannot occur.

  MEMORY tables cannot contain TEXT or BLOB columns.

• Question 132:

  Which of the following statements are true?

  A. InnoDB tables will be automatically recovered after a crash.
  B. MyISAM tables will be automatically recovered after a crash.
  C. InnoDB tables will be recovered after a crash if the innodb-recover option is configured.
  D. MyISAM tables will be recovered after a crash if the myisam-recover option is configured.
  E. InnoDB tables cannot be recovered after a crash, you have to restore data from backup.
  F. MyISAM tables cannot be recovered after a crash, you have to restore data from backup.
  A. InnoDB tables will be automatically recovered after a crash.
  D. MyISAM tables will be recovered after a crash if the myisam-recover option is configured.

  ---

  A: InnoDB's internal recovery mechanism is pretty good. If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. In most cases it will succeed and the entire process is transparent.

  D: Automatic recovery is activated if you start mysqld with the --myisam-recover option. In this case, when the server opens a MyISAM table, it checks whether the table is marked as crashed or whether the open count variable for the table is not 0 and you are running the server with external locking disabled. If either of these conditions is true, the following happens:

  The server checks the table for errors.

  If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).

  If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.

  If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements. References:

• Question 133:

  Suppose you have a server that has been started with the --myisam-recover option. When does the server perform the check on the MyISAM tables?

  A. Each time the server is started.
  B. Each time it encounters an error.
  C. Each time it opens the MyISAM table files.
  D. Each time the CHECK TABLE command is issued.
  C. Each time it opens the MyISAM table files.

  ---

  30.5. Enabling MyISAM Auto-Repair The MySQL server can be instructed to check and repair MyISAM tables automatically. With automatic repair enabled, the server checks each MyISAM table when it opens it to see whether the table was closed properly the last time it was used and is not marked as needing repair. If the table is not okay, the server repairs it. To enable automatic MyISAM table maintenance, start the server with the --myisam-recover option.

• Question 134:

  You are using a multi-byte character set with variable-length encoding. You need to store records whose values are always 20 characters. Which of the following column types would be the most efficient to use in terms of storage space?

  A. CHAR
  B. VARCHAR
  C. The storage requirements for CHAR or VARCHAR would be the same
  B. VARCHAR

  ---

  27.2. Choosing Data Types for Character Columns For multi-byte character sets that have variable-length encoding, a variable-length data type may be appropriate even if stored values always have the same number of characters. The utf8 character set uses one to three bytes per characters. For fixed-length data types, three bytes per character must always be allocated to allow for the possibility that every character will require the "widest" encoding. Thus, CHAR (32) requires 96 bytes, even if most stored values contain 32 single-byte characters. For variable-length data types, only as much storage is allocated as required. In a VARCHAR(32) column, a 32-character string that consists entirely of three-byte characters requires 96 bytes plus a length byte, whereas it requires only 32 bytes plus a length byte if the string consists entirely of single-byte characters.

• Question 135:

  Suppose your are adding rows to a MyISAM table and the server runs out of disk space. What will happen?

  A. The server will crash.
  B. An error message will be returned to the client.
  C. The server suspends that INSERT operation until space becomes available.
  D. The server suspends operations on all MyISAM tables until space becomes available.
  C. The server suspends that INSERT operation until space becomes available.

  ---

  29.2. The MyISAM Engine

  If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

• Question 136:

  Which of the following steps describe how to do a proper binary backup of InnoDB data?

  A. Stop the server prior to the backup
  B. Stop the server or lock the tables prior to the backup
  C. Make a copy of the .frm files.
  D. Make a copy of the .MYD and .MYI files.
  E. Make a copy of the InnoDB log and tablespace files.
  F. Make a copy of any InnoDB configuration options.
  A. Stop the server prior to the backup
  C. Make a copy of the .frm files.
  E. Make a copy of the InnoDB log and tablespace files.
  F. Make a copy of any InnoDB configuration options.

  ---

  32.3.2. Making Binary InnoDB Backups

  To make an InnoDB binary backup, use the following procedure:

  1.

  Stop the server for the duration of the copy operation. The tablespace must not be in use when copying the tablespace files.

  2.

  Make sure that the server shut down without error. Binary InnoDB backups require a clean shutdown to be certain that the server has completed any pending transactions.

  3.

  Make a copy of each of the following components:

  The .frm file for each InnoDB table.

  The tablespace files. This includes the files for the shared tablespace. It also includes the .ibd files if you have configured InnoDB to use per-table tablespaces.

  The InnoDB log files.

  Any InnoDB configuration options, such as those stored in option files. The configuration options are required in case you need to restore the backup from scratch. In that case, you'll need to know how the tablespace and log files were

  created originally.

  4.

  Restart the server.

• Question 137:

  When you acquire an advisory lock using GET_LOCK(), the lock is released if

  A. You issue another GET_LOCK() statement
  B. You issue a RELEASE_LOCK() statement
  C. Your connection to the server terminates
  D. None of the above
  A. You issue another GET_LOCK() statement
  B. You issue a RELEASE_LOCK() statement
  C. Your connection to the server terminates

  ---

  28.3. Advisory Locking

  A client that has acquired an advisory lock can release it by calling RELEASE_LOCK(). An advisory lock also is released if the client makes another call to GET_LOCK() or closes its connection to the server.

• Question 138:

  What are some general principles of making a good MySQL backup?

  A. Enable the backup log.
  B. Make backups regularly.
  C. Enable the binary log.
  D. Flush the logs after backup.
  E. Never flush the logs.
  F. Store your data directory and backup files on separate physical drives or locations.
  B. Make backups regularly.
  C. Enable the binary log.
  D. Flush the logs after backup.
  F. Store your data directory and backup files on separate physical drives or locations.

  ---

  32.1. Introduction to Data Backup and Recovery Methods Here are some principles to keep in mind with regard to backups:

• Question 139:

  Which of the following is/are properties of the mysqldump client program?

  A. It dumps table contents to a file.
  B. It can put complete dump files on both the client and server host.
  C. It can only dump files to the server host.
  D. It bypasses the server and reads the corresponding data files directly.
  A. It dumps table contents to a file.
  B. It can put complete dump files on both the client and server host.

  ---

  25.6. mysqldump The mysqldump client program dumps table contents to files. It is useful for making database backups or for transferring database contents to another server. mysqldump can export tables as tab-delimited data files or produce SQL-format dump files that contain CREATE TABLE and INSERT statements for re- creating the dumped files.