Ace Your 1z0-071 Oracle PL Exam

Regarding the use of SAVEPOINT in transactions:

C. A SAVEPOINT does not issue a COMMIT: A SAVEPOINT in Oracle SQL is used to mark a point within a transaction. It does not commit the transaction; rather, it allows partial rollbacks to the point of the SAVEPOINT without affecting other uncommitted changes outside it.

E. Rolling back to a SAVEPOINT can undo a DELETE statement: SAVEPOINT allows you to rollback DML changes like INSERT, UPDATE, and DELETE that occur after the SAVEPOINT was established, but before a COMMIT.

Incorrect options:

A: CREATE INDEX is a DDL statement, and like most DDL statements, it implicitly commits the transaction, making it impossible to rollback using a SAVEPOINT.

B: Multiple SAVEPOINTS can be defined within a single transaction, each with unique or reused names.

D: TRUNCATE is a DDL statement that also implicitly commits the transaction; hence it cannot be rolled back to a SAVEPOINT.

The SET VERIFY ON command is related to how SQL*Plus and SQL Developer display information about substitution variables:

A. It displays values for variables created by the DEFINE command: When VERIFY is set to ON, SQL*Plus and SQL Developer will display the old and new values of a substitution variable when it is redefined using the DEFINE command or when a new value is provided for it during the session.

B. It can be used in SQL Developer and SQL*Plus: While traditionally associated with SQL*Plus, the SET VERIFY command is also supported in SQL Developer, allowing you to control the display of substitution variable values in both environments.

References:

Oracle SQL*Plus User's Guide and Reference, especially the section on the SET command and substitution variables.

In Oracle, *_TABLES views provide information about tables.

B. USER_TABLES displays all tables owned by the current user, making this statement true. No additional privileges are required to see your own tables.

D. ALL_TABLES displays all tables that the current user has access to, either through direct ownership or through privileges, making this statement true.

A, C, E, and F are incorrect. Specifically:

A and E are incorrect because you do not need ANY TABLE system privileges to view tables in DBA_TABLES or ALL_TABLES; you need the SELECT_CATALOG_ROLE or equivalent privileges.

C is incorrect because as a user, you do not need additional privileges to see your own tables in USER_TABLES.

F is incorrect because not all users can query DBA_TABLES; this requires specific privileges or roles.

References:

Oracle Database Reference, 12c Release 1 (12.1): "Static Data Dictionary Views"

A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.

B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.

C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.

D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.

E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.

These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.