[20220322]探究oracle sequence 2.txt

--//对oracle sequence做一个简单探究.sequence一般使用更新表的主键.

1.环境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//drop SEQUENCE SCOTT.SEQ1A2B3C;
CREATE SEQUENCE SCOTT.SEQ1A2B3C
  START WITH 88888888
  INCREMENT BY 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 77777777
  NOCYCLE
  CACHE 2222
  ORDER;

--//注:这样主要目的看看对应的堆里面是否可以看到相关信息.

2.测试:

SCOTT@book> select seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88888888

--//注:前面的测试已经知道sott.seq1a2b3c HASH_VALUE=4074355949,这次直接使用.

SYS@book> @curheapp '' 4074355949
  KGLNAHSH KGLHDPAR         SQL_ID            CHILD# KGLHDADR         KGLOBHD0            SIZE0    SIZE1    SIZE2    SIZE3 KGLOBHD4            SIZE4    SIZE5 KGLOBHD6            SIZE6    SIZE7     STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
4074355949 000000007BDD51D0                       10 000000007BDD51D0 000000007BDD4CE8     4728        0        0        0 00                      0        0 00                      0        0          1

HEAP  ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
----- ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
HEAP0 00007F2A8F2E0A48      16015          1          1          1 KGLH0^f2d9bced   000000007BDD3C80       4096 recr           4095 000000007BDD4CE8

no rows selected
no rows selected

$ cat aa.txt
select seq1a2b3c.nextval from dual ;
host sleep &&1

SYS@book> @ fchaz 000000007BDD4CE8
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- -----------------
SGA 000000007BDD4C80          1          1 KGLDA                   240 freeabl           0 00               000000007BDD4D6F

$seq 150 | xargs -IQ -P 150 bash -c "sqlplus scott/book @aa.txt 100"


SYS@book> @ fchaz 000000007BDD4CE8
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- -----------------
SGA 000000007BDD4C80          1          1 KGLDA                   240 freeabl           0 00               000000007BDD4D6F

--//堆描述符大小没有变化。

3.前面的测试提到回话seq的当前值应该在用户的回话区UGA,我当时发现在一个很大的chunk里面,昨天的测试时间问题,没有把我的测试结
果贴出来,今天重复测试看看.

SCOTT@book> select seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88889039

--//88889039-88888888 = 151

SCOTT@book> select dump(88889039,16) from dual ;
DUMP(88889039,16)
---------------------------
Typ=2 Len=5: c4,59,59,5b,28

SCOTT@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.

--//检查转储发现
7F9BF35DB700 F35DB6F8 00007F9B F35C6F98 00007F9B  [..]......o\.....]
7F9BF35DB710 F35C6F98 00007F9B 45530009 32413151  [.o\.......SEQ1A2]
7F9BF35DB720 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB730 00000000 00000000 45530009 32413151  [..........SEQ1A2]
7F9BF35DB740 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB750 00000000 00000000 43530005 0054544F  [..........SCOTT.]
7F9BF35DB760 00000000 00000000 00000000 00000000  [................]
7F9BF35DB770 00000000 00000000 7BDD51D0 00000000  [.........Q.{....]
7F9BF35DB780 7BEDBE60 00000000 00051116 5959C409  [`..{..........YY]
                                        ~~~~~~~~
7F9BF35DB790 0000285B 00000000 00000000 00000000  [[(..............]
             ~~~~~~~~
7F9BF35DB7A0 00000005 00000000 00000000 00000000  [................]

--//打开另外的会话使用SYS用户查询：
SYS@book> @ fchaz 7F9BF35DB790
no rows selected
--//确实查询不到对应的chunk。

SCOTT@book> select seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88889040

SCOTT@book> select dump(88889040,16) from dual ;
DUMP(88889040,16)
---------------------------
Typ=2 Len=5: c4,59,59,5b,29
 

SCOTT@book> @ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_43803_0001.trc

SCOTT@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.

7F9BF35DB710 F35C6F98 00007F9B 45530009 32413151  [.o\.......SEQ1A2]
7F9BF35DB720 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB730 00000000 00000000 45530009 32413151  [..........SEQ1A2]
7F9BF35DB740 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB750 00000000 00000000 43530005 0054544F  [..........SCOTT.]
7F9BF35DB760 00000000 00000000 00000000 00000000  [................]
7F9BF35DB770 00000000 00000000 7BDD51D0 00000000  [.........Q.{....]
7F9BF35DB780 7BEDBE60 00000000 00051116 5959C409  [`..{..........YY]
                                        ~~~~~~~~~
7F9BF35DB790 0000295B 00000000 00000000 00000000  [[)..............]
             ~~~~~~~~
7F9BF35DB7A0 00000005 00000000 00000000 00000000  [................]

--//基本可以确定这个位置就是记录了当前会话的seq的currval值。

4.换成sys用户重复测试看看:

--//drop SEQUENCE SCOTT.SEQ1A2B3C;
CREATE SEQUENCE SCOTT.SEQ1A2B3C
  START WITH 88888888
  INCREMENT BY 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 77777777
  NOCYCLE
  CACHE 2222
  ORDER;

--//重启数据库略.

SYS@book> select scott.seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88888888

SYS@book> select dump(88888888,16) from dual ;
DUMP(88888888,16)
---------------------------
Typ=2 Len=5: c4,59,59,59,59

SYS@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.

--//检查转储可以发现：
7F226EF71900 45530009 32413151 00433342 00000000  [..SEQ1A2B3C.....]
7F226EF71910 00000000 00000000 00000000 00000000  [................]
7F226EF71920 45530009 32413151 00433342 00000000  [..SEQ1A2B3C.....]
7F226EF71930 00000000 00000000 00000000 00000000  [................]
7F226EF71940 43530005 0054544F 00000000 00000000  [..SCOTT.........]
7F226EF71950 00000000 00000000 00000000 00000000  [................]
7F226EF71960 7BEE9400 00000000 7BF2E900 00000000  [...{.......{....]
7F226EF71970 00051118 5959C409 00005959 00000000  [......YYYY......]
                      ~~~~~~~~~~~~~~~~~~
SYS@book> @ fchaz 7F226EF71970
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- -----------------
UGA 00007F226EF70040                       permanent memor        7344 perm              0 00               00007F226EF71CEF
--//再打开一个sys用户查询看看。

SYS@book> @ fchaz 7F226EF71970
no rows selected
--//噢这样就很容易理解了，其它用户即使是sys也看不到其它sys用户的UGA chunk。

SYS@book> oradebug peek 0x7F226EF71974 8
[7F226EF71974, 7F226EF7197C) = 5959C409 00005959

SYS@book> oradebug peek 0x7F226EF71978 4
[7F226EF71978, 7F226EF7197C) = 00005959

--//做一个crack看看，修改该值。

SYS@book> oradebug poke 0x7F226EF71978 4 0x00006459
BEFORE: [7F226EF71978, 7F226EF7197C) = 00005959
AFTER:  [7F226EF71978, 7F226EF7197C) = 00006459

--//0x64相当于十进制99.  0x64 = 100,100-1 = 99.

SYS@book> select scott.seq1a2b3c.currval from dual ;
   CURRVAL
----------
  88888899
--//你可以发现查询当前值不再是前面的88888888，而是88888899,也验证了我修改正确，并且该位置记录的就是当前值。
--//打开新的会话执行：
SYS@book> select scott.seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88888889

--//下一个值还是88888889。