Oracle 1Z0-062 Online Practice Questions and Exam Preparation

Oracle 1Z0-062 Online Questions & Answers

• Question 61:

  You are connected using SQL* Plus to a multitenant container database (CDB) with SYSDBA privileges and execute the following sequence statements: What is the result of the last SET CONTAINER statement and why is it so?

  

  A. It succeeds because the PDB_ADMIN user has the required privileges.
  B. It fails because common users are unable to use the SET CONTAINER statement.
  C. It fails because local users are unable to use the SET CONTAINER statement.
  D. If fails because the SET CONTAINER statement cannot be used with PDB$SEED as the target pluggable database (PDB).
  C. It fails because local users are unable to use the SET CONTAINER statement.

• Question 62:

  Which statement is true about Oracle Net Listener?

  A. It acts as the listening endpoint for the Oracle database instance for all local and non-local user connections.
  B. A single listener can service only one database instance and multiple remote client connections.
  C. Service registration with the listener is performed by the process monitor (PMON) process of each database instance.
  D. The listener.ora configuration file must be configured with one or more listening protocol addresses to allow remote users to connect to a database instance.
  E. The listener.ora configuration file must be located in the ORACLE_HOME/network/admin directly.
  C. Service registration with the listener is performed by the process monitor (PMON) process of each database instance.

  ---

  https://docs.oracle.com/database/121/CNCPT/process.htm

• Question 63:

  Which three statements are true about adaptive SQL plan management? (Choose three.)

  A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
  B. The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
  C. It adds new, bettor plans automatically as fixed plans to the baseline.
  D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
  E. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
  A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
  D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
  E. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.

  ---

  With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.

  Note:

  *

  The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the plan.

  *

  Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.

• Question 64:

  You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.

  Which method or feature should you use?

  A. Compare Period ADDM report
  B. AWR Compare Period report
  C. Active Session History (ASH) report
  D. Taking a new snapshot and comparing it with a preserved snapshot
  A. Compare Period ADDM report

• Question 65:

  You create a new pluggable database, HR_PDB, from the seed database. Which three tablespaces are created by default in HR_PDB? (Choose three.)

  A. SYSTEM
  B. SYSAUX
  C. EXAMPLE
  D. UNDO
  E. TEMP
  F. USERS
  A. SYSTEM
  B. SYSAUX
  E. TEMP

  ---

  *

  A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces.It can also contains other user created tablespaces in it.

  *

  Oracle Database creates both the SYSTEM and SYSAUX tablespaces as part of every database.

  *

  tablespace_datafile_clauses

  Use these clauses to specify attributes for all data files comprising the SYSTEM and SYSAUX tablespaces in the seed PDB.

  Incorrect:

  Not D: a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB.

  Note:

  *

  Example: CONN pdb_admin@pdb1

  SELECT tablespace_name FROM dba_tablespaces;

  TABLESPACE_NAME

  SYSTEM SYSAUX TEMP USERS

  SQL>

• Question 66:

  Your database is in ARCHIVELOG mode.

  You want to disable archiving for the database.

  Examine these steps:

  1.

  Execute the ALTER DATABASE NOARCHIVELOG command

  2.

  Execute SHUTDOWN IMMEDIATE

  3.

  Execute STARTUP MOUNT

  4.

  Set the DB_RECOVERY_FILE_DEST parameter to $ORACLE_HOME/dbs/

  5.

  Execute STARTUP NOMOUNT

  6.

  Open the database

  7.

  Execute SHUTDOWN TRANSACTIONAL

  Identify the required steps in the correct sequence.

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

  ---

  Reference: http://dba-oracle.com/bk_disable_archive_log_mode.htm

• Question 67:

  Which two would you recommend to an application developer for reducing locking conflicts on tables in an Oracle database? (Choose two.)

  A. Create objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) enabled.
  B. Avoid coding long-running transactions.
  C. Allow the database to handle locks ln default locking mode.
  D. Create objects in locally managed tablespaces with manual segment space management enabled.
  E. Enable undo retention guarantee.
  B. Avoid coding long-running transactions.
  C. Allow the database to handle locks ln default locking mode.

• Question 68:

  Your multitenant container (CDB) contains two pluggable databases (PDB), HR_PDB and ACCOUNTS_PDB, both of which use the CDB tablespace. The temp file is called temp01.tmp.

  A user issues a query on a table on one of the PDBs and receives the following error:

  ERROR at line 1:

  ORA-01565: error in identifying file `/u01/app/oracle/oradata/CDB1/temp01.tmp'

  ORA-27037: unable to obtain file status

  Identify two ways to rectify the error.

  A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.
  B. Shut down the database instance, restore the temp01.tmp file from the backup, and then restart the database.
  C. Take the temporary tablespace offline, recover the missing temp file by applying redo logs, and then bring the temporary tablespace online.
  D. Shutdown the database instance, restore and recover the temp file from the backup, and then open the database with RESETLOGS.
  E. Shut down the database instance and then restart the CDB and PDBs.
  A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.
  E. Shut down the database instance and then restart the CDB and PDBs.

  ---

  *

  Because temp files cannot be backed up and because no redo is ever generated for them, RMAN never restores or recovers temp files. RMAN does track the names of temp files, but only so that it can automatically re-create them when needed.

  *

  If you use RMAN in a Data Guard environment, then RMAN transparently converts primary control files to standby control files and vice versa. RMAN automatically updates file names for data files, online redo logs, standby redo logs, and temp files when you issue RESTORE and RECOVER.

• Question 69:

  Examine the following query output: You issue the following command to import tables into the hr schema:

  

  $ > impdp hr/hr directory = dumpdir dumpfile = hr_new.dmp schemas=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING: Y

  Which statement is true?

  A. All database operations performed by the impdp command are logged.
  B. Only CREATE INDEX and CREATE TABLE statements generated by the import are logged.
  C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.
  D. None of the operations against the master table used by Oracle Data Pump to coordinate its activities are logged.
  C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.

  ---

  Oracle Data Pump disable redo logging when loading data into tables and when creating indexes.

  The new TRANSFORM option introduced in data pumps import provides the flexibility to turn off the redo generation for the objects during the course of import. The Master Table is used to track the detailed progress information of a Data

  Pump job.

  The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks of lots of detailed information.

• Question 70:

  Which two statements correctly describe the relationship between data files and logical database structures? (Choose two.)

  A. A segment cannot span data files.
  B. A data file can belong to only one tablespace.
  C. An extent cannot span data files.
  D. The size of an Oracle data block in a data file should be the same as the size of an OS block.
  B. A data file can belong to only one tablespace.
  C. An extent cannot span data files.

  ---

  A single extent can never span data files. https://docs.oracle.com/database/121/CNCPT/logical.htm#CNCPT1095