Oracle 1Z0-883 Free Practice Questions

Q1. Compare a typical Distributed Replicated Block Device (DRBD) with MySQL Standard Replication using master-slave replication. 

Which two statements are correct? 

A. Both technologies use the TCP/IP stack as their primary transmission medium. 

B. DRBD uses shared-disk technology. 

C. Both technologies guarantee an identical copy of data on the secondary node. 

D. Only MySQL can maintain a time-delayed copy of data on the secondary node. 

View Answer

Q2. A general purpose MySQL instance is configured with the following options: 

-- log-slow-queries -- long-query-time=,0001 -- log-slow-admin-queries -- general-log -- log-bin -- binlog-format=STATEMENT -- innodb-flush-log-at-trx-commit=1 

Which three statements are true? 

A. The General Query Log records more data than the Binary Log. 

B. The binary Log records more data than the General Query Log. 

C. The Slow Query Log records more data than the General Query Log. 

D. The General Query Log records more data than the Slow Query Log. 

E. The Slow Query Log records more data than the Binary Log. 

F. The Binary Log records more data than the Slow Query Log. 

View Answer

Q3. You need to replicate a table from a master to a slave. The master and slave copies of the 

table will have different number of columns. 

Which two conditions must be true? 

A. Each extra column in the copy with more columns must not have a default value. 

B. Columns that are common to both versions of the table must be defined in the same order on the master and the slave. 

C. The slave database cannot have more columns than the master. Only the master database can have more columns. 

D. Columns that are common to both versions of the table must come first in the table definition, before any additional columns are additional columns are defined on either server. 

E. The master database cannot have more columns than the slave. Only the slave deatbase can have more columns. 

View Answer

Q4. You are attempting to secure a MySQL server by using SSL encryption. 

On starting MySQL, you get this error: 

130123 10:38:02 [ERROR] mysqld: unknown option ‘—ssl’ 

What is the cause of the error? 

A. The --- ssl level was not specified. 

B. The server was not started with the – enable--ssl-plugin option. 

C. -- ssl is not a valid server option. 

D. The mysqld binary was not compiled with SSL support. 

E. The server’s SSL certificate was invalid. 

View Answer

Q5. 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. 

View Answer

Q6. Consider the following table: 

CREATE TABLE ‘game’ ( 

‘id’ int (10) unsigned NOT NULL AUTO_INCREMENT, 

‘keyword’ varchar (45) DEFAULT NULL, 

‘date’ datetime NOT NULL, 

PRIMARY KEY (‘id’ , ‘date’), 

UNIQUE KEY ‘keyword_idx’ (‘keyword’ , ‘date’) 

) ENGINE=InnoDB DEFAULT CHARSET=latin1 

PARTITION BY RANGE (TO_DAYS (date) ) ( 

PARTITION g201301 VALUES LESS THAN (TO_DAYS (‘2013-01-01 00:00:00’) ), 

PARTITION g201302 VALUES LESS THAN (TO_DAYS (‘2013-02-01 00:00:00’) ), 

PARTITION g201303 VALUES LESS THAN (TO_DAYS (‘2013-03-01 00:00:00’) ), 

PARTITION g201304 VALUES LESS THAN (TO_DAYS (‘2013-04-01 00:00:00’) ), 

PARTITION gMORES VALUES LESS THAN (MAXVALUE) ); 

Which method should used to add a new g201305 partition to the table? 

A. ALTER TABLE games 

REORGANIZE PARTITION (gMORES) 

INTO 

g01305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ), 

gMORES VALUES LESS THAN (MAXVALUE) ); 

B. ALTER TABLE games 

ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ); 

C. ALTER TABLE games 

COALESCE PARTITION (gMORES) 

INTO 

g01305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ), 

gMORES VALUES LESS THAN (MAXVALUE) ); 

D. ALTER TABLE games 

SPLIT PARTITION (gMORES) 

INTO 

g201305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ), 

gMORES VALUES LESS THAN (MAXVALUE) ); 

E. ALTHER TABLE games 

DROP PATITION gMORES, 

ADD PARTITION 

g201305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ), 

gMORES VALUES LESS THAN (MAXVALUE) ); 

View Answer

Q7. A Mysql Server has been running an existing application successfully for six months. 

The my.cnf is adjusted to contain the following additional configuration: 

[mysqld] 

Default-authentication-plugin=sha256_password 

The Mysql Server is restarted without error. 

What effect will the new configuration have in existing accounts? 

A. They will have their passwords updated on start-up to sha256_password format. 

B. They will have to change their password the next time they login to the server. 

C. They are not affected by this configuration change. 

D. They all connect via the secure sha256_password algorithm without any configuration change. 

View Answer

Q8. You have enabled the Slow Query Log for a short period. 

When you process the Slow Query Log, you receive the following snip of output: 

Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost 

CREATE TABLE ‘t1’ (id serial,id0 varchar(N) unique key,intcaoll INT (N) 

,intco12 INT(N) ,intco13 INT(N) ,intco14 INT(N) ,intco15 INT(N) 

,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N) 

,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N) 

,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 

VACHAR (N) ) 

Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ 

localhost 

SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18 

,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6 

,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = ‘s’ 

Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost 

SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, 

Process_priv FROM mysql.user WHERE CONCAT (user, ‘s’, host) = CURRENT_USER () 

Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root[root]@localhost 

SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3 

,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id 

= ‘s’ 

You want to tune the query such that it provides the greatest overall time savings. 

Which query will accomplish this? 

A. CHEATE TABLE ‘t1’ (id serial, id0 varchar (N) unique key, intcol1 INT (N) ,intcol2 INT (N), intcol3 INT(N) ,intcol4 INT(N), intcol5 INT(N), charool1 VARCHAR 

(N) 

,charcol2 VARCHAR (N), charcol3 VARCHAR(N), charcol4 VARCHAR(N), charcol5 

VARCHAR (N) 

,charcol6 VARCHAR (N), charcol7 VARCHAR(N), charcol8 VARCHAR(N), charcol9 

VARCHAR (N) 

,charcol10 VARCHAR (N); 

B. SELECT intcol1, intcol2, intcol3, intcol4, intcol5, intcol6, intcol7, intcol8, intcol9, 

Intcol10, intcol11, intcol12, intcol13, intcol14, intcol15, intcol16, intcol17, intcol18, intcol19, 

charcol10 

FROM t1 

WHERE id = ‘s’; 

C. SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv 

FROM mysql.user 

WHERE CONCAT (user,’s’, host) = CURRENT_USER(); 

D. SELECT intcol1, intcol2, intcol3, intcol4, intcol5, charcol1, charcol2, charcol3, charcol4, 

charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 

FROM t1 

WHERE id = ‘s’; 

View Answer

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

Last_SQL_Error: Error 'Duplicate entry '8' for key 'PRIMARY' ' on query. Default database: 

'mydb'. Query: 'insert into mytable VALUES ('8' , 'George') ' 

Skip_Counter: 0 

Retrieved _Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8 

Auto _Position: 1 

You execute a “SHOW CREATE TABLE mytable” on the slave: 

CREATE TABLE ‘mytable’ ( 

‘ID’ int(11) NOT NULL DEFAULT ‘0’, 

‘name’ char(10) DEFAULT NULL, 

PRIMARY KEY (‘ID’) 

) 

The table mytable on the slave contains the following: 

You have issued a STOP SLAVE command. 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 GLOBAL SQL_SKIP_SLAVE_COUNTER=1 

B. SET GTID_NEXT=”CONSISTENCY”; 

BEGIN; COMMIT; 

SET GTID_NEXT=” AUTOMATIC’; 

C. SET GLOBAL enforce_gtid_consistency=ON 

D. SET GTID_EXECUTED=”38f32e23480a7-32a1-c323f78067fd37821 : 9”; 

E. SET GTID_NEXT=”38f32e23480a7-32a1-c323f78067fd37821 : 9”; 

BEGIN; COMMIT; 

SET GTID_NEXT=”AUTOMATIC”; 

View Answer

Q10. When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes. 

A. BIT columns are written by InnoDB at the head of the row, meaning they are always the first to be retrieved. 

B. Multiple BIT columns pack tightly into a row, using less space. 

C. BIT (8) takes less space than eight TINYINT fields. 

D. The BIT columns can be manipulated with the bitwise operators &, |, ~, ^, <<, and >>. The other integer types cannot. 

View Answer

Q11. What are four capabilities of the mysql client program? 

A. Creating and dropping databases 

B. Creating, dropping, and modifying tables and indexes 

C. Shutting down the server by using the SHUTDOWN command 

D. Creating and administering users 

E. Displaying replication status information 

F. Initiating a binary backup of the database by using the START BACKUP command 

View Answer

Q12. 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 

View Answer

Q13. Review the definition of the phone_list view. 

CHEATE OR REPLACE ALGORITHM=MERGE DEFINER= ‘root’@localhost’ SQL SECURITY DEFINER VIEW ‘phone_list’ AS SELECT 

e . id as id 

‘e . first_name AS ‘first_name’ 

‘e . last_name AS ‘last_name’ 

‘coalesce ( ph1.phone_no, ‘--') AS ‘office_no’ 

‘coalesce (ph2 .phone_no, '--‘) AS ‘cell_no’ 

FROM employees e 

LEFT JOIN employee_phone ph1 

ON ph1.emp_id = e.id AND ph1.type = ‘office’ 

LEFT JOIN employee_phone ph2 

ON ph2 .emp_id = e.id AND ph2 .type = ‘mobile’ 

The tables employees and employee_phone are InnoDB tables; all columns are used in this view. 

The contents of the phone_list view are as follows: 

Mysql> select * from phone_list; 

1 row in set (0.00 sec) 

Which method can you use to change the cell_no value to ‘555-8888’ for John Doe? 

A. DELETE FROM phone_list WHERE first_name= ‘John’ and last_name= ‘Doe’; INSERT INTO phone_list (first_name, last_name, office_no, cell_no) VALUES (‘John’ , ‘Doe’ , ‘x1234’ , ‘555-8888); 

B. INSERT INTO employee_phone (emp_id, phone_no, type) VALUES (1, ‘555-8888’,’mobile’); 

C. UPDATE phone_list SET cell_name ‘555-8888’ WHERE first_name= ‘John’ and last_name= ‘Doe’; 

D. UPDATE employee_phone SET phone_no= ‘555-8888’ where emp_id=1; 

View Answer

Q14. Which two requirements would lead towards a high availability solution? 

A. When uptime is critical 

B. When data must be refactored 

C. When application concurrency is static 

D. When data loss is unacceptable 

E. When application is a single point of failure 

View Answer

Q15. Which two statements are true about InnoDB auto-increment locking? 

A. The auto-increment lock can be a table-level lock. 

B. InnoDB never uses table-level locks. 

C. Some settings for innodb_autoinc_lock_mode can help reduce locking. 

D. InnoDB always protects auto-increment updates with a table-level lock. 

E. InnoDB does not use locks to enforce auto-increment uniqueness. 

View Answer