[20180416]clob的插入.txt

lfreeali 2018-04-16

测试 Image 脚本

[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;
/

登录 后评论
下一篇
云栖号资讯小编
20035人浏览
2020-07-13
相关推荐
[20180416]connect by和nocycle.txt
615人浏览
2018-04-16 15:24:06
[20180415]blob的插入.txt
910人浏览
2018-04-15 16:11:42
0
0
0
893