Oracle 1Z0-083 Online Practice Questions and Exam Preparation

Oracle 1Z0-083 Online Questions & Answers

• Question 101:

  In one of your databases, unified auditing is enabled and an SH.SALES table exists.

  You successfully execute these commands: SQL> CREATE AUDIT POLICY sales_pol ACTIONS select on sh.sales; SQL> AUDIT POLICY sales_pol;

  Which statement is true about SALES_POL?

  A. It succeeds and needs to be enabled to capture all SQL statements that require either the specified privileges or any privilege granted to the HR_ADMIN and role. OE_ADMIN
  B. It fails because system privileges cannot be granted with roles in the same audit policy.
  C. It succeeds and starts capturing only successful SQL statements for all users who have either the specified privileges or roles granted to them.
  D. It fails because the command does not specify when the unified audit policy should be enforced.
  A. It succeeds and needs to be enabled to capture all SQL statements that require either the specified privileges or any privilege granted to the HR_ADMIN and role. OE_ADMIN

• Question 102:

  Which two are true about SQL Performance Analyzer (SPA)? (Choose two.)

  A. It is integrated with the SQL Access Advisor.
  B. It predicts the impact of system changes on SQL workload response time.
  C. It provides before and after execution statistics for each SQL statement in the analysis task
  D. It offers fine-grained analysis of all the SQL statements in the analysis task as a group.
  E. SQL statements that were originally run concurrently are run concurrently by SPA.
  B. It predicts the impact of system changes on SQL workload response time.
  C. It provides before and after execution statistics for each SQL statement in the analysis task

  ---

  Explanation/Reference:

  Oracle Real Application Testing ... enables businesses to fully assess the outcome of a system changes in test or production. Oracle Real Application Testing is comprised of the following features: Performance Analyzer (SPA), SPA ...

  C:

  SQL Performance Analyzer executes the SQL statements captured in the SQL Tuning Set and generates execution plans and execution statistics for each statement

  For D , though group (summary) view is shown during analysis, i dont think that what they mean by fine grained

  Reference: https://www.oracle.com/technetwork/database/manageability/spa-ow09-131455.pdf (4)

• Question 103:

  The CATDB12c database contains an Oracle Database version 12c catalog schema owned by the RC12C user.

  The RCAT11 database contains an Oracle Database version 11g catalog schema owned by the RC11 user in which several databases are registered. Both the databases are open.

  You execute the RMAN commands:

  RMAN> CONNECT CATALOG rc12c/pass12c@catdb12c RMAN> IMPORT CATALOG rc11/pwdcat11@ract11

  Which two tasks are performed as part of the import? (Choose two.)

  A. Databases from the RC12C catalog are implicitly unregistered if the RC11 catalog has registered databases with same name.
  B. Recovery catalog is resynchronized with the control files of the registered databases.
  C. Imported databases are unregistered from the RC11 catalog.
  D. Local and global stored scripts that have the same name as existing scripts in the RC12C catalog are automatically renamed.
  E. All databases registered in the RC11 catalog are registered in the RC12C catalog.
  C. Imported databases are unregistered from the RC11 catalog.
  E. All databases registered in the RC11 catalog are registered in the RC12C catalog.

  ---

  Explanation/Reference:

  By default, the imported database IDs are unregistered from the source recovery catalog schema after a successful import. It is possible for global scripts, but not local scripts, to have name conflicts during import because the destination schema contains an object with the same name. In this case, RMAN renames the global script name to COPY OF script_name. For example, RMAN renames bp_cmd to COPY OF bp_cmd

  https://docs.oracle.com/database/121/RCMRF/rcmsynta026.htm#RCMRF198

• Question 104:

  Which two are true about RMAN backups when using a media manager to write backups to tape when there are only two tape drives? (Choose two.)

  A. SBT tape compression can be used even if no RMAN compression is configured.
  B. Any backup set written to the SBT device in this configuration can contain a maximum of two backup pieces.
  C. Any backup written to the SBT device in this configuration can contain a maximum of two backup sets.
  D. SBT tape compression and RMAN backup compression should be used in parallel.
  E. The SBT device should be configured to use PARALLELISM 2 to allow both tape drive to be used simultaneously.
  A. SBT tape compression can be used even if no RMAN compression is configured.
  E. The SBT device should be configured to use PARALLELISM 2 to allow both tape drive to be used simultaneously.

  ---

  Explanation/Reference:

  A. SBT tape compression can be used even if no RMAN compression is configured.

  B. Any backup set written to the SBT device in this configuration can contain a maximum of two backup pieces.

  C. Any backup written to the SBT device in this configuration can contain a maximum of two backup sets.

  D. SBT tape compression and RMAN backup compression should be used in parallel.

  E. The SBT device should be configured to use PARALLELISM 2 to allow both tape drive to be used simultaneously.

  A - sbt tape compression independently can be used on the TAPE side. Oracle recommends You should not use both tape compression provided by the media manager _and_ binary compression provided by RMAN. If the media manager compression is efficient, then it is usually the better choice. Using RMAN-compressed backup sets can be an effective alternative to reduce bandwidth used to move uncompressed backup sets over a network to the media manager, if the CPU overhead required to compress the data in RMAN is acceptable.

• Question 105:

  choose one You enable Flashback Data Archive for a table for which you want to track and save all transactions for four years. After some time, the requirement changes for keeping transactions from four to two years.

  You execute the following command to change the requirement: SQL> ALTER FLASHBACK ARCHIVE fdal MODIFY RETENTION 2 YEAR;

  What is the outcome?

  A. All historical data older than two years, if any, is purged from the flashback archive FDA1.
  B. An error is returned because the retention period cannot be reduced.
  C. All the flashback data archives are purged and the subsequently created flashback archives are maintained for two years
  D. All historical data older than two years, if any, is archived to flashback logs and the flashback archive is set to new retention time.
  A. All historical data older than two years, if any, is purged from the flashback archive FDA1.

• Question 106:

  Which two are true about a read-only Oracle home? (Choose two).

  A. It is enabled by performing a software-only Oracle Database installation and then running the roohctl -enable script.
  B. It requires the Rapid Home Provisioning service.
  C. Its configuration data and log files reside in subdirectories under the Oracle home.
  D. It is enabled by performing an Oracle Database installation with database creation and then running the roohctl -enable script.
  E. It contains an image that can be distributed across multiple servers.
  A. It is enabled by performing a software-only Oracle Database installation and then running the roohctl -enable script.
  E. It contains an image that can be distributed across multiple servers.

• Question 107:

  Examine these queries and their output:

  

  After a system crash, an instance restart and an attempted opening of the PDBs result in:

  

  Which two are true? (Choose two.)

  A. Data file 24 can be recovered while PDB2 is opened.
  B. Data file 24 must be recovered while the CDB is opened.
  C. Data file 24 can be recovered while CDB$ROOT and PDB$SEED are opened.
  D. Data file 24 cannot be recovered while the CDB is opened.
  E. Data file 24 must be recovered while PDB2 is closed.
  A. Data file 24 can be recovered while PDB2 is opened.
  C. Data file 24 can be recovered while CDB$ROOT and PDB$SEED are opened.

  ---

  Explanation/Reference:

  Datafile 24 belongs to PDB1.

  A. It can be recovered while CDB while PDB2 is opened, as it belongs to PDB1.

  C. the datafile belongs to PDB1 and CAN be recovered while CDB is opened or in mount state.

• Question 108:

  Which two are true about Oracle Optimizer Statistics, their use, and their collection? (Choose two.)

  A. The number of table rows is considered when evaluating the cost of accessing a table using an index.
  B. Index balanced B*Tree height is considered when evaluating the cost of using an index.
  C. The Statistics Advisor can help recommend the best way to gather statistics.
  D. Statistics collected using DBMS_STATS always yield the best optimizer result.
  E. The Statistics Advisor generates actions for all recommendations.
  B. Index balanced B*Tree height is considered when evaluating the cost of using an index.
  C. The Statistics Advisor can help recommend the best way to gather statistics.

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-advisor.html#GUID-D81A7708-FDA0-45BB-A6E2-103858B047AE

• Question 109:

  Which two are true about the automatic execution of operating system scripts when performing silent mode installation starting from Oracle Database 19c? (Choose two.)

  A. The response file can specify the root or sudo password.
  B. The installer will prompt for the root or sudo password.
  C. Silent install always runs operating scripts automatically.
  D. The response file must contain the root or sudo password.
  E. The response file can specify the path of the sudo program.
  B. The installer will prompt for the root or sudo password.
  E. The response file can specify the path of the sudo program.

  ---

  Explanation/Reference:

  #-------------------------------------------------------------------------------------# Specify the configuration method to be used for automatic root script execution.

  #

  # Following are the possible choices:

  # - ROOT

  # - SUDO

  #-------------------------------------------------------------------------------------oracle.install.db.rootconfig.configMethod=ROOT

  #-------------------------------------------------------------------------------------# Specify the absolute path of the sudo program.

  #

  # Applicable only when SUDO configuration method was chosen.

  #-------------------------------------------------------------------------------------oracle.install.db.rootconfig.sudoPath=

  #-------------------------------------------------------------------------------------# Specify the name of the user who is in the sudoers list.

  # Applicable only when SUDO configuration method was chosen.

  # Note:For Single Instance database installations,the sudo user name must be the username of the user installing the database.

  Reference: https://oraclefact.wordpress.com/2019/08/05/1-install-oracle-database-19c-with-automatic-root-scripts-execution/

• Question 110:

  Which statement correctly describes the SQL profiling performed by the SQL Tuning Advisor?

  A. It is a set of recommendations by the optimizer to create new indexes.
  B. It is a set of recommendations by the optimizer to restructure a SQL statement to avoid suboptimal execution plans.
  C. It is auxiliary information collected by the optimizer for a SQL statement to help use better joins orders.
  D. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.
  E. It is a set of recommendations by the optimizer to change the access methods used.
  D. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.