1Z0-071 — Frequently Asked Questions

Implicit conversion uses the session NLS_DATE_FORMAT of DD-MON-RR, and the literal '01-JAN-2019' carries a four-digit year, so Oracle raises ORA-01861 instead of subtracting.

No. DATE subtraction already returns days as a number; C rounds the fractional value, while E returns it with the time-of-day fraction still attached, which is also accepted.

UTL_FILE reads and writes through a DIRECTORY object, and READ/WRITE on that directory are object privileges granted to a specific user, not catalog-wide system privileges.

No. System privileges are passed on with WITH ADMIN OPTION; WITH GRANT OPTION applies only to the object-privilege form of GRANT.

Option E uses valid Oracle syntax: aliases can omit AS and need no quotes if not reserved. The question is flawed because it asks for two, but E also works.

Option B uses single quotes for aliases, which Oracle treats as string literals, not identifiers. Also, DATE is a reserved word and cannot be an unquoted alias.

Oracle does not allow granting object privileges with WITH GRANT OPTION to roles. It is only permitted for users or PUBLIC.

No. Revoking a system privilege granted with WITH ADMIN OPTION does not remove the privilege from those who received it via that grant.

In Oracle a schema is the set of objects owned by one user and carries that user's name, so the user-to-schema relationship is strictly one-to-one; you can be granted privileges on other schemas, but never own a second one.

No. A transaction and its rollback belong to the session that made the change, so another user cannot undo your uncommitted UPDATE — which makes option A false.

No. Zero rows produce a NULL comparison and no exception; only a result of more than one row raises ORA-01427, which is why option C is false.

Oracle's documented examples and exam doctrine place the subquery on the right of the comparison operator, with the outer column or expression on the left.

There AVG_SAL is only a column alias for the group function AVG(e.salary), not a table column, so Oracle refuses to qualify it with the table alias E and raises ORA-00904.

Both keep every department row and attach salary data through an outer join or outer-joined inline view, so a department with no employees still appears with a NULL average salary.

emp is a role name, not a privilege, so Oracle reads it as a second system privilege and raises ORA-00990. Role names are only valid after TO, in the grantee list.

No. CREATE TABLE is a system privilege and SELECT ON hr.employees is an object privilege; they need separate GRANT statements, which is why E fails while D succeeds.

VARCHAR2 requires an explicit length in SQL; omitting it raises ORA-00906. Unlike CHAR, there is no default length for VARCHAR2.

The default length is 1, but the unit depends on NLS_LENGTH_SEMANTICS (BYTE by default, or CHAR if set). So it is not always one character.

SELECT is also grantable on sequences, so it does not represent an exclusive restriction like the correct options A, D, and E.

No, DELETE is only valid on tables and views; sequences do not support DELETE object privileges.

Only Oracle-supplied predefined roles such as DBA, CONNECT and RESOURCE are created in the SYS schema; a role you create with CREATE ROLE is owned by you, as option E states.

PUBLIC is a built-in group that every database user belongs to implicitly. You can revoke a privilege granted to PUBLIC, but you cannot revoke PUBLIC membership itself.

System privileges and roles are extended with WITH ADMIN OPTION instead; WITH GRANT OPTION applies to object privileges such as SELECT, INSERT or UPDATE on a table or view.

No. The documented grant-option privilege lets the grantee grant the object privilege to any user in the database, but not to PUBLIC, which makes option D true.

Oracle blocks it with ORA-12081 because read-only mode prohibits any operation that deletes row data or resets the table's high-water mark, even though TRUNCATE is DDL.

No. SET UNUSED is structural DDL and fails with ORA-12081; only DROP UNUSED COLUMNS, which cleans up columns already marked unused, is permitted.