SQL> create table tt(id int);
表已创建。
SQL> edit
已写入 file afiedt.buf
1 declare
2 v_string varchar2(100);
3 begin
4 for i in 1..1000 loop
5 v_string := 'alter table tt add col'||i||' int' ;
6 execute immediate v_string;
7 end loop ;
8* end;
SQL> /
declare
*
第 1 行出现错误:
ORA-01792: 表或视图中的最大列数为 1000
ORA-06512: 在 line 6
SQL>
－－＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Row Format and Size
Oracle stores each row of a database table containing
data for less than 256 columns as one or more row
pieces. If an entire row can be inserted into a
single data block, then Oracle stores the row as
one row piece. However, if all of a row's data
cannot be inserted into a single data block or if
an update to an existing row causes the row to
outgrow its data block, then Oracle stores the
row using multiple row pieces. A data block usually
contains only one row piece for each row. When Oracle
must store a row in more than one row piece, it is
chained across multiple blocks.
When a table has more than 255 columns, rows that have
data after the 255th column are likely to be chained
within the same block. This is called intra-block
chaining. A chained row's pieces are chained together
using the rowids of the pieces. With intra-block chaining,
users receive all the data in the same block. If the row
fits in the block, users do not see an effect in I/O
performance, because no extra I/O operation is required
to retrieve the rest of the row.
--======================================================
接着上面的测试再做个测试看看
SQL> insert into tt(id) values(1)
2 ;
已创建 1 行。
SQL> commit;
提交完成。
SQL> edit
已写入 file afiedt.buf
1 declare
2 v_string varchar2(100);
3 begin
4 for i in 1..999 loop
5 v_string := 'update tt set col'||i||'='||i ;
6 execute immediate v_string;
7 commit;
8 end loop ;
9* end;
SQL> /
SQL> analyze table tt compute statistics;
表已分析。
SQL> select blocks,chaiN_cnt from user_tables where table_name='TT';
BLOCKS CHAIN_CNT
---------- ----------
4 0
SQL> select id , rowid from tt;
ID ROWID
---------- ------------------
1 AAADD5AAEAAAAAUAAA
SQL> alter system dump datafile 4 block 20;
系统已更改。
--===============================================================
dump:
Start dump data blocks tsn: 8 file#: 4 minblk 20 maxblk 20
buffer tsn: 8 rdba: 0x01000014 (4/20)
scn: 0x0000.001ae24e seq: 0x01 flg: 0x04 tail: 0xe24e0601
frmt: 0x02 chkval: 0x641b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x06DB2200 to 0x06DB4200
..................................................
6DB41F0 3503C203 3603C203 3703C203 E24E0601 [...5...6...7..N.]
Block header dump: 0x01000014
Object id on Block? Y
seg/obj: 0x30f9 csc: 0x00.1ae24e itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.022.00000106 0x00000000.0000.00 C--- 0 scn 0x0000.001ae231
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x6db227c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x06db227c
bdba: 0x01000014
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x102f
avsp=0x1015
tosp=0x1015
0xe<ccid_file values="images/smilies/13"
border="0" alt="吐舌" />ti[0] nrow=4 offs=0
0x12<ccid_file values="images/smilies/13"
border="0" alt="吐舌" />ri[0] offs=0x1be1
0x14<ccid_file values="images/smilies/13"
border="0" alt="吐舌" />ri[1] offs=0x17df
0x16<ccid_file values="images/smilies/13"
border="0" alt="吐舌" />ri[2] offs=0x13dc
0x18<ccid_file values="images/smilies/13"
border="0" alt="吐舌" />ri[3] offs=0x102f
block_row_dump:
tab 0, row 0, @0x1be1
tl: 927 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x01000014.1
col 0: [ 2] c1 02
col 1: [ 2] c1 02
col 2: [ 2] c1 03
col 3: [ 2] c1 04
col 4: [ 2] c1 05
col 5: [ 2] c1 06
col 6: [ 2] c1 07
col 7: [ 2] c1 08
col 8: [ 2] c1 09
col 9: [ 2] c1 0a
col 10: [ 2] c1 0b
col 11: [ 2] c1 0c
col 12: [ 2] c1 0d
col 13: [ 2] c1 0e
col 14: [ 2] c1 0f
col 15: [ 2] c1 10
col 16: [ 2] c1 11
col 17: [ 2] c1 12
col 18: [ 2] c1 13
col 19: [ 2] c1 14
col 20: [ 2] c1 15
col 21: [ 2] c1 16
col 22: [ 2] c1 17
col 23: [ 2] c1 18
col 24: [ 2] c1 19
col 25: [ 2] c1 1a
col 26: [ 2] c1 1b
col 27: [ 2] c1 1c
col 28: [ 2] c1 1d
col 29: [ 2] c1 1e
col 30: [ 2] c1 1f
col 31: [ 2] c1 20
col 32: [ 2] c1 21
col 33: [ 2] c1 22
col 34: [ 2] c1 23
col 35: [ 2] c1 24
col 36: [ 2] c1 25
col 37: [ 2] c1 26
col 38: [ 2] c1 27
col 39: [ 2] c1 28
col 40: [ 2] c1 29
col 41: [ 2] c1 2a
col 42: [ 2] c1 2b
col 43: [ 2] c1 2c
col 44: [ 2] c1 2d
col 45: [ 2] c1 2e
col 46: [ 2] c1 2f
col 47: [ 2] c1 30
col 48: [ 2] c1 31
col 49: [ 2] c1 32
col 50: [ 2] c1 33
col 51: [ 2] c1 34
col 52: [ 2] c1 35
col 53: [ 2] c1 36
col 54: [ 2] c1 37
col 55: [ 2] c1 38
col 56: [ 2] c1 39
col 57: [ 2] c1 3a
col 58: [ 2] c1 3b
col 59: [ 2] c1 3c
col 60: [ 2] c1 3d
col 61: [ 2] c1 3e
col 62: [ 2] c1 3f
col 63: [ 2] c1 40
col 64: [ 2] c1 41
col 65: [ 2] c1 42
col 66: [ 2] c1 43
col 67: [ 2] c1 44
col 68: [ 2] c1 45
col 69: [ 2] c1 46
col 70: [ 2] c1 47
col 71: [ 2] c1 48
col 72: [ 2] c1 49
col 73: [ 2] c1 4a
col 74: [ 2] c1 4b
col 75: [ 2] c1 4c
col 76: [ 2] c1 4d
col 77: [ 2] c1 4e
col 78: [ 2] c1 4f
col 79: [ 2] c1 50
col 80: [ 2] c1 51
col 81: [ 2] c1 52
col 82: [ 2] c1 53
col 83: [ 2] c1 54
col 84: [ 2] c1 55
col 85: [ 2] c1 56
col 86: [ 2] c1 57
col 87: [ 2] c1 58
col 88: [ 2] c1 59
col 89: [ 2] c1 5a
col 90: [ 2] c1 5b
col 91: [ 2] c1 5c
col 92: [ 2] c1 5d
col 93: [ 2] c1 5e
col 94: [ 2] c1 5f
col 95: [ 2] c1 60
col 96: [ 2] c1 61
col 97: [ 2] c1 62
col 98: [ 2] c1 63
col 99: [ 2] c1 64
col 100: [ 2] c2 02
col 101: [ 3] c2 02 02
col 102: [ 3] c2 02 03
col 103: [ 3] c2 02 04
col 104: [ 3] c2 02 05
col 105: [ 3] c2 02 06
col 106: [ 3] c2 02 07
col 107: [ 3] c2 02 08
col 108: [ 3] c2 02 09
col 109: [ 3] c2 02 0a
col 110: [ 3] c2 02 0b
col 111: [ 3] c2 02 0c
col 112: [ 3] c2 02 0d
col 113: [ 3] c2 02 0e
col 114: [ 3] c2 02 0f
col 115: [ 3] c2 02 10
col 116: [ 3] c2 02 11
col 117: [ 3] c2 02 12
col 118: [ 3] c2 02 13
col 119: [ 3] c2 02 14
col 120: [ 3] c2 02 15
col 121: [ 3] c2 02 16
col 122: [ 3] c2 02 17
col 123: [ 3] c2 02 18
col 124: [ 3] c2 02 19
col 125: [ 3] c2 02 1a
col 126: [ 3] c2 02 1b
col 127: [ 3] c2 02 1c
col 128: [ 3] c2 02 1d
col 129: [ 3] c2 02 1e
col 130: [ 3] c2 02 1f
col 131: [ 3] c2 02 20
col 132: [ 3] c2 02 21
col 133: [ 3] c2 02 22
col 134: [ 3] c2 02 23
col 135: [ 3] c2 02 24
col 136: [ 3] c2 02 25
col 137: [ 3] c2 02 26
col 138: [ 3] c2 02 27
col 139: [ 3] c2 02 28
col 140: [ 3] c2 02 29
col 141: [ 3] c2 02 2a
col 142: [ 3] c2 02 2b
col 143: [ 3] c2 02 2c
col 144: [ 3] c2 02 2d
col 145: [ 3] c2 02 2e
col 146: [ 3] c2 02 2f
col 147: [ 3] c2 02 30
col 148: [ 3] c2 02 31
col 149: [ 3] c2 02 32
col 150: [ 3] c2 02 33
col 151: [ 3] c2 02 34
col 152: [ 3] c2 02 35
col 153: [ 3] c2 02 36
col 154: [ 3] c2 02 37
col 155: [ 3] c2 02 38
col 156: [ 3] c2 02 39
col 157: [ 3] c2 02 3a
col 158: [ 3] c2 02 3b
col 159: [ 3] c2 02 3c
col 160: [ 3] c2 02 3d
col 161: [ 3] c2 02 3e
col 162: [ 3] c2 02 3f
col 163: [ 3] c2 02 40
col 164: [ 3] c2 02 41
col 165: [ 3] c2 02 42
col 166: [ 3] c2 02 43
col 167: [ 3] c2 02 44
col 168: [ 3] c2 02 45
col 169: [ 3] c2 02 46
col 170: [ 3] c2 02 47
col 171: [ 3] c2 02 48
col 172: [ 3] c2 02 49
col 173: [ 3] c2 02 4a
col 174: [ 3] c2 02 4b
col 175: [ 3] c2 02 4c
col 176: [ 3] c2 02 4d
col 177: [ 3] c2 02 4e
col 178: [ 3] c2 02 4f
col 179: [ 3] c2 02 50
col 180: [ 3] c2 02 51
col 181: [ 3] c2 02 52
col 182: [ 3] c2 02 53
col 183: [ 3] c2 02 54
col 184: [ 3] c2 02 55
col 185: [ 3] c2 02 56
col 186: [ 3] c2 02 57
col 187: [ 3] c2 02 58
col 188: [ 3] c2 02 59
col 189: [ 3] c2 02 5a
col 190: [ 3] c2 02 5b
col 191: [ 3] c2 02 5c
col 192: [ 3] c2 02 5d
col 193: [ 3] c2 02 5e
col 194: [ 3] c2 02 5f
col 195: [ 3] c2 02 60
col 196: [ 3] c2 02 61
col 197: [ 3] c2 02 62
col 198: [ 3] c2 02 63
col 199: [ 3] c2 02 64
col 200: [ 2] c2 03
col 201: [ 3] c2 03 02
col 202: [ 3] c2 03 03
col 203: [ 3] c2 03 04
col 204: [ 3] c2 03 05
col 205: [ 3] c2 03 06
col 206: [ 3] c2 03 07
col 207: [ 3] c2 03 08
col 208: [ 3] c2 03 09
col 209: [ 3] c2 03 0a
col 210: [ 3] c2 03 0b
col 211: [ 3] c2 03 0c
col 212: [ 3] c2 03 0d
col 213: [ 3] c2 03 0e
col 214: [ 3] c2 03 0f
col 215: [ 3] c2 03 10
col 216: [ 3] c2 03 11
col 217: [ 3] c2 03 12
col 218: [ 3] c2 03 13
col 219: [ 3] c2 03 14
col 220: [ 3] c2 03 15
col 221: [ 3] c2 03 16
col 222: [ 3] c2 03 17
col 223: [ 3] c2 03 18
col 224: [ 3] c2 03 19
col 225: [ 3] c2 03 1a
col 226: [ 3] c2 03 1b
col 227: [ 3] c2 03 1c
col 228: [ 3] c2 03 1d
col 229: [ 3] c2 03 1e
col 230: [ 3] c2 03 1f
col 231: [ 3] c2 03 20
col 232: [ 3] c2 03 21
col 233: [ 3] c2 03 22
col 234: [ 3] c2 03 23
col 235: [ 3] c2 03 24
col 236: [ 3] c2 03 25
col 237: [ 3] c2 03 26
col 238: [ 3] c2 03 27
col 239: [ 3] c2 03 28
col 240: [ 3] c2 03 29
col 241: [ 3] c2 03 2a
col 242: [ 3] c2 03 2b
col 243: [ 3] c2 03 2c
col 244: [ 3] c2 03 2d
col 245: [ 3] c2 03 2e
col 246: [ 3] c2 03 2f
col 247: [ 3] c2 03 30
col 248: [ 3] c2 03 31
col 249: [ 3] c2 03 32
col 250: [ 3] c2 03 33
col 251: [ 3] c2 03 34
col 252: [ 3] c2 03 35
col 253: [ 3] c2 03 36
col 254: [ 3] c2 03 37
tab 0, row 1, @0x17df
tl: 1026 fb: -------- lb: 0x0 cc: 255
nrid: 0x01000014.2
col 0: [ 3] c2 03 38
col 1: [ 3] c2 03 39
col 2: [ 3] c2 03 3a
col 3: [ 3] c2 03 3b
col 4: [ 3] c2 03 3c
col 5: [ 3] c2 03 3d
col 6: [ 3] c2 03 3e
col 7: [ 3] c2 03 3f
col 8: [ 3] c2 03 40
col 9: [ 3] c2 03 41
col 10: [ 3] c2 03 42
col 11: [ 3] c2 03 43
col 12: [ 3] c2 03 44
col 13: [ 3] c2 03 45
col 14: [ 3] c2 03 46
col 15: [ 3] c2 03 47
col 16: [ 3] c2 03 48
col 17: [ 3] c2 03 49
col 18: [ 3] c2 03 4a
col 19: [ 3] c2 03 4b
col 20: [ 3] c2 03 4c
col 21: [ 3] c2 03 4d
col 22: [ 3] c2 03 4e
col 23: [ 3] c2 03 4f
col 24: [ 3] c2 03 50
col 25: [ 3] c2 03 51
col 26: [ 3] c2 03 52
col 27: [ 3] c2 03 53
col 28: [ 3] c2 03 54
col 29: [ 3] c2 03 55
col 30: [ 3] c2 03 56
col 31: [ 3] c2 03 57
col 32: [ 3] c2 03 58
col 33: [ 3] c2 03 59
col 34: [ 3] c2 03 5a
col 35: [ 3] c2 03 5b
col 36: [ 3] c2 03 5c
col 37: [ 3] c2 03 5d
col 38: [ 3] c2 03 5e
col 39: [ 3] c2 03 5f
col 40: [ 3] c2 03 60
col 41: [ 3] c2 03 61
col 42: [ 3] c2 03 62
col 43: [ 3] c2 03 63
col 44: [ 3] c2 03 64
col 45: [ 2] c2 04
col 46: [ 3] c2 04 02
col 47: [ 3] c2 04 03
col 48: [ 3] c2 04 04
col 49: [ 3] c2 04 05
col 50: [ 3] c2 04 06
col 51: [ 3] c2 04 07
col 52: [ 3] c2 04 08
col 53: [ 3] c2 04 09
col 54: [ 3] c2 04 0a
col 55: [ 3] c2 04 0b
col 56: [ 3] c2 04 0c
col 57: [ 3] c2 04 0d
col 58: [ 3] c2 04 0e
col 59: [ 3] c2 04 0f
col 60: [ 3] c2 04 10
col 61: [ 3] c2 04 11
col 62: [ 3] c2 04 12
col 63: [ 3] c2 04 13
col 64: [ 3] c2 04 14
col 65: [ 3] c2 04 15
col 66: [ 3] c2 04 16
< |
