[20220321]探究oracle sequence.txt

来源：这里教程网时间：2026-03-03 17:31:27 作者：

[20220321]探究oracle sequence.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 CREATE SEQUENCE SCOTT.SEQ1A2B3C START WITH 88888888 INCREMENT BY 111111 MAXVALUE 9999999999999999999999999999 MINVALUE 77777777 NOCYCLE CACHE 22222222 ORDER; --//注:这样主要目的看看对应的堆里面是否可以看到相关信息. 2.测试: SCOTT@book> select seq1a2b3c.nextval from dual ; NEXTVAL ---------- 88888888 3.分析: SCOTT@book> SELECT * FROM V$DB_OBJECT_CACHE WHERE NAME = 'SEQ1A2B3C' 2 @pr ============================== OWNER : SCOTT NAME : SEQ1A2B3C DB_LINK : NAMESPACE : TABLE/PROCEDURE TYPE : SEQUENCE SHARABLE_MEM : 4728 LOADS : 1 EXECUTIONS : 0 LOCKS : 1 PINS : 0 KEPT : NO CHILD_LATCH : 113901 INVALIDATIONS : 0 HASH_VALUE : 4074355949 LOCK_MODE : NULL PIN_MODE : NONE STATUS : VALID TIMESTAMP : 2022-03-21/09:31:33 PREVIOUS_TIMESTAMP : LOCKED_TOTAL : 2 PINNED_TOTAL : 3 PROPERTY : FULL_HASH_VALUE : 1d266b5d5dcc1827e00d941ef2d9bced ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PL/SQL procedure successfully completed. --//CHILD_LATCH = HASH_VALUE % 2^17 , 4074355949 %2^17 = 113901. --//手工计算FULL_HASH_VALUE看看,是否可以对上. --//sequence namespace与table namespace一样等于1,这样seq的FULL_HASH_VALUE的计算字符串使用seq_name.owner\01\0\0\0. $ echo -e -n 'SEQ1A2B3C.SCOTT\01\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0\+ //' -e 's/ //gp' 1d266b5d5dcc1827e00d941ef2d9bced --//对比前面下划线的内容，正好一致,intel系列的cpu要做一个大小头对调,其他的cpu我不是很清楚. SYS@book> @sharepool/shp4 0 4074355949 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- parent handle address 000000007BCA5918 000000007BCA5918 SEQ1A2B3C 1 0 0 000000007BCA5430 00 4728 0 0 4728 4728 4074355949 10 SYS@book> @curheapp '' 4074355949 KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS ---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ---------- 4074355949 000000007BCA5918 10 000000007BCA5918 000000007BCA5430 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 00007F9CFC0DC558 16529 1 1 1 KGLH0^f2d9bced 000000007BCA43C8 4096 recr 4095 000000007BCA5430 --//堆0在0x000000007BCA43C8开始，大小4096。 SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug dump heapdump_addr 1 0x000000007BCA5430 Statement processed. --//查看转储: *** 2022-03-21 09:42:03.142 Processing Oradebug command 'dump heapdump_addr 1 0x000000007BCA5430' ****************************************************** HEAP DUMP heap name="KGLH0^f2d9bced" desc=0x7bca5430 extent sz=0xfe8 alt=32767 het=56 rec=9 flg=2 opc=0 parent=0x60001190 owner=0x7bca53e0 nex=(nil) xsz=0xfd0 heap=(nil) fl2=0x26, nex=(nil), dsxvers=1, dsxflg=0x0 dsx first ext=0x7bca43f8 EXTENT 0 addr=0x7bca43f8 Chunk 07bca4408 sz= 80 perm "perm " alo=80 Chunk 07bca4458 sz= 3952 perm "perm " alo=448 Total heap size = 4032 FREE LISTS: Bucket 0 size=0 Chunk 07bca4428 sz= 0 kghdsx Total free space = 0 UNPINNED RECREATABLE CHUNKS (lru first): ++++++++++++++++++++++++++++++++++++++ PERMANENT CHUNKS: Chunk 07bca4458 sz= 3952 perm "perm " alo=448 Chunk 07bca4408 sz= 80 perm "perm " alo=80 Permanent space = 4032 ****************************************************** SYS@book> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_36541_0002.trc SYS@book> oradebug peek 0x000000007BCA43C8 4096 1 [07BCA43C8, 07BCA53C8) = 00001001 80B38F00 7BCA33C8 00000000 7BCA54C8 00000000 7BDB8B70 00000000 00000000 00000000 00000001 00060FFF 7BCA5430 00000000 ... *** 2022-03-21 09:43:22.517 Processing Oradebug command 'peek 0x000000007BCA43C8 4096 1' [07BCA43C8, 07BCA53C8) = 00001001 80B38F00 7BCA33C8 00000000 7BCA54C8 ... Dump of memory from 0x07BCA43DC to 0x07BCA53C8 07BCA43D0 00000000 [....] 07BCA43E0 7BDB8B70 00000000 00000000 00000000 [p..{............] 07BCA43F0 00000001 00060FFF 7BCA5430 00000000 [........0T.{....] 07BCA4400 00000000 00000000 00000051 40B38F00 [........Q......@] 07BCA4410 00000000 00000000 00000000 00000000 [................] 07BCA4420 00000050 00000000 00000001 C0B38F00 [P...............] 07BCA4430 00000000 00000000 7BCA54A8 00000000 [.........T.{....] 07BCA4440 7BCA54A8 00000000 00000001 00000000 [.T.{............] 07BCA4450 7BCA43F8 00000000 00000F71 50B38F00 [.C.{....q......P] 07BCA4460 7BCA4408 00000000 7BCA4408 00000000 [.D.{.....D.{....] 07BCA4470 000001C0 00000000 7BCA5918 00000000 [.........Y.{....] 07BCA4480 00000000 00000000 00040000 00000000 [................] 07BCA4490 7BCA53E0 00000000 00000000 00000000 [.S.{............] 07BCA44A0 00000000 00000000 00000000 00000000 [................] Repeat 6 times 07BCA4510 00000000 00000000 00000006 00000000 [................] 07BCA4520 7BCA4560 00000000 00000000 00000000 [`E.{............] 07BCA4530 00000000 00000000 00051100 FFFFFFFF [................] 07BCA4540 00000000 00000000 00000000 00000000 [................] 07BCA4550 00000053 00000053 00000053 00000000 [S...S...S.......] 07BCA4560 00000005 00051100 0004000A 000F0005 [................] 07BCA4570 00080005 0C0C0CC3 00000000 00000000 [................] -------- 07BCA4580 00000000 4EC40000 004E4E4E 00000000 [.......NNNN.....] 07BCA4590 00000000 00000000 646464CE 64646464 [.........ddddddd] 07BCA45A0 64646464 00646464 17C40000 00171717 [ddddddd.........] 07BCA45B0 00000000 00000000 00000000 5D2F03C7 [............../]] ~~~~~~~~ 07BCA45C0 1F4C1417 00000000 00000000 2D2D0000 [..L...........--] ~~~~~~~~ 07BCA45D0 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D2D2D [----------------] 07BCA45E0 2D2D2D2D 2D2D2D2D 2D2D2D2D 00002D2D [--------------..] 07BCA45F0 00000000 59C40005 00646464 00000000 [.......Yddd.....] +++++++++++++++++ 07BCA4600 00000000 00000000 00000000 00000000 [................] Repeat 217 times 07BCA53A0 00000000 00000000 00000021 D0B38F00 [........!.......] 07BCA53B0 7BCA4458 00000000 0A994C30 00000000 [XD.{....0L......] 07BCA53C0 00000000 00000000 [........] SCOTT@book> select dump(88888888,16), dump(111111,16), dump(9999999999999999999999999999,16), dump(77777777,16), dump(22222222,16) from dual ; DUMP(88888888,16) DUMP(111111,16) DUMP(9999999999999999999999999999,16) DUMP(77777777,16) DUMP(22222222,16) --------------------------- --------------------- ---------------------------------------------------------- --------------------------- --------------------------- Typ=2 Len=5: c4,59,59,59,59 Typ=2 Len=4: c3,c,c,c Typ=2 Len=15: ce,64,64,64,64,64,64,64,64,64,64,64,64,64,64 Typ=2 Len=5: c4,4e,4e,4e,4e Typ=2 Len=5: c4,17,17,17,17 CREATE SEQUENCE SCOTT.SEQ1A2B3C START WITH 88888888 INCREMENT BY 111111 MAXVALUE 9999999999999999999999999999 MINVALUE 77777777 NOCYCLE CACHE 22222222 ORDER; --//其它值都可以在堆0里面找到，而START WITH值没有查询到。 SYS@book> @ ddl scott.seq1a2b3c C300 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE SEQUENCE "SCOTT"."SEQ1A2B3C" MINVALUE 77777777 MAXVALUE 9999999999999999999999999999 INCREMENT BY 111111 START WITH 2469222197530 CACHE 22222222 ORDER NOCYCLE ; --//噢，实际上nextval后已经变成了2469222197530，为什么呢？ SCOTT@book> select seq1a2b3c.nextval from dual ; NEXTVAL ---------- 88999999 --//(2469222197530-88888888)/22222222 = 111111,实际上你可以看出cache是"缓存"seq的数量。 SCOTT@book> select dump(2469222197530,16) from dual ; DUMP(2469222197530,16) ----------------------------------- Typ=2 Len=8: c7,3,2f,5d,17,14,4c,1f --//注意看下划线就是对应值，也就是seq加载后在共享池里面记录seq的一些属性。 --//那么应该有一个地方记录seq的下一个值88999999,注我没有再次转储，如果再次转储记录的值应该对应88999999+111111 = 89111110. SCOTT@book> select dump(88999999,16) from dual; DUMP(88999999,16) --------------------------- Typ=2 Len=5: c4,59,64,64,64 --//注意看++++++++是对应值，注意大小头对调。现在记录的是 SYS@book> oradebug peek 0x07BCA45F4 8 [07BCA45F4, 07BCA45FC) = 5AC40005 000B0C0C SCOTT@book> select dump(89111110,16) from dual; DUMP(89111110,16) ------------------------ Typ=2 Len=5: c4,5a,c,c,b --//仔细比较完全可以对上。如果我修改如下，相当于在原来基础上增加了16. SYS@book> oradebug poke 0x07BCA45F8 4 0x001B0C0C BEFORE: [07BCA45F8, 07BCA45FC) = 000B0C0C AFTER: [07BCA45F8, 07BCA45FC) = 001B0C0C --//这样下一次记录应该如下： SCOTT@book> select dump(89111110+16+111111,16) from dual; DUMP(89111110+16+111111,16) --------------------------- Typ=2 Len=5: c4,5a,17,17,26 SCOTT@book> select seq1a2b3c.nextval from dual ; NEXTVAL ---------- 89111126 --//89111110+16 = 89111126 SYS@book> oradebug peek 0x07BCA45F4 8 [07BCA45F4, 07BCA45FC) = 5AC40005 00261717 --//可以和前面dump(89111110+16+111111,16)对上. --//这样存在一个疑问会话的当前值在那里，一定不记录在该堆里面，而是记录在用户的会话区，继续测试: SCOTT@book> select seq1a2b3c.currval from dual ; CURRVAL ---------- 89111126 SCOTT@book> select seq1a2b3c.currval from dual ; CURRVAL ---------- 89111126 SCOTT@book> select dump(89111110+16,16) from dual; DUMP(89111110+16,16) ------------------------- Typ=2 Len=5: c4,5a,c,c,1b --//在scoot用户下执行 SCOTT@book> alter session set events 'immediate trace name heapdump level 0x1004'; Session altered. Chunk 7f88bd5d00f8 sz= 47496 perm "perm " alo=47496 Dump of memory from 0x00007F88BD5D00F8 to 0x00007F88BD5DBA80 7F88BD5D00F0 0000B989 40B38F00 [.......@] 7F88BD5D0100 BD5D00A8 00007F88 BD5D00A8 00007F88 [..].......].....] ... 7F88BD5DB710 BD5C6F98 00007F88 45530009 32413151 [.o\.......SEQ1A2] 7F88BD5DB720 00433342 00000000 00000000 00000000 [B3C.............] 7F88BD5DB730 00000000 00000000 45530009 32413151 [..........SEQ1A2] 7F88BD5DB740 00433342 00000000 00000000 00000000 [B3C.............] 7F88BD5DB750 00000000 00000000 43530005 0054544F [..........SCOTT.] 7F88BD5DB760 00000000 00000000 00000000 00000000 [................] 7F88BD5DB770 00000000 00000000 7BCA5918 00000000 [.........Y.{....] 7F88BD5DB780 7BEBD788 00000000 00051100 0C5AC409 [...{..........Z.] ~~~~~~~~ 7F88BD5DB790 00001B0C 00000000 00000000 00000000 [................] ~~~~~~~~~ 7F88BD5DB7A0 00000005 00000000 BD5DB168 00007F88 [........h.].....] 7F88BD5DB7B0 BD5C61A0 00007F88 00001000 00000000 [.a\.............] 7F88BD5DB7C0 00000000 00000000 00000000 00000000 [................] --//注意看下划线,在一个很大的chunk里面，有一点奇怪的是使用fcha脚本无法定位。不过应该可以确定下划线内容就是该seq的当前值。 SYS@book> @ fcha 7f88bd5d00f8 Find in which heap (UGA, PGA or Shared Pool) the memory address 7f88bd5d00f8 resides... Press ENTER to continue, CTRL+C to cancel... no rows selected --//你可以再做一个select seq1a2b3c.nextval from dual ;,然后再做一次转储,就可以验证我的判断,结果不再贴出了,

编辑推荐：

返回列表

[20220321]探究oracle sequence.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推