Update 1z0-082 Prep 2021

NEW QUESTION 1
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEE_ID column is indexed.
Rows exist for employees 100 and 200. Examine this statement:

Which two statements are true? (Choose two.)

• A. Employee 100 will have SALARY set to the same value as the SALARY of employee 200
• B. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
• C. Employee 200 will have SALARY set to the same value as the SALARY of employee 100
• D. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
• E. Employees 100 and 200 will have the same JOB_ID as before the update command
• F. Employees 100 and 200 will have the same SALARY as before the update command

Answer: AB

NEW QUESTION 2
Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)? (Choose two.)

• A. PCTFREE defaults to 10% for all blocks in all segments for all compression methods
• B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
• C. Update operations always attempt to find blocks with free space appropriate to the length of the row being updated
• D. Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
• E. A block will always be eligible for inserts if the row is short enough to fit into the block

Answer: CE

NEW QUESTION 3
Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

• A. The Oracle join syntax supports creation of a Cartesian product of two tables
• B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
• C. The SQL:1999 compliant ANSI join syntax supports natural joins
• D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables
• E. The Oracle join syntax only supports right outer joins
• F. The Oracle join syntax supports natural joins
• G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

Answer: CDF

NEW QUESTION 4
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

Answer: AC

NEW QUESTION 5
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

Answer: BE

NEW QUESTION 6
Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

• A. Column names in each SELECT in the compound query can be different
• B. The number of columns in each SELECT in the compound query can be different
• C. Reversing the order of the intersected tables can sometimes affect the output
• D. INTERSECT returns rows common to both sides of the compound query
• E. INTERSECT ignores NULLs

Answer: AE

NEW QUESTION 7
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

Answer: ABF

NEW QUESTION 8
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)

• A. Column positions must be used in the ORDER BY clause
• B. Only column names from the first SELECT statement in the compound query are recognized
• C. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause
• D. Each SELECT statement in the compound query must have its own ORDER BY clause
• E. Each SELECT statement in the compound query can have its own ORDER BY clause

Answer: BE

NEW QUESTION 9
Which two statements are true about the DUAL table? (Choose two.)

• A. It can be accessed only by the SYS user
• B. It consists of a single row and single column of VARCHAR2 data type
• C. It can display multiple rows but only a single column
• D. It can be used to display only constants or pseudo columns
• E. It can be accessed by any user who has the SELECT privilege in any schema
• F. It can display multiple rows and columns

Answer: AB

NEW QUESTION 10
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: 2021-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

Answer: CDF

NEW QUESTION 11
In your data center, Oracle Managed Files (OMF) is used for all databases. All tablespaces are smallfile tablespaces.
SALES_Q1 is a permanent user-defined tablespace in the SALES database.
Examine this command which is about to be issued by a DBA logged in to the SALES database: ALTER TABLESPACE sales_q1 ADD DATAFILE;
Which are two actions, either one of which you could take to ensure that the command executes successfully? (Choose two.)

• A. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least50 Mb of available space.
• B. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.
• C. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.
• D. Add the AUTOEXTEND ON clause with NEXT set to 100M.
• E. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

Answer: BD

NEW QUESTION 12
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

Answer: BD

NEW QUESTION 13
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

Answer: ABF

NEW QUESTION 14
You want to use table compression suitable for OLTP that will:
Compress rows for all DML statements on that table
Minimize the overheads associated with compression
Which compression option is best suited for this?

• A. COLUMN STORE COMPRESS FOR QUERY LOW
• B. ROW STORE COMPRESS BASIC
• C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
• D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
• E. ROW STORE COMPRESS ADVANCED

Answer: E

NEW QUESTION 15
You have been tasked to create a table for a banking application. One of the columns must meet three requirements:
Be stored in a format supporting date arithmetic without using conversion functions
Store a loan period of up to 10 years
Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?

• A. INTERVAL YEAR TO MONTH
• B. INTERVAL DAY TO SECOND
• C. TIMESTAMP WITH LOCAL TIMEZONE
• D. TIMESTAMP
• E. TIMESTAMP WITH TIMEZONE

Answer: B

NEW QUESTION 16
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

Answer: C

NEW QUESTION 17
You want to apply the principle of Least Privilege in all your live databases.
One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis.
Which three types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose three.)

• A. analysis of all privileges used by all users including administrative users in the database
• B. analysis of all privileges used by all users but excluding administrative users in the database
• C. analysis of privileges that a user has on their own schema objects that they did not use
• D. analysis of privileges that a user has on their own schema objects that they did use
• E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role
• F. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role

Answer: ACF

NEW QUESTION 18
Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)

• A. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
• B. SELECT DISTINCT promo_cost || ’ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;
• C. SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;
• D. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
• E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Answer: DE

NEW QUESTION 19
The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace. Segment creation is not deferred.
You execute this command:

Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose three.)

• A. The sales user must have a quota on the TEMP tablespace
• B. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema
• C. The sales user must have been granted the CREATE SESSION privilege
• D. The sales user must have their quota on the users tablespace removed
• E. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema
• F. The sales user must have been granted the CREATE TABLE privilege

Answer: BDF

NEW QUESTION 20
In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE
UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens. What will happen when the next update is attempted by any transaction?

• A. It succeeds and the generated undo is stored in SYSTEM.
• B. It fails and returns the error message “ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’ “.
• C. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.
• D. It succeeds and the generated undo is stored in SYSAUX.
• E. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

Answer: C

NEW QUESTION 21
Which three statements are true about the DESCRIBE command? (Choose three.)

• A. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
• B. It can be used from SQL Developer
• C. It displays the NOT NULL constraint for any columns that have that constraint
• D. It can be used to display the structure of an existing view
• E. It displays all constraints that are defined for each column
• F. It can be used only from SQL *Plus

Answer: CDF

NEW QUESTION 22
In one of your databases, you create a user, HR, and then execute this command: GRANT CREATE SESSION TO hr WITH ADMIN OPTION;
Which three actions can HR perform? (Choose three.)

• A. Revoke the CREATE SESSION privilege from other users
• B. Revoke the CREATE SESSION privilege from user HR
• C. Log in to the database instance
• D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
• E. Execute DDL statements in the HR schema
• F. Execute DML statements in the HR schema

Answer: ADE

NEW QUESTION 23
Which three are types of segments in an Oracle Database? (Choose three.)

• A. undo
• B. index
• C. stored procedures
• D. sequences
• E. tables
• F. clusters

Answer: BEF

NEW QUESTION 24
......