1. An Index is not mandatory for
Enabling Unique constraint
Enforcing Foreign key constraint
Enforcing primary key constraint
2. A global temporary table
can be partitioned
can be index organized
cannot have foreign key constraint
3. If INSERT statement omits a value for the column defined as NOT NULL
DEFAULT clause used while table creation will be used
The insert will always fail
A trigger cannot be used to assign value to the column
4. For an Index Organized table
Primary key constraint is required
Unique constraint is required
Unique index is required
5. An external table
can be temporary
Can have indexes
Cannot have constraints
6. For LOB storage in table
The entire LOB value is stored outside the row
LOB records above 4000 bytes are stored outside the row
The LOB locator is always stored inline regardless of where the LOB value is stored.
7. PCTVERSION in LOB storage
Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB.
Specifies the number of versions to be kept
Specifies the retention period for LOB values
8. For an index-organized table, the column list of range partitioning
The table can be partitioned by any column
must be a subset of the primary key columns of the table.
An index-organized table cannot be partitioned
9. The number of partitions in a table
has to be more than one
has an upper limit
depends on the disk storage
10. A hash partitioned table
can be sub partitioned
cannot specify more than 16 partitioning columns.
can use LOB columns as partition key
11. A table is being updated by online applications between 9am and 6pm. The reporting application run aggregate query based on this table between 6am and 9am. A materialized view is create for this query. Which refresh option is best suited
FAST REFRESH ON COMMIT
COMPLETE REFRESH ON DEMAND
Either FAST or COMPLETE REFRESH ON DEMAND
12. Oracle optimizer cannot use Materialized View instead of the table
if the materialized view is created as select * from base table
The materialized view contains nextval of sequence
If Query Rewrite is enabled
13. To execute a package with the privileges of the user running the package
Specify AUTHID CURRENT_USER
By default it takes the privileges of the user running the package
Specify AUTHID DEFINER
14. :NEW cannot be used
in AFTER DELETE trigger
in BEFORE DELETE trigger
in INSERT trigger
15. Which of the following is FALSE for an INSTEAD OF trigger defined on a view
You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.
When an insert happens the database fires the triggers instead of performing DML on the view
If a view is inherently updatable then DML operations take preference
16. Which is FALSE about triggers
The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT)
If you specify WHEN clause for a DML event trigger, then you must also specify FOR EACH ROW.
You cannot use LOB columns in the trigger action inside the PL/SQL block.
17. To create a user authorized by the enterprise directory service (Oracle Internet Directory)
create user IDENTIFIED BY password
created user INDENTIFIED externally
create user identified GLOBALLY
18. to create the view regardless of whether the base tables of the view or the referenced object types exist or not
Specify ignore option
Use FORCE clause
The view will be created with errors
19. An updateable view
can contain DISTINCT operator
Cannot have set operator
can have a subquery in a SELECT list
20. Which is FALSE about Roles
You cannot grant a role to itself.
IDENTIFIED EXTERNALLY is used to validate a role through a package
any user can create a role and it will be private to the user
Grade =
Correct answers are: