1Z0-082 practice questions 51–60

1. Question 51 · 167 in bank

   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?

   • ASELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') FROM customers;
   • BSELECT REPLACE(TRIM(TRAILING 'son' FROM cust_last_name), 'An', 'O') FROM customers;
   • CSELECT INITCAP(REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O')) FROM customers;
   • DSELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O') FROM customers;

   Show answer

   Answer: D

   Explanation

   D is OK. B is not the wright answer. You will got an error: ORA-30001: trim set should have only one character 30001. 00000 - "trim set should have only one character" *Cause: Trim set contains more or less than 1 character. This is not allowed in TRIM function.

   Source: Adapted from community discussion — [Removed]

   Next question

2. Question 52 · 167 in bank

   Select 2

   Which two statements are true about GLOBAL TEMPORARY TABLES? (Choose two.)

   • AGLOBAL TEMPORARY TABLE space allocation occurs at session start.
   • BGLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
   • CA TRUNCATE command issued in a session causes all news in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
   • DA GLOBAL TEMPORARY TABLE 's definition is available to multiple sessions.
   • EA DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

   Show answer

   Answer: CD

   Next question

3. Question 53 · 167 in bank

   Select 3

   Which three statements are true about Resumable Space Allocation in Oracle databases? (Choose three.)

   • AA user's session may be suspended and resumed multiple times.
   • BThe AFTER SUSPEND event trigger can itself be suspended due to space conditions.
   • CAll sessions must have the same timeout value when waiting for resumable space allocations.
   • DResumable space allocation is only possible with locally managed tablespaces.
   • EA user's session may be suspended even if the user has the UNLTMTTED TABLESPACE system privilege.
   • FResumable space allocation may be enabled for some sessions and not others.

   Show answer

   Answer: AEF

   Explanation

   A - true, "A resumable statement can be suspended and resumed multiple times during execution." B - false, "SQL statements executed within a AFTER SUSPEND trigger are always non-resumable and are always autonomous." C - false, ALTER SESSION SET RESUMABLE_TIMEOUT sets specific timeout for the session it is executed in, it does not affect other sessions D - false, "There are two ways to specify alert thresholds for both locally managed and dictionary managed tablespaces:" E - true, there is no notice about UNLIMITED TABLESPACE in documentation claiming this system privilege prevents raising Resumable Space Allocation event F - true, ALTER SESSION ENABLE RESUMABLE can be executed in some sessions and it is not necessary to run it in all sessions

   Source: Adapted from community discussion — nautil2

   Next question

4. Question 54 · 167 in bank

   Examine the description of the PRODUCT_INFORMATION table:

   

   Which query retrieves the number of products with a null list price?

   • ASELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL;
   • BSELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
   • CSELECT COUNT(DISTINCT list price) FROM product_information WHERE list_price IS NULL;
   • DSELECT COUNT(list_price) FROM product_information WHERE list_price = NULL;

   Show answer

   Answer: B

   Explanation

   B is correct. COUNT function can calculate NULL only it use asterik. COUNT(*) : can count NULL COUNT(Column Name) : cannot count NULL

   Source: Adapted from community discussion — j_tw

   Next question

5. Question 55 · 167 in bank

   Select 2

   Examine this command:

   

   Which two statements are true? (Choose two.)

   • ADML may be performed on tables with one or more extents in this data file during the execution of this command.
   • BThe tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.
   • CThe tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.
   • DIf Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.
   • EThe file is renamed and stored in the same location

   Show answer

   Answer: AE

   Explanation

   answer is A & E Queries and DML and DDL operations can be performed while the data file is being moved, for example: 1-SELECT statements against tables and partitions 2-Creation of tables and indexes 3- Rebuilding of indexes Other notes: 1- If objects are compressed while the data file is moved, the compression remains the same. 2- You do not have to shut down the database or take the data file offline while you move a data file to another location, disk, or storage system. 3- You can omit the TO clause only when an Oracle-managed file is used. In this case, the DB_CREATE_FILE_DEST initialization parameter should be set to indicate the new location. 4-If the REUSE option is specified, the existing file is overwritten. note: The REUSE keyword indicates the new file should be created even if it already exists. 5-If the KEEP clause is specified, the old file will be kept after the move operation. The KEEP clause is not allowed if the source file is an Oracle-managed file.

   Source: Adapted from community discussion — khalilshahin01

   Next question

6. Question 56 · 167 in bank

   Select 2

   Examine the description of the CUSTOMERS table:

   

   You must determine if any customers' details have been entered more than once using a different CUSTINO, by listing all duplicate names. Which two methods can you use to get the required result? (Choose two.)

   • Asubquery
   • Bself join
   • CLEFT OUTER JOIN with self join
   • DRIGHT OUTER JOIN With self join
   • EFULL OUTER JOIN with self join

   Show answer

   Answer: AB

   Next question

7. Question 57 · 167 in bank

   Select 2

   Which two statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose two.)

   • AUndo segments can be stored in the SYSAUX tablespace.
   • BUndo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment.
   • CA single transaction may use multiple undo segments simultaneously.
   • DUndo segments can extend when a transaction fills the last extent of the undo segment.
   • EUndo segments can be stored in the SYSTEM tablespace.

   Show answer

   Answer: BD

   Explanation

   maybe BDE ? BD from the 64 and E because "When the database instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace. This is not recommended, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace."

   Source: Adapted from community discussion — zouve

   Next question

8. Question 58 · 167 in bank

   Select 2

   DATADIR1 and DATATDIR2 are database directory objects. Examine this command:

   

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

   • AIt fails if the total size of the dump file is more than 2GB.
   • BIt starts only when four worker processes are available.
   • CIt creates a master table to store details of the export operation.
   • DIt creates dump files for the entire database.
   • EIt creates dump files only for objects in the SYSTEM schema.

   Show answer

   Answer: CD

   Explanation

   D is correct: "You can use Data Pump to carry out a full database export by using the FULL parameter. In a full database export, the ENTIRE DATABASE is unloaded. This mode requires that you have the DATAPUMP_EXP_FULL_DATABASE role." https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump- export-utility.html#GUID-079769D8-40F4-432F-88AD-E7264D7A2E2D

   Source: Adapted from community discussion — sobrinho

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

9. Question 59 · 167 in bank

   Select 2

   Which two statements are true about Enterprise Manager Database Express? (Choose two.)

   • AIt is available only when the database is open
   • BIt can be used to perform database recovery when the database is opened
   • CThe same port number can be used for Database Express configurations for databases on different hosts
   • DIt can be used to switch a database into ARCHIVELOGMODE
   • EThe same port number can be used for multiple Database Express configurations for multiple databases on the same host
   • FIt can be used to perform database recovery when the database is mounted

   Show answer

   Answer: AC

   Next question

10. Question 60 · 167 in bank

    Select 2

    Examine these SQL statements which execute successfully:

    

    Which two statements are true after execution? (Choose two.)

    • AThe primary key constraint will be enabled and IMMEDIATE.
    • BThe primary key constraint will be enabled and DEFERRED.
    • CThe foreign key constraint will be enabled and DEFERRED.
    • DThe foreign key constraint will be disabled.
    • EThe foreign key constraint will be enabled and IMMEDIATE.

    Show answer

    Answer: AD

    Next question

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

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