[20180416]clob的插入.txt
--//论坛上有人问的问题,如何限制BLOB字段的大小，用户只能上传15K至30K的图片，超过30K就不给上传.
--//链接:http://www.itpub.net/thread-482195-1-1.html
--//我自己也测试许久,始终没有找到好的解决方法,这个主要是插入blob时,实际上先插入empty_blob(),获取定位符,
--//然后插入相关信息,这个过程中无法获得blob字段的大小,关于限制blob大小的问题先放弃,
--//先探究blob的插入:
--//前面探究了blob插入,今天测试clob插入的情况.
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 clob);
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个字符.这样加上回车正好2048.
$ cat c2.txt
declare
b_file bfile;
b_lob clob;
begin
insert into t values(2,empty_clob()) 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;
/
--//c2.txt脚本不测试了.主要看看insert是否先插入empty_clob(),然后获得定位符,然后修改相关信息.
3.插入跟踪看看:
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
13277813404 2018-04-16 09:19:07
SCOTT@book> insert into t values (1,lpad('a',1983,'1'));
1 row created.
SCOTT@book> insert into t values (2,lpad('b',1982,'2'));
1 row created.
SCOTT@book> commit;
Commit complete.
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
13277813446 2018-04-16 09:19:50
4.通过logminer观察:
BEGIN
DBMS_LOGMNR.START_LOGMNR
(
STARTSCN => 13277813404
,ENDSCN => 13277813446
,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_CLOB());
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_c from "SCOTT"."T" where "ID" = '1' for update;
LOB_WRITE
buf_c := '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';
dbms_lob.write(loc_c, 1024, 1, buf_c);
END;
LOB_WRITE
buf_c := '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111a';
dbms_lob.write(loc_c, 959, 1025, buf_c);
END;
INSERT insert into "SCOTT"."T"("ID","IMAGE") values ('2',EMPTY_CLOB());
UPDATE update "SCOTT"."T" set "IMAGE" = '222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222b' where "ID" = '2' and ROWID = '
AAAWItAAEAAAA30AAD';
6 rows selected.
--//你可以发现插入1982个字符到image,执行先insert后update.
--//而插入1983个字符到iamge,调用的是存储过程.
--//总之插入都是先插入empty_clob().
5.通过bbed观察:
SCOTT@book> select rowid,id from t ;
ROWID ID
------------------ ------------
AAAWItAAEAAAA30AAA 1
AAAWItAAEAAAA30AAD 2
SCOTT@book> @ &r/rowid AAAWItAAEAAAA30AAD
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90669 4 3572 3 0x1000DF4 4,3572 alter system dump datafile 4 block 3572
BBED> set dba 4,3572
DBA 0x01000df4 (16780788 4,3572)
BBED> x /rdx *kdbr[3]
rowdata[0] @3948
----------
flag@3948: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3949: 0x02
cols@3950: 2
col 0[2] @3951: -63 3
col 1[4000] @3954: 0x00 0x54 0x00 0x01 0x02 0x0c 0x80 0x00 0x00 0x02 0x00 0x00 0x00 0x01 0x00 0x00 0x01 0xb6 0x2c 0x17 0x0f 0x8c 0x09
0x00 0x00 0x00 0x00 0x00 0x0f 0x7c 0x00 0x00 0x00 0x00 0x00 0x01 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
...
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00
0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x32 0x00 0x62
--//你可以发现保存的是1111,而实际上变成0x00 0x32.我个人不推荐选择clob类型,因为受字符集的影响,如果你保存的信息都是英文,这样占用空间加倍.
--//而是选择blob类型.这样可以原样保存.
--//转储相应数据块也能说明问题:
SCOTT@book> alter system dump datafile 4 block 3572;
System altered.
Block header dump: 0x01000df4
Object id on Block? Y
seg/obj: 0x1622d csc: 0x03.176b5a8a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000df0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00f.00000703 0x00c000ed.0283.09 --U- 2 fsc 0x005a.176b5a8c
0x02 0x000a.01e.000053ea 0x00c001d1.102e.16 --U- 2 fsc 0x0000.176b5ac4
bdba: 0x01000df4
data_block_dump,data header at 0x7f1455b3d864
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f1455b3d864
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0xf08
avsp=0xf48
tosp=0xfa6
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1eb1
0x14:pri[1] offs=0x1f0f
0x16:pri[2] offs=0x1ee0
0x18:pri[3] offs=0xf08
block_row_dump:
tab 0, row 0, @0x1eb1
tl: 47 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 01 b6 2c 16 00 14 05 00 00
00 00 00 0f 7e 00 00 00 00 00 02 01 00 0d ff
LOB
Locator:
Length: 84(40)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.01.b6.2c.16
Flags[ 0x02 0x0c 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 20
Flag: 0x05 [ Valid InodeInRow(ESIR) ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 3966
Version: 00000.0000000002
DBA Array[1]:
0x01000dff
tab 0, row 1, @0x1f0f
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1ee0
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 3, @0xf08
tl: 4009 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [4000]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 01 b6 2c 17 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 00 01 00 32 00 32 00 32 00 32 00 32 00 32 00 32
00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00
...
00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00
32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 32 00 62
LOB
Locator:
Length: 84(4000)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.01.b6.2c.17
Flags[ 0x02 0x0c 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 3980
Flag: 0x09 [ Valid DataInRow ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 3964
Version: 00000.0000000001
Inline data[3964]
Dump of memory from 0x00007F1455B3E799 to 0x00007F1455B3F715
7F1455B3E790 00320001 00320032 [..2.2.2.]
7F1455B3E7A0 00320032 00320032 00320032 00320032 [2.2.2.2.2.2.2.2.]
Repeat 246 times
7F1455B3F710 00320032 02022C62 [2.2.b,..]
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 3572 maxblk 3572
--//(4000-36)/2 = 1982(注:如果你保存empty_blob占用36字节),这样如果插入1982英文字符在块内.
6.测试使用c2.txt插入:
SCOTT@book> @ c2.txt
PL/SQL procedure successfully completed.
--//不过检查是乱码,不知道clob如何插入通过脚本.看了一些文档,写这些真不是自己的强项.
--//按照链接修改:https://blog.csdn.net/weixin_36408281/article/details/53318947
$ cat c3.txt
DECLARE
lobloc CLOB;
fileloc BFILE;
v_acount INT;
src_offset INT := 1;
dest_offset INT := 1;
csid INT := 0;
lc INT := 0;
warning INT;
BEGIN
fileloc := BFILENAME ('DATA_PUMP_DIR', '1.txt');
DBMS_LOB.fileopen (fileloc, 0);
v_acount := DBMS_LOB.getlength (fileloc);
insert into t values(2,empty_clob()) return image into lobloc ;
DBMS_LOB.loadclobfromfile
(
lobloc
,fileloc
,v_acount
,dest_offset
,src_offset
,csid
,lc
,warning
);
DBMS_LOB.fileclose (fileloc);
COMMIT;
END;
/
SCOTT@book> @ c3.txt
PL/SQL procedure successfully completed.
/* Formatted on 2018/4/16 10:17:49 (QP5 v5.252.13127.32867) */
DECLARE
b_file BFILE;
b_lob CLOB;
src_offset INT := 1;
dest_offset INT := 1;
csid INT := 0;
lc INT := 0;
warning INT;
BEGIN
INSERT INTO t
VALUES (2, EMPTY_CLOB ())
RETURN image
INTO b_lob;
b_file := BFILENAME ('DATA_PUMP_DIR', '1.txt');
DBMS_LOB.open (b_file, DBMS_LOB.file_readonly);
DBMS_LOB.loadclobfromfile
(
b_lob
,b_file
,DBMS_LOB.getlength (b_file)
,dest_offset
,src_offset
,csid
,lc
,warning
);
DBMS_LOB.close (b_file);
COMMIT;
END;
/
