1Z0-082 practice questions 71–80

1. Question 71 · 167 in bank

   Select 3

   Which three statements are true about the Oracle Database server during and immediately after SHUTDOWN TRANSACTIONAL? (Choose three.)

   • ASessions remain connected even if they are not running a transaction.
   • BUncommitted transactions are allowed to continue to the next COMMIT.
   • CUncommitted transactions are allowed to continue to the next ROLLBACK.
   • DInstance recovery occurs automatically during the shutdown.
   • EInstance recovery occurs at the next restart.
   • FNew connection requests made to the database instance are refused.

   Show answer

   Answer: BCF

   Explanation

   shutdown TRANSACTIONAL: Specifies that a transactional shutdown is to be performed. A transactional shutdown prevents new user logins and disconnects any user whose transaction is complete (i.e., no COMMIT is pending). As each remaining user completes the current transaction via a COMMIT or ROLLBACK statement, the session is disconnected. When all users are disconnected, the instance will be shut down. so BCF for D,E The Instance Recovery will only happen if issuing shutdown abort or a crash.

   Source: Adapted from community discussion — EddieY

   Next question

2. Question 72 · 167 in bank

   Select 3

   Which three are benefits of using temp UNDO when performing DML on global temporary tables? (Choose three.)

   • AIt reduces the amount of redo generated.
   • BIt permits DML on Global temporary tables even if the database is opened read only.
   • CIt reduces I/Os to the SYSAUX tablespace.
   • DIt reduces I/Os to the SYSTEM tablespace.
   • EIt reduces the amount of UNDO stored in the UNDO tablespace.

   Show answer

   Answer: BDE

   Explanation

   B, D, E B: Undo tablespace is typically locked in read-only mode. Using temp undo allows DML operations to occur on global temporary tables even in a read-only database D: By using temp UNDO, the system avoids unnecessary writes to SYSTEM tablespace, which improves performance by reducing I/O operations E: Usage of UNDO space in the UNDO tablespace is minimized since the UNDO for DML operations is stored in temporary storage

   Source: Adapted from community discussion — cf1f99b

   Next question

3. Question 73 · 167 in bank

   Select 2

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

   • APCTFREE defaults to 10% for all blocks in all segments for all compression methods
   • BASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
   • CUpdate operations always attempt to find blocks with free space appropriate to the length of the row being updated
   • DInsert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
   • EA block will always be eligible for inserts if the row is short enough to fit into the block

   Show answer

   Answer: BD

   Explanation

   the answer is B and D 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: Value Meaning 0000 Unformatted Block 0001 Block is logically full 0010 25% but 50% but 75% free space Table 1: Bitmap value meanings.

   Source: Adapted from community discussion — ioio

   Next question

4. Question 74 · 167 in bank

   Select 2

   Which two statements are true regarding non-equijoins? (Choose two.)

   • ATable aliases must be used.
   • BThe Oracle join syntax can be used.
   • CThe SQL1999 compliant ANSI join syntax must be used.
   • DThe USING clause can be used.
   • EThe ON clause can be used.

   Show answer

   Answer: BE

   Next question

5. Question 75 · 167 in bank

   Select 3

   Which three statements are true about an SPFILE? (Choose three.)

   • AIt must exist for a database instance to start.
   • BIt can be used to create a pfile.
   • CIt contains initialization parameters whose values can be changed using the ALTER SYSTEM command.
   • DIt can be created by SYS from an idle instance.
   • EIt contains only static initialization parameters.

   Show answer

   Answer: BCD

   Next question

6. Question 76 · 167 in bank

   Select 2

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

   • ACOUNT (*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
   • BCOUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column.
   • CA SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
   • DIt can only be used for NUMBER data types.
   • ECOUNT (inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

   Show answer

   Answer: AB

   Next question

7. Question 77 · 167 in bank

   Select 2

   Which two tasks can be performed in the NOMOUNT state? (Choose two.)

   • Arenaming data files
   • Bfull database recovery
   • Cre-creating control files
   • Dcreating a database
   • Eenabling online redo log archiving

   Show answer

   Answer: CD

   Next question

8. Question 78 · 167 in bank

   Select 2

   TBS1 is a permanent user-defined tablespace. Oracle Managed Files (OMF) is not used in this database. Examine this command: DROP TABLESPACE tbsl; Which two will ensure that TBS1 is dropped and its datafiles are deleted from the file system? (Choose two.)

   • ATBS1 must not contain any temp files.
   • BThere must be no uncommitted transactions involving segments stored in TBS1.
   • CTBS1 must be READ WRITE.
   • DThe INCLUDING CONTENTS AND DATAFILES clause must be added to the command.
   • ETBS1 must be altered READ ONLY.

   Show answer

   Answer: BD

   Explanation

   A is incorrect for permanent tablespaces. The presence of temp files is unrelated to dropping a permanent tablespace as temp files are associated with temporary tablespaces

   Source: Adapted from community discussion — cf1f99b

   Next question

9. Question 79 · 167 in bank

   Select 2

   Which two statements are true about constraints? (Choose two.)

   • AA UNIQUE constraint can use a pre-existing index on the constrained column or columns
   • BA FOREIGN KEY column can contain NULLs.
   • CA PRIMARY KEY constraint can only be added to an empty table.
   • DA column can have only one CHECK constraint.
   • EA CHECK constraint can refer to values in other rows.

   Show answer

   Answer: AB

   Next question

10. Question 80 · 167 in bank

    Select 2

    Evaluate these commands which execute successfully:

    

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

    • AIf sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS
    • BAny user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ
    • CColumn 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
    • DSequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
    • ESequence ORD_SEQ is guaranteed not to generate duplicate numbers

    Show answer

    Answer: BC

    Explanation

    A. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS --> False, the column is NOT NULL. B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ --> True 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 --> True D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times --> False, it goes back to 1 after 1.000.000... E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers --> False, having CYCLE in the create statement, duplicates will arrive after the first cycle.

    Source: Adapted from community discussion — auwia

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

1Z0-082 practice, question 71. 167 questions available.