1z0-071 Questions Bank

External tables using the ORACLE_DATAPUMP access driver have specific characteristics:

C. Creating an external table using the ORACLE_DATAPUMP access driver allows you to select data from other tables, including another external table or a regular table whose rows are stored in database blocks.

A, B, D, and E are incorrect. Specifically:

A is incorrect because creating an external table does not automatically create a directory object; the directory object must exist prior to or be created separately.

B is incorrect as it limits the creation to tables stored in database blocks, which is not a restriction.

D is incorrect because creating an external table does not create a dump file; it reads from an existing dump file created by Data Pump.

E is also incorrect because the dump file is not created as part of the external table creation process.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE TABLE" for External Tables

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"

The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.

A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.

B. The subquery is a correlated subquery, as explained above.

C. The subquery is executed for each row, not before the DELETE statement.

D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.

E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.

References:

Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement

Oracle Database SQL Language Reference 12c Release 1 (12.1), Subquery Factoring

Oracle Database SQL Language Reference 12c Release 1 (12.1), Correlated Subqueries

B. True. In Oracle, synonyms can be created for sequences, allowing them to be referenced with an alternative name. This can be particularly useful when sequences are accessed across different schemas.

C. True. Oracle provides the ability to create public synonyms which are available to all users in the database. A public synonym is accessible to any user that has the necessary privileges on the underlying object.

D. True. A synonym can be created by a user to refer to an object owned by another user, assuming that the creator of the synonym has the necessary privileges to access the object owned by the other user.