1z0-082 Exam Dumps - Oracle Database Administration I

Question # 4

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?

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

no segments are created

T1 only

T1 and an index segment created for the primary key only

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

Full Access

Question # 5

Which two queries execute successfully?

SELECT NULLIF(NULL, 100) FROM DUAL;

SELECT COALESCE (100, 'AM FROM DUAL;

SELECT 2TOLLIF(100, 'A') FROM DUAL;

SELECT NULLIF(100,100) FROM DUAL;

SELECT COALESCE(100, NULL, 200) FROM DUAL;

Full Access

Question # 6

Examine this description of the TRANSACTIONS table:

Which two SQL statements execute successfully? (Choose two.)

SELECT customer_id AS â€œCUSTOMER-IDâ€, transaction_date AS DATE, amount + 100 â€œDUESâ€ FROM transactions;

SELECT customer_id AS â€œCUSTOMER-IDâ€, transaction_date AS â€œDATEâ€, amount + 100 DUES FROM transactions;

SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 â€œDUES AMOUNTâ€ FROM transactions;

SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

SELECT customer_id AS â€˜CUSTOMER-IDâ€™, transaction_date AS DATE, amount + 100 â€˜DUES AMOUNTâ€™ FROM transactions;

Full Access

Question # 7

Your database instance is started with a PFILE.

Examine these parameters:

You want to increase the size of the buffer cache.

Free memory is available to increase the size of the buffer cache.

You execute the command:

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;

What is the outcome?

The value is changed only in the PFILE and takes effect at the next instance startup

The value is changed for the current instance and in the PFILE

It fails because the SCOPE clause is missing

Change is applied to the current instance, but does not persist after instance restart

Full Access

Question # 8

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

undo

index

stored procedures

sequences

tables

clusters

Full Access

Question # 9

Which two statements are true about the PMON background process? (Choose two.)

It registers database services with all local and remote listeners known to the database instance

It frees resources held by abnormally terminated processes

It records checkpoint information in the control file

It frees unused temporary segments

It kills sessions that exceed idle time

Full Access

Question # 10

Your database instance is started with an SPFILE.

A PFILE is also available. You execute this command:

ALTER SYSTEM SET DB_CACHE_SIZE=100K;

Where Is the value changed?

in the SPFILE, SPFILE, and memory

in the SPFILE and in memory

only In the SPFILE

in the SPFILE and SPFILEc

only in memory

Full Access

Question # 11

Which two statements are true about UNDO and REDO? (Choose two.)

The generation of UNDO generates REDO

DML modifies Oracle database objects and only generates UNDO

The generation of REDO generates UNDO

DML modifies Oracle database objects and only generates REDO

DML modifies Oracle database objects and generates UNDO and REDO

Full Access

Question # 12

Which two statements are true about the SET VERIFY ON command? (Choose two.)

It can be used only in SQL*Plus

It displays values for variables used only in the WHERE clause of a query

It can be used in SQL Developer and SQL*Plus

It displays values for variables created by the DEFINE command

It displays values for variables prefixed with &&

Full Access

Question # 13

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_income_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;

Full Access

Question # 14

Which three statements are true regarding single row subqueries?

They must be placed on the right side of the comparison operator or condition.

They must be placed on the left side of the comparison operator or condition.

They can be used in the where clause.

A SQL statement may have multiple single row subquery blocks.

They must return a row to prevent errors in the SQL statement.

They can be used in the having clause.

Full Access

Question # 15

Which two statements are true about single row functions? (Choose two.)

MOD : returns the quotient of a division operation

FLOOR : returns the smallest integer greater than or equal to a specified number

TRUNC : can be used with NUMBER and DATE values

CONCAT : can be used to combine any number of values

CEIL : can be used for positive and negative numbers

Full Access

Question # 16

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?

COLUMN STORE COMPRESS FOR QUERY LOW

ROW STORE COMPRESS BASIC

COLUMN STORE COMPRESS FOR ARCHIVE LOW

COLUMN STORE COMPRESS FOR ARCHIVE HIGH

ROW STORE COMPRESS ADVANCED

Full Access

Question # 17

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?

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

TIMESTAMP WITH LOCAL TIMEZONE

TIMESTAMP

TIMESTAMP WITH TIMEZONE

Full Access

Question # 18

Examine the description of the members table:

Examine the partial query:

SELECT city, last__name 1NAME FROM members

You want to display all cities that contain the string an. The cities must be returned in ascending order, with the last names further sorted in descending order.

Which two clauses must you add to the query?

ORDER BY 1, LNAME DESC

ORDER BY 1, 2

WHERE city LIKE *%AN%*

WHERE city = =%AN%'

WHERE city IN (â€¢%AN%')

ORDER BY last_narae DESC, city ASC

Full Access

Question # 19

You execute this query:

SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), â€˜MONâ€™), â€˜dd â€œMonday forâ€ fmMonth rrrrâ€™)

What is the result?