Oracle 1Z0-067 Online Practice Questions and Exam Preparation

Oracle 1Z0-067 Online Questions & Answers

• Question 111:

  Examine the output from v$DIAG_INFO:

  

  Which statement is true about the Automatic Diagnostic Repository (ADR)?

  A. The SQL trace file for a session is stored in Diag Trace
  B. The text alert log is available in Diag Trace
  C. The XML version of the alert log is available in Diag Trace
  D. An Automatic Database Diagnostic Management (ADDM) report is generated and stored in the Health Monitor whenever an incident occurs
  B. The text alert log is available in Diag Trace

• Question 112:

  Examine the command to duplicate a database:

  RMAN> DUPLICATE TARGET DATABASE TO cdb PLUGGABLE DATABASE pdb1, pdb5;

  Which two statements are true about the DUPLICATE command?

  A. The SPFILE is copied along with the data files of the pluggable databases (PDBs).
  B. The root and the seed database in the container database (CDB) are also duplicated.
  C. A backup of pdb1 and pd5 must exist before executing the command.
  D. The duplicate command first creates a backup, and then duplicates the PDBs by using the backup.
  E. An auxiliary instance must be started with the initialization parameter ENABLE_PLUGGABLE_DATABASE set to TRUE.
  B. The root and the seed database in the container database (CDB) are also duplicated.
  E. An auxiliary instance must be started with the initialization parameter ENABLE_PLUGGABLE_DATABASE set to TRUE.

  ---

  Do backups of the source database exist? The principal advantage of active database duplication is that it does not require source database backups. Active duplication copies mounted or online database files over a network to the auxiliary instance. One disadvantage of this technique is the negative performance effect on the network. Another disadvantage is that the source database is running processes required to transfer the files to the auxiliary host, thereby affecting the source database and production workload. If the source database backups exist, and if the effect on the network is unacceptable, then backup-based duplication may be a better option. You can copy backups to temporary storage and transfer them manually to the destination host. If duplication is made with a connection to the target or the recovery catalog, then the backup files on the destination host must have the same file specification as they had on the source host. Otherwise, this is not a requirement.

• Question 113:

  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 SELECTDBMS_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.reportwcol_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 statistics

  Here 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 114:

  Which two statements are true about Resource Manager plans for individual pluggable databases (PDB plans) in a multitenant container database (CDB)?

  A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB.
  B. In a PDB plan, subplans may be used with up to eight consumer groups.
  C. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups across all PDBs in the CDB.
  D. If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically calculated.
  E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.
  A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB.
  E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.

  ---

  A: Setting a PDB resource plan is optional. If not specified, all sessions within the PDB are treated equally. In a non-CDB database, workloads within a database are managed with resource plans. In a PDB, workloads are also managed with resource plans, also called PDB resource plans. The functionality is similar except for the following differences: Non-CDB Database Multi-level resource plans Up to 32 consumer groups Subplans PDB Database Single-level resource plans only Up to 8 consumer groups (Not B) No subplans

• Question 115:

  Which statement is true regarding the creation of nested plans using the Oracle Resource Manager?

  A. Only one nested subplan is allowed per parent plan
  B. The plans can be nested up to four levels.
  C. Nested plans only control resources used by recursive SQL
  D. Nested plans control only the degree of parallelism and I/O requests
  E. Each nested plan gets a proportion of the CPU resources assigned to its parent group.
  E. Each nested plan gets a proportion of the CPU resources assigned to its parent group.

• Question 116:

  Which three statements are true about Scheduler Windows? (Choose three.)

  A. Only one window can be open at one time.
  B. Consumer groups are associated with windows.
  C. Windows work with job classes to control resource allocation.
  D. A database service name must be provided during window creation.
  E. The Resource Manager can automatically start windowed jobs or change resource allocation among windowed jobs.
  A. Only one window can be open at one time.
  C. Windows work with job classes to control resource allocation.
  D. A database service name must be provided during window creation.

  ---

  Reference: https://docs.oracle.com/cd/E18283_01/server.112/e17120/schedover002.htm#insertedID9

• Question 117:

  LDAP_DIRECTORY_SYSAUTH is set to YES.

  Users requiring DBAs access have been granted the sysdba enterprise role in Oracle Internet Directory(OID).

  SSL has been configure for the database and OLD and the password file has been configured for the database.

  User scott with sysdba privilege tries to connect remotely using this command:

  $sqlplusscott/tiger@DB0l As sysdba where DB01 is the net service name.

  Which authentication method will be attempted first?

  A. authentication by password file
  B. authentication by using certificates over SSL
  C. authentication by using the Oracle Internet Directory
  D. authentication by using the local OS of the database server
  A. authentication by password file

  ---

  If the database is configured to use a password file for remote authentication, Oracle Database checks the password file first.

  Reference:

  http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm

• Question 118:

  Examine the commands executed in CDB$ROOT of your multitenant container database (CDB) that has multiple pluggable databases (PDB):

  SQL> CREATE ROLE c ##role1 CONTAINER-ALL;

  SQL> GRANT CREATE SESSION, CREATE TABLE TO c##role1 CONTAINER'ALL;

  SQL>CREATE USER c##admin IDENTIFIED BY orcl123;

  SQL>GRANT c##role1 TO c##admin CONTAINER=ALL;

  SQL> GRANT SELECT ON DBA_USERS to c##role1 CONTAINER*ALL;

  Which statement is true about granting the select privilege on the DBA_users view to the c##ROLE1role?

  A. The command fails and gives an error because object privileges cannot be granted to a common user.
  B. The command fails because container is not set to current.
  C. The command succeeds and the common user c##admin can create a session and query the DBA_users view in CDB$ROOT and all the PDBs.
  D. The command succeeds and the common user c##admin can create a session in CDB$ROOT and all the PDBs, but can only query the dba_users view in CDB$ROOT
  E. The command succeeds and the common user c##admin can create a session and query the DBA users view only in CDB$ROOT
  C. The command succeeds and the common user c##admin can create a session and query the DBA_users view in CDB$ROOT and all the PDBs.

• Question 119:

  Which three statements are true about the database instance startup after an instance failure?

  A. The RECO process recovers the uncommitted transactions at the next instance startup.
  B. Online redo log files and archived redo log files are required to complete the rollback stage of instance recovery.
  C. Uncommitted changes are rolled back to ensure transactional consistency.
  D. The SMON process automatically performs the database recovery.
  E. Media recovery is required to complete the database recovery.
  F. Changes committed before the failure, which were not written to the data files, are re-applied.
  C. Uncommitted changes are rolled back to ensure transactional consistency.
  D. The SMON process automatically performs the database recovery.
  F. Changes committed before the failure, which were not written to the data files, are re-applied.

• Question 120:

  Which two statements are true regarding SecureFile lobs?

  A. The amount of undo retained is user controlled.
  B. They can be used only for nonpartitioned tables.
  C. Fragmentation is minimized by using variable-sized chunks.
  D. They support random reads and writes of encrypted LOB data.
  C. Fragmentation is minimized by using variable-sized chunks.
  D. They support random reads and writes of encrypted LOB data.

  ---

  References: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB46