Oracle 1Z0-062 Online Practice Questions and Exam Preparation

Oracle 1Z0-062 Online Questions & Answers

• Question 171:

  You determine that database performance is sub-optimal due to hard parsing statements. Automatic Shared Memory Management (ASMM) is disabled for your database instance. Which tool would you use to get advice on how to improve performance?

  A. Memory Advisor for the PGA
  B. SQL Access Advisor
  C. Memory Advisor for the shared pool
  D. SQL Tuning Advisor
  C. Memory Advisor for the shared pool

  ---

  References: http://docs.oracle.com/cd/E25178_01/server.1111/e10897/montune.htm#CHDGFCFJ

• Question 172:

  In your database instance, the UNDO_RETENTION parameter is set to 1000 and undo retention is not guaranteed for the fixed size undo tablespace.

  Which statement is true about undo retention?

  A. Undo is retained in the UNDO tablespace for 1000 seconds, and then moved to the SYSTEM tablespace to provide read consistency.
  B. Inactive undo is retained for at least 1000 seconds if free undo space is available.
  C. Inactive undo is retained for 1000 seconds even if new transactions fall due to lack of space in the undo tablespace.
  D. Undo becomes expired obsolete after 1000 seconds.
  B. Inactive undo is retained for at least 1000 seconds if free undo space is available.

• Question 173:

  What is the effect of setting the STATISTICS_LEVEL initialization parameter to BASIC?

  A. Optimizer statistics are collected automatically.
  B. Only timed operating system (OS) statistics and plan execution statistics are collected.
  C. Automatic Workload Repository (AWR) snapshots are not generated automatically.
  D. The Oracle server dynamically generates the necessary object-level statistics on tables as part of query optimization.
  C. Automatic Workload Repository (AWR) snapshots are not generated automatically.

  ---

  References: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214

• Question 174:

  You plan to implement the distributed database system in your company. You invoke Database Configuration Assistant (DBCA) to create a database on the server. During the installation, DBCA prompts you to specify the Global Database

  Name.

  What must this name be made up of?

  A. It must be made up of a database name and a domain name.
  B. It must be made up of the value in ORACLE_SID and HOSTNAME.
  C. It must be made up of the value that you plan to assign for INSTANCE_NAME and HOSTNAME.
  D. It must be made up of the value that you plan to assign for ORACLE_SID and SERVICE_NAMES.
  A. It must be made up of a database name and a domain name.

  ---

  Using the DBCA to Create a Database (continued)

  3.

  Database Identification: Enter the Global Database Name in The form database_name.domain_name, and the system identifier (SID). The SID defaults lo the database name and uniquely identifies the instance associated with the database.

  4.

  Management Options: Use this page to set up your database so that it can be managed with Oracle Enterprise Manager. Select the default: "Configure the Database with Enterprise Manager." Optionally, this page allows you to configure alert notifications and daily disk backup area settings.

  Note: Yon must configure the listener before you can configure Enterprise Manager (as shown earlier).

• Question 175:

  Which two files must you copy from the Oracle home of the database that is being upgraded to the new Oracle home for Oracle Database 12c? (Choose three.)

  A. the tnsnames.ora file
  B. the sqlnet.ora file
  C. the initialization parameter file
  D. the password file
  E. the listener.ora file
  A. the tnsnames.ora file
  B. the sqlnet.ora file
  E. the listener.ora file

  ---

  References: https://docs.oracle.com/cd/E11882_01/server.112/e23633/afterup.htm#UPGRD52747

• Question 176:

  You use multiple temporary tables frequently in your database. Which two are benefits of configuring temporary undo? (Choose two.)

  A. Performance improves because less redo is written to the redo log.
  B. Temporary undo reduces the amount of undo stored in undo tablespaces.
  C. Performance improves because data manipulation language (DML) operations performed on temporary tables do not use the buffer cache.
  D. Performance improves because no redo and undo are generated for the temporary tables.
  A. Performance improves because less redo is written to the redo log.
  B. Temporary undo reduces the amount of undo stored in undo tablespaces.

• Question 177:

  Examine the query and its output:

  

  What might have caused three of the alerts to disappear?

  A. The threshold alerts were cleared and transferred to DBA_ALERT_HISTORY.
  B. An Automatic Workload Repository (AWR) snapshot was taken before the execution of the second query.
  C. An Automatic Database Diagnostic Monitor (ADOM) report was generated before the execution of the second query.
  D. The database instance was restarted before the execution of the second query.
  D. The database instance was restarted before the execution of the second query.

• Question 178:

  All of your live databases run in ARCHIVELOG mode.

  On the SALES database there is no LEVEL 0 backup.

  Examine these commands issued against the SALES database target:

  RMAN> BACKUP AS BACKUPSET DATABASE;

  ..

  backup set complete, elapsed time: 00:00:03

  RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

  What is true about the execution of these commands?

  A. A Level 0 backup of the database is created instead of the Level 1.
  B. A backup is created which only contains blocks that have changed since BACKUP AS BACKUPSET command.
  C. It throws an error because incremental level 1 backups require image copy backups as the Level 0 backup.
  D. It throws an error because a Level 0 backup does not exist.
  B. A backup is created which only contains blocks that have changed since BACKUP AS BACKUPSET command.

• Question 179:

  You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema.

  Examine the following steps:

  1.

  Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (`SH', `CUSTOMERS') FROM dual statement.

  2.

  Execute the DBMS_STATS.SEED_COL_USAGE (null, `SH', 500) procedure.

  3.

  Execute the required queries on the CUSTOMERS table.

  4.

  Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (`SH', `CUSTOMERS') FROM dual statement.

  Identify the correct sequence of steps.

  A. 3, 2, 1, 4
  B. 2, 3, 4, 1
  C. 4, 1, 3, 2
  D. 3, 2, 4, 1
  B. 2, 3, 4, 1

  ---

  Step 1 (2). Seed column usage Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload. Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for some of your longer running queries to ensure column group information is recorded for these queries. Step 3. (1) Create the column groups At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage information captured during the monitoring window. You simply have to call the DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema name and the table name. From then on, statistics will be maintained for each column group whenever statistics are gathered on the table.

  Note:

  *

  DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object.

  *

  The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same table. While the optimizer has traditionally analyzed the distribution of values within a column, he does not collect value-

  based relationships between columns.

  *

  Creating extended statisticsHere are the steps to create extended statistics for related table columns withdbms_stats.created_extended_stats:

  1 - The first step is to create column histograms for the related columns.2 ?Next, we run dbms_stats.create_extended_stats to relate the columns together. Unlike a traditional procedure that is invoked via an execute ("exec") statement,

  Oracle extended statistics are created via a select statement.

• Question 180:

  Examine the following impdp command to import a database over the network from a pre-12c Oracle database (source):

  

  Which three are prerequisites for successful execution of the command? (Choose three.)

  A. The import operation must be performed on the target database by a user with the DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the DATAPUMP_EXP_FULL_DATABASE role on the source database.
  B. All user-defined tablespaces must be in read-only mode on the source database.
  C. The export dump file must be created before starting the import on the target database.
  D. The source and target database must be running on the same operating system (OS) with the same endianness.
  E. The impdp operation must be performed by the same user that performed the expdp operation.
  A. The import operation must be performed on the target database by a user with the DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the DATAPUMP_EXP_FULL_DATABASE role on the source database.
  B. All user-defined tablespaces must be in read-only mode on the source database.
  D. The source and target database must be running on the same operating system (OS) with the same endianness.

  ---

  In this case we have run the impdp without performing any conversion if endian format is different then we have to first perform conversion.