Oracle Oracle Certifications 1Z0-063 Questions & Answers

• Question 21:

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

  ORA-01654f:unable to extend index USERS.ORDERS_IND by 8 in 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? (Choose two.)

  A. drop and re-create the index

  B. coalesce the order.ind index

  C. coalesce the indexes tablespace

  D. perform an on line table rebuild using dbms_redefinition

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

  Correct Answer: BE

• Question 22:

  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))

  PARTITION BY RANGE(po_date)

  (PARTITIONQ1 VALUES LESS THAN (TO_DATE('01-apr-2007','dd-mm-yyyy')),

  PARTITIONQ2VALUESLESSTHAN(TO_DATE('01-jul-2007','dd-mm-yyyy')),

  PARTITIONQ3VALUESLESSTHAN (TO~DATE('01-oct- 2007','dd-mm-yyyy')),

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

  CREATE TABLE purchase_order_items

  (po_idNUM3ER(4)NOT NULL,

  product_idNUMBER(6)NOT NULL,

  unit_price NUMBER(8,2),

  quantity NUMBER(8),

  CONSTRAINT po_items_f k

  FOREIGN KEY(po_id)REFERENCES purchase_orders(po_id))

  PARTITION BY REFERENCE(po_items_fk);

  Which two statements are true? (Choose two.)

  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 partition each.

  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

• Question 23:

  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? (Choose two.)

  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 24:

  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:

  CREATE OR REPLACE PROCEDURE create_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

• Question 25:

  You are administering a multitenant container database (CDB).

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

  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

• Question 26:

  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: B

  Reference:

  http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta023.htm (prerequisites, third para)

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

• Question 27:

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

  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

  Reference:

  http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#BRADV89481 (See unused block

  compression)

  https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#BRADV89481

• Question 28:

  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

  Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28275/tdppt_sqltune.htm#TDPPT160 (4th para)

• Question 29:

  Your database instance is started using an SPFILE. You are connected to cdb$root, as a DBA. You issue:

  SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;

  Which two statements are true about the statistics level parameter? (Choose two.)

  A. It is immediately set to all in the SPFILE and the CDB instance.

  B. It is immediately set to all in only those pluggable databases (PDBs) where the value is set to typical.

  C. It is immediately set to all only for cd3Sroot.

  D. It is immediately set to all in all PDBs where the statistics_level parameter is not set.

  E. It is set to all for all PDBs only in the SPFILE.

  Correct Answer: AB

• Question 30:

  Which two are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled? (Choose two.)

  A. Reduced logical I/O

  B. Reduced virtual memory utilization

  C. Improved Serial Execution performance

  D. Reduced physical I/O

  E. Reduced CPU utilization

  Correct Answer: BE