原题
在Oracle数据库中，有如下内容的一张表TAB1，请问，以下创建唯一索引的语句中，哪些可以成功执行？
SQL> select * from tab1;
ID COL1
---------- ----------
1 A
2 B
C
D
A、create unique index ind_tab1_id on tab1(id);
B、create unique index ind_tab1_col1_desc on tab1(col1 desc);
C、create unique index ind_tab1_id_desc on tab1(id desc);
D、create unique index ind_tab1_col1 on tab1(col1);
答案：ABD
一、建表
scott@orcl> create table t(id int,name varchar2(20),card varchar2(20),pho number);
Table created.
scott@orcl> insert into t values(1,'A',111111111,13111111111);
1 row created.
scott@orcl> insert into t values(2,'b',2222222222,13222222222);
1 row created.
scott@orcl> insert into t values('','c',333333333,133333333333);
1 row created.
scott@orcl> insert into t values('','d','',13444444444);
1 row created.
scott@orcl> commit;
Commit complete.
scott@orcl> select * from t;
ID NAME CARD PHO
---------- -------------------------------------------------- ---------------------------------------- ----------
1 A 111111111 1.3111E+10
2 b 2222222222 1.3222E+10
c 333333333 1.3333E+11
d 1.3444E+10
scott@orcl> set numw 20
scott@orcl> /
ID NAME CARD PHO
-------------------- -------------------------------------------------- ---------------------------------------- --------------------
1 A 111111111 13111111111
2 b 2222222222 13222222222
c 333333333 133333333333
d 13444444444
二、dump数据块
scott@orcl> select id,name,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid) block# from t;
ID NAME FILE# BLOCK#
-------------------- -------------------------------------------------- -------------------- --------------------
1 A 7 350
2 b 7 350
c 7 350
d 7 350
scott@orcl> alter system dump datafile 7 block 350;
System altered.
scott@orcl> select * from v$diag_info;
INST_ID NAME
-------------------- --------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------
CON_ID
--------------------
1 Diag Enabled
TRUE
0
1 ADR Base
/u01/app/oracle
0
1 ADR Home
/u01/app/oracle/diag/rdbms/orcl/orcl
0
1 Diag Trace
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
0
1 Diag Alert
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
0
1 Diag Incident
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
0
1 Diag Cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
0
1 Health Monitor
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
0
1 Default Trace File
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_48870.trc
0
1 Active Problem Count
0
0
1 Active Incident Count
0
0
1 ORACLE_HOME
/u01/app/oracle/product/19.0.0/db_1
0
三、数据块
Block header dump: 0x01c0015e
Object id on Block? Y
seg/obj: 0x12501 csc: 0x00000000005138eb itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c00158 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.005.0000067c 0x010001d7.01cb.08 --U- 4 fsc 0x0000.00513a98 --事物已经提交，但是锁还没有清除,影响4行
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01c0015e
data_block_dump,data header at 0x965be064
0x01c0015e--> 0001 1100 0000 0000 0001 0101 1110-->0001 1100 00 00 0000 0001 0101
===============
-- 0x01c0015e-->0000 0001 1100 0000 0000 0001 0101 1110-->(0000 0001 11)(00 0000 0000 0001 0101 1110)-->7 350
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x965be064
76543210
flag=--------
ntab=1 --表数量
nrow=4 --行数量
frre=-1 --需要创建索引
fsbo=0x1a
fseo=0x1f3c
avsp=0x1f22
tosp=0x1f22
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f7e
0x14:pri[1] offs=0x1f63
0x16:pri[2] offs=0x1f4b
0x18:pri[3] offs=0x1f3c
block_row_dump:
tab 0, row 0, @0x1f7e
tl: 26 fb: --H-FL-- lb: 0x1 cc: 4 --行数
此处代表每一行得数据，不同的字符类型转换方式不一样
col 0: [ 2] c1 02
col 1: [ 1] 41 --A
col 2: [ 9] 31 31 31 31 31 31 31 31 31 --111111111
col 3: [ 7] c6 02 20 0c 0c 0c 0c --
tab 0, row 1, @0x1f63
tl: 27 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 2] c1 03
col 1: [ 1] 62
col 2: [10] 32 32 32 32 32 32 32 32 32 32
col 3: [ 7] c6 02 21 17 17 17 17
tab 0, row 2, @0x1f4b
tl: 24 fb: --H-FL-- lb: 0x1 cc: 4
col 0: *NULL*
col 1: [ 1] 63
col 2: [ 9] 33 33 33 33 33 33 33 33 33
col 3: [ 7] c6 0e 22 22 22 22 22
tab 0, row 3, @0x1f3c
tl: 15 fb: --H-FL-- lb: 0x1 cc: 4
col 0: *NULL*
col 1: [ 1] 64
col 2: *NULL*
col 3: [ 7] c6 02 23 2d 2d 2d 2d
end_of_block_dump
四、创建第一个索引
scott@orcl> alter session set events '10046 trace name context forever,level 12';
Session altered.
scott@orcl> create unique index ind_t_id on t(name desc);
Index created.
EXEC #139904316939736:c=825,e=888,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=4,plh=2683643009,tim=95981534675
CLOSE #139904316939736:c=1,e=1,dep=1,type=3,tim=95981534728
EXEC #139904316090312:c=87924,e=192722,p=9,cr=361,cu=91,mis=0,r=0,dep=0,og=1,plh=605356462,tim=95981534943
STAT #139904316090312 id=1 cnt=1 pid=0 pos=1 obj=0 op='INDEX BUILD UNIQUE IND_T_ID (cr=13 pr=0 pw=0 str=1 time=3914 us)'
STAT #139904316090312 id=2 cnt=4 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=7 pr=0 pw=0 str=1 time=63 us)'
STAT #139904316090312 id=3 cnt=4 pid=2 pos=1 obj=75009 op='TABLE ACCESS FULL T (cr=7 pr=0 pw=0 str=1 time=34 us cost=2 size=984 card=82)'
WAIT #139904316090312: nam='log file sync' ela= 2217 buffer#=2672 sync scn=5333913 p3=0 obj#=328 tim=95981537433
WAIT #139904316090312: nam='PGA memory operation' ela= 31 p1=0 p2=0 p3=0 obj#=328 tim=95981537570
WAIT #139904316090312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=328 tim=95981537597
WAIT #139904316090312: nam='SQL*Net message from client' ela= 433 driver id=1650815232 #bytes=1 p3=0 obj#=328 tim=95981538046
PARSE #139904317414640:c=29,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=95981538117
BINDS #139904317414640:
Bind#0
oacdty=123 mxl=656(656) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1400000 frm=00 csi=00 siz=680 off=0
toid ptr value=6F5B3900 length=16
86B64B666E77012EE053F706E80A06
kxsbbbfp=7f3e0324d3d0 bln=656 avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=656
kxsbbbfp=7f3e0324d660 bln=22 avl=02 flg=01
value=15
WAIT #139904317414640: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=328 tim=95981538369
EXEC #139904317414640:c=240,e=240,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=95981538392
两个等待
log file sync、PGA memory operation
有意思的等待事件，详细见
https://fritshoogland.wordpress.com/2017/03/01/oracle-12-2-wait-event-pga-memory-operation/
五、dump第一个索引
scott@orcl> select object_name,object_id from dba_objects where object_name='IND_T_ID';
OBJECT_NAME OBJECT_ID
------------------------------ ----------
IND_T_ID 75025
scott@orcl> alter system set events 'immediate trace name treedump level 75025';
System altered.
scott@orcl> select * from v$diag_info;
----- begin tree dump
leaf: 0x1c497eb 29661163 (0: row:4.4 avs:7944)
----- end tree dump
[oracle@19c01:/home/oracle]$vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_63035.trc
sys@orcl> select dbms_utility.data_block_address_file(29661163),dbms_utility.data_block_address_block(29661163) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29661163) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29661163)
---------------------------------------------- -----------------------------------------------
7 301035
Object id on Block? Y
seg/obj: 0x12511 csc: 0x000000000051638e itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c497e8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000051638e
Leaf block dump
===============
header address 139726569345124=0x7f14a0881064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7988=0x1f34
kdxcoavs 7944
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 03
col 0; len 2; (2): 9b ff
row#1[8010] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 02
col 0; len 2; (2): 9c ff
row#2[7999] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 01
col 0; len 2; (2): 9d ff
row#3[7988] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 00
col 0; len 2; (2): be ff
六、第二个索引
scott@orcl> alter system set events 'immediate trace name treedump level 75030';
System altered.
scott@orcl> select dbms_utility.data_block_address_file(29661171),dbms_utility.data_block_address_block(29661171) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29661171) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29661171)
---------------------------------------------- -----------------------------------------------
7 301043
scott@orcl> alter system dump datafile 7 block 301043;
System altered.
\
Block header dump: 0x01c497f3
Object id on Block? Y
seg/obj: 0x12516 csc: 0x0000000000517fd7 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c497f0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000000000517fd7
Leaf block dump
===============
header address 140276218159204=0x7f949a276064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8010=0x1f4a
kdxcoavs 7970
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 00
col 0; len 2; (2): c1 02
row#1[8010] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 01
col 0; len 2; (2): c1 03
只有两行
七、第三个索引
header address 140360327327844=0x7fa82f737064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7988=0x1f34
kdxcoavs 7944
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 03
col 0; len 2; (2): 9b ff
row#1[8010] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 02
col 0; len 2; (2): 9c ff
row#2[7999] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 01
col 0; len 2; (2): 9d ff
row#3[7988] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5e 00 00
col 0; len 2; (2): be ff
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
八、错误
scott@orcl> create unique index idx on t(id desc);
create unique index idx on t(id desc)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
EXEC #139858163333920:c=554,e=658,p=0,cr=2,cu=1,mis=0,r=1,dep=1,og=4,plh=0,tim=99722425398
CLOSE #139858163333920:c=1,e=0,dep=1,type=3,tim=99722425421
WAIT #139858163973528: nam='PGA memory operation' ela= 17 p1=65536 p2=1 p3=0 obj#=-1 tim=99722425614
EXEC #139858163973528:c=6921,e=6776,p=0,cr=27,cu=54,mis=0,r=0,dep=0,og=1,plh=1638586637,tim=99722425699
ERROR #139858163973528:err=1452 tim=99722425712
最后修改时间：2022-06-08 12:29:17
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
