之前整理的一篇文章： & &&
之前整理的一篇文章。&那是还是写blog初期的作品。&2009年10月份的。&转眼一年，写Blog&也比以前成熟了很多。
一.&理论知识
先看一个创建Sequence的语句：
SQL& create sequence seq_tmp
&&2&&increment by 1
&&3&&start with 1
&&4&&nomaxvalue
&&5&&nocycle
序列已创建。
相关参数说明：
&&&&& INCREMENT BY 1 --&每次加几个
&&&&& START WITH 1 --&从1开始计数
&&&&& NOMAXvalue --&不设置最大值
&&&&& NOCYCLE --&一直累加，不循环
&&&&& CACHE 10;&&--设置缓存cache个序列
&&&&& CURRVAL=返回&sequence的当前值
&&&&& NEXTVAL=增加sequence的值，然后返回&sequence&值
更多信息，参考Oracle&联机文档：
CACHE&CACHE(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
CACHENote:
CACHENOCACHE&&NOCACHECACHENOCACHEORDER&&to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDERNOORDER&&&if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
查看user_sequences&表的结构：
SQL& desc user_
&名称&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&是否为空?&类型
&----------------------------------------- -------- ---------------
&SEQUENCE_NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&NOT NULL VARCHAR2(30)
&MIN_VALUE&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&NUMBER
&MAX_VALUE&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&NUMBER
&INCREMENT_BY&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&NOT NULL NUMBER
&CYCLE_FLAG&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&VARCHAR2(1)
&ORDER_FLAG&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&VARCHAR2(1)
&CACHE_SIZE&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&NOT NULL NUMBER
&LAST_NUMBER&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&NOT NULL NUMBER
查看刚才创建的序列seq_tmp&的值：
SQL&&select * from user_sequences where sequence_name='SEQ_TMP';
SEQUENCE_N&&MIN_VALUE MAX_VALUE INCREMENT_BY C O&CACHE_SIZE&LAST_NUMBER
---------- ---------- ---------- ------------ - - ---------- -----------
SEQ_TMP&&&&&&&&&&&&&1 1.0000E+28&&&&&&&&&&&&1 N N&&&&&&&&&20&&&&&&&&&&21
这里有个CACHE_SIZE的值。&我们在创建sequence的时候，启用了cache，但是没有给它值。&所以这里的cache_size&就是系统的模式值。&即20个。
取下一个sequence的值：
SQL&&select seq_tmp.
&&&NEXTVAL
----------
&&&&&&&&&1
SQL&&select seq_tmp.
&&&NEXTVAL
----------
&&&&&&&&&2
查看当前sequence的值：
SQL&&select seq_tmp.
&&&CURRVAL
----------
修改Cache 大小：
&&&&&&&&&&& 如果Cache已经指定，我们可以修改Cache 大小。 alter 命令可以修改sequence中除了start 以外的所有参数。
alter sequence emp_sequence cache 100;
select * from user_sequences where sequence_name=upper('emp_sequence');
一个网友RAC&系统上的测试时结果：
nocache：&&&&&&&&&&&　　&&2100s
cache&＝1000：　　&&&&&&&&55s
差别很明显。
SQL&&create sequence seq_1
序列已创建。
SQL& declare
&&3&&begin
&&4&&for i in 1 .. 10000 loop
&&5&&select seq_1.next
PL/SQL&过程已成功完成。
已用时间:&&00: 00: 02.26
SQL&&create sequence seq_2 cache 20;
序列已创建。
已用时间:&&00: 00: 00.01
SQL& declare
&&3&&begin
&&4&&for i in 1 .. 10000 loop
&&5&&select seq_2.next
PL/SQL&过程已成功完成。
已用时间:&&00: 00: 00.46
SQL&&create sequence seq_3 cache 100;
序列已创建。
已用时间:&&00: 00: 00.05
SQL& declare
&&3&&begin
&&4&&for i in 1 .. 10000 loop
&&5&&select seq_3.next
PL/SQL&过程已成功完成。
已用时间:&&00: 00: 00.37
SQL&&create sequence seq_4 cache 1000;
序列已创建。
已用时间:&&00: 00: 00.04
SQL& declare
&&3&&begin
&&4&&for i in 1 .. 40000 loop
&&5&&select seq_4.next
PL/SQL&过程已成功完成。
已用时间:&&00: 00: 01.31
SQL& declare
&&3&&begin
&&4&&for i in 1 .. 40000 loop
&&5&&select seq_1.next
PL/SQL&过程已成功完成。
已用时间:&&00: 00: 09.33
在自己的本本上测试的，Oracle 11gR2.&&单Instance数据库单会话循环不间断取1-4万个值。
nocache：&&&&&&&&&&&&&2.26s&&&&&&&&&&10000　　　
cache:20&&&&&&&&&&&&&&0.46s&&&&&&&&&&10000
cache:100&&&&&&&&&&&&&0.37s&&&&&&&&&&10000
cache:1000&&&&&&&&&&&&1.31s&&&&&&&&&&40000
nocache：&&&&&&&&&&&&&9.33s&&&&&&&&&40000
基本上cache&大于20的时候性能基本可以接受，nocache的时候性能确实很差．
----------------------------------------------------------------------------------------
以下是本人添加的官网文档说明&
Sequence Pseudocolumns
A&sequence&is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:
CURRVAL: Returns the current value of a sequence
NEXTVAL: Increments the sequence and returns the next value
You must qualify&CURRVAL&and&NEXTVAL&with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL
To refer to the current or next value of a sequence in the schema of another user, you must have been granted either&SELECT&object privilege on the sequence or&SELECT&ANY&SEQUENCE&system privilege, and you must qualify the sequence with the schema containing it:
schema.sequence.CURRVAL
schema.sequence.NEXTVAL
To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:
schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink
A sequence can be accessed by many users concurrently with no waiting or locking.
Where to Use Sequence Values
You can use&CURRVAL&and&NEXTVAL&in the following locations:
The select list of a&SELECT&statement that is not contained in a subquery, materialized view, or view
The select list of a subquery in an&INSERT&statement
The&VALUES&clause of an&INSERT&statement
The&SET&clause of an&UPDATE&statement
You cannot use&CURRVAL&and&NEXTVAL&in the following constructs:
A subquery in a&DELETE,&SELECT, or&UPDATE&statement
A query of a view or of a materialized view
A&SELECT&statement with the&DISTINCT&operator
A&SELECT&statement with a&GROUP&BY&clause or&ORDER&BY&clause
A&SELECT&statement that is combined with another&SELECT&statement with the&UNION,&INTERSECT, or&MINUS&set operator
The&WHERE&clause of a&SELECT&statement
The&DEFAULT&value of a column in a&CREATE&TABLE&or&ALTER&TABLE&statement
The condition of a&CHECK&constraint
Within a single SQL statement that uses&CURRVAL&or&NEXTVAL, all referenced&LONG&columns, updated tables, and locked tables must be located on the same database.
How to Use Sequence Values
When you create a sequence, you can define its initial value and the increment between its values. The first reference to&NEXTVAL&returns the initial value of the sequence. Subsequent references to&NEXTVAL&increment the sequence value by the defined increment and return the new value. Any reference to&CURRVALalways returns the current value of the sequence, which is the value returned by the last reference to&NEXTVAL.
Before you use&CURRVAL&for a sequence in your session, you must first initialize the sequence with&NEXTVAL. Refer to&&for information on sequences.
Within a single SQL statement containing a reference to&NEXTVAL, Oracle increments the sequence once:
For each row returned by the outer query block of a&SELECT&statement. Such a query block can appear in the following places:
A top-level&SELECT&statement
An&INSERT&...&SELECT&statement (either single-table or multitable). For a multitable insert, the reference to&NEXTVAL&must appear in the&VALUESclause, and the sequence is updated once for each row returned by the subquery, even though&NEXTVAL&may be referenced in multiple branches of the multitable insert.
A&CREATE&TABLE&...&AS&SELECT&statement
A&CREATE&MATERIALIZED&VIEW&...&AS&SELECT&statement
For each row updated in an&UPDATE&statement
For each&INSERT&statement containing a&VALUES&clause
For each&INSERT&... [ALL&|&FIRST] statement (multitable insert). A multitable insert is considered a single SQL statement. Therefore, a reference to theNEXTVAL&of a sequence will increase the sequence only once for each input record coming from the&SELECT&portion of the statement. If&NEXTVAL&is specified more than once in any part of the&INSERT&... [ALL&|&FIRST&] statement, then the value will be the same for all insert branches, regardless of how often a given record might be inserted.
For each row merged by a&MERGE&statement. The reference to&NEXTVAL&can appear in the&merge_insert_clause&or the&merge_update_clause&or both. TheNEXTVALUE&value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation. If&NEXTVAL&is specified more than once in any of these locations, then the sequence is incremented once for each row and returns the same value for all occurrences of&NEXTVAL&for that row.
For each input row in a multitable&INSERT&ALL&statement.&NEXTVAL&is incremented once for each row returned by the subquery, regardless of how many occurrences of the&insert_into_clause&map to each row.
If any of these locations contains more than one reference to&NEXTVAL, then Oracle increments the sequence once and returns the same value for all occurrences of&NEXTVAL.
If any of these locations contains references to both&CURRVAL&and&NEXTVAL, then Oracle increments the sequence and returns the same value for both&CURRVALand&NEXTVAL.
This example selects the next value of the employee sequence in the sample schema&hr:
SELECT employees_seq.nextval
FROM DUAL;
This example increments the employee sequence and uses its value for a new employee inserted into the sample table&hr.employees:
INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212',
TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30);
This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);
CREATE SEQUENCE
Use the&CREATE&SEQUENCE&statement to create a&sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
After a sequence is created, you can access its values in SQL statements with the&CURRVAL&pseudocolumn, which returns the current value of the sequence, or the&NEXTVAL&pseudocolumn, which increments the sequence and returns the new value.
If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.
Prerequisites
To create a sequence in your own schema, you must have the&CREATE&SEQUENCE&system privilege.
To create a sequence in another user's schema, you must have the&CREATE&ANY&SEQUENCE&system privilege.
Specify the schema to contain the sequence. If you omit&schema, then Oracle Database creates the sequence in your own schema.
Specify the name of the sequence to be created. The name must satisfy the requirements listed in&.
If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying onlyINCREMENT&BY&-1 creates a descending sequence that starts with -1 and decreases with no lower limit.
To create a sequence that increments without bound, for ascending sequences, omit the&MAXVALUE&parameter or specify&NOMAXVALUE. For descending sequences, omit the&MINVALUE&parameter or specify the&NOMINVALUE.
To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the&MAXVALUE&parameter. For a descending sequence, specify a value for the&MINVALUE&parameter. Also specify&NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
To create a sequence that restarts after reaching a predefined limit, specify values for both the&MAXVALUE&and&MINVALUE&parameters. Also specify&CYCLE.
Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits for an ascending sequence and 27 or fewer digits for a descending sequence. The absolute of this value must be less than the difference of&MAXVALUE&and&MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.
&Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values.
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values.&MAXVALUE&must be equal to or greater than&START&WITH&and must be greater than&MINVALUE.
&Specify&NOMAXVALUE&to indicate a maximum value of 1028-1 for an ascending sequence or -1 for a descending sequence. This is the default.
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values.&MINVALUE&must be less than or equal to&START&WITH&and must be less than&MAXVALUE.
&Specify&NOMINVALUE&to indicate a minimum value of 1 for an ascending sequence or -(1027&-1) for a descending sequence. This is the default.
&Specify&CYCLE&to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
&Specify&NOCYCLE&to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for&CACHE&must be less than the value determined by the following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the&CACHE&parameter.
&Specify&NOCACHE&to indicate that values of the sequence are not preallocated. If you omit both&CACHE&and&NOCACHE, then the database caches 20 sequence numbers by default.
Specify&ORDER&to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER&is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
&Specify&NOORDER&if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
The following statement creates the sequence&customers_seq&in the sample schema&oe. This sequence could be used to provide customer ID numbers when rows are added to the&customers&table.
CREATE SEQUENCE customers_seq
START WITH
INCREMENT BY
The first reference to&customers_seq.nextval&returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.
阅读(...) 评论()关于SEQUENCE CACHE的一点小常识-kisscactus-ITPUB博客
关于SEQUENCE CACHE的一点小常识
360阅读　0评论　
[oracle@rac-node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 10 22:49:39 2016
Copyright (c) , Oracle. &All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create sequence seq_test500
& 2 &minvalue 1
& 3 &maxvalue 999
& 4 &start with 1
& 5 &increment by 500
& 6 &cache 500;
Sequence created.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500'; & & & & & & & & & & & & & &
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE
------------------------------ ------------------------------ ----------
&MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1
1.0000E+15 & & & & &500 N N & & & &500 & & & & & 1
SQL> set line 300
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & & & & 1
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & & & & 1
-----创建时并不会产生cache
SQL> select seq_test500.
& &NEXTVAL
----------
& & & & &1
& &NEXTVAL
----------
& & & &501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &250001
---第一次调用后会CACHE
SQL> select seq_test500.
& &NEXTVAL
----------
& & & 1001
& &NEXTVAL
----------
& & & 1501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &250001
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area & bytes
Fixed Size & & & & & & & & &2257272 bytes
Variable Size & & & & & &
Database Buffers & & & & & bytes
Redo Buffers & & & & & & & &6828032 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & & &2001
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & & &2001
SQL> &select seq_test500.
& &NEXTVAL
----------
& & & 2001
---正常关闭不会将CACHE清空，将上将执行后的下一个值存为last_number，再次调用nextval 和上次的连续
SQL> / & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &&
& &NEXTVAL
----------
& & & 2501
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &252001
--调用一次后，LAST_NUMBER更新为&252001=（CACHE_SIZE*&"&increment by"+currval - "&increment by"）。
SQL> alter system flush SHARED_POOL;
System altered.
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &252001
----数据字典信息不变
SQL> select seq_test500.
& &NEXTVAL
----------
& & 252001
--cache缓存在shared pool中，清空后，从数字字典里面取值
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &502001
SQL> select seq_test500.
& &NEXTVAL
----------
& & 252501
& &NEXTVAL
----------
& & 253001
--上述结果无悬念
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area & bytes
Fixed Size & & & & & & & & &2257272 bytes
Variable Size & & & & & &
Database Buffers & & & & & bytes
Redo Buffers & & & & & & & &6828032 bytes
Database mounted.
Database opened.
SQL> &SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &502001
SQL> select seq_test500. & & & & & & & & & & & & & &
& &NEXTVAL
----------
& & 502001
SQL> SELECT * FROM dba_sequences WHERE sequence_name = 'SEQ_TEST500';
SEQUENCE_OWNER & & & & & & & & SEQUENCE_NAME & & & & & & & & & MIN_VALUE &MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS & & & & & & & & & & & & & &SEQ_TEST500 & & & & & & & & & & & & & & 1 1.0000E+15 & & & & &500 N N & & & &500 & & &752001
--shu abort后，结果可想而知。
--正常shutdown的时候，个人理解为，shared pool 里面的信息，会写回数据字典。
--但不是所有的都写.
SQL> SELECT MIN(sample_time) FROM v$active_session_
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.24.25.263 PM
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.06.46.468 PM
-------------------
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area & bytes
Fixed Size & & & & & & & & &2257272 bytes
Variable Size & & & & & &
Database Buffers & & & & & bytes
Redo Buffers & & & & & & & &6828032 bytes
Database mounted.
Database opened.
SQL> SELECT MIN(sample_time) FROM v$active_session_
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.06.46.468 PM
-------------------
--细看会发现，&v$active_session_history并没写入到dba_ash里面。
参考官网：
DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY.
创建个快照试试：
SQL> exec dbms_workload_repository.create_snapshot('TYPICAL');
PL/SQL procedure successfully completed.
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.34.40.115 PM
SQL> SELECT MIN(sample_time) FROM v$active_session_
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
-------------------
细看，好像少了近三分钟的数据。改下类型试试：
SQL> exec dbms_workload_repository.create_snapshot('ALL');-- ALL
PL/SQL procedure successfully completed.
SQL> SELECT MAX(sample_time) FROM dba_hist_active_sess_
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.38.00.582 PM
SQL> SELECT MIN(sample_time) FROM v$active_session_
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
10-MAR-16 11.27.20.403 PM
SQL> SELECT SYSDATE FROM DUAL;
-------------------
-----还是不太对劲，动态视图是基于表的。对dictionary cache，library cache，等等各种SGA模块理解还需要加强.....
北京皓辰网域网络信息技术有限公司. 版权所有}