10 free Oracle 1Z0-908 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 228 questions. Work through them, then open each answer to check your reasoning.
Get all 228 questions (US$39) · Download these 10 as a PDF
Question 1
Examine this statement, which executes successfully:
You want to improve the performance of this query:
Which change enables the query to succeed while accessing fewer rows?
Show answer and explanation
Correct answer: C. ALTER TABLE world.city ADD INDEX (Population);
The query filters rows using a BETWEEN condition on the Population column, which is a range query. A B-tree index on Population (option C) allows the database engine to quickly locate and access only the rows where Population falls between 1000000 and 2000000, significantly reducing the number of rows scanned. SPATIAL indexes are designed for geometric data and are inappropriate for integer range queries. FULLTEXT indexes are designed for text search operations, not numeric range filtering. An index on Name would not help because the WHERE clause does not filter by Name.
Why the other options are wrong
- A. SPATIAL indexes are for geographic/geometric data types, not for numeric range filtering on integer columns.
- B. SPATIAL indexes cannot efficiently process BETWEEN conditions on numeric columns; they are designed for spatial queries.
- D. An index on Name provides no benefit since the WHERE clause filters only on Population, not Name.
- E. FULLTEXT indexes are for text search (MATCH/AGAINST), not for numeric range queries.
- F. FULLTEXT indexes are for text search operations and cannot process BETWEEN conditions on numeric columns.
Question 2
Which three are characteristics of a newly created role? (Choose three.)
Show answer and explanation
Correct answer: A, C, E
A. It can be dropped using the DROP ROLE statement. C. It is created as a locked account. E. It can be granted to user accounts. A newly created role can be dropped using DROP ROLE, is created as a locked account by default (requiring a password to be set before use), and can be granted to user accounts. Roles are stored in the mysql.user table along with users, not a separate mysql.role table. Roles cannot be renamed with a RENAME ROLE statement, this statement doesn't exist for roles. While roles can be associated with passwords, this is not an inherent characteristic of a newly created role.
Why the other options are wrong
- B. Roles are stored in the mysql.user table, not mysql.role.
- D. There is no RENAME ROLE statement in MySQL.
- F. While roles can have passwords assigned, this is not a default characteristic of a newly created role.
Question 3
You have configured GTID-based asynchronous replication with one master and one slave.
A user accidentally updated some data on the slave.
To fix this, you stopped replication and successfully reverted the accidental changes.
Examine the current GTID information:
You must fix GTID sets on the slave to avoid replicating unwanted transactions in case of failover.
Which set of actions would allow the slave to continue replicating without erroneous transactions?
Show answer and explanation
Correct answer: E. RESET SLAVE; SET GLOBAL gtid_purged=aaaaaaaaaaaa-aaaaaaaa- aaaaaaaaaaaa:1-10167;
gtid_purged=aaaaaaaaaaaa-aaaaaaaa-aaaaaaaaaaaa:1-10167; After reverting accidental changes on the slave, the slave's gtid_executed set (aaaaaaaa- aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10300) contains transactions beyond what the master has executed (aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10300 executed, but only 1- 3820 purged). The slave also has its own transaction (bbbbbbbb-bbbb-bbbb-bbbb- bbbbbbbbbbbb:1-9) that should not be replicated. RESET SLAVE clears replication state while preserving the GTID information needed. Setting gtid_purged to aaaaaaaa-aaaa- aaaa-aaaa-aaaaaaaaaaaa:1-10167 marks all master transactions up to that point as already applied, allowing replication to resume from the master's perspective without re- executing those transactions. This approach removes the slave's own GTID (bbbbbbbb transaction) from consideration and synchronizes the slave's GTID state with the master's actual executed range, preventing erroneous transaction replication during failover.
Why the other options are wrong
- A. RESET MASTER is inappropriate for a slave as it clears binary logs and GTID state entirely, breaking replication recovery.
- B. Attempting to set gtid_executed to a higher value than gtid_purged violates GTID constraints and creates an inconsistent state.
- C. Sets gtid_purged to the master's gtid_purged value (3820) rather than the master's gtid_executed value (10300), leaving a gap where transactions won't be recognized as applied, causing re-execution.
- D. RESET MASTER is inappropriate for a slave and would destroy necessary replication metadata; additionally, the gtid_executed value exceeds what should be set.
Question 4
The data in this instance is transient; no backup or replication will be required. It is currently under performing.
The database size is static and including indexes is 19G.
Total system memory is 32G.
After profiling the system, you highlight these MySQL status and global variables:
The OS metrics indicate that disk is a bottleneck.
Other variables retain their default values.
Which three changes will provide the most benefit to the instance? (Choose three.)
Show answer and explanation
Correct answer: B, D, E
B. buffer_pool_size=24G D. sync_binlog=0 E. innodb_doublewrite=0 Given that the database is 19G with 32G total system memory, disk is the bottleneck, and data is transient with no replication needed, the three most impactful changes are: (B) increasing buffer_pool_size to 24G allows the entire dataset plus indexes to reside in memory, eliminating disk I/O for read operations; (D) setting sync_binlog=0 disables synchronous binary log flushing to disk since no replication is required, reducing disk writes; and (E) disabling innodb_doublewrite=0 eliminates the write-ahead doubling to disk, significantly reducing I/O overhead for a non-critical transient dataset. These three directly address the identified disk bottleneck by either caching data in memory or reducing disk write operations.
Why the other options are wrong
- A. Setting innodb_flush_log_at_trx_commit=1 increases disk I/O by forcing log flush on every transaction, worsening the disk bottleneck rather than improving it.
- C. Increasing innodb_log_file_size to 1G does not directly address the disk bottleneck identified in the profiling; this affects log rotation frequency, not the primary performance issue.
- F. Increasing max_connections to 10000 is irrelevant to the disk bottleneck problem and could actually waste memory resources that should be allocated to the buffer pool.
- G. While /dev/shm could reduce disk I/O for undo logs, this is a minor optimization compared to the three primary changes and doesn't directly leverage the available 32G of system memory for caching the 19G dataset.
Question 5
Which statement is true about InnoDB persistent index statistics?
Show answer and explanation
Correct answer: A. Updating index statistics is an I/O expensive operation.
operation. Updating index statistics is computationally and I/O expensive because it requires scanning table pages and recalculating statistics. Index statistics are calculated from actual table pages on disk during ANALYZE TABLE operations, not just from buffer pool pages. The innodb_stats_persistent_sample_pages parameter controls sampling precision , increasing it scans more pages for better accuracy but at higher I/O cost, not memory cost. Transient statistics are less precise than persistent statistics. Statistics are not automatically recalculated on server restart; they are persisted and reused.
Why the other options are wrong
- B. Statistics are calculated from table pages scanned during analysis, not just from buffer pool pages.
- C. Setting innodb_stats_auto_recalc=ON updates statistics when table data changes significantly, not when new indexes are created.
- D. Increased sampling pages improve precision but don't improve transient statistics, persistent statistics are more precise.
- E. Increasing sampling pages increases I/O cost, not memory usage.
- F. Statistics are persisted to disk and reused after restart; they are not recalculated automatically.
Question 6
Which two are features of MySQL Enterprise Firewall? (Choose two.)
Show answer and explanation
Correct answer: A, B
A. recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands B. blocking of potential threats by configuring pre-approved whitelists The MySQL Enterprise Firewall records incoming SQL statements to build a whitelist of permitted commands, and it blocks potential threats by enforcing these pre-approved whitelists. The firewall cannot dynamically modify SQL statements with substitutions, it only permits or denies them. It does not automatically lock user accounts that violate firewall rules. The firewall operates at the application layer, not as a stateless TCP/3306 firewall.
Why the other options are wrong
- C. The firewall does not modify SQL statements; it only allows or blocks them.
- D. The firewall blocks unauthorized SQL but does not automatically lock user accounts.
- E. The firewall is application-layer based, not a stateless TCP firewall.
Question 7
Examine the modified output:
Seconds_Behind_ Master value is steadily growing.
What are two possible causes? (Choose two.)
Show answer and explanation
Correct answer: C, D
C. This value shows only I/O latency and is not indicative of the size of the transaction queue. D. The master is producing a large volume of events in parallel but the slave is processing them serially. When Seconds_Behind_Master grows steadily, it indicates the slave cannot keep pace with the master's binary log events. Option C is correct because this metric only measures I/O thread latency (time for the slave to read events from the master), not the actual size of the relay log queue or SQL thread processing time. Option D is correct because MySQL's slave SQL thread historically processed events serially by default, while the master can execute transactions in parallel. When the master generates events faster than the slave can apply them sequentially, replication lag accumulates. Modern MySQL versions support parallel slave threads, but without proper configuration or with lock contention, serial processing remains a bottleneck.
Why the other options are wrong
- A. The master's ability to transmit data is not the limiting factor; the slave's ability to apply transactions is the typical bottleneck in replication lag.
- B. Missing primary keys affects query performance but does not directly cause replication lag to grow steadily; this is a performance issue, not a replication synchronization issue.
- E. While lock contention can cause replication slowness, this option specifically refers to parallel slave threads, which are not the default mode and the question context implies serial processing is the issue.
Question 8
You must configure the MySQL command-line client to provide the highest level of trust and security when connecting to a remote MySQL Server.
Which value of –ssl-mode will do this?
Show answer and explanation
Correct answer: D. VERIFY_IDENTITY
VERIFY_IDENTITY provides the highest level of trust and security by verifying both that an SSL connection is established and that the server certificate is valid for the hostname being connected to. PREFERRED allows unencrypted connections if SSL fails. REQUIRED enforces SSL but does not verify the server's certificate matches the hostname. VERIFY_CA verifies the certificate authority but not the server identity against the hostname.
Why the other options are wrong
- A. PREFERRED allows downgrade to unencrypted connections, providing minimal security.
- B. VERIFY_CA checks certificate validity but does not verify the certificate matches the target hostname.
- C. REQUIRED enforces encryption but does not verify certificate identity against the hostname.
Question 9
Consider this shell output and executed commands:
[root@oel7 ~]# ps aux | grep mysqld mysql 2076 3.5 24.6 1386852 372572 ?
Ssl 12:01 0:01 /usr/sbin/mysqid [root@oel7 ~]# kill -15 2076
Which statement is true about MySQL server shutdown?
Show answer and explanation
Correct answer: C. kill -15 carries out a normal shutdown process, such as mysqladmin shutdown.
as mysqladmin shutdown. kill -15 sends SIGTERM to the mysqld process, which triggers a graceful shutdown sequence identical to mysqladmin shutdown or systemctl stop mysqld. The server completes transactions, flushes data to disk, and closes cleanly. This is a safe shutdown method that preserves data integrity. kill -9 (SIGKILL) is a forced termination that bypasses shutdown procedures and risks data loss.
Why the other options are wrong
- A. kill -15 is an acceptable shutdown method that performs a normal graceful shutdown.
- B. kill -15 and kill -9 are not equivalent; kill -15 is graceful while kill -9 is forced.
- D. mysqld_safe does not prohibit kill -15; this signal is handled normally by the server.
Question 10
You wish to protect your MySQL database against SQL injection attacks.
Which method would fail to do this?
Show answer and explanation
Correct answer: A. installing and configuring the Connection Control plugin
plugin Installing the Connection Control plugin only limits failed login attempts and does not prevent SQL injection attacks. The plugin addresses brute-force password attacks, not SQL injection vulnerabilities. Prepared statements with parameterized queries prevent SQL injection by separating code from data. Stored procedures can prevent injection when they use parameterized inputs rather than string concatenation. Avoiding concatenation of user-supplied values prevents attackers from injecting malicious SQL.
Why the other options are wrong
- B. Avoiding string concatenation with user input is a fundamental SQL injection prevention technique.
- C. Stored procedures prevent SQL injection when implemented with parameterized queries.
- D. Prepared statements are one of the most effective SQL injection prevention methods.
That was 10 of 228.
The full Oracle 1Z0-908 pack has all 228 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.