OCP-1Z0-051-2015-21题

最新推荐文章于 2024-08-09 09:09:44 发布

原创最新推荐文章于 2024-08-09 09:09:44 发布 · 444 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文深入解析数据库序列的创建、使用和管理，特别关注CURRVAL的功能与序列最大值限制的调整。同时，讨论了序列在数据库实例异常关闭时的行为，以及如何在内存崩溃情况下恢复序列的连续性。通过实际代码示例，阐述了序列如何在多个表中共享，但可能带来的数据断点问题，并提供了有效的解决方案。

Which two statements are true about sequences created in a single instance database? (Choosetwo.)、

A. CURRVAL is used to refer to the last sequence number that has been generated
B. DELETE <sequencename> would remove a sequence from the database
C. The numbers generated by a sequence can be used only for one table
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit
by using the ALTER SEQUENCE statement
E. When a database instance shuts down abnormally, the sequence numbers that have been

cached but not used would be available once again when the database instance is restarted

Answer: A,D

Explanation: 在单个数据库实例里面创建序列

A 正确，对，CURRVAL是最后的那个值

B不正确，因为删除序列的语句为DROP SEQUENCE sequencename

To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;

C不正确，因为一个序列生成的值可以用于多个表

D正确，对当最大值达到极限时，可以通过ALTER SEQUENCE修改序列来增加最大值

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are

allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE.

To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement

E不正确，因为如果数据库实例关闭，内存非正常关闭，缓存的数会丢失。

如果建立序列时不指定CACHE和NOCACHE，默认值为20，

即一次性从序列里取20个数放入内存，如果内存崩溃，则这20个数就会丢失，再取值时从第21个数开始取值，CACHE设置的最小值为2。

　1、oracle中sequence就是所谓的序列号，每次取自动增加

Create Sequence 首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限，

　　CREATE SEQUENCE emp_sequence

　　INCREMENT BY 1　 -- 每次加几个

　　START WITH 1　　 -- 从1开始计数

　　NOMAXVALUE　　　 -- 不设置最大值

　　NOCYCLE　　　　　-- 一直累加，不循环

　　CACHE 10;

　　一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL

　　CURRVAL=返回 sequence的当前值

　　NEXTVAL=增加sequence的值，然后返回 sequence 值

Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs
independent of a commit or rollback. Therefore, if you roll back a statement containing a
sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence caches
values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence