Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 71:

  In which three situations does a new transaction always start? (Choose three.)

  A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

  B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session

  C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

  D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session

  E. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session

  F. when issuing a DML statement after a DML statement failed in the same session

  Correct Answer: ADE

  A transaction begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement. A transaction ends when any of the following actions occurs: A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause. 　 Answer D A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER. The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

  o SELECT do not have a transaction start/end. Check by command: SELECT XID AS "txn id", XIDUSN AS "undo seg", XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn status" FROM V$TRANSACTION;

  https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-7690181F8293-47B9-ADC1-905BD9C3AF57

• Question 72:

  Which three statements are true about single-row functions? (Choose three.)

  A. They can be used only in the WHERE clause of a SELECT statement

  B. The argument can be a column name, variable, literal or an expression

  C. The data type returned can be different from the data type of the argument

  D. They can be nested to any level

  E. They can accept only one argument

  F. They return a single result row per table

  Correct Answer: BCD

  Reference: https://www.folkstalk.com/2012/01/oracle-single-row-functions-examples.html

• Question 73:

  You want to use table compression suitable for OLTP that will:

  1.

  Compress rows for all DML statements on that table

  2.

  Minimize the overheads associated with compression

  Which compression option is best suited for this?

  A. COLUMN STORE COMPRESS FOR QUERY LOW

  B. ROW STORE COMPRESS BASIC

  C. COLUMN STORE COMPRESS FOR ARCHIVE LOW

  D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH

  E. ROW STORE COMPRESS ADVANCED

  Correct Answer: E

  https://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN-GUID-34D15DD1-0925-4C9A-BE8A3EE91671E526

• Question 74:

  Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

  A. Private Temporary Tables (PTTS) store metadata in memory only

  B. An index created with the UNUSABLE attribute has no segment

  C. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement

  D. An index that is altered to be UNUSABLE will retain its segment

  E. A table that is truncated will always have its segment removed

  Correct Answer: AB

  A - https://oracle-base.com/articles/18c/private-temporary-tables-18c Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup. B - https://docs.oracle.com/html/E25494_01/indexes003.htm#CIHFIGDG Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index. C - not always, depends on the setup of the table creation

• Question 75:

  Which two statements are true about the PMON background process? (Choose two.)

  A. It registers database services with all local and remote listeners known to the database instance

  B. It frees resources held by abnormally terminated processes

  C. It records checkpoint information in the control file

  D. It frees unused temporary segments

  E. It kills sessions that exceed idle time

  Correct Answer: BE

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14220/process.htm

• Question 76:

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

  Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)

  A. Column positions must be used in the ORDER BY clause

  B. Only column names from the first SELECT statement in the compound query are recognized

  C. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause

  D. Each SELECT statement in the compound query must have its own ORDER BY clause

  E. Each SELECT statement in the compound query can have its own ORDER BY clause

  Correct Answer: BC

  select employee_id,last_name "Last Name" FROM employees where employee_id=101 union select employee_id EMP_NO, last_name from employees where employee_id=113; C is correct.

• Question 78:

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

• Question 79:

  Evaluate these commands which execute successfully:

  

  Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)

  A. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS

  B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ

  C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO

  D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

  E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers

  Correct Answer: BC

• Question 80:

  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.