Oracle SQL Oracle SQL DDL and Schema Objects 3

Question 1

What does the INVISIBLE option do when creating an index in Oracle SQL (11g+)?

Accepted Answer

Creates the index but the optimizer ignores it by default

Answer

An invisible index exists and is maintained by DML, but the optimizer ignores it unless OPTIMIZER_USE_INVISIBLE_INDEXES is set or the index is hinted.

Question 2

Which Oracle SQL object stores the result of a query and updates it periodically?

Accepted Answer

Materialized view

Answer

A materialized view physically stores query results and can be refreshed on a schedule or on demand, improving query performance.

Question 3

What does the ENABLE NOVALIDATE constraint state do in Oracle SQL?

Accepted Answer

Enables the constraint for new rows only, without validating existing rows

Answer

ENABLE NOVALIDATE enforces the constraint only on new or modified rows without checking existing rows that may violate it.

Question 4

Which Oracle SQL statement creates a table that exists only for the duration of a session?

Accepted Answer

CREATE GLOBAL TEMPORARY TABLE

Answer

CREATE GLOBAL TEMPORARY TABLE creates a temporary table whose data is private to each session and is deleted at session or transaction end.

Question 5

What is the purpose of the GRANT statement in Oracle SQL?

Accepted Answer

Gives privileges on database objects to users or roles

Answer

GRANT gives specified privileges (SELECT, INSERT, UPDATE, etc.) on database objects to users or roles.

Oracle SQL Practice Test

Oracle SQL Practice Test

Oracle SQL Oracle SQL DDL and Schema Objects 3