Pass 1z0-071 Exam Guide

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

• C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.
• D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

• A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.
• B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.
• E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.
• F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

Roles are named collections of privileges in Oracle databases.

A. False. Roles cannot be assigned to other roles using the ALTER ROLE statement.

B. False. Roles can be assigned to both users and other roles.

C. True. Roles are assigned to users using the ALTER USER statement, but this is not the only method.

D. True. A single role can be assigned to multiple users, simplifying the management of user privileges.

E. True. A single user can be assigned multiple roles.

F. False. Privileges are not assigned to a role using the ALTER ROLE statement.

G. True. Privileges are assigned to a role using the GRANT statement.

In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.

• Statement C, the LOCATION clause, is required. The LOCATION clause specifies one or more external data source locations, typically a file or a directory that the external table will read from. Without this, Oracle would not know where to find the external data for the table.
• Statement E, the access driver TYPE clause, is also required. The access driver tells Oracle how to interpret the format of the data files. The most common access driver is ORACLE_LOADER, which allows the reading of data files in a format compatible with the SQL*Loader utility. Another option could be ORACLE_DATAPUMP, which reads data in a Data Pump format.
• Statements A, B, and D are not strictly required for the command to execute successfully, although they are often used in practice:

For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.

The true statements about built-in data types in Oracle are:

• B: A BFILE is a data type in Oracle that allows for a read-only link to binary files stored outside the database in the operating system. This is correct as per Oracle's documentation.
• D: The default length for a CHAR column, when not specified, is one character. This is according to the Oracle SQL standard.
• F: A BLOB is used for storing binary data within the Oracle database, allowing for storage of large amounts of unstructured binary data.

The incorrect options are:

• A: A VARCHAR2 column does not blank-pad values; it is CHAR that may blank-pad to the fixed length.
• C: A CHAR column requires a length specification, although if omitted, the default is one character.
• E: A VARCHAR2 column requires a length specification; without it, the statement will fail.

References:

• Oracle Documentation on Data Types: Data Types
• Oracle Documentation on LOBs: LOBs
• Oracle Documentation on Character Data Types: Character Data Types