Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 91:

  Which two actions can you perform using DBCA for an existing database?

  A. Create a template that can be used to clone the database.
  B. Change the server mode from dedicated to shared, and vice versa.
  C. Change the character set.
  D. Create an additional listener.
  E. Create nonstandard block size tablespaces.
  B. Change the server mode from dedicated to shared, and vice versa.
  C. Change the character set.

• Question 92:

  Examine the description of the CUSTOMERS table:

  

  For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used?

  A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level IS NOT NULLAND due_amount IS NOT NULL;
  B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level != NULLAND cust_credit_level !=NULL;
  C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level NULLAND due_amount NULL;
  D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level != NULLAND due_amount != NULL;
  E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;
  E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;

• Question 93:

  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.
  A. The second rollback command restores the row that was inserted.
  C. The second rollback command rolls back the rollback to savepoint a command.
  E. The first RollBack command restores the row that was inserted.

• Question 94:

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

  A. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
  B. The Oracle join syntax lacks the ability to do outer joins.
  C. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
  D. The Oracle join syntax supports creation of a Cartesian product of two tables.
  E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
  C. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
  D. The Oracle join syntax supports creation of a Cartesian product of two tables.

  ---

  Explanation/Reference:

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

• Question 95:

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

  A. It is held inside an Oracle database schema.
  B. The ADR base is specified in the DIAGNIOSTIC_DEST database parameter.
  C. It is only used for Oracle Database diagnostic information.
  D. It is a file-based repository held outside any database.
  E. It can be used for problem diagnosis of a database when that database's instance is down.
  B. The ADR base is specified in the DIAGNIOSTIC_DEST database parameter.
  D. It is a file-based repository held outside any database.
  E. It can be used for problem diagnosis of a database when that database's instance is down.

  ---

  Explanation/Reference:

• Question 96:

  Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

  A. The data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.
  B. The number, but not names, of columns must be identical for all SELECT statements in the query.
  C. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
  D. The names and number of columns must be identical for all SELECT statements in the query.
  E. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
  A. The data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.
  B. The number, but not names, of columns must be identical for all SELECT statements in the query.

  ---

  Explanation/Reference:

  https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE

• Question 97:

  Which two statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose two.)

  A. Undo segments can be stored in the SYSAUX tablespace.
  B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment.
  C. A single transaction may use multiple undo segments simultaneously.
  D. Undo segments can extend when a transaction fills the last extent of the undo segment.
  E. Undo segments can be stored in the SYSTEM tablespace.
  B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment.
  D. Undo segments can extend when a transaction fills the last extent of the undo segment.

  ---

  Explanation/Reference:

• Question 98:

  The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE.

  NLS_DATE_FORMAT is set to DD-MON-RR.

  Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

  A. CONCAT (qty_sold, invoice_date) : requires explicit conversion
  B. invoice_date = '15-march-2019' : uses implicit conversion
  C. invoie_date > '01-02-2019' : uses implicit conversion
  D. qty_sold BETWEEN `101' AND '110' : uses implicit conversion
  E. qty_sold = `0554982' uses implicit conversion
  B. invoice_date = '15-march-2019' : uses implicit conversion
  D. qty_sold BETWEEN `101' AND '110' : uses implicit conversion

  ---

  Explanation/Reference:

• Question 99:

  Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

  A. the first column in the first select of the compound query
  B. the first varchab2 column in the first select of the compound query
  C. the first number column in the first select of the compound query
  D. the first number or varchar2 column in the last select of the compound query
  E. the first column in the last select of the compound query
  A. the first column in the first select of the compound query

  ---

  Explanation/Reference:

• Question 100:

  The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE

  User U1 has a 1 MB quota in tablespace DATA.

  U1 executes this command:

  SQL> CREATE TABLE t1 AS

  (SELECT object_name, sharing, created

  FROM dba_objects);

  U1 complains that the command is taking too long to execute.

  In the alert log, the database administrator (DBA) finds this:

  2017-03-06T12:15:17.183438+05:30

  statement in resumable session `User U1(136), Session 1, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace `DATA'

  Which are three actions any one of which the DBA could take to resume the session? (Choose three.)

  A. Add a data file to DATA
  B. Drop other U1 objects in DATA
  C. Increase U1's quota sufficiently in DATA
  D. Set DEFERRED_SEGMENT_CREATION to TRUE
  E. Grant UNLIMITED TABLESPACE to U1
  F. Set AUTOEXTEND ON for data files in DATA
  B. Drop other U1 objects in DATA
  C. Increase U1's quota sufficiently in DATA
  E. Grant UNLIMITED TABLESPACE to U1

  ---

  Explanation/Reference:

  Set DEFERRED_SEGMENT_CREATION to TRUE, when creating the table you are already inserting rows as select, therefore the segment has already been created the D is not. Configure Set AUTOEXTEND ON for data files in DATA, if you have 1M quota it will not work.

  A. Add a data file to DATA -- Won't help as problem is quota to user not the space

  B. Drop other U1 objects in DATA -- Will help as it will reduce the used space from quota for that user

  C. Increase U1's quota sufficiently in DATA -- Will help.

  D. Set DEFERRED_SEGMENT_CREATION to TRUE -- Won't help, segments has to be created as its CTAS

  E. Grant UNLIMITED TABLESPACE to U1 -- Will help.

  F. Set AUTOEXTEND ON for data files in DATA -- Won't help as problem is quota to user not the space