Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 41:

  Examine the description of the members table:

  

  Examine the partial query:

  SELECT city, last_name LNAME FROM members ...;

  You want to display all cities that contain the string an. The cities must be returned in ascending order, with the last names further sorted in descending order.

  Which two clauses must you add to the query?

  A. ORDER BY 1, LNAME DESC

  B. ORDER BY 1, 2

  C. WHERE city LIKE '%AN%

  D. WHERE city = '%AN%'

  E. WHERE city IN ('%AN%')

  F. ORDER BY last_name DESC, city ASC

  Correct Answer: AC

• Question 42:

  Which three statements are true about the Oracle Data Dictionary? (Choose three.)

  A. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables

  B. The data dictionary is created and maintained by the database administrator

  C. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary

  D. Base tables can be queried directly

  E. It is owned by the SYSTEM user

  F. Usernames of all users including database administrators are stored in the data dictionary

  Correct Answer: CDF

  https://docs.oracle.com/cd/B28359_01/server.111/b28318/datadict.htm#CNCPT1210

• Question 43:

  In the promotions table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? (Choose two.)

  A. TO_NUMBER(PROMO_BEGIN_DATE) - 5 will return a number.

  B. PROMO_BEGIN_DATE - SYSDATE will return a number.

  C. PROMO_BEGIN_DATE - SYSDATE will return an error.

  D. PROMO_BEGIN_DATE - 5 will return a date.

  E. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

  Correct Answer: BD

• Question 44:

  In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.

  Examine this command:

  SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);

  Which segment or segments, if any, are created as a result of executing the command?

  A. T1, an index segment for the primary key, a LOB segment, and a lobindex segment

  B. no segments are created

  C. T1 only

  D. T1 and an index segment created for the primary key only

  E. T1, an index segment for the primary key, and a LOB segment only

  Correct Answer: B

  DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

  Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time

  SQL> alter session set deferred_segment_creation=TRUE;

  Session altered.

  SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);

  Table created.

  SQL> select segment_name,segment_type,bytes from user_segments;

  no rows selected

• Question 45:

  Examine this SQL statement:

  SELECT cust_id, cust_last_name "Last Name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30

  Identify three ORDER BY clauses, any one of which can complete the query successfully. (Choose three.)

  A. ORDER BY "Last Name"

  B. ORDER BY 2, 1

  C. ORDER BY 2, cust_id

  D. ORDER BY CUST_NO

  E. ORDER BY "CUST_NO"

  Correct Answer: ABC

• Question 46:

  Which four account management capabilities can be configured using Oracle profiles? (Choose four.)

  A. the number of hours for which an account is locked after the configured number of login attempts has been reached

  B. the number of days for which an account may be inactive before it is locked

  C. the maximum amount of CPU time allowed for a user's sessions before their account is locked

  D. the ability to prevent a password from ever being reused

  E. the number of password changes required within a period of time before a password can be reused

  F. the number of days for which an account is locked after the configured number of login attempts has been reached

  G. the maximum number of sessions permitted for a user before the account is locked

  Correct Answer: BDEF

• Question 47:

  Which three instance situations are possible with the Oracle Database server without multi-tenant? (Choose three.)

  A. two or more instances on separate servers all associated with one database

  B. one instance on one server associated with one database

  C. one instance on one server associated with two or more databases on the same server

  D. one instance on one server not associated with any database

  E. one instance on one server associated with two or more databases on separate servers

  Correct Answer: ABD

  A: RAC 2,3 or 4 nodes

  B: Single instance (standalone) with database

  C: one instance running multiple INDEPENDENT databases!!

  D: Single instance (standalone) without database

  E: absurd configuration!

• Question 48:

  Examine the description of the CUSTOMERS table:

  

  For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used?

  A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level IS NOT NULLAND due_amount IS NOT NULL;

  B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level != NULLAND cust_credit_level !=NULL;

  C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level <> NULLAND due_amount <> NULL;

  D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level != NULLAND due_amount != NULL;

  E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;

  Correct Answer: E

• Question 49:

  Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)? (Choose two.)

  A. PCTFREE defaults to 10% for all blocks in all segments for all compression methods

  B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows

  C. Update operations always attempt to find blocks with free space appropriate to the length of the row being updated

  D. Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted

  E. A block will always be eligible for inserts if the row is short enough to fit into the block

  Correct Answer: BD

  Characteristics of Bitmap Segment Management

  Bitmap space management uses four bits inside each data block header to indicate the amount of available space in the data block. Unlike traditional space management with a fixed relink and unlink threshold, bitmap space managements allow Oracle to compare the actual row space for an INSERT with the actual available space on the data block. This enables better reuse of the available free space especially for objects with rows of highly varying size. Here are the values inside the four-bit space:

  1.

  Value Meaning

  2.

  0000 Unformatted Block

  3.

  0001 Block is logically full

  4.

  0010 <25% free space

  5.

  0011 >25% but <50% free space

  6.

  0100 > 50% but <75% free space

  7.

  0101 >75% free space Table 1: Bitmap value meanings.

• Question 50:

  In one of your databases, the user HR has the password HRMGR.

  You want to connect to a database instance whose listener listens on port 1531 by using this statement:

  CONNECT HR/HRMGR@orcl

  No name server is used.

  Which statement is true about ORCL?

  A. It must be the value of the SERVICE_NAMES parameter on the client side

  B. It must resolve to a valid connect descriptor in the server's tnsnames.ora file

  C. It must resolve to a valid connect descriptor in the client's tnsnames.ora file

  D. It must be the name of the database to whose instance HR wishes to connect

  E. It must be the name of the server running the database to whose instance HR wishes to connect

  Correct Answer: C