Managing Databases with Oracle Questions and Answers

Answer: B

Correct answer: False

A sequence guarantees only uniqueness of the numbers it issues. Values are lost when a transaction rolls back or an instance fails, so gaps appear; nothing forces the value to be used in the intended column; and a value can be inserted directly, bypassing the sequence and colliding with it later. Uniqueness is not the same as validity.

Answer: B

Correct answer: Datafile

The datafiles hold the actual contents of the database - the user tables and indexes and the data dictionary alike. The control file records the physical structure of the database and the state of the files, and the redo logs record the changes made, so all three are needed for recovery but only the datafiles hold the data itself.

Answer: A

Correct answer: Using the Oracle Database Configuration Assistant.

The Configuration Assistant is a guided tool that creates the database, its files, its parameter file and its initial users in one operation, and it is the recommended route because it is difficult to omit a step. The CREATE DATABASE command gives complete control at the price of having to get every detail right by hand.

Answer: A

Correct answer: True

The system change number is a single database-wide counter, advanced as changes are committed and recorded in the redo stream and in every datafile header. Because it orders all changes across the whole database, it is what lets Oracle tell whether a set of files is mutually consistent and how far forward recovery must be applied.

Answer: D

Correct answer: Indexes are created with the ALTER TABLE command.

Indexes are created with CREATE INDEX, and a unique index is also created implicitly when a primary key or unique constraint is declared. ALTER TABLE modifies the table, not the indexes on it. The other three statements are correct: indexes enforce uniqueness, speed retrieval, and are most worthwhile on columns frequently tested for equality.

Answer: B

Correct answer: False

Pressing Enter simply moves to the next line of the buffer, so a statement can be spread over as many lines as is convenient for reading. Execution needs an explicit terminator: a semicolon at the end of the statement, or a slash on a line of its own to run whatever the buffer already holds.

Answer: A

Correct answer: True

DROP removes a structure from the database entirely - DROP TABLE, DROP VIEW, DROP INDEX, DROP SEQUENCE. It is distinct from DELETE, which removes rows and leaves the table in place, and from ALTER, which changes a structure without removing it.

Answer: A

Correct answer: True

Both are genuine purposes and they often coincide. A unique index enforces the uniqueness of a primary key or unique constraint, and any index - unique or not - lets the optimiser find rows by column value without scanning the whole table. The cost is that every insert, update and delete must maintain the index as well as the table.

Answer: A

Correct answer: True

Oracle supports ON DELETE CASCADE but has no ON UPDATE CASCADE, so a change to a parent key value cannot be propagated to the children automatically. The remedy is either a trigger or, more usually, a surrogate primary key whose value never needs to change - which removes the problem rather than working around it.

Answer: D

Correct answer: Sequences guarantee valid surrogate key values.

A sequence guarantees only that it will not hand out the same number twice. It cannot guarantee that the number is used, or used in the intended table, or that no one has inserted a conflicting value directly - numbers are also lost on rollback and on instance failure. The other three statements are correct descriptions of what a sequence is and does.