在一个表中最多可以有1000列还是255列

来源：赛迪网    作者：张辰钧

问：表最多能有1000列还是255列？

答：最多可以有1000列，每一row pieve最多255列。

具体示例如下：

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

0xeti[0] nrow=4 offs=0

0x12ri[0] offs=0x1be1

0x14ri[1] offs=0x17df

0x16ri[2] offs=0x13dc

0x18ri[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
<