oracle中只存储两年数据,Oracle中关于超过253列内部数据的存储

建表语句declare v_sql varchar2(32767) ;begin v_sql :=

建表语句

declare

v_sql varchar2(32767) ;

begin

v_sql := 'create table test ( ' ;

for i in 1..500 loop

v_sql := v_sql || 'name'||i||' varchar2(2000) ,' ;

end loop ;

v_sql := substr(v_sql,1,length(v_sql)-1) ;

v_sql := v_sql || ');' ;

dbms_output.put_line(v_sql) ;

end ;

复制粘贴创建表

_dex@DAVID> desc test

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

NAME1 VARCHAR2(2000)

NAME2 VARCHAR2(2000)

NAME3 VARCHAR2(2000)

.....

NAME500 VARCHAR2(2000)

_dex@DAVID> insert into test (name500) values (lpad(1,300,'d')) ;

1 row created.

_dex@DAVID> select

2 length(t.name500),

3 dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",

4 dbms_rowid.rowid_block_number(t.rowid) as "BLK#",

5 dbms_rowid.rowid_row_number(t.rowid) as "ROW#"

6 from dex.test t

7 /

LENGTH(T.NAME500) FNO# BLK# ROW#

----------------- ---------- ---------- ----------

300 4 925 1

1 4 925 3

300 4 925 5

使用bbed查看

首先看row#为1长度为300的数据

BBED> set dba 4,925

DBA 0x0100039d (16778141 4,925)

BBED> p kdbr

sb2 kdbr[0] @142 7504

sb2 kdbr[1] @144 7250

sb2 kdbr[2] @146 6991

sb2 kdbr[3] @148 6737

sb2 kdbr[4] @150 6177

sb2 kdbr[5] @152 5923

BBED> p *kdbr[1]

rowdata[1327]

-------------

ub1 rowdata[1327] @7374 0x28

BBED> dump /v offset 7374 count 128

File: /u01/apps/Oracle/oradata/david/users01.dbf (4)

Block: 925 Offsets: 7374 to 7501 Dba:0x0100039d

-------------------------------------------------------

2801f501 00039d00 00ffffff ffffffff l (.õ.............

ffffffff ffffffff ffffffff ffffffff l ................

ffffffff ffffffff ffffffff ffffffff l ................

ffffffff ffffffff ffffffff ffffffff l ................

ffffffff ffffffff ffffffff ffffffff l ................

ffffffff ffffffff ffffffff ffffffff l ................

ffffffff ffffffff ffffffff ffffffff l ................

ffffffff ffffffff ffffffff ffffffff l ................

<16 bytes per line>

这些是row piece header

2801f501 00039d00 00

1个字节的flag=28

16进制28=二进制00101000=--H-F---=head of rowpiece + first data piece

1个字节的lb(itl slot)=01

1个字节的cc column count=f5=245 (因为最后一列name500不为空，所以null都需要使用ff来表示，也就是说需要存储500列的数据) 本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网