Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 1:

  Which three files are used by conventional path SQL*Loader when the TABLE option is not specified? (Choose three.)

  A. dump files

  B. control files

  C. password files

  D. bad files

  E. input files

  Correct Answer: BDE

  SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc, DISCARDMAX=5

  https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm

• Question 2:

  Which two statements are true about the configuration and use of UNDO_RETENTION with GURANTEED RETENTION? (Choose two.)

  A. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.

  B. UNDO_RETENTION specifies how long all types of UNDO are retained.

  C. Unexpired UNDO is always retained.

  D. Active UNDO is always retained.

  E. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.

  Correct Answer: CD

  By definition, undo extents that still have to overlive the retention period are unexpired. So, when undo_retention is set and retention is guaranteed, unexpired undo is always retained.

  As opposed to the case in which retention is noguarantee, where also unexpired extents can be overwritten IF and when needed.

  In this particolar case, so, I would say C-D are the correct answers.

• Question 3:

  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.

  Correct Answer: AE

  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.

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

• Question 4:

  Which two statements are true about Enterprise Manager (EM) Express?

  A. You can use a single instance of EM Express to manage multiple database running on the same server.

  B. EM Express uses a separate repository database to store target database metadata.

  C. By default, EM express is available for a database after database creation using DBCA.

  D. You can shut down a database instance using EM Express.

  E. You cannot start up a database instance using EM Express.

  Correct Answer: CE

• Question 5:

  Examine this SQL statement:

  SELECT cust_id, cust_last_name "Last Name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30

  Identify three ORDER BY clauses, any one of which can complete the query successfully. (Choose three.)

  A. ORDER BY "Last Name"

  B. ORDER BY 2, 1

  C. ORDER BY 2, cust_id

  D. ORDER BY CUST_NO

  E. ORDER BY "CUST_NO"

  Correct Answer: ABC

• Question 6:

  Which three activities are recorded in the database alert log? (Choose three.)

  A. Data Definition Language (DDL) statements

  B. non-default database parameters

  C. block corruption errors

  D. deadlock errors

  E. session logins and logouts

  Correct Answer: BCD

  All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur The values of all initialization parameters that had nondefault values at the time the database and instance start

  https://docs.oracle.com/cd/B28359_01/server.111/b28310/monitoring001.htm#ADMIN11247

• Question 7:

  In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.

  Examine this command:

  SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);

  Which segment or segments, if any, are created as a result of executing the command?

  A. T1, an index segment for the primary key, a LOB segment, and a lobindex segment

  B. no segments are created

  C. T1 only

  D. T1 and an index segment created for the primary key only

  E. T1, an index segment for the primary key, and a LOB segment only

  Correct Answer: B

  DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table. Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time

  SQL> alter session set deferred_segment_creation=TRUE;

  Session altered.

  SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);

  Table created.

  SQL> select segment_name,segment_type,bytes from user_segments;

  no rows selected

• Question 8:

  You execute this command:

  CREATE SMALLFILE TABLESPACE sales DATAFILE `/u01/app/oracle/sales01.dbf SIZE 5G SEGMENT SPACE MANAGEMENT AUTO;

  Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose two.)

  A. It must be smaller than the smallest BIGFILE tablespace

  B. Free space is managed using freelists

  C. Any data files added to the tablespace must have a size of 5 gigabytes

  D. It uses the database default blocksize E. It is a locally managed tablespace

  Correct Answer: DE

  Incorrect segment management clause It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace. AUTO :Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. MANUAL :Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists

  E - Correct https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11360 Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces,

  D - Correct https://docs.oracle.com/cd/B28359_01/server.111/b28310/create005.htm#ADMIN11105 The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific.

• Question 9:

  Examine this command:

  CREATE UNDO TABLESPACE undotbs01

  DATAFILE `undotbs_01.dbf'

  SIZE 100M

  AUTOEXTEND ON;

  Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace?

  (Choose two.)

  A. Add the SEGMENT SPACE MANAGEMENT AUTO clause

  B. Set UNDO_TABLESPACE to UNDOTBS01

  C. Add the NOLOGGING clause

  D. Make certain that the database operates in automatic undo management mode

  E. Add the ONLINE clause

  Correct Answer: BD

  SQL>alter system set undo_management=manual scope=spfile; Bounce instance.

  SQL> show parameter undo NAME TYPE VALUE

  temp_undo_enabled boolean FALSE

  undo_management string MANUAL

  undo_retention integer 900

  undo_tablespace string UNDOTBS1

  SQL> alter system set undo_tablespace='UNDOTBS2';

  alter system set undo_tablespace='UNDOTBS2'

  *

  ERROR at line 1:

  ORA-02097: parameter cannot be modified because specified value is invalid

  ORA-30014: operation only supported in Automatic Undo Management mode

• Question 10:

  Which three statements are true about views in an Oracle database? (Choose three.)

  A. Views can be updated without the need to re-grant privileges on the view

  B. Tables in the defining query of a view must always exist in order to create the view

  C. The WITH CHECK clause prevents certain rows from being displayed when querying the view

  D. Data Manipulation Language (DML) can always be used on views

  E. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error

  F. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error

  G. The WITH CHECK clause prevents certain rows from being updated or inserted

  Correct Answer: EFG

  A is wrong because it should be views can be replaced*** without the need to re-grant privileges on the view. Not update

  G is correct, certain rows cannot be updated or inserted . for example. you try to update the department_id to 100 from 20 but the view was created with a where department id_= 20 or insert a deparment_id other than 20 . it will cause a ORA-01402 error