[20250804]简单探究oracle long数据类型.txt

来源：这里教程网时间：2026-03-03 22:36:33 作者：

[20250804]简单探究oracle long数据类型.txt --//工作中遇到的问题，看看简单探究oracle long数据类型。long类型基本不会出现应用程序中，基本使用lob类型取代，但是oracle内 --//部还是在使用这些数据类型,一直并没有改成其他数据类型。简单讲long类型就是支持更长的varchar2类型。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试环境建立： SCOTT@book01p> create table tt (id number , la long,lb long , vc varchar2(32)); create table tt (id number , la long,lb long , vc varchar2(32)) * ERROR at line 1: ORA-01754: a table may contain only one column of type LONG --//一个表仅仅包含1个long类型。 SCOTT@book01p> create table tt (id number , la long, vc varchar2(32)); Table created. SCOTT@book01p> insert into tt values (1,'1测试2','abcdef'); 1 row created. SCOTT@book01p> insert into tt values (2,'a中文b','ABCDEF'); 1 row created. SCOTT@book01p> commit; Commit complete. SCOTT@book01p> select * from tt where la like '1%'; select * from tt where la like '1%' * ERROR at line 1: ORA-00932: inconsistent datatypes: expected CHAR got LONG --//不能出现在谓词查询。 SCOTT@book01p> create table tx as select * from tt; create table tx as select * from tt * ERROR at line 1: ORA-00997: illegal use of LONG datatype --//ctas表不能包含long类型。 SYS@book01p> @ o2 scott.tt SYS@book01p> @ pr ============================== O_OWNER : SCOTT O_OBJECT_NAME : TT O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 147389 D_OID : 147389 CREATED : 2025-08-02 10:45:42 LAST_DDL_TIME : 2025-08-02 10:45:42 PL/SQL procedure successfully completed. SYS@book01p> select OBJ#,COL#,SEGCOL#,NAME from col$ where obj#=147389; OBJ# COL# SEGCOL# NAME ---------- ---------- ---------- ------------------------------ 147389 1 1 ID 147389 2 3 LA 147389 3 2 VC --//col#表示定义字段的顺序，id -> la -> vc。 --//SEGCOL#表示在数据段的存储顺序 id -> vc -> la。 --//可以看出oracle long类型的特点，1个表最多仅仅有1个字段类型是long类型，并且定义字段顺序与存储字段顺序不同，long类型被 --//放在最后。 3.简单验证看看： SCOTT@book01p> select rowid, id,la c20 ,vc from scott.tt ; ROWID ID C20 VC ------------------ ---------- -------------------- -------------------------------- AAAj+9AASAAAAnlAAA 1 1测试2 abcdef AAAj+9AASAAAAnlAAB 2 a中文b ABCDEF SCOTT@book01p> @ rowid AAAj+9AASAAAAnlAAA DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT -------------- ---------- ---------- ---------- -------------------- -------------------- -------------------------------------------------- 147389 18 2533 0 0x48009E5 18,2533 alter system dump datafile 18 block 2533 ; SCOTT@book01p> alter system checkpoint; System altered. SCOTT@book01p> alter system dump datafile 18 block 2533 ; System altered. --//查看跟踪文件： data_block_dump,data header at 0x8b4b0064 =============== tsiz: 0x1f98 hsiz: 0x18 pbl: 0x8b4b0064 76543210 flag=-------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0xfc0 avsp=0xfa8 tosp=0x1f58 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x1f84 0x14:pri[1] offs=0x1f70 0x16:pri[2] offs=0xfc0 block_row_dump: tab 0, row 0, @0x1f84 tl: 20 fb: --H-FL-- lb: 0x0 cc: 3 col 0: [ 2] c1 02 --//id=1 col 1: [ 6] 61 62 63 64 65 66 --//vc='abcdef' col 2: [ 6] 31 b2 e2 ca d4 32 --//la='1测试2' tab 0, row 1, @0x1f70 tl: 20 fb: --H-FL-- lb: 0x0 cc: 3 col 0: [ 2] c1 03 --//id=2 col 1: [ 6] 41 42 43 44 45 46 --//vc='ABCDEF' col 2: [ 6] 61 d6 d0 ce c4 62 --//la='a中文b' tab 0, row 2, @0xfc0 tl: 2 fb: --HDFL-- lb: 0x1 end_of_block_dump 4.超过4000个字符的情况： --//不想插入4000个字符到tt表中，直接拿view$ 表的text查看。 SYS@book> @ desc view$ Name Null? Type ------------------------------- -------- ---------------------------- 1 OBJ# NOT NULL NUMBER 2 AUDIT$ NOT NULL VARCHAR2(38) 3 COLS NOT NULL NUMBER 4 INTCOLS NOT NULL NUMBER 5 PROPERTY NOT NULL NUMBER 6 FLAGS NOT NULL NUMBER 7 TEXTLENGTH NUMBER 8 TEXT LONG SYS@book> column text noprint SYS@book> select rowid, view$.* from view$ where TEXTLENGTH>=8000 and TEXTLENGTH<=12000 and rownum=1; ROWID OBJ# AUDIT$ COLS INTCOLS PROPERTY FLAGS TEXTLENGTH ------------------ ---------- -------------------------------------- ---------- ---------- --------- ---------- ---------- AAAABfAABAAADG2AAA 4803 -------------------------------------- 47 47 0 0 8629 SYS@book> column text print SYS@book> @ rowid AAAABfAABAAADG2AAA DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT -------------- ---------- ---------- ---------- -------------------- -------------------- -------------------------------------------------- 95 1 12726 0 0x4031B6 1,12726 alter system dump datafile 1 block 12726 ; --//不使用转储，使用bbed观察： BBED> x /rncnnnnnc dba 0x4031B6 *kdbr[0] rowdata[0] @7451 ---------- flag@7451: 0x29 (KDRHFN, KDRHFF, KDRHFH) lock@7452: 0x02 cols@7453: 8 nrid@7454:0x00403294.0 col 0[3] @7460: 4803 col 1[38] @7464: -------------------------------------- col 2[2] @7503: 47 col 3[2] @7506: 47 col 4[1] @7509: 0 col 5[1] @7511: 0 col 6[3] @7513: 8629 col 7[668] @7517: select o.owner#, o.name, o.obj#,. c.name, c.intcol#,. decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), . 2, decode(c.scale, null,. decode(c.precision#, null, 'NUMBER', 'FLOAT'),. 'NUMBER'),. 8, 'LONG',. 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),. 12, 'DATE',. 23, 'RAW', 24, 'LONG RAW',. 58, nvl2(ac.synobj#, (select o.name from obj$ o. where o.obj#=ac.synobj#), ot.name),. --//col7就是text long类型信息，这里仅仅显示长度668.出现行链接以及迁移nrid@7454:0x00403294.0。 --//注：bbed里面的.有一些表示\n,实际上不可见字符都是使用它来替代。 BBED> x /rc dba 0x00403294 *kdbr[0] rowdata[0] @215 ---------- flag@215: 0x06 (KDRHFP, KDRHFL) lock@216: 0x00 cols@217: 1 col 0[7961] @218: 69, 'ROWID',. 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),. 100, 'BINARY_FLOAT',. 101, 'BINARY_DOUBLE',. 105, 'MLSLABEL',. 106, 'MLSLABEL',. 111, nvl2(ac.s ynobj#, (select o.name from obj$ o. where o.obj#=ac.synobj#), ot.name),. 112, decode(c.charsetform, 2 , 'NCLOB', 'CLOB'),. 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',. 119, 'JSON',. 121, nvl2(ac .synobj#, (select o.name from obj$ o. where o.obj#=ac.synobj#), ot.name),. 122, nvl2(ac.synobj#, (sel ect o.name from obj$ o. where o.obj#=ac.synobj#), ot.name),. 123, nvl2(ac.synobj#, (select o.name fro m obj$ o. where o.obj#=ac.synobj#), ot.name),. 178, 'TIME(' ||c.scale|| ')',. 1 79, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',. 180, 'TIMESTAMP(' ||c.scale|| ')',. 181, 'TIMESTAMP(' ||c.sc ale|| ')' || ' WITH TIME ZONE',. 231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',. 182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',. 183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||. c.scale || ')',. 208, 'UROWID',. 'UNDEFINED'),. decode(c.type#, 111, 'REF'),. nvl2(ac.synobj#, (select u.name from "_BASE_USER" u, obj$ o. where o.owner#=u.user# and o.obj#=ac.synobj#),. ut.name),. c.length, c.precision#, c. scale,. decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),. decode(c.col#, 0, to_number(null), c.col#), c.deflength,. c.default$, h.distcnt,. case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1. then h.lowval. else null. end,. case when SYS_OP_DV_CHECK(o.name, o.o wner#) = 1. then h.hival. else null. end,. h.density, h.null_cnt,. case when nvl(h.distcnt,0) = 0 then h.distcnt. -- no histogram. when h.row_cnt = 0 then 1. -- hybrid histogram.. when exists(select 1 from sys.histgrm$ hg. where c.obj# = hg.obj# and c.intcol# = hg.intcol#. and hg.ep_repeat_count > 0 and rownum < 2) then h.row_cnt. -- top-frequency histogram. when bitand(h.spare2, 64) > 0. then h.row_cnt. -- frequency histogram. when (b itand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or. (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1)). then h.row_ cnt. -- height-balanced histogram. else h.bucket_cnt. end,. h.timestamp#, h.sample_size,. decode(c.charsetform, 1, 'CHAR_CS',. 2, 'NCHAR_CS',. 3, NLS_CHARSET_NAME(c.charsetid),. 4, 'ARG: '||c.charsetid),. decode(c.charsetid, 0, to_number(NULL),. nls_charset_decl_len(c.length, c.charsetid)),. decode(bita nd(h.spare2, 2), 2, 'YES', 'NO'),. decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),. decode(bitand(h.spare2, 8), 8, 'INCREMENTAL ', '') ||. decode(bitand(h.spare2, 128), 128, 'HIST_FOR_INCREM_STATS ', '') ||. decode(bitand(h.spare2, 256), 256, 'HISTOGRAM_ONLY ', '') ||. decode( bitand(h.spare2, 512), 512, 'STATS_ON_LOAD ', '') ||. -- The following bit only applies to table level column stats. It does. -- not apply to partition/subpartition level stats so we do not have. -- to change *_part/subpart_col_statistics. decode(bitand(h.spare2, 2048), 2048, ' HYBRID_GLOBAL_NDV', ''),. h.avgcln,. c.spare3,. decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),. 96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),. null),. decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),. decode( o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),. decode(bitand(ac.flags, 2), 2, 'NO',. d ecode(bitand(ac.flags, 4), 4, 'NO',. decode(bitand(ac.flags, 8), 8, 'NO',. 'N/A')))),. decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',. 'NO')),. decode(c. property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',. 'NO')),. decode(c.segcol#, 0, to_number(null), c .segcol#), c.intcol#,. -- warning! If you update stats related info, make sure to also update. -- GTT session private stats in cdoptim.sql. case when nvl(h.row_cnt,0) = 0 then 'NONE'. when exists(select 1 from sys.histgrm$ hg. where c.obj# = hg.obj# and c.in tcol# = hg.intcol#. and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'. when bitand(h.spare2, 64) > 0. then 'TOP-FREQUENCY'. when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or. (h.bucket_cnt >= h.distcnt and h.density*h.b ucket_cnt < 1)). then 'FREQUENCY'. else 'HEIGHT BALANCED'. end,. decode(bitand(c.property, 1024), 1024,. (select decode(bitand(cl.property, 1), 1, rc.name, cl.name). from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1. and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and. cl.intcol# = rc.intcol#(+)),. decode(bitand(c.property, 1), 0, c.name,. (select tc.name from sys.attrcol$ tc. where c.obj# = tc.obj# and c.intcol# = tc.intcol#))),. decode(bitand(c.property , 17179869184), 17179869184, 'YES',. decode(bitand(c.property, 32), 32, 'NO', 'YES')),. decode(bitand(c.property, 68719476736), 68719476 736, 'YES', 'NO'),. decode(bitand(c.property, 137438953472 + 274877906944),. 137438953472, 'YES', 274877906944, 'YES', 'NO'),. . case when c.evaledition# is null then null. else (select name from obj$ where obj# = c.evaledition#) end,. case when c.unusablebefore# is null then null. else (select name from obj$ where obj# = c.unusablebefore#) end,. case when c.unusablebeginning# is null then null. else (select name from obj$ where obj# = c.unusablebeginning#) end,. case when (c.type# in (1,8,9,96,112)). then nls_collation_name(nv l(c.collid, 16382)). else null end,. c.collintcol# .from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h,. sys.coltype$ ac , sys.obj$ ot, sys."_BASE_USER" ut, sys.tab$ t.where o.obj# = c.obj#. and o.obj# = t.obj#(+). and bitand(o.flags, 128) = 0. and o.owner# = userenv('SCHE MAID'). and o.obj# = h.obj#(+) and c.intcol# = h.intcol#(+). and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+). and ac.toid = ot.oid$(+). and ot.ty pe#(+) = 13. and ot.owner# = ut.user#(+). and (o.type# in (3, 4) /* cluster, view */. or. (o.type# = 2 /* tables, excluding iot - overflow and nested tables */. and. not exists (select null. from sys.tab$ t. where t.obj# = o.obj#. and (bitand(t.property, 512) = 512 or. bitand(t.property, 8192) = 8192 or. bitand(t.property, power(2,65)) = power(2,65))))). --//668+7961 = 8629,正好等于text的长度。 --//这样基本清楚了long类型的保存形式。 5.小结： --//1.1个表最多仅仅有1个字段类型是long类型， --//2.定义字段顺序与存储字段顺序不同，long类型被放在最后。 --//3.超长串通过行迁移与链接并且起来。 --//找到一个链接说明long类型,补充如下： https://www.oracle-developer.net/display.php?id=430 --//working with long columns --//摘要如下： The use of LONG values is subject to these restrictions: A table can contain only one LONG column. You cannot create an object type with a LONG attribute. LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints). LONG columns cannot be indexed. LONG data cannot be specified in regular expressions. A stored function cannot return a LONG value. You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL. Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column. In addition, LONG columns cannot appear in these parts of SQL statements: GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements The UNIQUE operator of a SELECT statement The column list of a CREATE CLUSTER statement The CLUSTER clause of a CREATE MATERIALIZED VIEW statement SQL built-in functions, expressions, or conditions SELECT lists of queries containing GROUP BY clauses SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators SELECT lists of CREATE TABLE ... AS SELECT statements ALTER TABLE ... MOVE statements SELECT lists in subqueries in INSERT statements

编辑推荐：

返回列表

[20250804]简单探究oracle long数据类型.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推