Oracle 1Z0-888 Quiz 10 Topic 11 Questions 46-50

Question: 1

The 'applicationdb' is using InnoDB and consuming a large amount of file system space. You have a /backup partition available on NFS where backups are stored.

You investigate and gather this information:

The table data_current has 1,000,000 rows.

The table data_reports has 1,500,000 rows.

The table data_archive has 4,500,000 rows.

q1_1Z0-888

Three tables are stored in the InnoDB shared tablespace and the details are as follows:

q1_1Z0-888

You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storing it on your backup partition.

q1_1Z0-888

Unfortunately, this action does not free any actual disk space back to the file system and the server disk space is running out.

Which set of actions will allow you to free disk space back to the file system?

AEnable compression on the table, causing InnoDB to release unused pages on disk to the file system:

BExecute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the file system:

CSet the server to use its own tablespace, and then alter the table so that data is moved from the shared tablespace to its own:

DTake a backup, stop the server, remove the data files, and restore the backup:

Show Answer

Question: 2

How does the InnoDB storage engine handle deadlocks when they are detected?

ABoth the affected transactions will be rolled back.

BThe affected transactions wait for innodb_lock_wait_timeout seconds, and then roll back.

COne of the affected transactions will be rolled back, the other is allowed to proceed.

DThe transaction isolation level determines which transaction is rolled back.

EThe innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.
References:

Show Answer

Question: 3

A MySQL instance is running on a dedicated server. Developers access the server from the same network subnet. Users access the database through an application that is running on a separate server in a DMZ.

Which two will optimize the security of this setup? (Choose two.)

Aenabling and using SSL for connections to the MySQL database

Brunning the server with ---skip-networking specified

Cdisabling connections from named pipes or socket files (depending on the operating system of the server)

Dstarting the server with ---bind-address=0.0.0.0 specified

Elimiting logins to originate from the application server or the server's subnet

Finstalling MySQL on the application server, and running the database and application on the same server

Show Answer

Question: 4

Consider the table people with this definition:

q4_1Z0-888

The application uses a query such as:

SELECT * FROM people WHERE YEAR(Birthday) = 1980;

The query is not using an index.

Which two methods can be used to allow the query to use an index? (Choose two.)

AChange the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.

BAdd a functional index for YEAR(Birthday).

CExecute ANALYZE TABLE to update the index statistics.

DAdd a generated column calculating YEAR(Birthday) and index that column.

EAdd FORCE INDEX (Birthday) to the query.

Show Answer

Question: 5

Which two statements are true regarding the creating of new MySQL physical and logical backups?

APhysical backups can be used to recover from data corruption.

BLogical backups are human-readable whereas physical backups are not.

CLogical backups are always larger than physical backups.

DPhysical backups are usually slower than text backups.

EPhysical backups are usually faster than text backups.

Show Answer

Oracle 1Z0-888 Quiz:10 Topic:11 Questions:46-50