Exam 1z0-071 topic 1 question 220 discussion

Actual exam question from Oracle's 1z0-071

Question #: 220
Topic #: 1

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)

A. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
B. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times.
C. Column ORD_NO gets the next number from sequence and ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
D. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
E. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

Show Suggested Answer

Suggested Answer: CE 🗳️

by Shrimathi at July 20, 2023, 11:31 a.m.

Comments

Submit Cancel

826cb95

1 week, 6 days ago

Selected Answer: CD

C no doubts then its D not E This is a common misconception. If ord_seq.NEXTVAL is used in a DEFAULT clause, users do not need direct privileges on the sequence to insert into the table. The database evaluates the default on behalf of the user.

upvoted 2 times

luisem

6 days, 17 hours ago

To support D is correct: When a sequence is used in a DEFAULT clause, Oracle Database does not require that the user has direct SELECT privileges on the sequence. The value is generated by the system as part of the insert. [https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SEQUENCE.html] - See “Using Sequences in DEFAULT clauses”

upvoted 1 times

...

yaya32

11 months, 1 week ago

C for sure, then I am unsure D or E

upvoted 1 times

...

zangado

1 year ago

E is correct only if we assume that user doesn't insert ord_no explicitly. If he does than he doesn't need the select privilege on the sequence. In my oppinion only C is correct.

upvoted 1 times

ogi33

7 months, 2 weeks ago

• During table creation, the sequence must exist and you must have select privilege on it for it to be used as a column default. • The users performing inserts against the table must have select privilege on the sequence, as well as insert privilege on the table.

upvoted 1 times

...