探究oracle clob字段是怎样存储的
clob字段存储的是文本,假设存储少于4000个字节则clob字段会和记录存放在一起,假设存储超过4000个字节,则不会与记录存放在一起。
值得注意的是,这里的4000个字节并非等同于varchar2(4000),那相当于什么呢。请看下列实验,数据库版本号是10.2.0.1.0:
drop table test_clob purge;
create table test_clob
(
id number,
clob1 clob
);
insert into test_clob values(1,Lpad('111',100,'1'));--236
insert into test_clob values(2,Lpad('222',500,'1'));--1036
insert into test_clob values(3,Lpad('333',1000,'1'));--2036
insert into test_clob values(4,Lpad('444',1982,'1'));--4000
insert into test_clob values(5,Lpad('555',1983,'1'));--4002
commit;
select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test_clob;
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ---------- ---------- ---------- ----------
AAAYQbAAFAADRpcAAA 99355 5 858716 0
AAAYQbAAFAADRpcAAB 99355 5 858716 1
AAAYQbAAFAADRpcAAC 99355 5 858716 2
AAAYQbAAFAADRpdAAA 99355 5 858717 0
AAAYQbAAFAADRpgAAA 99355 5 858720 0
alter system dump datafile 5 block 858716;
alter system dump datafile 5 block 858717;
alter system dump datafile 5 block 858720;
查看dump文件能够看到:
col 0: [ 2] c1 02 --记录id=1
为啥是1 请看我曾经的帖子:http://blog.csdn.net/stevendbaguo/article/details/8010105
col 1: [236]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 02 1a 85 00 d8 09 00 00
00 00 00 00 c8 00 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31
...........................................................................
...........................................................................
col 0: [ 2] c1 03 --记录id=2
col 1: [1036]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 02 1a 86 03 f8 09 00 00
00 00 00 03 e8 00 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31
...........................................................................
...........................................................................
col 0: [ 2] c1 04 --记录id=3
col 1: [2036]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 02 1a 87 07 e0 09 00 00
00 00 00 07 d0 00 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31
...........................................................................
...........................................................................
col 0: [ 2] c1 05 --记录id=4
col 1: [4000]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 02 1a 88 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31
...........................................................................
...........................................................................
col 0: [ 2] c1 06 --记录id=5
col 1: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 02 1a 89 00 14 05 00 00
00 00 00 0f 7e 00 00 00 00 00 02 01 4d 98 ac
记录 Lpad('111',100,'1'));--236
记录 Lpad('222',500,'1')--1036
记录 Lpad('333',1000,'1')--2036
记录 Lpad('444',1982,'1')--4000
记录 Lpad('555',1983,'1')--4002
看出规律来了,第一条记录我们觉得的长度是100,存在block中的长度是236,就是236=100*2+36。其它的规律都是这样。同一时候也能够看到存储超过4000个字节,就是长度为1982时这个就是临界值,大于这个之后就clob就存到其它地方了。
相关文章
- Oracle如何查看当前PDB容器信息
- oracle存储过程函数
- ORACLE 存储过程
- oracle存储过程报错> ORA-24344: success with compilation error的原因,execute immediate 执行动态SQL
- Navicat 用户界面 | MySQL、Oracle、MariaDB、SQLsever、SQLite、PostgreSQL
- [转]Oracle存储过程总结
- [转]Oracle的update语句优化研究
- oracle 存储过程重新编译方法
- ORACLE中通过DBMS_CRYPTO包对表敏感字段进行加密
- oracle多实例的启动与关闭
- Oracle DB优化
- ORACLE存储过程实现等表WAIT_TABLE
- Mybatis调用Oracle存储过程,Oracle创建存储过程,Mybatis调用procedure
- oracle交互命令
- Oracle定时存储过程
- Oracle 9.2.0.1 升级至 Oracle11.2 (32位windows)
- 如何评估oracle AWR 的在oracle 中的存储情况
- cmd命令实现Oracle数据库用户授权
- [Oracle]快速构造大量数据的方法
- Oracle SQL Loader切身实例
- CYQ.Data 数据框架 数据库分页方式及存储过程[SQL2000/SQL2005/Oracle]
- Oracle问题小记五:服务启动-索引-子查询-分页存储过程
- Oracle的expdp导出、impdp导出命令
- Oracle 数据库、表、方案的逻辑备份与恢复
- Oracle 系统常用命令
- Oracle给Select结果集加锁,Skip Locked(跳过加锁行获得可以加锁的结果集)