Oracle Oracle Database Application Development 1Z0-074 Questions & Answers

• Question 1:

  You plan to execute this command:

  

  Which statement is true and why?

  A. It creates partitions P1 and P2 where COUNTRY is a single column partition key and CHANNEL is a subpartition key.

  B. It returns an error because only one set of multiple values can be specified for each partition.

  C. It returns an error because only a single column key can be used for a list-partitioned table.

  D. It returns an error because the AUTOMATIC option must be specified.

  E. It creates partitions P1 and P2 where COUNTRY and CHANNEL are multi-column partition keys.

  Correct Answer: E

• Question 2:

  Which three statements are true about the SQL Performance Analyzer? (Choose three.)

  A. It recommends the use of SQL Tuning Advisor for regressed SQL statements.

  B. It recommends the creation of indexes for regressed SQL statements.

  C. It compares and analyzes execution plans for the SQL statements in a SQL Tuning Set (STS).

  D. It always executes triggers that are fired recursively in FULLDML mode.

  E. It generates execution plans for each SQL statement in an STS.

  Correct Answer: ACE

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/e12024/spa.htm

• Question 3:

  Which three types of database objects can have their columns stored in the In-Memory column store? (Choose three.)

  A. heap tables with composite primary key constraints

  B. heap tables with dependent materialized views

  C. clustered tables with dependent materialized views

  D. index-organized tables stored in system-defined tablespaces

  E. sys-owned objects stored in sysaux-defined tablespaces

  Correct Answer: BDE

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/populating-objects-inmemory.html#GUID-C5F856BF-70E3-41C6-A1BA- 1E94D7D230B8

• Question 4:

  A multi-tenant container database (CDB) contains three PDBs.

  Which three are prerequisites for performing a cross-platform transport of any of the PDBs? (Choose three.)

  A. The CDB should be in MOUNT state.

  B. The source and target platforms must have the same endianness.

  C. The COMPATIBLE parameter must be set to 12.2 or greater in the CDB.

  D. The source PDBs must be closed for a cross-platform PDB backup.

  E. Flashback database must be enabled for the CDB.

  F. The CDB must use Automatic Memory Management.

  Correct Answer: BCD

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-transportingdata-across-platforms.html#GUID-B0178538-6E41-455D- 8166-FBB1EDC5D198

• Question 5:

  Which three resources can be controlled or managed by using Oracle Database Resource Manager? (Choose three.)

  A. number of parallel queries issued by a consumer group

  B. maximum number of DML operations performed by a consumer group

  C. sessions that are blocking other sessions

  D. sessions that are not blocking other sessions

  E. UNDO quota for a consumer group

  Correct Answer: ACE

• Question 6:

  Which three database objects can have columns stored in the In-Memory (IM) column store? (Choose three.)

  A. subpartitions

  B. clustered tables

  C. materialized views

  D. index-organized tables

  E. heap tables

  F. any SYS owned objects

  Correct Answer: BDF

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/populating-objects-inmemory.html#GUID-5D4B2A1F-815F-4FDC-91F8- E5CF7179985B

• Question 7:

  You execute this command in your database: ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = DDL;

  How does this affect the creation of tablespaces?

  A. User tablespaces with no ENCRYPTION clause specified in the CREATE TABLESPACE statement are not encrypted anywhere.

  B. TEMP and UNDO tablespaces are transparently encrypted with AES128 by default only in the cloud.

  C. TEMP and UNDO tablespaces are transparently encrypted with AES128 by default in an on-premise database.

  D. User tablespaces with no ENCRYPTION clause specified in the CREATE TABLESPACE statement are transparently encrypted with AES128 only in an on- premise database.

  E. User tablespaces with no ENCRYPTION clause specified in the CREATE TABLESPACE statement are transparently encrypted with AES128 only in the cloud.

  Correct Answer: B

  Reference: https://blogs.oracle.com/pshuff/database-options-advanced-security

• Question 8:

  Which three statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose three.)

  A. It analyzes only those memory components of the database instance that are impacting performance.

  B. It determines the segments on which waits are occurring when the database has a high number of buffer busy waits.

  C. It analyzes the performance of the database instance based on the latest two AWR snapshots.

  D. It determines SQL statements that are performing poorly.

  E. It recommends implementing SQL profiles to generate better execution plans.

  F. It analyzes only the components and waits that are impacting database performance.

  Correct Answer: BCD

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14211/diagnsis.htm#g41683

• Question 9:

  Which statement is true about the Automatic Diagnostic Repository (ADR)?

  A. Space used by the ADR can be limited to a user-specified value.

  B. Space used by the ADR is limited based only on the retention period.

  C. The retention period for the ADR is set based on the space limit specified.

  D. When the ADR reaches the space limit, it deletes incident dumps and metadata before deleting old core dumps.

  E. Manual purging of all types of ADR files is allowed.

  Correct Answer: E

• Question 10:

  One of your databases supports an OLTP workload.

  If a better plan is discovered for a statement, then you want that plan to be used.

  Which two can cause better plans to be used for a statement? (Choose two.)

  A. adaptive cursor sharing

  B. using literals in SQL statements

  C. SQL Access Advisor

  D. SQL Performance Analyzer

  E. adaptive execution plans

  Correct Answer: CE