Oracle__12. Oracle 1Z0-051 Exam - Sequence Objects Flashcards
What is returned by this statement? CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; SELECT seq1.nextval FROM dual
1The first statement creates the sequence but does not have the MINVALUE so the sequences starts as 1
Can a Sequence Object be referenced in a CHECK constraint at a column level or table level?
Neither.A Sequence object cannot be used as a constraint
Can a Sequence Object be referenced in a DEFAULT value?
No
What keyword from a SEQUENCE object refers to the last sequence number that has been generated?
CURRVAL
Are numbers generated from a sequence object only available for one table?
No.Sequence number are available to multiple tables or queries.
What happens when the sequence object reached the MAXVALUE.
an additional request for a NEXTVAL will receive an error
What statement can be used to increase the maximum value of a Sequence object?
ALTER SEQUENCE sequencename MAXVALUE newvalue
What happens with sequence number if any are used in a transaction and the transactions is rolled back?
They are lost.
What is the syntax to remove a sequence object from a database?
DROP SEQUENCE sequencename
What happens to a Sequence object when a table is dropped?
Nothing
What is the default minimum value of an ascending Sequence?
1
What is the default sequence value of an ascending Sequence?
1
What two references are used to retrieve information from a Sequence object?
- NEXTVAL2. CURRVAL
What 3 clauses can use a NEXTVAL or CURRVAL?
- SELECT2. VALUES of an INSERT3. SET of an UPDATE
What keyword added to a SELECT statement does not allow NEXTVAL or CURRVAL?
DISTINCT
Can a NEXTVAL or CURRVAL be used as a DEFAULT value in a CREATE statement?
No
What clause added to a select statement does not allow NEXTVAL or CURRVAL?
GROUP BY
Can NEXTVAL or CURRVAL be used in a Subquery?
No
What 3 instances can a sequence have a gap in the numbers?
- roll back2. system crash3. same sequence is used on multiple tables
How many values will oracle cache for a Sequence object?
20 values
What is the primary use of the SEQUENCE object?
Primary keys
What keyword in the CREATE SEQUENCE statement is not recommended if the Sequence is used for Primary keys?
CYCLE
Between CYCLE and NOCYCLE what is the default value when creating a Sequence?
NOCYCLE
What happens when a sequence reaches the MAXVALUE and the CYCLE values is set?
The sequence restarts at the MINVALUE
The CYCLE and NOCYCLE are keyword for which database object?
SEQUENCE
What happens to a sequence when the NEXTVAL reaches the MAXVALUE and the CYCLE option is specified?
The NEXTVAL becomes the MINVALUE
