Managing Databases with Oracle Questions and Answers

Q: Given the following statementALTER TABLE employee MOVE TABLESPACE data 1.The action taken is :

The statement moves the data to a new segment.In Oracle, ALTER TABLE ... MOVE relocates the data of a non-partitioned table into a new segment, here in the tablespace named in the statement. It is used to change a table's storage or tablespace and to reorganise it, removing fragmentation; the indexes on the table become unusable and must be rebuilt afterwards.It does not rename a tablespace. It moves the table rather than leaving a copy behind, and it moves the rows, not just the table's structure.

Q: Sequences guarantee valid surrogate key values.

Correct answer: FalseA 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.

Q: What Oracle backup and recover file contains user and system data?

Correct answer: DatafileThe 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.

Q: Oracle maintains a System Change Number (SCN) which is a database-wide value that is incremented by Oracle whenever database changes are made.

Correct answer: TrueThe 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.

Q: When you type into SQL*Plus and press enter, the statement will be finished and executed.

Correct answer: FalsePressing 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.

Q: You can use the DROP statement to remove structures from the database.

Correct answer: TrueDROP 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.

Q: Indexes are created to enforce uniqueness on columns and to enable fast retrieval by column values.

Correct answer: TrueBoth 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.

Q: Oracle does NOT support a CASCADE UPDATE constraint.

Correct answer: TrueOracle 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.

Given the following statement

ALTER TABLE employee MOVE TABLESPACE data 1.

The action taken is :

A.table space is renamed
B.data is moved to new segment
C.A copy of table is moved into new segment
D.The table structure is moved in to new segment

Exam Year(s)

MH SET - 2018, SET Exam - 2018,

Answer: B

The statement moves the data to a new segment.

In Oracle, ALTER TABLE ... MOVE relocates the data of a non-partitioned table into a new segment, here in the tablespace named in the statement. It is used to change a table's storage or tablespace and to reorganise it, removing fragmentation; the indexes on the table become unusable and must be rebuilt afterwards.

It does not rename a tablespace. It moves the table rather than leaving a copy behind, and it moves the rows, not just the table's structure.

Discuss in Forum

Sequences guarantee valid surrogate key values.

A.True
B.False

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.

Discuss in Forum

What Oracle backup and recover file contains user and system data?

A.Control file
B.Datafile
C.OnLine ReDo file
D.Offline ReDo file

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.

Discuss in Forum

Of the three ways to create an Oracle database, which one is the easiest and most recommended?

A.Using the Oracle Database Configuration Assistant.
B.Using the Oracle-supplied database creation procedures.
C.Using the SQL CREATE DATABASE command.
D.None of the above is correct.

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.

Discuss in Forum

Oracle maintains a System Change Number (SCN) which is a database-wide value that is incremented by Oracle whenever database changes are made.

A.True
B.False

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.

Discuss in Forum

Which of the following is not true about indexes?

A.Indexes are created to enforce uniqueness on columns.
B.Indexes are created to enable fast retrieval by column values.
C.Columns that are frequently used with equal conditions in WHERE clauses are good candidates for indexes.
D.Indexes are created with the ALTER TABLE command.

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.

Discuss in Forum

When you type into SQL*Plus and press enter, the statement will be finished and executed.

A.True
B.False

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.

Discuss in Forum

You can use the DROP statement to remove structures from the database.

A.True
B.False

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.

Discuss in Forum

Indexes are created to enforce uniqueness on columns and to enable fast retrieval by column values.

A.True
B.False

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.

Discuss in Forum

10.

Oracle does NOT support a CASCADE UPDATE constraint.

A.True
B.False

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.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Managing Databases with Oracle Questions and Answers || Managing Databases with Oracle MCQ Questions and Answers || Managing Databases with Oracle GK Questions and Answers || Managing Databases with Oracle GK MCQ Questions || Managing Databases with Oracle Multiple Choice Questions and Answers || Managing Databases with Oracle GK || GK on Managing Databases with Oracle || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Managing Databases with Oracle Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications