[20220322]探究oracle sequence 2.txt

[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。