Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 141:

  You start your database instance in NOMOUNT state. Which two actions are performed?

  A. The control files are opened.

  B. The consistency of the database is checked.

  C. All required background processes are started.

  D. SYS can access the database.

  E. Memory is allocated for the SGA.

  Correct Answer: CE

• Question 142:

  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

  Correct Answer: BD

• Question 143:

  Examine the description of the SALES1 table:

  

  SALES2 is a table with the same description as SALES1.

  Some sales data is duplicated in both tables.

  You want to display the rows from the SALES1 table which are not present in the SALES2 table.

  Which set operator generates the required output?

  A. INTERSECT

  B. UNION ALL

  C. UNION

  D. SUBTRACT

  E. MINUS

  Correct Answer: B

• Question 144:

  Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

  A. Duplicates are eliminated automatically by the UNION ALL operator

  B. The number of columns selected in each SELECT statement must be identical

  C. The names of columns selected in each SELECT statement must be identical

  D. The output is sorted by the UNION ALL operator

  E. NULLS are not ignored during duplicate checking

  Correct Answer: BE

• Question 145:

  Examine these commands:

  

  Which two statements are true about the sqlldr execution? (Choose two.)

  A. It overwrites data in EMP with data in EMP.DAT

  B. It uses the database buffer cache to load data

  C. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations

  D. It generates a sql script that it uses to load data from EMP.DAT to EMP

  E. It appends data from EMP.DAT to EMP

  Correct Answer: CE

  C and E are correct - ..."SQL*Loader express mode generates a log file that includes a SQL*Loader control file. The log file also contains SQL scripts for creating the external table and performing the load using a SQL INSERT AS SELECT statement. Neither the control file nor the SQL scripts are used by SQL*Loader express mode. They are made available to you in case you want to use them as a starting point to perform operations using regular SQL*Loader or standalone external tables; the control file is for use with SQL*Loader, whereas the SQL scripts are for use with standalone external tables operations."

• Question 146:

  Examine these SQL statements which execute successfully:

  

  Which two statements are true after execution? (Choose two.)

  A. The primary key constraint will be enabled and IMMEDIATE.

  B. The primary key constraint will be enabled and DEFERRED.

  C. The foreign key constraint will be enabled and DEFERRED.

  D. The foreign key constraint will be disabled.

  E. The foreign key constraint will be enabled and IMMEDIATE.

  Correct Answer: AD

• Question 147:

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

  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

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

  You must create a tablespace of non-standard block size in a new file system and plan to use this command:

  

  The standard block size is 8k but other non-standard block sizes will also be used. Which two are requirements for this command to succeed?

  A. DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.

  B. DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.

  C. DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.

  D. The /u02 file system must have at least 100g space for the datafile.

  E. The operating system must use a 32k block size.

  F. DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.

  Correct Answer: BD

• Question 150:

  Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

  A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

  B. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.

  C. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.

  D. Partition key columns cannot be dropped.

  E. A DROP COLUMN command can be rolled back

  F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table

  Correct Answer: ADF

  Answer A is RIGHT: Oders is Parent table with PRIMARY KEY ord_no, Order_items is child table which ord_no is REFERENCE KEY that reference ord_no of Parent table, now drop PRIMARY KEY on Orders by command: ALTER TABLE orders DROP COLUMN ORD_NO CASCADE CONSTRAINTS; Answer D is RIGHT: ORA-12984: cannot drop partitioning column Answer F is RIGHT: Unused Columns Count against 1000-column Table Limit Causing ORA-01792 on Compressed Table (Doc ID 2259600.1) and "ORA-01792: Maximum Number Of Columns In A Table Or View Is 1000" AND HIDDEN_COLUMN name is recreated with date and timestamp (Doc ID 2624150.1)