Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 11:

  Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

  A. The Oracle join syntax supports creation of a Cartesian product of two tables

  B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

  C. The SQL:1999 compliant ANSI join syntax supports natural joins

  D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables

  E. The Oracle join syntax only supports right outer joins

  F. The Oracle join syntax supports natural joins

  G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

  Correct Answer: ACD

  https://oracle-base.com/articles/9i/ansi-iso-sql-support

• Question 12:

  Which two tasks can you perform using DBCA for databases? (Choose two.)

  A. Configure a nonstandard block size for a new database

  B. Register a new database with an available Enterprise Manager Management server

  C. Change the standard block size of an existing database

  D. Configure incremental backups for a new database

  E. Enable flashback database for an existing database

  Correct Answer: AB

  Reference: https://docs.oracle.com/cd/B16254_01/doc/server.102/b14196/install003.htm

• Question 13:

  You execute this query:

  SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), `MON'), `dd "Monday for" fmMonth rrrr')

  What is the result?

  A. It executes successfully but does not return any result

  B. It returns the date for the first Monday of the next month

  C. It generates an error

  D. It returns the date for the last Monday of the current month

  Correct Answer: B

  select sysdate from dual; 11-09-20 select TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'),'dd "Monday for" fmMonth rrrr') from dual 05 Monday for October 2020

• Question 14:

  Which three statements are true about external tables in Oracle 18c and later releases? (Choose three.)

  A. External table files can be used for other external tables in a different database

  B. The ORACLE_LOADER access driver can be used to unload data from a database into an external table

  C. The ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table

  D. They cannot be partitioned

  E. The ORACLE_DATAPUMP access driver can be used to load data into a database from an external table

  F. They support UPDATEs but not INSERTs and DELETEs

  Correct Answer: ACE

  https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm B - Incorrect The ORACLE_LOADER access driver is the default. It can perform only data loads, and the data must come from text datafiles C and E - Correct The ORACLE_DATAPUMP access driver can perform both loads and unloads. F - Incorrect https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID697B86CC-875C-4F68-AF80-49B41F33AE45 You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables. D - Incorrect Partitioned external tables were introduced in Oracle Database 12c Release 2 (12.2), allowing external tables to benefit from partition pruning and partition-wise. https://oracle-base.com/articles/12c/partitioned-external-tables-12cr2#:~:text=Partitioned%20external% 20tables%20were%20introduced,are%20supported%20with%20some%20restrictions.

• Question 15:

  View the Exhibit and examine the structure of the PRODUCTS table.

  Which two tasks require subqueries? (Choose two.)

  

  A. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE

  B. Display suppliers whose PROD_LIST_PRICE is less than 1000

  C. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

  D. Display the total number of products supplied by supplier 102 which have a product status of obsolete

  E. Display the minimum PROD_LIST_PRICE for each product status

  Correct Answer: AC

• Question 16:

  The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace.

  Segment creation is not deferred.

  You execute this command:

  

  Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose three.)

  A. The sales user must have a quota on the TEMP tablespace

  B. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema

  C. The sales user must have been granted the CREATE SESSION privilege D. The sales user must have their quota on the users tablespace removed

  E. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema

  F. The sales user must have been granted the CREATE TABLE privilege

  Correct Answer: BCF

  https://docs.oracle.com/database/121/DBSEG/users.htm#DBSEG99778 When you create a new user account, you should enable this user to access the database

• Question 17:

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

  A. Data block headers contain their own Data Block Address (DBA)

  B. A table row piece can be chained across several database blocks

  C. Multiple row pieces from the same row may be stored in different database blocks

  D. Multiple row pieces from the same row may be stored in the same block

  E. Data block free space is always contiguous in the middle of the block

  F. Index block free space is always contiguous in the middle of the block

  Correct Answer: ABC

  Reference: https://books.google.com.pk/books?id=lNtVjrmbzckCandpg=PA267andlpg=PA267anddq=Multiple +row+pieces+from+the+same+row+may+be+stored+in+different+database +blocksandsource=blandots=lEIhFe2Q52andsig=ACfU3U1TETJPy194pARxfIJ5ROaQdySCMQandhl=enandsa=Xandve d=2ahUKEwivveLa97_mAhWOzaQKHahYAVEQ6AEwAnoECAoQAQ#v=onepageandq=Multiple%20row% 20pieces%20from%20the% 20same%20row%20may%20be%20stored%20in%20different%20database% 20blocksandf=false

• Question 18:

  Examine the description of the BOOKS table:

  

  The table has 100 rows.

  Examine this sequence of statements issued in a new session:

  INSERT INTO books VALUES (`ADV112', `Adventures of Tom Sawyer', NULL, NULL);

  SAVEPOINT a;

  DELETE FROM books;

  ROLLBACK TO SAVEPOINT a;

  ROLLBACK;

  Which two statements are true? (Choose two.)

  A. The second ROLLBACK command does nothing

  B. The second ROLLBACK command replays the delete

  C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed

  D. The second ROLLBACK command undoes the insert

  E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row

  Correct Answer: CD

• Question 19:

  Which three statements are true regarding indexes? (Choose three.)

  A. A UNIQUE index can be altered to be non-unique

  B. A SELECT statement can access one or more indices without accessing any tables

  C. A table belonging to one user can have an index that belongs to a different user

  D. An update to a table can result in updates to any or all of the table's indexes

  E. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

  F. An update to a table can result in no updates to any of the table's indexes

  Correct Answer: BCF

• Question 20:

  Which three statements are true about Oracle synonyms? (Choose three.)

  A. A synonym cannot be created for a PL/SQL package

  B. A synonym can be available to all users

  C. A SEQUENCE can have a synonym

  D. A synonym created by one user can refer to an object belonging to another user

  E. Any user can drop a PUBLIC synonym

  Correct Answer: BCD

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm