Exam Prep Questions


1:

What command would you use to disable a constraint?

  • A. ALTER TABLE

  • B. DROP CONSTRAINT

  • C. ALTER SESSION

  • D. DELETE CONSTRAINT

A1:

Answer A is correct; you would disable the constraint with the ALTER TABLE command. Answer B is incorrect because the DROP CONSTRAINT clause to the ALTER TABLE statement would not disable the constraint but would delete it. Answer C is incorrect; ALTER SESSION allows you to change validation methods (DEFERRED, IMMEDIATE, VALIDATE, or NO VALIDATE) but does not allow you to disable it. Answer D is incorrect; DELETE CONSTRAINT isn't a valid command or clause to a command.

2:

Which keywords would you use to allow exiting data in a table to violate a constraint while causing the constraint to validate new data?

  • A. DISABLE NOVALIDATE

  • B. DISABLE VALIDATE

  • C. ENABLE NOVALIDATE

  • D. ENABLE VALIDATE

A2:

Answer C is correct; you would use the ENABLE NOVALIDATE command to enable constraint checking on new data entering the table but to also ignore any inconsistencies that would already exist in the tables. Answer A is incorrect; DISABLE NOVALIDATE means that neither the existing data nor the new data are validated. Answer B is incorrect because the constraint is disabled, but also because no alterations to data in the constrained columns can occur, including inserts, updates, or deletes. Answer D is incorrect because, although ENABLE VALIDATE will validate all new data entering the table, it will also validate all the data that already exists in the table.

3:

In what cases does Oracle automatically create indexes to support the constraints? [check all that apply]

  • A. NOT NULL

  • B. CHECK

  • C. PRIMARY KEY

  • D. FOREIGN KEY

  • E. UNIQUE

A3:

Answers C and E are correct. Oracle creates indexes to support primary keys and unique constraints. Answer A is incorrect; NOT NULL constraints are not supported by an index. Answer B is incorrect; CHECK constraints are used to make sure that data that falls within a range is inserted into a column and are not supported by an Oracle-generated index. Answer D is incorrect. It is often a good idea to index foreign key columns; however, these indexes are not created by Oracle.

4:

What are the two methods that can be used to create an integrity constraint on a user table? [Select two]

  • A. ALTER TABLE ENABLE CONSTRAINT

  • B. ALTER TABLE ADD CONSTRAINT

  • C. CREATE TABLE

  • D. CREATE DATABASE

  • E. CREATE UNIQUE INDEX

A4:

Answers B and C are correct; you can alter the table and add the constraint, or you can create the constraint when you create the table. Answer A is incorrect; ENABLE CONSTRAINT allows you to reenable a deferred and disabled constraint but not create a constraint. Answer D is incorrect; tables and therefore constraints are not created on user tables at database creation time. Answer E is incorrect, you can create a unique index on a constrained set of columns, or you can attempt to enforce integrity with a unique index, but it does not create a constraint.

5:

Which of the following choices identify a command that you might use if you want to disable the primary key integrity constraint?

  • A. ALTER TABLE

  • B. ALTER CONSTRAINT

  • C. ALTER INDEX

  • D. ALTER SYSTEM

A5:

Answer A is correct; you would alter the table to disable the primary key constraint. Answer B is incorrect; ALTER CONSTRAINT is an invalid command. Answer C is incorrect; altering the index won't disable the constraint. Answer D is incorrect; you can't disable the constraint by altering the system.

6:

You want to create a table into which information will be placed that will direct people to rows that violate integrity constraints. Which Oracle supplied script will accomplish this?

  • A. utlrp.sql

  • B. utlexcpt.sql

  • C. EXCEPTIONS.sql

  • D. catalog.sql

A6:

Answer B is correct; utlexcpt.sql creates the EXCEPTIONS table. Answer A is incorrect; utlrp.sql recompiles invalid objects in the database. Answer C is incorrect; EXCEPTIONS is the name of the table, not the name of the script to create it. Answer D is incorrect; catalog.sql creates the data dictionary tables and views.

7:

When attempting to reenable the primary key constraint on a table after a bulk data load, the DBA receives the following error:

 ORA-02299: cannot enable <primary key name> - duplicate keys found. 

Where might the DBA be able to find the rows that are causing the issues?

  • A. DBA_CONS_COLUMNS

  • B. DBA_CONSTRAINTS

  • C. DBA_PRIMARY_KEY_COLUMNS

  • D. EXCEPTIONS

A7:

Answer D is correct; the EXCEPTIONS table will point the DBA at the rows that are in error and the constraint that has been violated. Answer A is incorrect; from the DBA_CONS_COLUMNS view, you can find the constraints and their columns. Answer B is incorrect; from the DBA_CONSTRAINTS, you can find the names and types of constraints but not what rows violate the constraints. Answer C is incorrect; it is not a valid data dictionary view.

8:

You notice that the system-generated indexes associated with the integrity constraints in the database have been defined as nonunique. What can you infer from this?

  • A. The constraint is a nondeferrable primary key.

  • B. Something is wrong with the database.

  • C. An error caused a data load to abort.

  • D. The index was built on a deferrable unique constraint.

A8:

Answer D is correct; the constraint was unique but deferrable. Answer A is incorrect; nondeferrable primary keys would create a unique index. Answer B is incorrect; this is not a way to determine whether something is wrong with the database. Answer C is incorrect; an aborted load would not cause an index to be either unique or nonunique.

9:

Which data dictionary view contains a listing of all the columns that are a part of a data integrity constraint?

  • A. DBA_CONSTRAINT

  • B. DBA_CONS_COLUMNS

  • C. V$CONSTRAINTS

  • D. DBA_IND_COLS

A9:

Answer B is correct; DBA_CONS_COLUMNS provides you with the columns involved as a part of an integrity constraint. Answer A is incorrect; DBA_CONSTRAINTS gives you the constraint name but not the columns in the constraint. Answer C is incorrect; it is not a data dictionary view. Answer D is incorrect. It tells you columns in an index, not columns in a constraint; they can be different.

10:

At what point in a transaction will a deferred constraint be checked?

  • A. At the beginning of every DML statement

  • B. At the end of every DML statement

  • C. Whenever a trigger fires

  • D. Whenever the database checkpoints

  • E. At the end of the transaction, when it commits

A10:

Answer E is correct; the deferred constraint will commit whenever the transaction finishes and commits. Answer A is incorrect; constraints are not checked when a DML statement begins. Answer B is incorrect; nondeferred, or immediate, constraints are checked when the DML statement finishes. Answer C is incorrect; the point of integrity constraints is that they are not reliant on a trigger's code or on a trigger firing. Answer D is incorrect; checkpoints and integrity checking are independent and unrelated processes.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net