[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