Oracle 1Z0-067 Online Practice Questions and Exam Preparation

Oracle 1Z0-067 Online Questions & Answers

• Question 221:

  Your database supports a Decision Support System (DSS) workload that involves the execution of complex queries. Currently, the database is running with peak workload. You want to analyze some of the most resource-intensive statements cached in the library cache.

  What must you run to receive recommendations on the efficient use of indexes and materialized views to improve query performance?

  A. SQL Performance Analyzer
  B. SQL Access Advisor
  C. SQL Tuning Advisor
  D. Automatic Workload Repository (AWR) report
  E. Automatic Database Diagnostic Monitor (ADDM)
  B. SQL Access Advisor

  ---

  References: http://docs.oracle.com/cd/B28359_01/server.111/b28275/tdppt_sqltune.htm#TDPPT160

• Question 222:

  Your database is running in ARCHIVELOG mode and flashback is enabled.

  In which scenario must you use RMAN backups for recovering tables and table partitions? (Choose the best answer.)

  A. when recovering data that was lost after a DDL operation modified the structure of a table
  B. when recovering dropped tables that are present in the recyclebin
  C. when recovering a table that has been logically corrupted
  D. when recovering a table to a point in time
  E. when recovering the data of a table to the desired point in time, which is more recent than the oldest available undo.
  A. when recovering data that was lost after a DDL operation modified the structure of a table

• Question 223:

  Which parameter must be set to which value to implement automatic PGA memory management?

  A. Set memory_target to zero.
  B. Set STATISTICS_LEVEL to BASIC.
  C. Set pga_aggregate_target to a nonzero value.
  D. Set pga_aggregate_target and sga_target to the same value.
  E. Set sgajtarget to zero.
  C. Set pga_aggregate_target to a nonzero value.

  ---

  When automatic memory management is not enabled, the default method for the instance PGA is automatic PGA memory management References: http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm

• Question 224:

  Which two statements are true about dropping a pluggable database (PDB)?

  A. A PDB must be in mount state or it must be unplugged.
  B. The data files associated with a PDB are automatically removed from disk.
  C. A dropped and unplugged PDB can be plugged back into the same multitenant container database (CDB) or other CDBs.
  D. A PDB must be in closed state.
  E. The backups associated with a PDB are removed.
  F. A PDB must have been opened at least once after creation.
  A. A PDB must be in mount state or it must be unplugged.
  C. A dropped and unplugged PDB can be plugged back into the same multitenant container database (CDB) or other CDBs.

  ---

  Reference: http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13658

• Question 225:

  A user issues a query on the sales table and receives the following error:

  ERROR at line 1:

  ORA-01565: error in identifying file '/u0l/app/oracle/oradata/ORCL/temp01.dbf'

  ORA-27037: unable to obtain file status

  Which two actions would you take to recover the temporary tablespace?

  A. Drop the tenp01.dbf file, and then re-create the temp file.
  B. Add a new temp file to the temporary tablespace and drop the temp01.dbf file.
  C. Shut down the database instance, start up the database instance in mount state, create a new temporary tablespace, and then open the database.
  D. Take the temporary tablespace offline, recover the missing temp file, and then bring the temporary tablespace online.
  E. Create a new temporary tablespace and assign it as the default to the user.
  B. Add a new temp file to the temporary tablespace and drop the temp01.dbf file.
  E. Create a new temporary tablespace and assign it as the default to the user.

• Question 226:

  You created a tablespace with this statement:

  CREATE BIGFILE TABLESPACE adtbs

  DATAFILE '/proddb/data/adtbs.dbf' SIZE 10G;

  The tablespace is nearly full and you need to avoid any out of space errors for the load of a 5 gig table.

  Which two alter statements will achieve this?

  A. ALTER TA3LESPACE adtbs RESI2E 20G;
  B. ALTER TA3LESPACE adtbs ADD DATAFILE;
  C. ALTER TABLESPACE adtbs AUTOEXTEND ON;
  D. ALTER TA3LESPACE adtbs ADD DATAFILE '/proddb/data/adtbsl.dbf' SIZE 1QG;
  E. ALTER TA3LESPACE adtbs MODIFY DATAFILE '/proddb/data/adtbs.dbf AUTOEXTEND ON;
  A. ALTER TA3LESPACE adtbs RESI2E 20G;
  C. ALTER TABLESPACE adtbs AUTOEXTEND ON;

  ---

  References: http://www.techonthenet.com/oracle/tablespaces/alter_tablespace.php

• Question 227:

  Which two statements are true about the Automatic Diagnostic Repository (ADR)?

  A. The ADR base is shared across multiple instances.
  B. The ADR base keeps all diagnostic information in binary format.
  C. The ADR can be used to store statspack snapshots to diagnose database performance issues.
  D. The ADR can be used for problem diagnosis even when the database instance is down.
  E. The ADR is used to store Automatic Workload Repository (AWR) snapshots.
  A. The ADR base is shared across multiple instances.
  D. The ADR can be used for problem diagnosis even when the database instance is down.

  ---

  Reference: https://oracle-base.com/articles/11g/automatic-diagnostics-repository-11gr1

• Question 228:

  Which two statements are true about recovering logically corrupted tables or table partitions from an RMAN backup?

  A. Tables or table partitions can be recovered by using an auxiliary instance only.
  B. Tables or table partitions with a foreign key cannot be recovered.
  C. Tables or table partitions can be recovered only when the database is in mount state.
  D. Tables or table partitions from the system and sysaux tablespaces cannot be recovered.
  E. Tables with not null constraints cannot be recovered.
  A. Tables or table partitions can be recovered by using an auxiliary instance only.
  D. Tables or table partitions from the system and sysaux tablespaces cannot be recovered.

  ---

  References:

  https://www.pythian.com/blog/table-recovery-with-rman-in-database-12c/ https://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV695

• Question 229:

  In which situation can you use Flashback Database?

  A. when undoing a shrink data file operation
  B. when retrieving a dropped tablespace
  C. when returning to a point in time before the restoration or re-creation of a control file
  D. when returning to a point in time before the most recent open resetlogs operation
  D. when returning to a point in time before the most recent open resetlogs operation

  ---

  TO BEFORE RESETLOGS Returns the database to its state including all changes up to the SCN of the most recent OPEN RESETLOGS. Note: FLASHBACK DATABASE can only return the database to a point before the most recent OPEN

  RESETLOGS operation if your database has been upgraded to Oracle Database 10g Release 2 or later.

  Reference:

  http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta023.htm#RCMRF194

• Question 230:

  Evaluate these statements:

  CREATE TABLE purchase_orders

  (po_idNUMBER(4),

  po_dateTIMESTAMP,

  supplier_idNUM8ER(6),

  po_totalNUMBER(8,2), CONSTRAINT order_pk PRIMARY KEY(po_id))

  PARTITIONBYRANGE(po_date)

  (PARTITIONQ1 VALUESLESSTHAN (TO_DATE('01-apr-2007','dd-mon-yyyy')), PARTITIONQ2VALUESLESSTHAN(TO_DATE('01-jul-2007','dd-mon-yyyy')), PARTITIONQ3VALUESLESSTHAN (TO~DATE('01-oct-2007','dd-non-yyyy')),

  PARTITIONQ4VALUESLESSTHAN (TO_DATE('Ol-jan-2008','dd-non-yyyy' )));

  CREATETABLEpurchase_order_items

  (po_idNUM3ER(4)NOTNULL,

  product_idNUMBER(6)NOTNULL,

  unit_prlceNUMBER(8,2),

  quantity NUMBER(8),

  CONSTRAINTpo_items_f k

  FOREIGNKEY(po_id)REFERENCESpurchase_orders(po_id) )

  PARTITIONBYREFERENCE(po_items_fk);

  Which two statements are true?

  A. Partitions of purchase_order_items are assigned unique names based on a sequence.
  B. The purchase_orders and purchase_order_items tables are created with four partitioneach.
  C. purchase_order_items table partitions exist in the same tablespaces as the purchase_orders table partitions.
  D. The purckase_order_:teks table inherits the partitioning key by duplicating the key columns from the parent table.
  E. Partition maintenance operations on the purchase_order_items table require disabling the foreign key constraint.
  B. The purchase_orders and purchase_order_items tables are created with four partitioneach.
  C. purchase_order_items table partitions exist in the same tablespaces as the purchase_orders table partitions.

  ---

  The following example creates a parent table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition. Partitions of a reference-partitioned table will collocate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition. The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it will inherit its name from the corresponding partition in the parent table. Reference: http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm#BAJDDEEC