Oracle Oracle Certifications 1Z0-888 Questions & Answers

• Question 91:

  old_alter_table is disabled as shown. mysql> SELECT @@old_alter_table;

  

  Consider this statement on a RANGE-partitioned table: mysql> ALTER TABLE orders DROP PARTITION p1, p3; What is the outcome of executing this statement?

  A. All data in p1 and p3 partitions is removed and the table definition is changed.

  B. All data in p1 and p3 partitions is removed, but the table definition remains unchanged.

  C. Only the first partition (p1) will be dropped because only one partition can be dropped at any time.

  D. It results in a syntax error because you cannot specify more than one partition in the same statement.

  Correct Answer: B

• Question 92:

  When you examine a new MySQL installation with default configuration, you find a file called ibdata1 in the database directory. Which two statements are true about this file? (Choose two.)

  A. it contains the binary log.

  B. it contains a general tablespace.

  C. it is the default location for all new tables that you create.

  D. it contains the system tablespace.

  E. it contains the redo log.

  F. it contains the undo log.

  Correct Answer: CD

  Reference: https://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html

• Question 93:

  An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave must be "rolled forward" to provide all the latest data.

  The SHOW SLAVE STATUS indicates these values: RELAY_LOG_FILE=hostname-relay-bin.00004 RELAY_LOG_POS=1383

  Which command set would make the slave current?

  A. STOP SLAVE; SET GLOBAL master_delay=0; START SLAVE;

  B. STOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = `hostname-relay-bin.00004', RELAY_LOG_POS = 1383;

  C. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;

  D. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; RELAY_LOG_FILE = `hostname-relaybin.00004', RELAY_LOG_POS = 1383;

  Correct Answer: C

• Question 94:

  A simple master-to-slave replication is currently being used. This information is extracted from the SHOW SLAVE STATUS output:

  

  You execute a `SHOW CREATE TABLE mytable" on the slave:

  

  The table mytable on the slave contains:

  

  You have issued a STOP SLAVE command. You have determined that it is safe to skip the transaction in

  this case. One or more statements are required before you can issue a START SLAVE command to

  resolve the duplicate key error.

  Which statement should be used?

  A. SET GTID_NEXT="CONSISTENCY"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC";

  B. SET GTID_NEXT="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC";

  C. SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1

  D. SET GLOBAL enforce_gtid_consistency=ON

  E. SET GTID_EXECUTED="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8";

  Correct Answer: C

  Reference: https://dev.mysql.com/doc/refman/8.0/en/replication-problems.html

• Question 95:

  After rebooting the host, you attempt to start the mysqld service. You get the following error:

  Can't start the server: Bind on TCP/IP port: Address already in use

  What is the most likely cause of this error?

  A. The mysqld service has already been started on the same port.

  B. The network service process in the server is frozen, so all TCP/IP connections are paused and cannot be reused.

  C. You failed to specify the port number 3306 to the command to start the server, so it is defaulting to port 80, which is in use by the built-in web server.

  D. The /etc/hosts file does not have a valid IP entry for mysqld localhost, so it is binding to 127.0.0.1, which is already in use.

  E. The mysql.sock file in the MySQL /tmp directory was not removed after the reboot, so mysqld still thinks there is an active server running.

  Correct Answer: E

• Question 96:

  You enable binary logging on MySQL Server with the configuration: binlog-format=STATEMENT log-bin

  Which database updates are logged on the master server to the binary log by default?

  A. all updates except to the TEMPDB database

  B. all updates except to the PERFORMANCE_SCHEMA database

  C. all updates not involving temporary tables

  D. all updates to the default database, except temporary tables

  E. all updates to all databases

  Correct Answer: D

• Question 97:

  What two statements are true regarding FLUSH TABLES FOR EXPORT? (Choose two.)

  A. It can be used to export TEMPORARY tables.

  B. Table only exports when the table has its own tablespace.

  C. The InnoDB Storage engine must be used for the table being exported.

  D. It is the safest way to extract tables from the shared tablespace.

  E. Partitioned tables are not supported.

  Correct Answer: AB

• Question 98:

  Examine the mysqldumpslow output:

  

  Which two options could explain the slow query? (Choose two.)

  A. There is network congestion between client and server.

  B. No index has been defined on the filtered column.

  C. There are 108 queries still being executed.

  D. A table lock is causing delays.

  E. A full table scan is being used.

  Correct Answer: AE

• Question 99:

  Group Replication uses global transaction identifiers to track executed transactions and are fundamental in avoiding transaction conflict. Which additional three steps help in avoiding conflicts in group replication? (Choose three.)

  A. Set isolation level to be SERIALIZABLE.

  B. Use the binary log row format.

  C. Set isolation level to be READ COMMITTED.

  D. Configure IPv6 network for hosts.

  E. Guarantee a secondary index on every table.

  F. Guarantee a primary key on every table.

  G. Set multiple slave parallel worker threads.

  Correct Answer: ABF

• Question 100:

  Which statement best describes the purpose of the InnoDB buffer pool?

  A. It is amount of buffers available during a transaction.

  B. It caches only the indexes for InnoDB tables.

  C. It caches data and indexes for InnoDB tables.

  D. It holds changes made during a transaction before they are written to the log.

  E. It is a pool of memory for SQL query sort operations from within the InnoDB engine.

  Correct Answer: C

  Reference: https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-optimization.html