1Z0-082 practice questions 21–30

1. Question 21 · 167 in bank

   Select 2

   You want to apply the principle of Least Privilege in all your live databases. One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis. Which two are types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose two.)

   • Aanalysis of privileges that a user has on other schema’s objects
   • Banalysis of privileges that a user has on their own schema objects
   • Canalysis of all privileges used by the sys user.
   • Danalysis of privileges granted indirectly to a role that are then used by a user who has been granted that role
   • Eanalysis of privileges granted directly to a role that are then used by a user who has been granted that role

   Show answer

   Answer: DE

   Next question

2. Question 22 · 167 in bank

   Select 2

   Examine the description of the SALES table:

   

   The SALES table has 55,000 rows. Examine this statement:

   

   Which two statements are true? (Choose two.)

   • ASALES1 is created with l row.
   • BSALES1 is created with no rows.
   • CSALES1 has primary KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
   • DSALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.
   • ESALES1 is created with 55,000 rows.

   Show answer

   Answer: DE

   Next question

3. Question 23 · 167 in bank

   Select 3

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

   • AThe ON clause must be used
   • BThe query must use two different aliases for the table
   • CIt must be an equijoin
   • DIt must be an inner join
   • EThe ON clause can be used
   • FIt can be an outer join

   Show answer

   Answer: BEF

   Explanation

   A - FALSE; use OLD syntax, e.g. SELECT ... FROM employees e, employees m WHERE e.mgr=m.empno ... B - TRUE; when omitting, the error "ORA-00918: column ambiguously defined" appears C - FALSE; e.g. SELECT ... FROM employees e1, employees e2 WHERE e1.msal > e2.msal D - FALSE; valid query is e.g. SELECT e1.empno,e1.ename,e2.ename ... FROM employees e1 RIGHT OUTER JOIN employees e2 ON e1.mgr = e2.empno; E - TRUE; valid query is e.g. SELECT e.ename employee,e.bdate birth, m.ename manager FROM employees e JOIN employees m ON e.mgr = m.empno; F - TRUE; see answer B

   Source: Adapted from community discussion — nautil2

   Next question

4. Question 24 · 167 in bank

   Select 3

   Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

   • AViews cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
   • BThe WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
   • CDelete statements can always be done on a table through a view.
   • DViews cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
   • EViews cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
   • FInsert statements can always be done on a table through a view.

   Show answer

   Answer: BDE

   Next question

5. Question 25 · 167 in bank

   Select 3

   Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)

   • AAn index can be created as part of a CREATE TABLE statement.
   • BIf a query filters on an indexed column, then it will always be used during execution of the query.
   • CA descending index is a type of function-based index.
   • DA DROP INDEX statement always prevents updates to the table during the drop operation.
   • EA UNIQUE and non-unique index can be created on the same table column.
   • FAn INVINSIBLE INDEX is not maintained when Data Manipulation Language (DML) is performed on its underlying table.

   Show answer

   Answer: ACE

   Explanation

   A- CREATE TABLE Books( ID NUMBER PRIMARY KEY USING INDEX (CREATE INDEX idx_custom_books_id ON books (ID)), Title VARCHAR2(100) NOT NULL, Author VARCHAR2(100) NOT NULL, PublishYear NUMBER NOT NULL ); C- "Oracle Database treats descending indexes as if they were function-based indexes" https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__GUID-92F4F0FB-499A-4ED7-8630-B219F8A50B90 E- "When you have multiple indexes on the same set of columns, only one of these indexes can be visible at a time, and any other indexes must be invisible." "You can create multiple indexes on the same set of columns when at least one of the following index characteristics is different: The indexes have different uniqueness properties. You can create both a unique and a non-unique index on the same set of columns." https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-B4B30CF4-2B95-44D6-8596-EC2A378251EF Correct is ACE, according the above evidences.

   Source: Adapted from community discussion — piontk

   References

   • Oracle documentation
   • Oracle documentation

   Next question

6. Question 26 · 167 in bank

   Select 3

   Which three actions are ways to apply the principle of least privilege? (Choose three.)

   • Asetting the O7_DICTIONARY_ACCESSIBILITY parameter to true
   • Bsetting the REMOTE_OS_AUTHENT parameter to true
   • Crevoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the SYSTEM user
   • Denabling Unified Auditing
   • Eusing Access Control Lists (ACLs)
   • Frevoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the public user

   Show answer

   Answer: DEF

   Explanation

   Very good link, thanks dianiss1050. Yes correct answer are DEF. False answer: A - Protect the data dictionary. When parameter 07_DICTIONARY_ACCESSIBILITY is FALSE, people who has ANY TABLE privileges are restricted from accessing data dictionary-based tables. The default value of the parameter is FALSE and should not be changed. B - Restrict remote database authentication. REMOTE_OS_AUTHENT specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX parameter. The default value is FALSE and should be like this. If set to TRUE, users created as “CREATE USER… IDENTIFIED EXTERNALLY” will be able to log in to the system. C - The user granted by default for those packages is PUBLIC and NOT SYSTEM.

   Source: Adapted from community discussion — auwia

   Next question

7. Question 27 · 167 in bank

   The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which WHERE clause can be used?

   • AWHERE ADD_MONTHS(start_date, 25) <= SYSDATE
   • BWHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
   • CWHERE MONTHS_BETWEEN(SYSDATE, start_date) <=25
   • DWHERE TO_NUMBER(start date - SYSDATE) <= 25

   Show answer

   Answer: C

   Next question

8. Question 28 · 167 in bank

   Select 2

   Examine the description of the BOOKS_TRANSACTIONS table:

   

   Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result? (Choose two.)

   • AWHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
   • BWHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');
   • CWHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
   • DWHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR menber_id IN ('A101', 'A102'));
   • EWHERE (borrowed_date = SYSDATE AND transaction_type - 'RM') OR member_id IN ('A101', VA102');

   Show answer

   Answer: BE

   Explanation

   The AND operator is evaluated earlier, it has higher priority than the OR operator. B and E examples differ only in parentheses, and those parentheses does not change the priority, because the priority of the AND is higher than the priority of the OR operator.

   Source: Adapted from community discussion — nautil2

   Next question

9. Question 29 · 167 in bank

   Select 2

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

   • AThe WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view.
   • BThe WITH CHECK clause prevents certain rows from being displayed when querying the view.
   • CViews can be updated without the need to re-grant privileges on the view.
   • DA view must only refer to tables in its defining query.
   • EViews can be indexed.

   Show answer

   Answer: AC

   Explanation

   A) TRUE; see https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30 "Notes on Updatable Views". WITH CHECK OPTION prevents INSERT, UPDATE and DELETE in join views B) FALSE; same URL as in A), "subquery_restriction_clause, WITH CHECK OPTION" ... Oracle Database prohibits any changes to the table or view . This statements talks only about changes, not about reading the rows of a view. C) TRUE; https://www.oracletutorial.com/oracle-view/oracle-updatable-view/ no extra grants are mentioned D) FALSE; a view can be based on another view, see this example: SQL> CREATE TABLE a ( b int ); SQL> INSERT INTO a VALUES ( 0 ); SQL> CREATE VIEW v_a AS SELECT * FROM a; SQL> CREATE VIEW vv_a AS SELECT * FROM v_a; SQL> SELECT * FROM vv_a; B - 0 E) FALSE; https://asktom.oracle.com/pls/apex/asktom.search?tag=indexes-on-view

   Source: Adapted from community discussion — nautil2

   References

   • Oracle documentation

   Next question

10. Question 30 · 167 in bank

    Which compression method is recommended for Direct-Path Insert operations?

    • AROW STORE COMPRESS BASIC
    • BROW STORE COMPRESS ADVANCED
    • CCOLUMN STORE COMPRESS ADVANCED
    • DCOLUMN STORE COMPRESS BASIC

    Show answer

    Answer: A

    Next question

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

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