1Z0-082 practice questions 11–20

1. Question 11 · 167 in bank

   Select 3

   Which three statements are true about the Automatic Diagnostic Repository (ADR)? (Choose three.)

   • AIt can be used for problem diagnosis of a database when that database's instance is down.
   • BThe ADR base is specified in the DIAGNIOSTIC_DEST database parameter.
   • CIt is held inside an Oracle database schema.
   • DIt is a file-based repository held outside any database.
   • EIt is only used for Oracle Database diagnostic information.

   Show answer

   Answer: ABD

   Explanation

   ABD A - The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down. B - They are replaced by the initialization parameter DIAGNOSTIC_DEST, which identifies the location of the ADR. D - The data is then stored in the Automatic Diagnostic Repository (ADR)—a file-based repository outside the database—where it can later be retrieved by incident number and analyzed. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-43A0C671-CE6F-492F-9032-A010E96DC257

   Source: Adapted from community discussion — j_tw

   References

   • Oracle documentation

   Next question

2. Question 12 · 167 in bank

   Select 2

   Which two statements are true about the SET VERIFY ON command? (Choose two.)

   • AIt can be used only in SQL*Plus
   • BIt displays values for variables used only in the WHERE clause of a query
   • CIt can be used in SQL Developer and SQL*Plus
   • DIt displays values for variables created by the DEFINE command
   • EIt displays values for variables prefixed with &&

   Show answer

   Answer: CE

   Explanation

   The SET VERIFY ON command will enable the display of any substitution variable used in our last executed SQL statement. When you use the DEFINE clause to create a variable, and the variable is not used in the next SQL statement then that variable will not be "shown" by the SET VERIFY command. Oh, and the command is available in all apps that allow the execution of SQL language against your database.

   Source: Adapted from community discussion — Franky_T

   Next question

3. Question 13 · 167 in bank

   Select 2

   Which two statements are true about a self join? (Choose two.)

   • AIt can be an inner join.
   • BIt can be a left outer join.
   • CIt must be an equijoin.
   • DThe join key column must have an index.
   • EIt must be a full outer join.

   Show answer

   Answer: AB

   Next question

4. Question 14 · 167 in bank

   Which statement is true about database links?

   • APrivate database link creation requires the same user to exist in both the local and the remote databases.
   • BA database link created in a database allows a connection from that database's instance to the target database's instance, but not vice versa.
   • CA public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance.
   • DA public database link can be created only by SYS.
   • EA database link can be created only between two Oracle databases.

   Show answer

   Answer: B

   Explanation

   B PUBLIC does not means a user can use any schema. It means other users can use db link what you made it. When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-a-distributed-database.html#GUID-B5950167-35F8-4C88-B063-382E0290CB53

   Source: Adapted from community discussion — j_tw

   References

   • Oracle documentation

   Next question

5. Question 15 · 167 in bank

   Select 2

   Which two queries execute successfully? (Choose two.)

   • ASELECT NULLIF(NULL, 100) FROM DUAL;
   • BSELECT NULLIF(100, 'A') FROM DUAL;
   • CSELECT COALESCE(100, 'A') FROM DUAL;
   • DSELECT COALESCE(100, NULL, 200) FROM DUAL;
   • ESELECT NULLIF(100, 100) FROM DUAL;

   Show answer

   Answer: DE

   Explanation

   D,E is correct A. ORA-00932: inconsistent datatypes: expected - got CHAR B. ORA-00932: inconsistent datatypes: expected NUMBER got CHAR C. ORA-00932: inconsistent datatypes: expected NUMBER got CHAR D. SQL> SELECT COALESCE(100, NULL, 200) FROM DUAL; COALESCE(100,NULL,200) ---------------------- 100 E. SQL> SELECT NULLIF(100, 100) FROM DUAL; NULLIF(100,100) ---------------

   Source: Adapted from community discussion — BeomK

   Next question

6. Question 16 · 167 in bank

   Select 3

   Which three statements are true about data block storage in an Oracle Database? (Choose three.)

   • AAn index block can contain row data.
   • BA data block header is of a fixed length.
   • CRow data is stored starting at the end of the block.
   • DA table block must always contain row data.
   • EA block header contains a row directory pointing to all rows in the block.

   Show answer

   Answer: ACE

   Explanation

   A. An index block can contain row data. --> True. ref: https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch3.htm B. A data block header is of a fixed length. --> False. ref: https://www.dba-oracle.com/t_data_block_format.htm C. Row data is stored starting at the end of the block. --> True. ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html D. A table block must always contain row data. --> False. ref: https://richardfoote.wordpress.com/category/block-dumps/ E. A block header contains a row directory pointing to all rows in the block. --> True. ref: https://medium.com/geekculture/explain-about-oracle-database-data-block-interview-a-be137c52fe4d

   Source: Adapted from community discussion — auwia

   References

   • Oracle documentation
   • Oracle documentation

   Next question

7. Question 17 · 167 in bank

   Select 2

   Which two are benefits of external tables? (Choose two.)

   • AThey support DELETES which transparently deletes records in the file system as if they were table rows.
   • BThey can be queried, transformed, and joined with other tables without having to load the data first.
   • CThey support UPDATES which transparently updates records in the file system as if they were table rows.
   • DThe results of a complex join or aggregating function or both can be unloaded to a file for transportation to another database.
   • EThey can be queried while the database is in the MOUNT state like dynamic performance views.

   Show answer

   Answer: BD

   Next question

8. Question 18 · 167 in bank

   Select 2

   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? (Choose two.)

   • AORDER BY 1, 2
   • BWHERE city = '%AN%'
   • CWHERE city LIKE '%AN%'
   • DORDER BY last_name DESC, city ASC
   • EWHERE city IN ('%AN%')
   • FORDER BY 1, LNAME DESC

   Show answer

   Answer: CF

   References

   • Oracle documentation

   Next question

9. Question 19 · 167 in bank

   Select 3

   Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

   • ASequences can always have gaps.
   • BTwo or more tables cannot have keys generated from the same sequence.
   • CA sequence's unallocated cached values are lost if the instance shuts down.
   • DA sequence can issue duplicate values.
   • EA sequence can only be dropped by a DBA.
   • FA sequence number that was allocated can be rolled back if a transaction fails.

   Show answer

   Answer: ACD

   References

   • Oracle documentation

   Next question

10. Question 20 · 167 in bank

    Your database instance is started with an SPFILE. A PFILE is also available. You execute this command: ALTER SYSTEM SET DB_CACHE_SIZE=100K; Where is the value changed?

    • Ain the SPFILE, PFILE, and memory
    • Bin the SPFILE and in memory
    • Conly in the SPFILE
    • Donly in memory
    • Ein the SPFILE and PFILE

    Show answer

    Answer: B

    Explanation

    When you issue the ALTER SYSTEM statement while connected to a non-CDB or a CDB root, the scope depends on whether you started up the database using a traditional plain-text parameter file (pfile) or server parameter file (spfile). MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify. SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference. BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again. If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

    Source: Adapted from community discussion — RaNik69

    Next question

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

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