Last Attempt 1z0-071 Questions

In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.

A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.

B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.

C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.

D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.

E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ALTER TABLE"

Synonyms in Oracle Database are aliases for database objects.

A. Correct. A synonym can be created for another synonym, essentially chaining synonyms. B. Incorrect. Private synonyms must be unique within a schema. However, because each user has their own schema, multiple users can have private synonyms with the same name, each pointing to objects in their respective schemas. C. Correct. Any user with the necessary privileges can create a PUBLIC synonym, which is accessible to all users. D. Incorrect. A synonym cannot be created for an object within a package, but it can be created for the package itself. E. Incorrect. Synonyms, like views, do not have object numbers because they do not occupy space in the database as tables do.

References can be found in the Oracle Database SQL Language Reference documentation, which details the rules and functionality of synonyms.

Regarding the SESSION_PRIVS dictionary view:

• C. It contains the current system privileges available in the user session: This view displays the system privileges granted to the current user, making it a tool for understanding which actions the user is allowed to perform in the current session.

Incorrect options:

• A and B: SESSION_PRIVS does not contain information about privileges granted to or by other users; it specifically lists the privileges available to the current session.
• D: It does not contain object privileges; these are contained in other views such as USER_TAB_PRIVS.

A. True. This statement will work if customer_id is a character data type in the database. Oracle will implicitly convert the numeric literal 0001 to a string to compare with customer_id.

D. True. If the insert_date is of type DATE and the NLS_DATE_FORMAT matches 'DD-MON-YY', Oracle will implicitly convert the string literal '01-JAN-19' to a date type to compare with insert_date.

B is incorrect because if customer_id is a numeric data type, there is no need for implicit conversion. C is incorrect because using the DATE literal DATE '2019-01-01' is an explicit conversion. E is incorrect because TO_CHAR(customer_id) is an explicit conversion from a numeric to a string data type.