Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 31:

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

  A. An index can be created as part of a CREATE TABLE statement.
  B. If a query filters on an indexed column, then it will always be used during execution of the query.
  C. A descending index is a type of function-based index.
  D. A DROP INDEX statement always prevents updates to the table during the drop operation.
  E. A UNIQUE and non-unique index can be created on the same table column.
  F. An INVINSIBLE INDEX is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
  A. An index can be created as part of a CREATE TABLE statement.
  C. A descending index is a type of function-based index.
  E. A UNIQUE and non-unique index can be created on the same table column.

  ---

  Explanation/Reference:

• Question 32:

  Examine this command:

  

  Which two statements are true?

  A. Compressed objects in SALES01.DBF will be uncompressed In SALES02.DBF after the move.
  B. DML may be performed on tables with one or more extents in this data file during the execution of this command.
  C. It overwrites any existing file with the name SALES02.DBF.dbf in /u02 by default.
  D. The "to" clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used.
  E. Tables with one or more extents in this data file may be queried during the execution of this command.
  B. DML may be performed on tables with one or more extents in this data file during the execution of this command.
  E. Tables with one or more extents in this data file may be queried during the execution of this command.

• Question 33:

  A database is configured to use automatic undo management with temporary undo enabled.

  An UPDATE is executed on a temporary table.

  Where is the UNDO stored?

  A. in the undo tablespace
  B. in the SYSAUX tablespace
  C. in the SGA
  D. in the PGA
  E. in the temporary tablespace
  E. in the temporary tablespace

  ---

  Explanation/Reference:

  Oracle database 12c Release 1 (12.1) introduced the concept of temporary undo, allowing the undo segments for global temporary tables to be stored in the temporary tablespace. This allows global temporary tables to be used in physical standby databases and read-only databases, as well as removing the need to create redo.

  16.7 Managing Temporary Undo By default, undo records for temporary tables are stored in the undo tablespace and are logged in the redo, which is the same way undo is managed for persistent tables. However, you can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables. When this parameter is set to TRUE, the undo for temporary tables is called temporary undo.

  16.7.1 About Managing Temporary Undo Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments.

  Reference: http://www.dba-oracle.com/t_temp_undo_enabled.htm Reference: https://docs.oracle.com/database/121/ADMIN/undo.htm#ADMIN11479

• Question 34:

  A script abc.sql must be executed to perform a job.

  A database user HR, who is defined in this database, executes this command:

  $ sqlplus hr/hr@orcl @abc.sql

  What will happen upon execution?

  A. The command succeeds and HR will be connected to the orcl and abc.sql databases
  B. The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed
  C. The command fails because the script must refer to the full path name
  D. The command fails and reports an error because @ is used twice
  B. The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed

• Question 35:

  In the promotions table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? (Choose two.)

  A. TO_NUMBER(PROMO_BEGIN_DATE) - 5 will return a number.
  B. PROMO_BEGIN_DATE - SYSDATE will return a number.
  C. PROMO_BEGIN_DATE - SYSDATE will return an error.
  D. PROMO_BEGIN_DATE - 5 will return a date.
  E. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.
  B. PROMO_BEGIN_DATE - SYSDATE will return a number.
  D. PROMO_BEGIN_DATE - 5 will return a date.

  ---

  Explanation/Reference:

• Question 36:

  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
  A. DML may be performed on tables with one or more extents in this data file during the execution of this command.
  E. The file is renamed and stored in the same location

  ---

  Explanation/Reference:

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

  Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER.

  Examine these SQL statements:

  Statement 1:

  SELECT MAX(unit_price * quantity) "Maximum Order"

  FROM order_items;

  Statement 2:

  SELECT MAX(unit_price * quantity) "Maximum Order"

  FROM order_items

  GROUP BY order_id;

  Which two statements are true?

  A. Statement 1 returns only one row of output.
  B. Statement 2 returns only one row of output.
  C. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.
  D. Both the statements give the same output.
  E. Statement 2 may return multiple rows of output.
  A. Statement 1 returns only one row of output.
  E. Statement 2 may return multiple rows of output.

  ---

  Explanation/Reference:

  https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj27781.html

  The Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. It must evaluate to a built-in data type. You can therefore call methods that evaluate to built-in data types. (For example, a method that returns a java.lang.Integer or int evaluates to an INTEGER.) If an expression evaluates to NULL, the aggregate skips that value.

• Question 38:

  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?

  A. ORDER BY 1, LNAME DESC
  B. ORDER BY 1, 2
  C. WHERE city LIKE '%AN%
  D. WHERE city = '%AN%'
  E. WHERE city IN ('%AN%')
  F. ORDER BY last_name DESC, city ASC
  A. ORDER BY 1, LNAME DESC
  C. WHERE city LIKE '%AN%

  ---

  Explanation/Reference:

• Question 39:

  Which three statements are true about the Oracle Database server during and immediately after SHUTDOWN TRANSACTIONAL? (Choose three.)

  A. Sessions remain connected even if they are not running a transaction.
  B. Uncommitted transactions are allowed to continue to the next COMMIT.
  C. Uncommitted transactions are allowed to continue to the next ROLLBACK.
  D. Instance recovery occurs automatically during the shutdown.
  E. Instance recovery occurs at the next restart.
  F. New connection requests made to the database instance are refused.
  A. Sessions remain connected even if they are not running a transaction.
  B. Uncommitted transactions are allowed to continue to the next COMMIT.
  F. New connection requests made to the database instance are refused.

• Question 40:

  In which three situations does a new transaction always start? (Choose three.)

  A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
  B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session
  C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
  D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session
  E. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
  F. when issuing a DML statement after a DML statement failed in the same session
  A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
  D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session
  F. when issuing a DML statement after a DML statement failed in the same session

  ---

  Explanation/Reference:

  https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-7690181F-8293-47B9-ADC1-905BD9C3AF57