[20180416]clob的插入.txt
[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;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2152968/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180416]connect by和nocycle.txt
- [20231013]CLOB型別的編碼問題.txt型別
- [20180415]blob的插入.txt
- [20211229]sql語句包含中文儲存clob的編碼問題.txtSQL
- [20190104]bbed手工插入資料.txt
- 【解決方案】Oracle插入/更新CLOB欄位報ORA-01704：字串文字太長Oracle字串
- [20210301]第2行插入Y.txt
- 關於Oracle的BLOB和CLOBOracle
- Java 中 CLOB 和字串之間的轉換Java字串
- 7.82 EMPTY_BLOB, EMPTY_CLOB
- 包括clob segment 大欄位 表的大小統計
- Oracle DBLink中CLOB報錯ORA-22992Oracle
- 如何檢視ORACLE的LOB(BLOB和CLOB)物件佔用的大小Oracle物件
- JDBC 處理CLob和Blob型別資料JDBC型別
- 全面分析插入排序的三種插入方式排序
- Mybatis如何動態生成插入的列及批次插入值MyBatis
- Mysql在資料插入後立即獲取插入的IdMySql
- Git插入新的commitGitMIT
- Oracle優化案例-select中to_clob對效能的影響（二十一）Oracle優化
- mybatis插入資料、批量插入資料MyBatis
- Can GoldenGate Replicate An Oracle Table That Contains Only CLOB Column(s)? (Doc ID 971833.1)GoOracleAI
- interval 分割槽表clob預設表空間指定問題
- 簡單介紹2種Java讀取Oracle大欄位資料（CLOB）的方法JavaOracle
- 【SQL】Oracle建立CLOB型別上傳下載讀取檔案SQLOracle型別
- 三種插入排序 直接插入排序，折半插入排序，希爾排序排序
- 插入排序排序
- mybatis批次插入MyBatis
- Yii 批次插入
- JQuery插入元素jQuery
- 外部插入.after()
- html插入cssHTMLCSS
- MySQL 資料庫表格建立、資料插入及獲取插入的 ID：Python 教程MySql資料庫Python
- 【排序】插入類排序—(折半)插入排序、希爾排序排序
- 提高 PostgreSQL 插入效能的 5 個技巧SQL
- MySQL：JDBC批量插入資料的效率MySqlJDBC
- 搜尋插入位置
- 03 插入排序排序
- VIM 常用插入命令