Oracle 1Z0-062 Online Practice Questions and Exam Preparation

Oracle 1Z0-062 Online Questions & Answers

• Question 331:

  Which three statements are true about Oracle Data Pump? (Choose three.)

  A. IMPDP can be used to change target data file names, schemas, and tablespaces during import.
  B. The DBMS_DATAPUMP PL/SQL package can be used independently of Data Pump clients to perform export and import operations.
  C. EXPDP and IMPDP are the client components of Oracle Data Pump.
  D. Oracle Data Pump export and import operations can be performed only by users with the SYSDBA privilege.
  E. IMPDP always use the conventional path insert method to import data.
  A. IMPDP can be used to change target data file names, schemas, and tablespaces during import.
  B. The DBMS_DATAPUMP PL/SQL package can be used independently of Data Pump clients to perform export and import operations.
  C. EXPDP and IMPDP are the client components of Oracle Data Pump.

  ---

  References: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL2880

• Question 332:

  A database is open READ WRITE and the instance has multiple sessions some of which have active transactions. You execute this command:

  SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

  Which three are true about the active transactions? (Choose three.)

  A. They may issue COMMIT or ROLLBACK statements
  B. They are suspended and unable to issue any statements
  C. They may continue to issue DML statements
  D. They are rolled back automatically
  E. They may continue to issue queries
  F. They are terminated immediately
  B. They are suspended and unable to issue any statements
  D. They are rolled back automatically
  F. They are terminated immediately

• Question 333:

  The following parameter are set for your Oracle 12c database instance:

  OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

  You want to manage the SQL plan evolution task manually. Examine the following steps:

  1.

  Set the evolve task parameters.

  2.

  Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.

  3.

  Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK function.

  4.

  Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.

  5.

  Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function.

  Identify the correct sequence of steps:

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

  ---

  * Evolving SQL Plan Baselines

  

  *

  2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.

  This function creates an advisor task to prepare the plan evolution of one or more plans for a specified SQL statement. The input parameters can be a SQL handle, plan name or a list of plan names, time limit, task name, and description.

  1. Set the evolve task parameters.

  SET_EVOLVE_TASK_PARAMETER

  This function updates the value of an evolve task parameter. In this release, the only valid parameter is TIME_LIMIT.

  4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.

  This function executes an evolution task. The input parameters can be the task name, execution name, and execution description. If not specified, the advisor generates the name, which is returned by the function.

  3: IMPLEMENT_EVOLVE_TASK

  This function implements all recommendations for an evolve task. Essentially, this function is equivalent to using ACCEPT_SQL_PLAN_BASELINE for all recommended plans. Input parameters include task name, plan name, owner name,

  and execution name.

  5. Report the task outcome by using the DBMS_SPM_EVOLVE_TASK function.

  This function displays the results of an evolve task as a CLOB. Input parameters include the task name and section of the report to include.

• Question 334:

  A redaction policy was added to the SAL column of the SCOTT.EMP table:

  

  All users have their default set of system privileges.

  For which three situations will data not be redacted? (Choose three.)

  A. SYS sessions, regardless of the roles that are set in the session
  B. SYSTEM sessions, regardless of the roles that are set in the session
  C. SCOTT sessions, only if the MGR role is set in the session
  D. SCOTT sessions, only if the MGR role is granted to SCOTT
  E. SCOTT sessions, because he is the owner of the table
  F. SYSTEM session, only if the MGR role is set in the session
  A. SYS sessions, regardless of the roles that are set in the session
  B. SYSTEM sessions, regardless of the roles that are set in the session
  D. SCOTT sessions, only if the MGR role is granted to SCOTT

• Question 335:

  Which two statements are true about the Database Configuration Assistant (DBCA) in Oracle Database 12c? (Choose two.)

  A. It can upgrade an Oracle Database from an Older release to 12c.
  B. It can unplug a PDB from a CDB.
  C. It can plug in a PDB into a CDB.
  D. It never creates an Oracle 12c database including the sample schemas.
  E. It can migrate an Oracle 12c database to a different platform.
  F. It can create a Pluggable Database (PDB) in a Container Database (CDB).
  B. It can unplug a PDB from a CDB.
  F. It can create a Pluggable Database (PDB) in a Container Database (CDB).

  ---

  Reference: https://docs.oracle.com/database/121/UPGRD/upgrade.htm#UPGRD003

• Question 336:

  Which three statements are true about Oracle checkpoint processing? (Choose three.)

  A. Frequent thread checkpoints can degrade database performance
  B. Database Writer (DBWn) processes write checkpoint information to datafile headers and the control file
  C. It reduces the recovery time from instance failures
  D. Incremental checkpoints write some dirty buffers to the datafiles and unwritten redo to the online redo logs.
  E. Thread checkpoints ensure that all dirty buffers are written to data files during a normal shutdown
  B. Database Writer (DBWn) processes write checkpoint information to datafile headers and the control file
  C. It reduces the recovery time from instance failures
  E. Thread checkpoints ensure that all dirty buffers are written to data files during a normal shutdown

• Question 337:

  Which two statements are true about SQL *Loader Express Mode in an Oracle 12c database? (Choose two.)

  A. The DEGREE_OF_PARALLELISM parameter is set to AUTO.
  B. You cannot have multiple SQL *Loader data files.
  C. If no data file is specified, it assumes the data file to be .dat in the current directory and uses it.
  D. You can have multiple bad files created when loading in parallel.
  E. You can selectively load rows into a table based on a filter.
  A. The DEGREE_OF_PARALLELISM parameter is set to AUTO.
  C. If no data file is specified, it assumes the data file to be .dat in the current directory and uses it.

  ---

  References: https://docs.oracle.com/database/121/SUTIL/GUID-0F35B551-861B-450D-8BF3-2312893A67D7.htm#SUTIL3951

• Question 338:

  A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions

  The table has a local prefixed, range partitioned index.

  Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.

  This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month.

  You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned.

  Which three methods could transparently help to achieve this result? (Choose three.)

  A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.
  B. Using a partial local Index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
  C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
  D. Converting the partitioned table to a partitioned view that does a UNION ALL query on the monthly tables, which retains the existing local partitioned column.
  E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.
  F. Using a partial global index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
  A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.
  C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
  E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.

  ---

  Note:

  * Oracle 12c now provides the ability to index a subset of partitions and to exclude the others.

  Local and global indexes can now be created on a subset of the partitions of a table. Partial Global indexes provide more flexibility in index creation for partitioned tables. For example, index segments can be omitted for the most recent

  partitions to ensure maximum data ingest rates without impacting the overall data model and access for the partitioned object.

  Partial Global Indexes save space and improve performance during loads and queries. This feature supports global indexes that include or index a certain subset of table partitions or subpartitions, and exclude the others. This operation is

  supported using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions.

• Question 339:

  Examine these statements:

  DESC emp

  

  INSERT INTO emp VALUES ( 101, 'John', 12000);

  1 row created.

  SAVEPOINT after_insert;

  Savepoint created.

  UPDATE emp SET salary=16000 WHERE empno=101;

  1 row updated.

  SAVEPOINT after_update;

  Savepoint created.

  DELETE FROM emp WHERE empno=101;

  1 row deleted.

  SAVEPOINT after_delete;

  Savepoint created.

  ROLLBACK TO SAVEPOINT after_update;

  Rollback complete.

  INSERT INTO emp VALUES ( 102, 'Emma', 12000);

  ROLLBACK TO SAVEPOINT after_delete;

  Which is the result and effect of this ROLLBACK?

  A. The ROLLBACK backs out all DMLS before after_delete.
  B. The ROLLBACK backs out all DMLS until after_delete.
  C. The ROLLBACK throws an error.
  D. The ROLLBACK executes successfully but does not back out any DMLS.
  B. The ROLLBACK backs out all DMLS until after_delete.

• Question 340:

  Your database instance has the following parameter setting:

  OS_AUTHENT_PREFIX = OPS$

  You execute the following command:

  

  And then grant OPS$GUEST_USER the CREATE SESSION privilege.

  Which two statements are true? (Choose two.)

  A. GUEST_USER can query the tables created in the USERS tablespace by default.
  B. The authentication details for GUEST_USER are stored in the database password file.
  C. A local GUEST_USER OS account should exist before GUEST_USER can log on to the database.
  D. GUEST_USER can log on to the database without specifying a username and password.
  E. GUEST_USER is forced to change the password at the first login.
  C. A local GUEST_USER OS account should exist before GUEST_USER can log on to the database.
  D. GUEST_USER can log on to the database without specifying a username and password.