[20180415]blob的插入.txt
--//论坛上有人问的问题,如何限制BLOB字段的大小，用户只能上传15K至30K的图片，超过30K就不给上传.
--//链接:http://www.itpub.net/thread-482195-1-1.html
--//我自己也测试许久,始终没有找到好的解决方法,这个主要是插入blob时,实际上先插入empty_blob(),获取定位符合,
--//然后插入相关信息,这个过程中无法获得blob字段的大小,关于限制blob大小的问题先放弃,
--//先探究blob的插入:
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> alter database add supplemental log data;
Database altered.
--//开启附加日志主要目的是logminer能观察到相关日志.
2.建立测试脚本:
SCOTT@book> create table t (id number,image blob));
Table created.
--//从网上抄了一段代码,修改如下:
--//链接:zhidao.baidu.com/question/569359922.html
$ cd /u01/app/oracle/admin/book/dpdump
$ ls -l 1.txt
-rw-r--r-- 1 oracle oinstall 6578 2018-04-11 09:11:24 1.txt
--//文件我写的比较特殊每行2047个字符.这样加上回车正好2047.
$ cat c1.txt
declare
b_file bfile;
b_lob blob;
begin
insert into t values(2,empty_blob()) return image into b_lob;
b_file:=bfilename('DATA_PUMP_DIR','1.txt');
dbms_lob.open(b_file,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
dbms_lob.close(b_file);
commit;
end;
/
3.插入跟踪看看:
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
13277727401 2018-04-15 15:13:01
SCOTT@book> @ &r/10046on 12
Session altered.
SCOTT@book> insert into t values (1,'aabb');
1 row created.
SCOTT@book> @ c1.txt
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/10046off
Session altered.
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
13277727464 2018-04-15 15:14:04
--//从10046跟踪基本看不出来:
=====================
PARSING IN CURSOR #139798488606408 len=31 dep=0 uid=83 oct=2 lid=83 tim=1523776424786732 hv=1834852568 ad='7d2e7e68' sqlid='5321f9xqpv86s'
insert into t values (1,'aabb')
END OF STMT
PARSE #139798488606408:c=999,e=1587,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1523776424786726
EXEC #139798488606408:c=1000,e=494,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=0,tim=1523776424787335
STAT #139798488606408 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=438 us)'
WAIT #139798488606408: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1523776424787527
*** 2018-04-15 15:13:49.537
WAIT #139798488606408: nam='SQL*Net message from client' ela= 4750330 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1523776429537892
CLOSE #139798488606408:c=1000,e=29,dep=0,type=0,tim=1523776429538020
=====================
PARSING IN CURSOR #139798488606408 len=312 dep=0 uid=83 oct=47 lid=83 tim=1523776429543248 hv=850764855 ad='7dbaf510' sqlid='guc865stbb91r'
declare
b_file bfile;
b_lob blob;
begin
insert into t values(2,empty_blob()) return image into b_lob;
b_file:=bfilename('DATA_PUMP_DIR','1.txt');
dbms_lob.open(b_file,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
dbms_lob.close(b_file);
commit;
end;
END OF STMT
PARSE #139798488606408:c=4999,e=5138,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1523776429543246
=====================
PARSING IN CURSOR #139798488597632 len=59 dep=1 uid=83 oct=2 lid=83 tim=1523776429543820 hv=2186787885 ad='7e23fc00' sqlid='ftb5f8k15gg1d'
INSERT INTO T VALUES(2,EMPTY_BLOB()) RETURN IMAGE INTO :O0
END OF STMT
PARSE #139798488597632:c=0,e=280,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1523776429543818
BINDS #139798488597632:
Bind#0
oacdty=113 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=206001 frm=00 csi=00 siz=4000 off=0
kxsbbbfp=7f255f40ab68 bln=4000 avl=00 flg=05
EXEC #139798488597632:c=1000,e=873,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=1,plh=0,tim=1523776429544801
STAT #139798488597632 id=1 cnt=1 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=162 us)'
CLOSE #139798488597632:c=0,e=2,dep=1,type=3,tim=1523776429544938
WAIT #139798488606408: nam='BFILE open' ela= 89 =0 =0 =0 obj#=-1 tim=1523776429545286
WAIT #139798488606408: nam='BFILE get length' ela= 10 =0 =0 =0 obj#=-1 tim=1523776429545403
WAIT #139798488606408: nam='BFILE get length' ela= 8 =0 =0 =0 obj#=-1 tim=1523776429545478
WAIT #139798488572808: nam='BFILE internal seek' ela= 47 =0 =0 =0 obj#=-1 tim=1523776429545709
WAIT #139798488572808: nam='BFILE read' ela= 32 =0 =0 =0 obj#=-1 tim=1523776429546129
WAIT #139798488572808: nam='Disk file operations I/O' ela= 37 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=1523776429546371
WAIT #139798488572808: nam='Disk file operations I/O' ela= 23 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=1523776429546442
WAIT #139798488572808: nam='direct path write' ela= 37 file number=4 first dba=3581 block cnt=1 obj#=-1 tim=1523776429546553
WAIT #139798488572808: nam='direct path sync' ela= 17 File number=4 Flags=0 p3=0 obj#=-1 tim=1523776429546629
WAIT #139798488606408: nam='BFILE closure' ela= 23 =0 =0 =0 obj#=-1 tim=1523776429546824
=====================
4.通过logminer观察:
BEGIN
DBMS_LOGMNR.START_LOGMNR
(
STARTSCN => 13277727401
,ENDSCN => 13277727464
,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE
+ DBMS_LOGMNR.COMMITTED_DATA_ONLY
);
END;
/
set linesize 2000
set pagesize 4999
column sql_redo format a1024
select operation,sql_redo from V$LOGMNR_CONTENTS where seg_name='T' and seg_owner='SCOTT';
OPERATION SQL_REDO
--------------- ----------------------------------------------------------------------------------------------------
INSERT insert into "SCOTT"."T"("ID","IMAGE") values ('1',EMPTY_BLOB());
UPDATE update "SCOTT"."T" set "IMAGE" = HEXTORAW('aabb') where "ID" = '1' and ROWID = 'AAAWIeAAEAAAA30AAB';
INSERT insert into "SCOTT"."T"("ID","IMAGE") values ('2',EMPTY_BLOB());
SEL_LOB_LOCATOR DECLARE
loc_c CLOB;
buf_c VARCHAR2(6144);
loc_b BLOB;
buf_b RAW(6144);
loc_nc NCLOB;
buf_nc NVARCHAR2(6144);
BEGIN
select "IMAGE" into loc_b from "SCOTT"."T" for update;
LOB_WRITE
buf_b := HEXTORAW('61313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131
3233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233
3435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435
363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383
LOB_WRITE 96131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435360a');
dbms_lob.write(loc_b, 2048, 1, buf_b);
END;
LOB_WRITE
buf_b := HEXTORAW('62313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231
3233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233
3435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435
363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383
LOB_WRITE 96231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435360a');
dbms_lob.write(loc_b, 2048, 2049, buf_b);
END;
LOB_WRITE
buf_b := HEXTORAW('633132333435360a');
dbms_lob.write(loc_b, 8, 4097, buf_b);
END;
9 rows selected.
--//你可以看到当插入长度很小(具体应该是4000-36 = 3964)是(id=1)的情况,实际上插入也是执行先插入EMPTY_BLOB(),然后直接update.
insert into "SCOTT"."T"("ID","IMAGE") values ('1',EMPTY_BLOB());
update "SCOTT"."T" set "IMAGE" = HEXTORAW('aabb') where "ID" = '1' and ROWID = 'AAAWIeAAEAAAA30AAB';
--//而插入id=2(使用脚本),调用是一个存储过程:
select "IMAGE" into loc_b from "SCOTT"."T" for update;
--//先获得blob句柄loc_b(logminer的operation很有意思是SEL_LOB_LOCATOR).写内容到buf_b缓存,大小2048字节,然后调用dbms_lob.write函数写入,循环直到写完.
--//即使我脚本执行的是dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));,写入还是分段2k大小写入的.
4.你可以从上面的测试看到,仅仅当插入内容的blob长度很小(<=3964字节)时,oracle内部才使用update修改相关内容.
--//如果修改内容长度大于3964,保存在块外,通过调用dbms_lob.write实现blob的写入,这样看不到执行的update语句.
5.如果加约束呢:
SCOTT@book> alter table t add constraint c_t check (length(image)<=4002) enable ;
alter table t add constraint c_t check (length(image)<=4002) enable
*
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.C_T) - check constraint violated
--//已经有记录存在,不能执行.
SCOTT@book> alter table t add constraint c_t check (length(image)<=4002) enable novalidate;
Table altered.
SCOTT@book> insert into t select 3,image from t where id=2;
insert into t select 3,image from t where id=2
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_T) violated
--//而实际上如果执行脚本c1.txt:
SCOTT@book> @ c1.txt
PL/SQL procedure successfully completed.
--//可以发现可以正常插入,约束根本无效,为什么insert into t select 3,image from t where id=2;约束有效呢?
SCOTT@book> select length(image),t.id from t ;
LENGTH(IMAGE) ID
------------- ------------
4104 2
2 1
4104 2
SCOTT@book> delete from t where id=2 and rownum=1;
1 row deleted.
SCOTT@book> commit ;
Commit complete.
6.继续分析insert+select的情况:
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
13277729967 2018-04-15 15:51:48
SCOTT@book> insert into t select 3,image from t where id=2;
insert into t select 3,image from t where id=2
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_T) violated
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
13277729983 2018-04-15 15:51:58
BEGIN
DBMS_LOGMNR.START_LOGMNR
(
STARTSCN => 13277729967
,ENDSCN => 13277729983
,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE
);
END;
/
--//注意取消DBMS_LOGMAR.COMMITTED_DATA_ONLY,因为没成功插入记录.
set linesize 2000
set pagesize 4999
column sql_redo format a1024
select operation,sql_redo from V$LOGMNR_CONTENTS where seg_name='T' and seg_owner='SCOTT';
OPERATION SQL_REDO
-------------------------------- -----------------------------------------------------------------
INSERT insert into "SCOTT"."T"("ID","IMAGE") values ('3',EMPTY_BLOB());
SEL_LOB_LOCATOR DECLARE
loc_c CLOB;
buf_c VARCHAR2(6156);
loc_b BLOB;
buf_b RAW(6156);
loc_nc NCLOB;
buf_nc NVARCHAR2(6156);
BEGIN
select "IMAGE" into loc_b from "SCOTT"."T" where "ID" = '3' for update;
INTERNAL
LOB_WRITE
buf_b := HEXTORAW('61313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131
3233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233
3435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435
363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383
LOB_WRITE 96131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435360a');
dbms_lob.write(loc_b, 2048, 1, buf_b);
END;
LOB_WRITE
buf_b := HEXTORAW('62313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231
3233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233
3435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435
363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383
LOB_WRITE 96231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435360a');
dbms_lob.write(loc_b, 2048, 2049, buf_b);
END;
LOB_WRITE
buf_b := HEXTORAW('633132333435360a');
dbms_lob.write(loc_b, 8, 4097, buf_b);
END;
INTERNAL
INTERNAL
DELETE delete from "SCOTT"."T" where ROWID = 'AAAWIeAAEAAAA30AAD';
11 rows selected.
--//奇怪,这里定义长度变成了6156.最后回滚了dml操作.很奇怪这里约束其作用了,实在不理解.
SCOTT@book> alter table t drop constraint c_t ;
Table altered.
SCOTT@book> delete from t;
2 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter table t add constraint c_t check (length(image)<=4002) enable ;
Table altered.
SCOTT@book> @c1.txt
PL/SQL procedure successfully completed.
SCOTT@book> select length(image),t.id from t ;
LENGTH(IMAGE) ID
------------- ------------
4104 2
SCOTT@book> insert into t select 3,image from t where id=2;
insert into t select 3,image from t where id=2
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_T) violated
--//如果对比前面视乎insert into+select 内部多了一些个递归操作.操作多了INTERNAL.放弃,不知道问题在那里....
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2152958/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2152958/
344
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
