Oracle Oracle Certifications 1Z0-067 Questions & Answers

• Question 201:

  Users report this error message when inserting rows into the orders table:

  ERROR atline1:

  ORA-01654f:unable to extend index USERS.ORDERS_IND by 8in tablespace INDEXES

  You determine that the indexes tablespace is out of space and there is no free space on the filesystem

  used by the Oracle database.

  Which two must you do to fix this problem without affecting currently executing queries?

  A. drop and re-create the index

  B. coalesce the orders,,ind index

  C. coalesce the indexes tablespace

  D. perform an on line table rebuild using dbns_redefir.ition.

  E. rebuild the index online moving it to another tablespace that has enough free space for the index

  Correct Answer: BE

  Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm#i1006864

• Question 202:

  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.

  Correct Answer: BC

  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

• Question 203:

  You must unload data from the orders, order_items, and products database tables to four files using the External Tables.

  CREATE TABLE orders_ext (order_id, order_date, product_id, product_name,quantity) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext.dir LOCATION (`ordersl.dmp','orders2.dmp','orders3.dmp','lorders4.dmp') ) PARALLEL AS SELECT o.order_id,o.order_date,p.product_id,p.product_name,i.quantity FROM orders o,productsp,order_itemsi WHERE o.orderjd = i.order_id and i.product_id = p.product_id;

  You execute the command shown in the Exhibit, but only two files are created. Which parameter must be changed so that four files are created?

  A. TYPE

  B. LOCATION

  C. PARALLEL

  D. DEFAULT DIRECTORY

  E. ORGANIZATION EXTERNAL

  Correct Answer: C

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm#i1007483

• Question 204:

  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)

  Correct Answer: B

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

• Question 205:

  You install "Oracle Grid Infrastructure for a standalone server" on a host on which the orcl1 and orcl2 databases both have their instances running.

  Which two statements are true?

  A. Both orcl1 and orcl2 are automatically added to the Oracle Restart configuration.

  B. All database listeners running from the database home are automatically added to the Oracle Restart configuration.

  C. The srvct1 add database command must be used to add orcl1 and orcl2 to the Oracle Restart configuration.

  D. The crsct1 start has command must be used to start software services for Oracle Automatic Storage Management (ASM) after the "Oracle Grid Infrastructure for a standalone server" installation is complete.

  E. All databases subsequently created by using the Database Configuration Assistant (DBCA) are automatically added to the Oracle Restart configuration.

  Correct Answer: CE

  https://docs.oracle.com/cd/E18283_01/server.112/e17120/restart001.htm

• Question 206:

  In your multitenant container database (CDB) that contains pluggable databases (PDBs), the hr user

  executes the following commands to create and grant privileges on a procedure:

  CREATEORREPLACEPROCEDUREcreate_test_v

  (v_emp_idNUMBER,v_enameVARCHAR2,v_SALARYNUMBER,v_dept_idNUMBER)

  BEGIN

  INSERT INTO hr.test VALUES (v_emp_id, v_ename, v salary, v_dept_id);

  END;

  /

  GRANT EXECUTE ON CREATE_TEST TO John, jim, smith, king;

  How can you prevent users having the execute privilege on the create_test_v procedure from inserting

  values into tables on which they do not have any privileges?

  A. Create the create_test procedure with definer's rights.

  B. Grant the execute privilege to users with grant option on the create_test procedure.

  C. Create the create_test procedure with invoker's rights.

  D. Create the create_test procedure as part of a package and grant users the execute privilege on the package.

  Correct Answer: C

  You can control access to privileges that are necessary to run user-created procedures by using definer's rights, which execute with the privileges of the owner, or with invoker's rights, which execute with the privileges of the user running the procedure.

• Question 207:

  For your database, an incremental level 1 backup is taken every week day. On Tuesday, before the backup is performed, you add a new tablespace.

  You execute the command: RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

  Which statement is true about the execution of the command?

  A. It returns an error because there is no level 0 backup available for new data files.

  B. It performs an image copy backup of new data files, and a level 1 incremental backup of all other data files.

  C. It performs a level-0 backup of all data files including those that belong to the new tablespace.

  D. It performs an image copy backup of all data files including those that belong to the new tablespace.

  E. It performs a backup as a backup set of all data files including those that belong to the new tablespace.

  Correct Answer: B

  References: https://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup004.htm

• Question 208:

  Which three conditions must be true for unused block compression to be used automatically while performing backups by using RMAN?

  A. The compatible initialization parameter is set to 10.2 or higher.

  B. There are no guaranteed restore points defined for the database.

  C. The default device for the backup must be set to disk.

  D. The tablespaces are locally managed.

  E. The fast recovery area is less than 50 percent free.

  Correct Answer: ABD

  About Unused Block Compression When employing unused block compression, RMAN skips reading, and backing up, any database blocks that are not currently allocated to some database object. This is regardless of whether those blocks had previously been allocated. So if a database table is dropped, RMAN will not back up the space that was occupied by that table until new objects are created in that space. Unused block compression is used automatically when the following conditions are true: -The COMPATIBLE initialization parameter is set to 10.2 or higher. -There are currently no guaranteed restore points defined for the database. -The data file is locally managed. -The data file is being backed up to a backup set as part of a full backup or a level 0 incremental backup. -The backup set is created on disk, or Oracle Secure Backup is the media manager. References: http://docs.oracle.com/database/121/BRADV/rcmcncpt.htm#BRADV89481

• Question 209:

  You are administering a multitenant container database (CDB).

  Identify two ways to access a pluggable database (PDB) that is open in read-only mode.

  A. by using the connect statement as a local user having only the set container privilege

  B. by using easy connect

  C. by using external authentication

  D. as a common user with the set container privilege

  E. by executing the alter session set container command as a local user

  Correct Answer: BD

  Reference: https://docs.oracle.com/database/121/ADMIN/cdb_pdb_admin.htm#ADMIN13861

• Question 210:

  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

  Correct Answer: D

  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