Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 151:

  Which three statements are true about inner and outer joins? (Choose three.)

  A. A full outer join returns matched and unmatched rows

  B. An inner join returns matched rows

  C. Outer joins can only be used between two tables per query

  D. A full outer join must use Oracle syntax

  E. Outer joins can be used when there are multiple join conditions on two tables

  F. A left or right outer join returns only unmatched rows

  Correct Answer: ABE

  E. Outer joins can be used when there are multiple join conditions on two tables can be like following query:

  - select * from table a left join table1 b on a.col1 = b.col1 and a.col2=b.col2;

  Reference: https://www.studytonight.com/dbms/joining-in-sql.php

• Question 152:

  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: BDF

  https://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/indexes.htm https://docs.oracle.com/cd/B10501_01/server.920/a96521/indexes.htm

• Question 153:

  Examine this command:

  

  Which two statements are true? (Choose two.)

  A. DML may be performed on tables with one or more extents in this data file during the execution of this command.

  B. The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.

  C. The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.

  D. If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.

  E. The file is renamed and stored in the same location

  Correct Answer: AE

  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.

• Question 154:

  Which two statements are true about trace files produced by the Oracle Database server? (Choose two.)

  A. They can be written by server processes

  B. Trace files are written to the Fast Recovery Area (FRA)

  C. They can be written by background processes

  D. All trace files contain error information that require contacting Oracle Support

  E. Trace file names are based on the database name concatenated with a sequential number

  Correct Answer: AC

  Reference: https://gerardnico.com/db/oracle/trace_file https://docs.oracle.com/html/E25494_01/monitoring001.htm

  Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, and other information is for Oracle Support Services. Trace file information is also used to tune applications and instances.

• Question 155:

  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 156:

  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#GUID-697B86CC-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-tables12cr2#:~:text=Partitioned%20external%20tables%20were%20introduced,are%20supported%20with%20some%20restrictions.

• Question 157:

  Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

  A. The CURRENT_TIMESTAMP function returns data without time zone information

  B. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

  C. A TIMESTAMP data type column contains information about year, month, and day

  D. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  E. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  Correct Answer: BDE

  https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#GUID-CD2954CE-45E2-4938-A599-CCB96879510F

• Question 158:

  Which three statements are true about single-row functions? (Choose three.)

  A. They can be used only in the WHERE clause of a SELECT statement

  B. The argument can be a column name, variable, literal or an expression

  C. The data type returned can be different from the data type of the argument

  D. They can be nested to any level

  E. They can accept only one argument

  F. They return a single result row per table

  Correct Answer: BCD

  https://www.tutorialspoint.com/sql_certificate/using_single_row_functions_questions.htm

• Question 159:

  Which two statements are true about substitution variables?

  A. A substitution variable can be used with any clause in a SELECT statement.

  B. A substitution variable used to prompt for a column name must be enclosed in double quotation marks.

  C. A substitution variable used to prompt for a column name must be enclosed in single quotation marks.

  D. A substitution variable prefixed with and always prompts only once for a value in a session.

  E. A substitution variable can be used only in a SELECT statement.

  F. A substitution variable prefixed with andand prompts only once for a value in a session unless it is set to undefined in the session.

  Correct Answer: AF

• Question 160:

  Examine this description of the books table containing 100 rows:

  

  Now examine this sequence of statements Issued In a new session;

  

  Examine this description of the books table containing 100 rows:

  Which three statements are true?

  A. The second rollback command restores the row that was inserted.

  B. The first rollback command leaves the table's 100 original rows locked.

  C. The second rollback command rolls back the rollback to savepoint a command.

  D. The first rollback command leaves the inserted row locked.

  E. The first RollBack command restores the row that was inserted.

  F. The second rollback command restores the 100 rows that were in the table originally.

  G. The first rollback command restores the 100 rows that were in the table originally.

  Correct Answer: ACE