Oracle 1Z0-883 Online Practice Questions and Exam Preparation

Oracle 1Z0-883 Online Questions & Answers

• Question 81:

  You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are notified that users are getting the following error: mysql> CALL film_in_stock (40, 2, @count);

  ERROR 1449 (HY000): The user specified as a definer (`bon'@'localhost') does not exist

  How would you identify all stored procedures that pose the same problem?

  A. Execute SELECT * FROM mysql.routines WHERE DEFINER='bob@localhost';.
  B. Execute SHOW ROUTINES WHERE DEFINER='bob@localhost'.
  C. Execute SELECT * FROM INFORMATION_SCHEMA. ROUTINES WHERE DEFINER='bob@localhost';.
  D. Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER='bob' and HOST=' localhost';.
  E. Examine the Mysql error log for other ERROR 1449 messages.
  D. Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER='bob' and HOST=' localhost';.

• Question 82:

  When backing up a replication slave, which three should also be backed up in addition to data?

  A. The master.info and relay.info files
  B. The relay log files
  C. The relay index file
  D. Mysql.slave_master_info table
  E. Mysql.slave_relay_log_info table
  F. Mysql.slave_worker_info table
  A. The master.info and relay.info files
  B. The relay log files
  E. Mysql.slave_relay_log_info table

• Question 83:

  What are two methods of taking a binary backup of a Mysql Server using InnoDB storage engine?

  A. Mysql Enterprise Backup
  B. Mysqldump with binary-data option
  C. Mysqlhotcopy
  D. File system snapshots
  E. Mysqldumpslow
  A. Mysql Enterprise Backup
  B. Mysqldump with binary-data option

• Question 84:

  Consider the Mysql Enterprise Audit plugin.

  You are checking user accounts and attempt the following query:

  Mysql> SELECT user, host, plugin FROM mysql.users; ERROR 1146 (42S02): Table `mysql.users' doesn't exist

  Which subset of event attributes would indicate this error in the audit.log file?

  A. NAME="Query" STATUS="1146" SQLTEXT="select user,host from users"/>
  B. NAME="Error" STATUS="1146" SQLTEXT="Error 1146 (42S02): Table `mysql.users' doesn't exist"/>
  C. NAME="Query" STATUS="1146" SQLTEXT=" Error 1146 (42S02): Table `mysql.users' doesn't exist"/>
  D. NAME="Error" STATUS="1146" SQLTEXT="select user,host from users"/>
  E. NAME="Error" STATUS="0" SQLTEXT="Error 1146 (42S02): Table `mysql.users' doesn't exist"/>
  C. NAME="Query" STATUS="1146" SQLTEXT=" Error 1146 (42S02): Table `mysql.users' doesn't exist"/>

• Question 85:

  You examine the output of SHOW GLOBAL STATUS and notice that the value of Created_tmp_disk_tables is consistently increasing. Which two variables would likely fix this issue?

  A. Table_open_cache
  B. Table_open_cache_instancs
  C. Table_definition_cache
  D. Tmp_table_size
  E. Max_heap_table_size
  F. Max_tmp_tables
  D. Tmp_table_size
  E. Max_heap_table_size

• Question 86:

  You have table `apps','userdata' on server that uses MyISAM storage engine. You want to transfer this data to server but use InnoDB engine instead.

  You execute the following commands:

  ServerB commands:

  Shell> mysqldump u root h server no-data apps userdata | mysql u root p apps Shell> mysql u root p h server e `ALTER TABLE `apps','userdata' ENGINE=InnoDB;' Shell> mysqldump u root p h server no-create-info order-by-primary apps

  userdata | mysql u root p apps

  What effect does the order-by-primary argument have on the mysqldump command?

  A. It exports tables with the most indexes first to assist with import speeds.
  B. It ensures that unique indexes have no conflicts when the data is dumped.
  C. It orders by primary key to assist in speeding up importing to InnoDB tables.
  D. It must be specified so index data is dumped correctly when on-create-info is used.
  C. It orders by primary key to assist in speeding up importing to InnoDB tables.

• Question 87:

  You are investigating the performance of the server and see the following information: Events_waits_summary_global_by_event_name in the performance schema shows that the wait/synch/

  

  mutex/sql/LOCK_table_cache event is dominating other wait events. The table_open_cache_overflows status variable is 0.

  

  Which action should be taken to remove the performance bottleneck described here?

  A. Decrease the value of table_definition_cache.
  B. Increase the value of table_definition_cache.
  C. Decrease the value of table_open_cache.
  D. Increase the value of table_open_cache.
  E. Decrease the value of table_open_cache_instances.
  F. Increase the value of table_open_cache_instances.
  A. Decrease the value of table_definition_cache.
  D. Increase the value of table_open_cache.

• Question 88:

  Consider the following statement on a RANGE partitioned table:

  ALTER TABLE orders DROP PARTITION p1, p3;

  What is the outcome of executing the above statement?

  A. Only the first partition (p1) will be dropped as only one can be dropped at any time.
  B. All data in p1 and p3 partitions are removed, but the table definition remains unchanged.
  C. A syntax error will result as you cannot specify more than one partition in the same statement.
  D. All data in pi and p3 partitions are removed and the table definition is changed.
  B. All data in p1 and p3 partitions are removed, but the table definition remains unchanged.

• Question 89:

  A user has deleted the wrong row in a table and you are preparing a point-in-time recovery skipping the DELETE event. The server is configured with:

  

  You have identified that the DELETE statement to skip has the Global Transaction Identifier (GTID)

  `dbbe7da-fe25-11e2-b6c7-0800274aa49e:5 and you replay the binary log with:

  Mysqlbinlog exclude-gtides=' dbbe07da-fe25-11e2-b6c7-0800274aa49e:5' binlog.00000.2 | mysql

  However all events were skipped instead of just the one deleting the wrong row.

  What is the reason for this?

  A. Mysqlbinlog ignores arguments to exclude-gtids-it means ignore all events with GTIDs.
  B. The server keeps track of which GTIDs have already been executed and skips those.
  C. Enforce_gtid_consistency is set to ON.
  D. Gtid_mode must be set to AUTO during point in time recoveries.
  B. The server keeps track of which GTIDs have already been executed and skips those.

• Question 90:

  Following a server crash, the automatic recovery of InnoDB fails. How would you begin to manually repair the InnoDB tables?

  A. Start the server with the innodb_force_recovery option set to a non-zero value.
  B. Start the server as usual, and then execute the REPAIR TABLE command.
  C. Start the server as usual, and then execute the CHECK TABLE command.
  D. Start the server with the innodb_recover_options option set to FORCE.
  A. Start the server with the innodb_force_recovery option set to a non-zero value.