Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 81:

  Which three statements are true about roles?

  A. All roles granted to a user are set on by default when the user logs in,

  B. Object privileges may not be granted to roles.

  C. The SET ROLE statement can disable one or more roles for a session.

  D. Roles must be password protected.

  E. Roles may be granted to roles.

  F. The SET ROLE statement can enable one or more roles for a session.

  Correct Answer: BCE

• Question 82:

  Examine the description of the PRODUCT_STATUS table:

  

  The status column contains the values 'in stock' or 'out of stock' for each row.

  Which two queries will execute successfully?

  A. SELECT prod_id Il q" 's not available" FROM product_status where status = 'OUT OF STOCK';

  B. SELECT prod_id II q'l's not available)' FROM product_status WHERE status = 'OUT OF STOCK';

  C. SELECT prod_id "CURRENT AVAILABILITY" II q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';

  D. SELECT prod_id I I q' ('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

  E. SELECT prod_id I I q' ('s not available)' ' CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';

  F. SELECT prod_id q's not available" FROM product_status WHERE status = 'OUT OF STOCK';

  Correct Answer: BD

• Question 83:

  Examine this command:

  CREATE UNDO TABLESPACE undotbs01

  DATAFILE `undotbs_01.dbf'

  SIZE 100M

  AUTOEXTEND ON;

  Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose two.)

  A. Add the SEGMENT SPACE MANAGEMENT AUTO clause

  B. Set UNDO_TABLESPACE to UNDOTBS01

  C. Add the NOLOGGING clause

  D. Make certain that the database operates in automatic undo management mode

  E. Add the ONLINE clause

  Correct Answer: BD

  SQL>alter system set undo_management=manual scope=spfile; Bounce instance.

  SQL> show parameter undo NAME TYPE VALUE

  temp_undo_enabled boolean FALSE

  undo_management string MANUAL

  undo_retention integer 900

  undo_tablespace string UNDOTBS1

  SQL> alter system set undo_tablespace='UNDOTBS2';

  alter system set undo_tablespace='UNDOTBS2'

  *

  ERROR at line 1:

  ORA-02097: parameter cannot be modified because specified value is invalid

  ORA-30014: operation only supported in Automatic Undo Management mode

• Question 84:

  You execute this command:

  

  During the export operation, you detach from the job by using CTRL+C and then execute this command:

  Export> STOP_JOB=immediate

  Are you sure you wish to stop the job ([yes]/no): yes

  Which two statements are true about the job? (Choose two.)

  A. You can no longer monitor it

  B. You can reattach to it and monitor it

  C. It is paused and can be resumed

  D. It continues to run in the background

  E. It terminates

  Correct Answer: BC

  see the example which is done on 12c version

  B. You can reattach to it and monitor it

  C. It is paused and can be resumed

  Reference: https://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

• Question 85:

  View the Exhibit and examine the description of the tables. You execute this SQL statement:

  

  Which three statements are true?

  A. The statement will fall if a row already exists in the sales table for product 23.

  B. The sales table has five foreign keys.

  C. A product can have a different unit price at different times.

  D. A customer can exist in many countries.

  E. The statement will execute successfully and a new row will be inserted into the sales table.

  F. The statement will fail because a subquery may not be contained in a values clause.

  Correct Answer: BCE

• Question 86:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

  The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.

  Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') FROM customers;

  B. SELECT REPLACE(TRIM(TRAILING 'son' FROM cust_last_name), 'An', 'O') FROM customers;

  C. SELECT INITCAP(REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O')) FROM customers;

  D. SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O') FROM customers;

  Correct Answer: D

• Question 87:

  The sales table has columns PROD_ID and QUANTITY_SOLD of data type number. Which two queries execute successfully? (Choose two.)

  A. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;

  B. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;

  C. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;

  D. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY proa_id HAVING COUNT(*) > 10;

  E. SELECT prcd_id FROM sales WHERE quantity_sold > 55000 AND COUNT (*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;

  Correct Answer: AD

• Question 88:

  Examine the description of the BOOKS_TRANSACTIONS table:

  

  Examine this partial SQL statement:

  SELECT * FROM books_transactions

  Which two WHERE conditions give the same result? (Choose two.)

  A. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

  B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');

  C. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'))

  D. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR menber_id IN ('A101', 'A102'));

  E. WHERE (borrowed_date = SYSDATE AND transaction_type - 'RM') OR member_id IN ('A101', VA102');

  Correct Answer: BE

• Question 89:

  Which three statements are true about sequences in a single instance Oracle database?

  A. Sequences can always have gaps.

  B. A sequence can Issue duplicate values.

  C. A sequence's unallocated cached values are lost if the instance shuts down.

  D. Two or more tables cannot have keys generated from the same sequence.

  E. A sequence can only be dropped by a DBA.

  F. A sequence number that was allocated can be rolled back if a transaction fails.

  Correct Answer: ABC

• Question 90:

  Which two statements are true about Database Instances and Real Application Clusters (RAC)?

  A. A RAC database can have one Instance.

  B. A RAC database can have instances on separate servers.

  C. Two RAC databases can share their instances.

  D. A RAC database must have three or more Instances.

  E. A RAC database must have two or more instances.

  Correct Answer: AB