Oracle Updated 1z0-071 Exam Questions and Answers by lily-rose

Roles in Oracle Database are designed to simplify the management of privileges. The following statements about roles are true:

• B: A single user can indeed be assigned multiple roles. This allows for easy management of user privileges as they can be grouped into roles, and these roles can be granted to users.
• D: A single role can be assigned to multiple users. This is one of the primary purposes of roles, to provide an efficient way to grant the same set of privileges to different users.
• G: Privileges are granted to a role using the GRANT statement. This allows the role to encapsulate the privileges which can then be granted to users.

The incorrect options are:

• A: Roles cannot be assigned to other roles using the ALTER ROLE statement; they are granted to other roles using the GRANT statement.
• C: Roles are not assigned to users using the ALTER USER statement; roles are granted to users using the GRANT statement.
• E: Privileges are not assigned to a role using the ALTER ROLE statement; they are granted using the GRANT statement.
• F: A role is not limited to being assigned to just one user; it can be assigned to multiple users.

References:

• Oracle Documentation on Roles: Database Security Guide - Roles
• Oracle Documentation on the GRANT statement: SQL Language Reference - GRANT

In Oracle Database 12c, the operations related to dropping columns from a table include several behaviors, each specified clearly in Oracle documentation and best practices:

• B. A column drop is implicitly committed: Dropping a column in Oracle using the ALTER TABLE ... DROP COLUMN command is a DDL (Data Definition Language) operation, which means it cannot be rolled back. DDL commands in Oracle are automatically and implicitly committed, meaning that once a column is dropped, the action is finalized immediately.
• C. A column that is referenced by another column in any other table cannot be dropped: In Oracle, if a column is being referenced by a foreign key constraint or any dependency from another table, you cannot directly drop it until those references are removed or disabled. This ensures data integrity across related tables.
• E. A primary key column cannot be dropped: The primary key constraint is critical for identifying unique rows within a table. Oracle does not allow the dropping of columns that are part of a primary key without first dropping the constraint or modifying it to exclude the column you intend to drop.

References:

• Oracle Database SQL Language Reference 12c, specifically sections discussing DDL operations and constraints.

In Oracle Database 12c, the operations related to dropping columns from a table include several behaviors, each specified clearly in Oracle documentation and best practices:

• B. A column drop is implicitly committed: Dropping a column in Oracle using the ALTER TABLE ... DROP COLUMN command is a DDL (Data Definition Language) operation, which means it cannot be rolled back. DDL commands in Oracle are automatically and implicitly committed, meaning that once a column is dropped, the action is finalized immediately.
• C. A column that is referenced by another column in any other table cannot be dropped: In Oracle, if a column is being referenced by a foreign key constraint or any dependency from another table, you cannot directly drop it until those references are removed or disabled. This ensures data integrity across related tables.
• E. A primary key column cannot be dropped: The primary key constraint is critical for identifying unique rows within a table. Oracle does not allow the dropping of columns that are part of a primary key without first dropping the constraint or modifying it to exclude the column you intend to drop.

References:

• Oracle Database SQL Language Reference 12c, specifically sections discussing DDL operations and constraints.

The data dictionary contains metadata (data about data) about the database and its objects.

A. False. DBA_ views display metadata for objects accessible to the database administrator, not only for the SYS schema.

B. True. ALL_ views display information about all the objects that the current user has access to. This does not necessarily mean the user has privileges on these objects, only that the user can see them.

C. False. The data dictionary is not accessible when the database is closed because it requires the database to be open in order to access the system tables.

D. True. Not all types of metadata are available in all three prefixes (ALL_, DBA_, USER_). Some specific metadata might only be available in one or two of these view types.

E. False. The data dictionary stores metadata in tables. The various views (ALL_, DBA_, USER_, etc.) provide different perspectives on this data.

References:

• Oracle Documentation on Data Dictionary and Dynamic Performance Views: https://docs.oracle.com/cd/B28359_01/server.111/b28310/datadict.htm

• A: This statement is false. A table can only have one primary key, although the primary key can consist of multiple columns (composite key).
• B: This statement is true. A table can have multiple foreign keys referencing the primary keys of other tables or the same table.
• C: This statement is false. A NUMBER column without data is NULL, not zero.
• D: This statement is false. A column definition must specify exactly one data type.
• E: This statement is true. A VARCHAR2 column without data defaults to NULL, not an empty string.