Latest 1Z0-082 exam dumps with real Oracle questions and answers [Q35-Q59]

Rate this post

Latest 1Z0-082 exam dumps with real Oracle questions and answers

1Z0-082 Exam in First Attempt Guaranteed

Oracle 1Z1-082 Certification Exam consists of 70 multiple-choice and multiple-answer questions that must be completed within 105 minutes. 1Z0-082 exam covers a range of topics, including database architecture, installation, configuration, backup and recovery, security, performance tuning, and more. Candidates are required to score at least 64% to pass the exam.

Q35. What is true about non-equijoin statement performance? (Choose two.)

Table aliases can improve performance

The BETWEEN condition always performs better than using the >= and <= conditions

The join syntax used makes no difference to performance

The BETWEEN condition always performs less well than using the >= and <= conditions

The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

Q36. Which two types of files are opened by a database instance in the NOMOUNT state?

control files

the alert log

the initialization parameter file

data files

online redo logs

Q37. 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?

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_incoms_level IS NOT NULLAND due_amount IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level != NULLAND cust_credit_level !=NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level <> NULLAND due_amount <> NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level != NULLAND due_amount !=NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHERE cust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;

Q38. 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.)

Add a data file to DATA

Drop other U1 objects in DATA

Increase U1’s quota sufficiently in DATA

Set DEFERRED_SEGMENT_CREATION to TRUE

Grant UNLIMITED TABLESPACE to U1

Set AUTOEXTEND ON for data files in DATA

Q39. 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.)

Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.

Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.

Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

Add the AUTOEXTEND ON clause with NEXT set to 100M.

Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

Q40. 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.)

Revoke the CREATE SESSION privilege from other users

Revoke the CREATE SESSION privilege from user HR

Grant the CREATE SESSION privilege with ADMIN OPTION to other users

Execute DDL statements in the HR schema

Execute DML statements in the HR schema

Q41. While one of your databases was in mount state, the datafiles were renamed because they had been moved to a new file system. The database was then opened.
Which two statements are true?

DBA_DATA_FILES displays the original name for the data files.

V$DATAFILE displays the new names for the data files.

DBA_DATA _FILES displays both the new name and the old name for the data files.

DBA_DATA_FILES displays the new name for the data files.

DBA_DATA_FILES must be resynchronized manually with the control file an order to have it display the new file names.

Q42. Examine this command and some partial output:

Why does the DB01.abc.comservice show unknown status?

The service DB01.abc.com is dynamically registered

The LOCAL_LISTENER database parameter is not set to a service name that refers to LISTENER_1

The service DB01.abc.com is statically registered

The listener is not listening on the default port 1521

The SID_LIST_LISTENER section is not contained in the LISTENER.ORA file

Q43. Which three functions are performed by dispatchers in a shared server configuration? (Choose three.)

writing inbound request to the common request queue from all shared server connections

checking for outbound shared server responses on the common outbound response queue

receiving inbound requests from processes using shared server connections

sending each connection input request to the appropriate shared server input queue

broadcasting shared server session responses back to requesters on all connections

sending shared server session responses back to requesters on the appropriate connection

Q44. 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.)

Employee 100 will have SALARY set to the same value as the SALARY of employee 200

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Employee 200 will have SALARY set to the same value as the SALARY of employee 100

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200

Employees 100 and 200 will have the same JOB_ID as before the update command

Employees 100 and 200 will have the same SALARY as before the update command

Q45. 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.)

The sales user must have a quota on the TEMP tablespace

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

The sales user must have been granted the CREATE SESSION privilege

The sales user must have their quota on the users tablespace removed

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

The sales user must have been granted the CREATE TABLE privilege

Q46. Which two statements are true about views used for viewing tablespace and datafile information?
(Choose two.)

Tablespace free space can be viewed in V$TABLESPACE

V$TABLESPACE displays information that is contained in the controlfile about tablespaces

V$TABLESPACE displays information about tablespaces contained in the data dictionary

Tablespace free space can be viewed in DBA_TABLESPACES

A datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened

Q47. Examine this command:

Which two statements are true? (Choose two.)

DML may be performed on tables with one or more extents in this data file during the execution of this command.

The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.

The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.

If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.

The file is renamed and stored in the same location

Q48. 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.)

SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

SELECT DISTINCT promo_cost || ‘ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;

SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;

SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Q49. 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.)

Employee 100 will have SALARY set to the same value as the SALARY of employee 200

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Employee 200 will have SALARY set to the same value as the SALARY of employee 100

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200

Employees 100 and 200 will have the same JOB_ID as before the update command

Employees 100 and 200 will have the same SALARY as before the update command

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

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

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

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

Partition key columns cannot be dropped.

A DROP COLUMN command can be rolled back

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

Q51. Which statement is true about aggregate functions?

Aggregate functions can be nested to any number of levels

The AVG function implicitly converts NULLS to zero

Aggregate functions can be used in any clause of a SELECT statement

The MAX and MIN functions can be used on columns with character data types

Q52. You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), ‘MON’), ‘dd “Monday for” fmMonth rrrr’) What is the result?

It executes successfully but does not return any result

It returns the date for the first Monday of the next month

It generates an error

It returns the date for the last Monday of the current month

Q53. View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval.
Examine this SQL statement:

Exhibit 1.

Exhibit 2.

What will be the result?

It gives an error because the ALL keyword is not valid

It gives an error because the GROUP BY clause is not valid

It executes successfully but does not give the required result

It executes successfully and gives the required result

Q54. 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?

It succeeds and the generated undo is stored in SYSTEM.

It fails and returns the error message “ORA-30036: unable to extend segment by 8 in undo tablespace
‘UNDOTBS1′”.

It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.

It succeeds and the generated undo is stored in SYSAUX.

It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

Q55. Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session:
INSERT INTO books VALUES (‘ADV112’, ‘Adventures of Tom Sawyer’, NULL, NULL); SAVEPOINT a; DELETE FROM books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true? (Choose two.)