Oracle 1Z0-062 Online Practice Questions and Exam Preparation

Oracle 1Z0-062 Online Questions & Answers

• Question 221:

  Automatic Shared Memory Management is enabled for your database instance. You notice that there are SQL statements performing poorly because of repeated parsing activity.

  Which action generates recommendations to overcome the performance issues?

  A. running the Memory Advisor for the buffer cache
  B. running the Memory Advisor for the library cache
  C. running the Memory Advisor for the SGA
  D. running the Memory Advisor for the PGA
  B. running the Memory Advisor for the library cache

• Question 222:

  Examine this command:

  SQL > exec DBMS_STATS.SET_TABLE_PREFS (`SH', `CUSTOMERS', `PUBLISH', `false');

  Which three statements are true about the effect of this command? (Choose three.)

  A. Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
  B. Statistics collection is not done for the CUSTOMERS table when database stats are gathered.
  C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
  D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
  E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
  C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
  D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
  E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.

  ---

  *

  SET_TABLE_PREFS Procedure

  This procedure is used to set the statistics preferences of the specified table in the specified schema.

  *

  Example: Using Pending Statistics

  Assume many modifications have been made to the employees table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:

  EXEC DBMS_STATS.SET_TABLE_PREFS('hr', 'employees', 'PUBLISH', 'false');

  By setting the employees tables publish preference to FALSE, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending.

• Question 223:

  You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity feature of Oracle Database 12c. Examine the table definition:

  

  Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table? (Choose three.)

  A. The valid time columns employee_time_start and employee_time_end are automatically created.
  B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
  C. The valid time columns are not populated by the Oracle Server automatically.
  D. The valid time columns are visible by default when the table is described.
  E. Setting the session valid time using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries performed by the session.
  A. The valid time columns employee_time_start and employee_time_end are automatically created.
  B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
  C. The valid time columns are not populated by the Oracle Server automatically.

• Question 224:

  You want to prevent a group of users in your database from performing long-running transactions that consume huge amounts of space in the undo tablespace. If the quota for these users is exceeded during execution of a data manipulation language (DML) statement, the operation should abort and return an error. However, queries should still be allowed, even if users have exceeded the undo space limitation.

  How would you achieve this?

  A. Specify the maximum amount of quota a user can be allocated in the undo tablespace.
  B. Decrease the number of Interested Transaction List (ITL) slots for the segments on which these users perform transactions.
  C. Implement a profile for these users.
  D. Implement a Database Resource Manager plan.
  D. Implement a Database Resource Manager plan.

• Question 225:

  Examine these statements:

  

  Which three are true about the effects of executing these statements? (Choose three.)

  A. The HR account will get locked by Oracle after ten consecutive failed login attempts.
  B. User HR is permitted to create more than three concurrent sessions.
  C. Password complexity checking is enforced for user HR.
  D. User HR sessions will terminate after ten minutes of continuous inactivity.
  E. The HR account will be locked after three consecutive failed login attempts.
  F. The password complexity will not enforce on the HR account.
  B. User HR is permitted to create more than three concurrent sessions.
  E. The HR account will be locked after three consecutive failed login attempts.
  F. The password complexity will not enforce on the HR account.

• Question 226:

  Examine the parameters for your database instance:

  

  Which three statements are true about the process of automatic optimization by using cardinality feedback? (Choose three.)

  A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
  B. The optimizer can re optimize a query only once using cardinality feedback.
  C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
  D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
  E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.
  A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
  C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
  D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.

  ---

  C: During the first execution of a SQL statement, an execution plan is generated as usual.

  D: if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback. (not B)* Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plans for repeated executions. optimizer_dynamic_sampling

  optimizer_features_enable

  *

  dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. Note:

  *

  OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer. Range of values. 0 to 10

  *

  Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.

• Question 227:

  A database instance is started by using an SPFILE. The database is configured in ARCHIVELOG mode and the control file autobackup is configured. Daily full database backups are performed by using RMAN.

  You lost all control files due to media failure.

  Given the steps to recover from the error in random order:

  1.

  Shut down the instance, if it is not already down.

  2.

  Restore the control file from autobackup to a new location.

  3.

  Start the database instance to NOMOUNT state.

  4.

  Recover the database to the point of failure of the control file.

  5.

  Open the database with the RESETLOGS option.

  6.

  Mount the database.

  7.

  Update the SPFILE with the new location of the control file by using the ALTER SYSTEM command. Identify the correct sequence of the required steps.

  A. 1, 3, 2, 6, 7, 4, 5
  B. 1, 3, 7, 2, 6, 4, 5
  C. 1, 3, 2, 4, 5
  D. 1, 2, 6, 4, 5
  E. 1, 6, 2, 4, 5
  A. 1, 3, 2, 6, 7, 4, 5

• Question 228:

  The persistent configuration settings for RMAN have default for all parameters. Identify four RMAN commands that produce a multi-section backup.

  A. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;
  B. BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;
  C. BACKUP ARCHIVELOG ALL SECTION SIZE 25M;
  D. BACKUP TABLESPACE "TEMP" SECTION SIZE 10M;
  E. BACKUP TABLESPACE "UNDO" INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;
  F. BACKUP SPFILE SECTION SIZE 1M;
  G. BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;
  A. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;
  B. BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;
  E. BACKUP TABLESPACE "UNDO" INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;
  G. BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;

• Question 229:

  Your production database PROD uses file system storage. You want to migrate storage including the Fast Recovery Area for the PROD database to Oracle Automatic Storage Management (ASM) by using RMAN. You back up the entire database.

  What should the next step be in this migration process?

  A. enabling row movement for the database
  B. disabling Oracle Flashback Database if enabled
  C. opening the database in exclusive mode
  D. placing all tablespaces in read-only mode
  B. disabling Oracle Flashback Database if enabled

  ---

  References: https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#OSTMG89995

• Question 230:

  The HR schema exists in two databases, BOSTON and DENVER, and has the same password, HR.

  You have the CREATE DATABASE LINK and CREATE SESSION privileges on both the database.

  BOSTON is defined as a service name in the tnsnames.ora of both the databases.

  You plan to use the command:

  CREATE DATABASE LINK hr_link CONNECT to hr IDENTIFIED BY hr USING 'denver';

  What must be done to ensure only the HR user in the BOSTON database can access the HR schema in the DENVER database?

  A. Execute this command as HR user in the BOSTON database and SYS user in the DENVER database.
  B. Execute this command as SYS user in both the databases.
  C. Execute this command as HR user in the DENVER database.
  D. Execute this command as HR user in the BOSTON database.
  D. Execute this command as HR user in the BOSTON database.